SQL: Creating a Storage Report of Databases Residing within Server

Method 1:

WITH fs
AS
(
    select database_id, type, size * 8.0/1048576 size
    from sys.master_files
)
SELECT 
    name,
    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeGB,
    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeGB
ORDER BY DataFileSizeGB Desc    
FROM sys.databases db

Method 2:

SET ANSI_WARNINGS OFF
GO

IF OBJECT_ID('tempdb.dbo.#storageReport') IS NOT NULL
    DROP TABLE #storageReport

CREATE TABLE #storageReport (
      database_name VARCHAR(255) PRIMARY KEY
    , log_size_gb DECIMAL(5,2)
    , db_size_gb DECIMAL(5,2)
    , total_size_gb DECIMAL(5,2)
)

DECLARE @command varchar(max) 
SELECT @command = '
	IF "?" NOT IN ("master","tempdb", "model","msdb","monitoring","ReportServer","ReportServerTempDB") BEGIN
	USE ?
    INSERT INTO #storageReport (database_name, log_size_gb, db_size_gb, total_size_gb)
	SELECT 
      database_name = DB_NAME(database_id)
    , log_size_gb = CAST(((SUM(CASE WHEN type_desc = "LOG" THEN size END)*8.00) / 1048576) AS DECIMAL(5,2))
    , db_size_gb = CAST(((SUM(CASE WHEN type_desc = "ROWS" THEN size END)*8.00) / 1048576) AS DECIMAL(5,2))
    , total_size_gb = CAST(SUM(size)*8.00/1048576 AS DECIMAL(5,2))
	FROM sys.master_files WITH(NOWAIT)
	WHERE database_id = DB_ID()
	GROUP BY database_id
	END
	'
EXEC sp_MSforeachdb @command 
SELECT * FROM #storageReport

Sample Output:

database_name log_size_gb db_size_gb total_size_gb
365_MSCRM 0.31 1.12 1.43
ABC_MSCRM 0.28 2.62 2.90
DEF_MSCRM 2.27 11.59 13.86
GHI_MSCRM 0.28 20.37 20.65
KLM_MSCRM 0.28 2.12 2.40
XYZ_MSCRM 13.87 144.09 157.96
ZYX_MSCRM 0.41 1.59 2.00
MLK_MSCRM 1.28 9.84 11.12
MSCRM_CONFIG 0.02 0.03 0.05
CONCU_MSCRM 0.28 3.12 3.40
CONCHYM_MSCRM 0.66 4.12 4.78
BANHBEO_MSCRM 2.74 29.59 32.34
AIKARAMBA_MSCRM 0.28 0.87 1.15

Troubleshooting:

These error messages are included in this post to help searchers locate certain solutions using warning messages in their consoles

EXEC sp_MSforeachdb
	'USE ? SELECT 
      database_name = DB_NAME(database_id)
    , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
	FROM sys.master_files WITH(NOWAIT)
	WHERE database_id = DB_ID() -- for current db 
	GROUP BY database_id
	'

Msg 103, Level 15, State 4, Line 1
The identifier that starts with 'USE ? SELECT 
      database_name = DB_NAME(database_id)
    , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size EN' is too long. Maximum length is 128.

This happens due to the fact that single quotes are being used to encapsulate the command as well as declaring a string.
    
DECLARE @command varchar(9000) 
SELECT @command = "USE ? SELECT 
      database_name = DB_NAME(database_id)
    , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
	FROM sys.master_files WITH(NOWAIT)
	WHERE database_id = DB_ID() -- for current db 
	GROUP BY database_id
	" 
EXEC sp_MSforeachdb @command     

Msg 131, Level 15, State 3, Line 1
The size (9000) given to the type 'varchar' exceeds the maximum allowed for any data type (8000).
Msg 103, Level 15, State 4, Line 2
The identifier that starts with 'USE ? SELECT 
      database_name = DB_NAME(database_id)
    , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size EN' is too long. Maximum length is 128.
Msg 137, Level 15, State 1, Line 2
Must declare the scalar variable "@command".
Msg 137, Level 15, State 2, Line 11
Must declare the scalar variable "@command".

a. Command declaration as varchar has a max value of 8000 - remember that. Perhaps, one could statically imprint this NVARCHAR(MAX) to declare maximum allowable characters in a command.
b. It's illegal to use variables inside a sql string and use them in exec statements. the variables would be out of scope. In T-SQL the single quotes are treated as variables. Hence, the workaround is to use double quotes to specify string values and single quotes to encapsulate the whole command.

Error:
-----------
Warning: Null value is eliminated by an aggregate or other SET operation.
Warning: Null value is eliminated by an aggregate or other SET operation.
Warning: Null value is eliminated by an aggregate or other SET operation.
Msg 8152, Level 16, State 2, Line 4
String or binary data would be truncated.
The statement has been terminated.

Resolution:
-----------
The SUM or aggregate function may encounter data point values of NULL.
When adding a INT/DOUBLE value with NULL, SQL would throw out warnings.
Hence, this can safely be ignored by adding the 'SET ANSI_WARNINGS OFF' declaration
The other error tells us that a string is being truncated. That's a hint for us to increase the VARCHAR length of a string datatype declaration

Leave a Reply

Your email address will not be published. Required fields are marked *