Select SQL Server database size

If you would like to get size of real data, you'll need to filter 'ONLINE' data files only. Otherwise you can get a size that significately different from the size you see in GUI. Some ghost | old garbage records can be in the sys.master_files, for example 'DEFUNCT' data files.

with fs
as
(
select database_id, type, size * 8.0 / 1024 size, physical_name
from sys.master_files
where state_desc = 'ONLINE' -- ONLINE,RESTORING,RECOVERING,RECOVERY_PENDING,SUSPECT,,OFFLINE, DEFUNCT
)
select
name,
(select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
(select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB,
(select sum(size) from fs where fs.database_id = db.database_id) TotalFileSizeMB
from sys.databases db
--order by database_id
order by TotalFileSizeMB desc

You can check how this query works following [this link](

[To see links please register here]

).


``` lang-sql
IF OBJECT_ID('tempdb..#spacetable') IS NOT NULL
DROP TABLE tempdb..#spacetable
create table #spacetable
(
database_name varchar(50) ,
total_size_data int,
space_util_data int,
space_data_left int,
percent_fill_data float,
total_size_data_log int,
space_util_log int,
space_log_left int,
percent_fill_log char(50),
[total db size] int,
[total size used] int,
[total size left] int
)
insert into #spacetable
EXECUTE master.sys.sp_MSforeachdb 'USE [?];
select x.[DATABASE NAME],x.[total size data],x.[space util],x.[total size data]-x.[space util] [space left data],
x.[percent fill],y.[total size log],y.[space util],
y.[total size log]-y.[space util] [space left log],y.[percent fill],
y.[total size log]+x.[total size data] ''total db size''
,x.[space util]+y.[space util] ''total size used'',
(y.[total size log]+x.[total size data])-(y.[space util]+x.[space util]) ''total size left''
from (select DB_NAME() ''DATABASE NAME'',
sum(size*8/1024) ''total size data'',sum(FILEPROPERTY(name,''SpaceUsed'')*8/1024) ''space util''
,case when sum(size*8/1024)=0 then ''divide by zero'' else
substring(cast((sum(FILEPROPERTY(name,''SpaceUsed''))*1.0*100/sum(size)) as CHAR(50)),1,6) end ''percent fill''
from sys.master_files where database_id=DB_ID(DB_NAME()) and type=0
group by type_desc ) as x ,
(select
sum(size*8/1024) ''total size log'',sum(FILEPROPERTY(name,''SpaceUsed'')*8/1024) ''space util''
,case when sum(size*8/1024)=0 then ''divide by zero'' else
substring(cast((sum(FILEPROPERTY(name,''SpaceUsed''))*1.0*100/sum(size)) as CHAR(50)),1,6) end ''percent fill''
from sys.master_files where database_id=DB_ID(DB_NAME()) and type=1
group by type_desc )y'
select * from #spacetable
order by database_name
drop table #spacetable
```

CREATE procedure SP_DBSIZE
AS
IF (OBJECT_ID('db_size','U')) IS NOT NULL
drop table DB_size
create table db_size(name nvarchar(100), db_size nvarchar(100), owner nvarchar(100), dbid int, created nvarchar(30), status nvarchar(255), compatibility_level nvarchar(10))
insert into db_size
exec sp_helpdb
update db_size set status = left(status,50)
alter table db_size alter column status nvarchar(50)
select * from db_size

Log size for all databases can be found by `DBCC SQLPERF(logspace)` Reference [SQLPERF](

[To see links please register here]

)

Also compare the results with the following query's result

EXEC sp_helpdb @dbname= 'MSDB'

It produces result similar to the following

![enter image description here][1]


There is a good article - [Different ways to determine free space for SQL Server databases and database files](

[To see links please register here]

)


[1]: