How to use grouping sets in this query that shows database sizes - adding the last time it was updated

Question

I have the following query that shows me the database sizes, including log dna data files.

SELECT [Database Name] = DB_NAME(s.database_id),

       [Type] = CASE WHEN s.Type_Desc = 'ROWS' THEN 'Data File(s)'

                     WHEN s.Type_Desc = 'LOG'  THEN 'Log File(s)'

                     ELSE s.Type_Desc END,

     --  LastUpdate= (select COALESCE(max(last_user_update),MAX(last_system_update)) 
        --             from sys.dm_db_index_usage_stats dius 
                    --where dius.database_id = s.database_id),

       [Size in MB] = CAST( ((SUM(s.Size)* 8) / 1024.00) AS DECIMAL(18,2) ),
       [Size in GB] = CAST( ((SUM(s.Size)* 8) / 1024.00/1024.00) AS DECIMAL(18,2) )

FROM   sys.master_files s
-- Uncomment if you need to query for a particular database
-- WHERE      database_id = DB_ID(‘Database Name’)

GROUP BY      GROUPING SETS

              (

                     (DB_NAME(s.database_id), s.Type_Desc),

                     (DB_NAME(s.database_id))

                     --(DB_NAME(s.database_id),s.database_id)

              )

ORDER BY      DB_NAME(s.database_id), s.Type_Desc DESC

GO

and that gives me the following picture in one of my test servers:

I would like to add the last time the database was updated to the results of my query, as you can see on the commented lines in the query above, but that is giving me the following result set:

SELECT [Database Name] = DB_NAME(s.database_id),

       [Type] = CASE WHEN s.Type_Desc = 'ROWS' THEN 'Data File(s)'

                     WHEN s.Type_Desc = 'LOG'  THEN 'Log File(s)'

                     ELSE s.Type_Desc END,

       LastUpdate= (select COALESCE(max(last_user_update),MAX(last_system_update)) 
                     from sys.dm_db_index_usage_stats dius 
                    where dius.database_id = s.database_id),

       [Size in MB] = CAST( ((SUM(s.Size)* 8) / 1024.00) AS DECIMAL(18,2) ),
       [Size in GB] = CAST( ((SUM(s.Size)* 8) / 1024.00/1024.00) AS DECIMAL(18,2) )

FROM   sys.master_files s
-- Uncomment if you need to query for a particular database
-- WHERE      database_id = DB_ID(‘Database Name’)

GROUP BY      GROUPING SETS

              (

                     (DB_NAME(s.database_id), s.Type_Desc),

                     (DB_NAME(s.database_id)),

                     (DB_NAME(s.database_id),s.database_id)

              )

ORDER BY      DB_NAME(s.database_id), s.Type_Desc DESC

GO

How can I change my query to display the last time the database was updated?

Answer 1

I just removed the DB_Name from Grouping

SELECT [Database Name] = DB_NAME(s.database_id),
       [Type] = CASE WHEN s.Type_Desc = 'ROWS' THEN 'Data File(s)'
                     WHEN s.Type_Desc = 'LOG'  THEN 'Log File(s)'
                     ELSE s.Type_Desc END,

       LastUpdate= case when s.type_desc ='ROWS' then NULL
                       when s.type_desc = 'LOG' then NULL
                       else        
                           (select COALESCE(max(last_user_update),MAX(last_system_update)) 
                                         from sys.dm_db_index_usage_stats dius 
                                        where dius.database_id = s.database_id)
                    end,
       [Size in MB] = CAST( ((SUM(s.Size)* 8) / 1024.00) AS DECIMAL(18,2) ),
       [Size in GB] = CAST( ((SUM(s.Size)* 8) / 1024.00/1024.00) AS DECIMAL(18,2) )

FROM   sys.master_files s
GROUP BY      GROUPING SETS
              (
                     (s.database_id, s.Type_Desc),
                     (s.database_id)
              )
ORDER BY      DB_NAME(s.database_id), s.Type_Desc DESC

output:

Database Name   Type         lastUpdate  Size in MB Size in GB  
db_xxx          Log File(s)   Null            8.00     0.01      
db_xxx          Data File(s)  Null            8.00     0.01      
db_xxx          NULL          2019-07-14 17:27:04.473  16.00       0.02