Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- IF OBJECT_ID('tempdb.dbo.#space') IS NOT NULL
- DROP TABLE #space
- CREATE TABLE #space (
- database_id INT PRIMARY KEY
- , data_used_size DECIMAL(18,2)
- )
- DECLARE @SQL NVARCHAR(MAX)
- SELECT @SQL = STUFF((
- SELECT '
- USE [' + d.name + ']
- INSERT INTO #space (database_id, data_used_size)
- SELECT
- DB_ID()
- , SUM(CASE WHEN [type] = 0 THEN space_used END)
- FROM (
- SELECT s.[type], space_used = SUM(FILEPROPERTY(s.name, ''SpaceUsed'') * 8. / 1024)
- FROM sys.database_files s
- GROUP BY s.[type]
- ) t;'
- FROM sys.databases d
- WHERE d.[state] = 0
- FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
- EXEC sys.sp_executesql @SQL
- SELECT
- d.database_id
- , d.name
- , d.state_desc
- , d.recovery_model_desc
- , t.data_size
- , s.data_used_size
- , bu.full_last_date
- , bu.full_size
- FROM (
- SELECT
- database_id
- , data_size = CAST(SUM(CASE WHEN [type] = 0 THEN size END) * 8. / 1024 AS DECIMAL(18,2))
- FROM sys.master_files
- GROUP BY database_id
- ) t
- JOIN sys.databases d ON d.database_id = t.database_id
- LEFT JOIN #space s ON d.database_id = s.database_id
- LEFT JOIN (
- SELECT
- database_name
- , full_last_date = MAX(CASE WHEN [type] = 'D' THEN backup_finish_date END)
- , full_size = MAX(CASE WHEN [type] = 'D' THEN backup_size END)
- FROM (
- SELECT
- s.database_name
- , s.[type]
- , s.backup_finish_date
- , backup_size =
- CAST(CASE WHEN s.backup_size = s.compressed_backup_size
- THEN s.backup_size
- ELSE s.compressed_backup_size
- END / 1048576.0 AS DECIMAL(18,2))
- , RowNum = ROW_NUMBER() OVER (PARTITION BY s.database_name, s.[type] ORDER BY s.backup_finish_date DESC)
- FROM msdb.dbo.backupset s
- WHERE s.[type] IN ('D', 'L')
- ) f
- WHERE f.RowNum = 1
- GROUP BY f.database_name
- ) bu ON d.name = bu.database_name
- ORDER BY t.data_size DESC
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement