Get Database File Sizes

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: Unknown

Date: Sep 2009

Description

Returns the Database Size and file size on disk for all databases on a server.

At first glance, when preparing this code for publishing, I thought it could be better written using the sys.master_files table directly without the need for a temporary stored proc/dynamic SQL. However, it appears that the FILEPROPERTY TSQL function only operates in the context of the curent database and therefore requires a USE statement and a loop. This is not immediately obvious from BOL.

Ideally, I'd do without the cursor for performance reasons, but in this case, it's not a high use piece of code, so I've left it as originally intended.

Code

CREATE PROCEDURE #uspGetFileStats

@dbName sysname

AS

DECLARE @sqlCmd NVARCHAR(max)

SELECT @sqlCmd=

'Use '

+ @dbName+'; '

+ 'SELECT '''

+ @dbName

+ ''' [DBName]'

+ ',sum(ROUND((cast ([size] as numeric)*8/1024),2)) [FileSizeMb] '

+ ',sum(CASE '

+ 'WHEN FILEPROPERTY([name], ''SpaceUsed'') IS NULL THEN 0 '

+ 'ELSE '

+ 'ROUND(CAST (FILEPROPERTY([name], ''SpaceUsed'')as numeric)*8/1024,2) '

+ 'END) [UsedMb] '

+ ',CASE WHEN type = 0 THEN ''DATABASE'' WHEN type =1 THEN ''LOG'' WHEN type=4 THEN ''FULLTEXT'' END [FileType] '

+ 'FROM [sys].[database_files] '

+ 'GROUP BY type'

EXECUTE (@sqlCmd)

GO

DECLARE databaseNamesArr CURSOR FAST_FORWARD READ_ONLY

FOR

SELECT [name]

FROM sys.databases

ORDER BY [name] ASC

DECLARE @dbName NVARCHAR(128)

DECLARE @tFileStats TABLE ([DBName] SYSNAME,[FileSizeMb] NUMERIC(18,2),[UsedSpaceMb]NUMERIC(18,2),[FileType] NVARCHAR (60))

OPEN databaseNamesArr

FETCH NEXT

FROM databaseNamesArr

INTO @dbName

WHILE @@FETCH_STATUS=0

BEGIN

INSERT @tFileStats

(

[DBName]

, [FileSizeMb]

, [UsedSpaceMb]

, [FileType]

)

EXECUTE #uspGetFileStats @dbName

FETCH NEXT

FROM databaseNamesArr

INTO @dbName

END

CLOSE databaseNamesArr

DEALLOCATE databaseNamesArr

SELECT [DBName]

,[UsedSpaceMb]

,[FileSizeMb]

,[FileType]

FROM @tFileStats

ORDER BY [DBName], [FileType]

GO

DROP PROCEDURE #uspGetFileStats