How to know database size by using Query ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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"

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!

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

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-26 : 10:03:45

sp_databases

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

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

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

vaddesuresh
Starting Member

5 Posts

Posted - 2010-11-15 : 01:40:27

sp_databases

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

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

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/

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources