Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 How to know database size by using Query ?

Author  Topic 

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-05-26 : 08:24:39
Hi,

How to know database size by using Query ?

-- Regards
Prashant

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-26 : 08:32:13
select * from sys.dm_db_file_space_usage

select *
from sysfiles


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-05-26 : 09:00:15
use yourdb
exec sp_spaceused

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-26 : 09:40:47
Also refer http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53843

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-26 : 10:03:45
sp_databases
Go to Top of Page

mdubey
Posting Yak Master

133 Posts

Posted - 2008-05-26 : 19:52:59
You can use SP_HELPDB.

It will give you execute this stored proc, you will get some sort of result sets.

Ex.
DB_name
db_size
dbid
created
Status
compatibility_level


Manoj
MCP, MCTS
Go to Top of Page

ksampatk
Starting Member

3 Posts

Posted - 2010-04-12 : 11:00:00
Try below query to fetch SQL Database Size

select db_name(dbid),str(convert(dec(15),sum(size))* 8192/ 1048576,10,2)+ N' MB' as Size from sys.sysaltfiles
group by dbid order by 2 desc
GO

Sampath Karnatakam - DBA Architect
Go to Top of Page

andyansryan
Starting Member

2 Posts

Posted - 2010-11-11 : 11:25:08
CREATE TABLE #DatabaseInfo (name varchar(100),
db_size varchar(255),
owner varchar(20),
dbid smallint,
created smalldatetime,
status varchar(250),
compatibility_level varchar(250))

INSERT INTO #DatabaseInfo
exec SP_HELPDB

SELECT *
FROM #DatabaseInfo

drop table #DatabaseInfo
Go to Top of Page

vaddesuresh
Starting Member

5 Posts

Posted - 2010-11-15 : 01:40:27
sp_databases
Go to Top of Page

humbertomoreno
Starting Member

1 Post

Posted - 2012-10-12 : 15:12:31
This is probably better. I am SUMming all the sizes of files per-database and grouping them. I think it's more accurate.

select sd.name, SUM(sf.size)
from sys.sysdatabases sd
inner join sys.sysaltfiles sf
on sd.dbid = sf.dbid
WHERE sd.name NOT IN ('master','model','msdb','tempdb')
group by sd.name
order by 2
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-10-13 : 07:15:52
You can use the sys.master_files - http://www.sqlserver-dba.com/2012/09/sql-server-find-the-database-file-size-and-log-file-size.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-10-15 : 05:56:29
I like to use following query to get all databases size on an instance:

SELECT d.name,
ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4 -- Skip system databases
GROUP BY d.name
ORDER BY d.name

http://connectsql.blogspot.com/2012/01/sql-server-get-all-databases-size.html

--------------------------
http://connectsql.blogspot.com/
Go to Top of Page

suneeloduru1
Starting Member

3 Posts

Posted - 2012-10-16 : 11:07:12
I am going to post a small script to get current database size of all databases


SET NOCOUNT ON
DECLARE @counter SMALLINT
DECLARE @counter1 SMALLINT
DECLARE @dbname VARCHAR(100)
DECLARE @size INT
DECLARE @size1 DECIMAL(15,2)
SET @size1=0.0

SELECT @counter=MAX(dbid) FROM master..sysdatabases
IF EXISTS(SELECT name FROM sysobjects WHERE name='sizeinfo')
DROP TABLE sizeinfo
CREATE TABLE sizeinfo(fileid SMALLINT, filesize DECIMAL(15,2), filename VARCHAR(1000))
WHILE @counter > 0
BEGIN
SELECT @dbname=name FROM master..sysdatabases WHERE dbid=@counter
TRUNCATE TABLE sizeinfo
EXEC ('INSERT INTO sizeinfo SELECT fileid,size,filename FROM '+ @dbname +'..SYSFILES')
SELECT @counter1=MAX(fileid) FROM sizeinfo
WHILE @counter1>0
BEGIN
SELECT @size=filesize FROM sizeinfo WHERE fileid=@counter1
SET @size1=@size1+@size
SET @counter1=@counter1-1
END
SET @counter=@counter-1
SELECT @dbname AS DBNAME,CAST(((@size1)*0.0078125) AS DECIMAL(15,2)) AS [DBSIZE(MB)]
SET @size1=0.0
END
SET NOCOUNT OFF
Go to Top of Page

suneeloduru1
Starting Member

3 Posts

Posted - 2012-10-18 : 05:41:35
-- You can use below code snippet to find the size of all databases

declare @db_size table (db_name varchar(100), size float )

insert into @db_size
Exec sp_msforeachdb "select '?' db_name, sum(size/128.0) [db_size(inMB)] from sysfiles"

select * from @db_size


--- Suneel
Go to Top of Page
   

- Advertisement -