Tuesday, May 28, 2013

A quick script to display the total size each database, taking into account multiple MDF and LDF files.

-------------------------------------------------------------------------------
-- Returns the total size of all databases' mdf and ldf files, and the grand   
-- total of the two.                                                           
-------------------------------------------------------------------------------

-- Create the temp table. 
IF OBJECT_ID('tempdb..##t') IS NOT NULL
    DROP TABLE ##t

CREATE TABLE ##t 
(
    [Database]   sysname, 
    [File Type]  NVARCHAR(50),
    [Size (MB)]  BIGINT
)

-- Populate the temp table with value for all the mdf and ldf files for all databases.  
DECLARE @CrLf CHAR(2)
SET @CrLf = CHAR(13) + CHAR(10)

DECLARE @Sql NVARCHAR(MAX)

SET @Sql = 'USE ? '                                                           + @CrLf
         + ''                                                                 + @CrLf
         + 'INSERT ##t '                                                      + @CrLf
         + '    SELECT ''?'' '                                                + @CrLf
         + '         , type_desc '                                            + @CrLf
         + '         , CAST(Size AS BIGINT) * (8 * 1024) / 1024 / 1024 '      + @CrLf
         + '      FROM sys.database_files '

EXEC sp_msforeachdb @Sql

-- Calculate the grand total (mdf files' sizes + ldf files' sizes; add to temp table. 
SET @Sql = 'USE ? '                                                           + @CrLf
         + ''                                                                 + @CrLf
         + 'INSERT ##t '                                                      + @CrLf
         + '    SELECT ''?'' '                                                + @CrLf
         + '         , '' - TOTAL - '' '                                      + @CrLf
         + '         , CAST(SUM(Size) AS BIGINT) * (8 * 1024) / 1024 / 1024 ' + @CrLf
         + '      FROM sys.database_files '
 
EXEC sp_msforeachdb @Sql

-- Display the totals for each database and file type. 
  SELECT [Database], [File Type], SUM([Size (MB)])   AS 'Size (MB)'
    FROM ##t
GROUP BY [Database], [File Type]
ORDER BY [File Type] DESC, [Size (MB)] DESC

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

0 comments:

Post a Comment