SHARE
TWEET

Untitled

a guest Nov 21st, 2019 94 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. IF OBJECT_ID('tempdb.dbo.#space') IS NOT NULL
  2.     DROP TABLE #space
  3.  
  4. CREATE TABLE #space (
  5.       database_id INT PRIMARY KEY
  6.     , data_used_size DECIMAL(18,2)
  7. )
  8.  
  9. DECLARE @SQL NVARCHAR(MAX)
  10.  
  11. SELECT @SQL = STUFF((
  12.     SELECT '
  13.     USE [' + d.name + ']
  14.     INSERT INTO #space (database_id, data_used_size)
  15.     SELECT
  16.           DB_ID()
  17.         , SUM(CASE WHEN [type] = 0 THEN space_used END)
  18.     FROM (
  19.         SELECT s.[type], space_used = SUM(FILEPROPERTY(s.name, ''SpaceUsed'') * 8. / 1024)
  20.         FROM sys.database_files s
  21.         GROUP BY s.[type]
  22.     ) t;'
  23.     FROM sys.databases d
  24.     WHERE d.[state] = 0
  25.     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
  26.  
  27. EXEC sys.sp_executesql @SQL
  28.  
  29. SELECT
  30.       d.database_id
  31.     , d.name
  32.     , d.state_desc
  33.     , d.recovery_model_desc
  34.     , t.data_size
  35.     , s.data_used_size
  36.     , bu.full_last_date
  37.     , bu.full_size
  38. FROM (
  39.     SELECT
  40.           database_id
  41.         , data_size = CAST(SUM(CASE WHEN [type] = 0 THEN size END) * 8. / 1024 AS DECIMAL(18,2))
  42.     FROM sys.master_files
  43.     GROUP BY database_id
  44. ) t
  45. JOIN sys.databases d ON d.database_id = t.database_id
  46. LEFT JOIN #space s ON d.database_id = s.database_id
  47. LEFT JOIN (
  48.     SELECT
  49.           database_name
  50.         , full_last_date = MAX(CASE WHEN [type] = 'D' THEN backup_finish_date END)
  51.         , full_size = MAX(CASE WHEN [type] = 'D' THEN backup_size END)
  52.     FROM (
  53.         SELECT
  54.               s.database_name
  55.             , s.[type]
  56.             , s.backup_finish_date
  57.             , backup_size =
  58.                         CAST(CASE WHEN s.backup_size = s.compressed_backup_size
  59.                                     THEN s.backup_size
  60.                                     ELSE s.compressed_backup_size
  61.                         END / 1048576.0 AS DECIMAL(18,2))
  62.             , RowNum = ROW_NUMBER() OVER (PARTITION BY s.database_name, s.[type] ORDER BY s.backup_finish_date DESC)
  63.         FROM msdb.dbo.backupset s
  64.         WHERE s.[type] IN ('D', 'L')
  65.     ) f
  66.     WHERE f.RowNum = 1
  67.     GROUP BY f.database_name
  68. ) bu ON d.name = bu.database_name
  69. ORDER BY t.data_size DESC
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top