SQL Server: how can I get the correct DB size from sys.master_files?

Question

I'm working on a query that gathers some information related to DB restores, and I'm having trouble getting the correct DB size. The following query provides me with the DB name, last restore date, DB size, and user name of the last person who restored it.

WITH lastrestores AS
(
    SELECT 
        DatabaseName = [d].[name], 
        [r].[restore_date], 
        [f].[size], 
        [r].[user_name], 
        RowNum = ROW_NUMBER() OVER (PARTITION BY d.NAME ORDER BY r.[restore_date] DESC) 
    FROM   
        master.sys.databases d 
    LEFT OUTER JOIN 
        msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.NAME 
    LEFT JOIN 
        master.sys.master_files f ON d.database_id = f.database_id
) 
SELECT * 
FROM [lastrestores] 
WHERE [rownum] = 1 
  AND databasename LIKE 'stuff%' 
ORDER BY restore_date DESC

However, this doesn't show the correct DB size. When I check the sizes in the .mdf file and database properties, it shows a smaller size than this query returns. When I check the sp_databases stored procedure, it shows this being done for the DB size:

DATABASE_SIZE = CONVERT(INT,
                        CASE   -- more than 2TB(maxint) worth of pages (by 8K each) can not fit an int...
                           WHEN SUM(CONVERT(BIGINT, s_mf.size)) >= 268435456
                              THEN NULL
                              ELSE SUM(CONVERT(BIGINT, s_mf.size)) * 8 -- Convert from 8192 byte pages to Kb
                        END)

I tried incorporating this section into my original query, but I ran into the "not contained in aggregate function or group by" error:

WITH lastrestores AS
(
    SELECT 
        DatabaseName = [d].[name], 
        [r].[restore_date], 
        CONVERT(INT,
                CASE
                   WHEN SUM(CONVERT(BIGINT, [f].[size])) >= 268435456
                      THEN NULL
                      ELSE SUM(CONVERT(BIGINT, [f].[size])) * 8
                END) AS DBSize, 
                [r].[user_name], 
        RowNum = ROW_NUMBER() OVER (PARTITION BY d.NAME ORDER BY r.[restore_date] DESC) 
    FROM   
        master.sys.databases d 
    LEFT OUTER JOIN 
        msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.NAME 
    LEFT JOIN 
        master.sys.master_files f ON d.database_id = f.database_id
) 
SELECT * 
FROM [lastrestores] 
WHERE [rownum] = 1 
  AND databasename LIKE 'stuff%' 
ORDER BY restore_date DESC

While I do understand the basics of this error, I'm not really sure how to adjust this so I can get what I need since this query is getting more complex than I'm used to. My ideal outcome would be the original query I posted up top, but with the correct DB sizes as shown in sp_databases. How can I achieve this?

score 3 · Accepted Answer · answered Apr 03 '19 at 21:33

If you're just looking to convert the size column to match the values in the properties, you just need to convert from pages to MB - so multiply by 8 to get KB and then divide by 1024 to get MB.

WITH lastrestores AS
(
    SELECT 
        DatabaseName = [d].[name], 
        [r].[restore_date], 
        [size] = CAST([f].[size] * 8 / 1024.0 AS DECIMAL(10,2)) , 
        [r].[user_name], 
        RowNum = ROW_NUMBER() OVER (PARTITION BY d.NAME ORDER BY r.[restore_date] DESC) 
    FROM   
        master.sys.databases d 
    LEFT OUTER JOIN 
        msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.NAME 
    LEFT JOIN 
        master.sys.master_files f ON d.database_id = f.database_id
) 
SELECT * 
FROM [lastrestores] 
WHERE [rownum] = 1 
ORDER BY restore_date DESC

This Size value here could be slightly more accurate than what you see in properties because SSMS rounds up.

My concern would be that if you have more than one data file, this won't accurately display the non-log size of your database.

I'd probably do something like this to include all the data files.

SELECT 
    DatabaseName = DB_NAME(f.database_id)
    ,r.restore_date
    ,CAST(SUM(f.size * 8 / 1024.0) AS DECIMAL(10,2))
    ,r.user_name
FROM sys.master_files f
OUTER APPLY 
    (SELECT TOP 1 * FROM msdb.dbo.[restorehistory] r WHERE r.[destination_database_name] = DB_NAME(f.database_id) ORDER BY restore_date desc) r 
WHERE f.type = 0
GROUP BY f.database_id, r.restore_date, r.user_name
ORDER BY r.restore_date desc

Thank you for your help, this does exactly what I need and makes sense. — Jeremiah Williams, Apr 04 '19 at 13:56

SQL Server: how can I get the correct DB size from sys.master_files?

1 Answers1