Cool Tips‎ > ‎Backup and Restore‎ > ‎

Backup - 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



ċ
Get all DBs size on disk.sql
(2k)
Andy Hughes,
Jun 7, 2012, 10:54 AM
Comments