如何在此查詢中使用顯示數據庫大小的分組集 - 添加上次更新時間

我有以下查詢顯示數據庫大小，包括logdnadata文件。

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

這在我的一台測試伺服器中為我提供了以下圖片：

我想將 添加last time the database was updated到我的查詢結果中，正如您在 上看到的那樣commented lines in the query above，但這給了我以下結果集：

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

如何更改我的查詢以顯示last time the database was updated？

我剛剛刪除DB_Name了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

輸出：

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      

引用自：https://dba.stackexchange.com/questions/242921