Actual Database Size

The below script would provide the Actual DB file usage size rather than DB size (Used + Free) should work for SQL 2008 and upwards

Borrowed the “dbo.sp_foreachdb” script from Aaron Bertrand (making-a-more-reliable-and-flexible-spmsforeachdb/) to list out all databases in a SQL server.

Step1: Create the below stored procedure.

USE [master];
GO
CREATE PROCEDURE dbo.sp_foreachdb
@command             NVARCHAR(MAX),
@replace_character   NCHAR(1)       = N’?’,
@print_dbname        BIT            = 0,
@print_command_only  BIT            = 0,
@suppress_quotename  BIT            = 0,
@system_only         BIT            = NULL,
@user_only           BIT            = NULL,
@name_pattern        NVARCHAR(300)  = N’%’,
@database_list       NVARCHAR(MAX)  = NULL,
@recovery_model_desc NVARCHAR(120)  = NULL,
@compatibility_level TINYINT        = NULL,
@state_desc          NVARCHAR(120)  = N’ONLINE’,
@is_read_only        BIT            = 0,
@is_auto_close_on    BIT            = NULL,
@is_auto_shrink_on   BIT            = NULL,
@is_broker_enabled   BIT            = NULL
AS
BEGIN
SET NOCOUNT ON;

DECLARE
@sql    NVARCHAR(MAX),
@dblist NVARCHAR(MAX),
@db     NVARCHAR(300),
@i      INT;

IF @database_list > N”
BEGIN
;WITH n(n) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY s1.name) – 1
FROM sys.objects AS s1
CROSS JOIN sys.objects AS s2
)
SELECT @dblist = REPLACE(REPLACE(REPLACE(x,”,’,’),
”,”),”,”)
FROM
(
SELECT DISTINCT x = ‘N”’ + LTRIM(RTRIM(SUBSTRING(
@database_list, n,
CHARINDEX(‘,’, @database_list + ‘,’, n) – n))) + ””
FROM n WHERE n <= LEN(@database_list)
AND SUBSTRING(‘,’ + @database_list, n, 1) = ‘,’
FOR XML PATH(”)
) AS y(x);
END

CREATE TABLE #x(db NVARCHAR(300));

SET @sql = N’SELECT name FROM sys.databases WHERE 1=1′
+ CASE WHEN @system_only = 1 THEN
‘ AND database_id IN (1,2,3,4)’
ELSE ” END
+ CASE WHEN @user_only = 1 THEN
‘ AND database_id NOT IN (1,2,3,4)’
ELSE ” END
+ CASE WHEN @name_pattern <> N’%’ THEN
‘ AND name LIKE N”%’ + REPLACE(@name_pattern, ””, ”””) + ‘%”’
ELSE ” END
+ CASE WHEN @dblist IS NOT NULL THEN
‘ AND name IN (‘ + @dblist + ‘)’
ELSE ” END
+ CASE WHEN @recovery_model_desc IS NOT NULL THEN
‘ AND recovery_model_desc = N”’ + @recovery_model_desc + ””
ELSE ” END
+ CASE WHEN @compatibility_level IS NOT NULL THEN
‘ AND compatibility_level = ‘ + RTRIM(@compatibility_level)
ELSE ” END
+ CASE WHEN @state_desc IS NOT NULL THEN
‘ AND state_desc = N”’ + @state_desc + ””
ELSE ” END
+ CASE WHEN @is_read_only IS NOT NULL THEN
‘ AND is_read_only = ‘ + RTRIM(@is_read_only)
ELSE ” END
+ CASE WHEN @is_auto_close_on IS NOT NULL THEN
‘ AND is_auto_close_on = ‘ + RTRIM(@is_auto_close_on)
ELSE ” END
+ CASE WHEN @is_auto_shrink_on IS NOT NULL THEN
‘ AND is_auto_shrink_on = ‘ + RTRIM(@is_auto_shrink_on)
ELSE ” END
+ CASE WHEN @is_broker_enabled IS NOT NULL THEN
‘ AND is_broker_enabled = ‘ + RTRIM(@is_broker_enabled)
ELSE ” END;

INSERT #x EXEC sp_executesql @sql;

DECLARE c CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR SELECT CASE WHEN @suppress_quotename = 1 THEN
db
ELSE
QUOTENAME(db)
END
FROM #x ORDER BY db;

OPEN c;

FETCH NEXT FROM c INTO @db;

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = REPLACE(@command, @replace_character, @db);

IF @print_command_only = 1
BEGIN
PRINT ‘/* For ‘ + @db + ‘: */’
+ CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
+ @sql
+ CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10);
END
ELSE
BEGIN
IF @print_dbname = 1
BEGIN
PRINT ‘/* ‘ + @db + ‘ */’;
END

EXEC sp_executesql @sql;
END

FETCH NEXT FROM c INTO @db;
END

CLOSE c;
DEALLOCATE c;
END

Step2: Run the below on the Server to generate the report, it can be modified as need by adding more columns or removing them.

Create Table ##temp
(
DatabaseName sysname,
DB_File_Name sysname,
[Size-Mb] decimal (18,2),
[Used-Mb] decimal (18,2),
[Maximum_Size-Mb] decimal (18,2),
[Growth] varchar(20),
[GroupName] sysname,
[%Free] int
)
Exec sp_foreachdb’
Use ?;
Insert Into ##temp (DatabaseName, DB_File_Name, [Size-Mb], [Used-Mb],[Maximum_Size-Mb],[Growth],[GroupName],[%Free])
Select DB_NAME() AS [DatabaseName], Name,
cast(size/128.0 as numeric(10,1)) as [size-mb],
cast(FILEPROPERTY(name, ”spaceused”)/128.0 as numeric(10,1)) as [used-mb],
maxsize [maximum size-mb],
cast(growth*8/1024 as varchar(11))+
case when status&0x100000>0 then ”%” else ”” end [growth],
isnull(filegroup_name(groupid),”log”) [groupname],
cast(100*(size/128-(FILEPROPERTY(name,”spaceused”)/128))/(size/128.0) as int) [% free]
from sysfiles

Select * From ##temp
drop table ##temp

Advertisements

About SQLKit

For almost 10 years supporting SQL versions starting 6.5 to current as an production SQL DBA and as Application admin. Interests or learning include Hadoop , MySQL, PostGRESql and SAP BASIS administration.
This entry was posted in Administration, MSSQL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s