Top 1% !
Popularity: 2546th place

Check the DB Size of a MS SQL Server instance

This stored procedure retrieves some interesting figures regarding the size of the current database both in terms of file sizes and available space.
It also stores the results into a table named DBSize in order to keep a story of such figures (e.g. It can be invoked in a maintenace plan...)
Copy Embed Code
<iframe id="embedFrame" style="width:600px; height:300px;"
Click on the embed code to copy it into your clipboard Width Height
Leave empty to retrieve all the content Start End
CREATE PROCEDURE [dbo].[DB_CheckDBSize] AS BEGIN SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; declare @dbname varchar(50) = db_name(); declare @dbsize table (Dbname sysname,dbstatus varchar(50),Recovery_Model varchar(40) default ('NA'), file_Size_MB decimal(30,2)default (0), Space_Used_MB decimal(30,2)default (0),Free_Space_MB decimal(30,2) default (0)) insert into @dbsize(Dbname,dbstatus,Recovery_Model,file_Size_MB,Space_Used_MB,Free_Space_MB) select DB_NAME() AS DbName, CONVERT(varchar(20),DatabasePropertyEx(@dbname,'Status')) , CONVERT(varchar(20),DatabasePropertyEx(@dbname,'Recovery')), sum(size)/128.0 AS File_Size_MB, sum(CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT))/128.0 as Space_Used_MB, SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,'SpaceUsed') AS INT))/128.0 AS Free_Space_MB from sys.database_files where type=0 group by type -------------------log size-------------------------------------- declare @logsize table (Dbname sysname, Log_File_Size_MB decimal(38,2)default (0),log_Space_Used_MB decimal(30,2)default (0),log_Free_Space_MB decimal(30,2)default (0)) insert into @logsize(Dbname,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB) select DB_NAME() AS DbName, sum(size)/128.0 AS Log_File_Size_MB, sum(CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT))/128.0 as log_Space_Used_MB, SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,'SpaceUsed') AS INT))/128.0 AS log_Free_Space_MB from sys.database_files where type=1 group by type --------------------------------database free size declare @dbfreesize table (name sysname, database_size varchar(50), Freespace varchar(50)default (0.00)) insert into @dbfreesize(name,database_size,Freespace) SELECT database_name = db_name() ,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + 'MB') ,'unallocated space' = ltrim(str(( CASE WHEN dbsize >= reservedpages THEN (convert(DECIMAL(15, 2), dbsize) - convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576 ELSE 0 END ), 15, 2) + ' MB') FROM ( SELECT dbsize = sum(convert(BIGINT, CASE WHEN type = 0 THEN size ELSE 0 END)) ,logsize = sum(convert(BIGINT, CASE WHEN type <> 0 THEN size ELSE 0 END)) FROM sys.database_files ) AS files ,( SELECT reservedpages = sum(a.total_pages) ,usedpages = sum(a.used_pages) ,pages = sum(CASE WHEN it.internal_type IN ( 202 ,204 ,211 ,212 ,213 ,214 ,215 ,216 ) THEN 0 WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) FROM sys.partitions p INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id LEFT JOIN sys.internal_tables it ON p.object_id = it.object_id ) AS partitions ----------------------------------- declare @alldbstate table (dbname sysname, DBstatus varchar(55), R_model Varchar(30)) insert into @alldbstate (dbname,DBstatus,R_model) select name,CONVERT(varchar(20),DATABASEPROPERTYEX(name,'status')),recovery_model_desc from sys.databases insert into @dbsize(Dbname,dbstatus,Recovery_Model) select dbname,dbstatus,R_model from @alldbstate where DBstatus <> 'online' insert into @logsize(Dbname) select dbname from @alldbstate where DBstatus <> 'online' insert into @dbfreesize(name) select dbname from @alldbstate where DBstatus <> 'online' select CONCAT(' ' + d.Dbname, ' (', d.dbstatus, ' - ', d.Recovery_Model, '): DBTotSize = ', (file_size_mb + log_file_size_mb), ' MB.') from @dbsize d join @logsize l on d.Dbname=l.Dbname union select CONCAT('Data: Used ', d.Space_Used_MB, ' MB + Free ', d.Free_Space_MB, ' MB = ', d.file_Size_MB, ' MB.') from @dbsize d union select CONCAT('Log: Used ', log_Space_Used_MB, ' MB + Free ', l.log_Free_Space_MB, ' MB = ', l.Log_File_Size_MB, ' MB.') from @logsize l union select 'TotalFreeSpace = ' + fs.Freespace from @dbfreesize fs INSERT INTO dbo.DBSize (Timestamp, Dbname, DBStatus, RecoveryModel, TotalFileSizeMB, DataSpaceUsedMB, DataFreeSpaceMB, DataFileSizeMB, LogSpaceUsedMB, LogFreeSpaceMB, LogFileSizeMB, TotalFreeSpaceMB) SELECT GETDATE(), d.Dbname, d.dbstatus, d.Recovery_Model, file_size_mb + log_file_size_mb, d.Space_Used_MB, d.Free_Space_MB, d.file_Size_MB, log_Space_Used_MB, l.log_Free_Space_MB, l.Log_File_Size_MB, fs.Freespace FROM @dbsize d join @logsize l on d.Dbname=l.Dbname join @dbfreesize fs on d.Dbname = END
If you want to be updated about similar snippets, Sign in and follow our Channels

blog comments powered by Disqus