--how much data we pad when we shrink, and the min disk size to start a shrink DECLARE @limit INT=512; IF OBJECT_ID('tempdb..#dbs')IS NOT NULL BEGIN; DROP TABLE #dbs; END; CREATE TABLE #dbs ( [database_name] NVARCHAR(128), [log_size_mb] DECIMAL(18,2), [row_size_mb] DECIMAL(18,2), [total_size_mb] DECIMAL(18,2) ) INSERT INTO [#dbs] ([database_name], [log_size_mb], [row_size_mb], [total_size_mb]) SELECT database_name = DB_NAME([F].[database_id]) , log_size_mb = CAST(SUM(CASE WHEN [F].[type_desc] = 'LOG' THEN [F].[size] END) * 8. / 1024 AS DECIMAL(18,2)) , row_size_mb = CAST(SUM(CASE WHEN [F].[type_desc] = 'ROWS' THEN [F].[size] END) * 8. / 1024 AS DECIMAL(18,2)) , total_size_mb = CAST(SUM([F].[size]) * 8. / 1024 AS DECIMAL(18,2)) FROM sys.master_files F WITH(NOWAIT) GROUP BY [F].[database_id] ORDER BY [total_size_mb] DESC IF OBJECT_ID('tempdb..#FileSize')IS NOT NULL BEGIN; DROP TABLE #FileSize; END; CREATE TABLE #FileSize( dbName NVARCHAR(128) NOT NULL, FileName NVARCHAR(128) NOT NULL, type_desc NVARCHAR(128) NOT NULL, CurrentSizeMB DECIMAL(10,2) NOT NULL, FreeSpaceMB DECIMAL(10,2) NULL ) INSERT INTO #FileSize(dbName, FileName, type_desc, CurrentSizeMB, FreeSpaceMB) exec [sys].[sp_MSforeachdb] 'use [?]; SELECT DB_NAME() AS DbName, name AS FileName, type_desc, size/128.0 AS CurrentSizeMB, size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB FROM sys.database_files WHERE type IN (0,1);'; /* SELECT * FROM #FileSize */ --details SELECT d.[name] AS dbName ,[F].[physical_name] AS physicalFileName ,f.[name] AS logicalFileName --,d2.[log_size_mb] --,d2.[row_size_mb] ,[d].[recovery_model_desc] ,'-----' AS separator --,CAST(f.size * 8.0 / 1024 AS DECIMAL(18,2)) AS fileSizeMB ,REPLACE(REPLACE(CONVERT(VARCHAR(100), CONVERT(MONEY, CAST([F].[size] * 8.0 / 1024 AS DECIMAL(18,2))), 1),',',''''),'.00','') as fileSizeMB --,fs.[FreeSpaceMB] AS FreeSpaceInFileMB ,REPLACE(REPLACE(CONVERT(VARCHAR(100), CONVERT(MONEY, [fs].[FreeSpaceMB]), 1),',',''''),'.00','') as FreeSpaceInFileMB --,(CAST(fs.[CurrentSizeMB] - fs.[FreeSpaceMB] AS MONEY))AS SpaceUsedMB ,REPLACE(REPLACE(CONVERT(VARCHAR(100), CONVERT(MONEY, [fs].[CurrentSizeMB] - [fs].[FreeSpaceMB]), 1),',',''''),'.00','') as SpaceUsedMB ,CAST(([fs].[CurrentSizeMB] - [fs].[FreeSpaceMB]) * 100 / (CAST([F].[size] * 8. / 1024 AS DECIMAL(18,2))) AS NUMERIC(6,2)) AS fileSpaceUsedPercent ,'-----' AS separator --,d2.[total_size_mb] AS totalDbSizeMB ,REPLACE(REPLACE(CONVERT(VARCHAR(100), CONVERT(MONEY, [d2].[total_size_mb]), 1),',',''''),'.00','') as totalDbSizeMB ,'USE '+d.name+'; DBCC SHRINKFILE (N'''+f.[name]+''' , 0, TRUNCATEONLY); GO ' AS shrinkData ,'USE '+d.name+'; DBCC SHRINKFILE (N'''+f.[name]+''' , 1024); GO ' AS reorganizeData ,'USE '+d.name+'; IF '+CAST(@limit AS VARCHAR(10))+' < '+CAST(CAST([F].[size] * 8.0 / 1024 AS DECIMAL(18,2)) AS VARCHAR(15))+' --file is above limit AND 95 > '+CAST(CAST(([fs].[CurrentSizeMB] - [fs].[FreeSpaceMB]) * 100 / (CAST([F].[size] * 8.0 / 1024 AS DECIMAL(18,2))) AS DECIMAL(6,2))AS VARCHAR(15))+' --and file container usage below 95% AND 0 < '+CAST(CAST([fs].[CurrentSizeMB] - [fs].[FreeSpaceMB] AS DECIMAL(16,2))AS VARCHAR(15))+' --and data are present AND '+CAST(CAST([fs].[CurrentSizeMB] - [fs].[FreeSpaceMB] + @limit AS DECIMAL(18,0)) AS VARCHAR(15))+' < '+CAST(CAST([fs].[CurrentSizeMB] AS DECIMAL(18,0)) AS VARCHAR(15))+' --target size is smaller than current size BEGIN --print ''Reorganize '+d.name+'.'+f.name+' '' RAISERROR(''Reorganize '+d.name+'.'+f.name+' '',0,0) WITH NOWAIT DBCC SHRINKFILE (N'''+f.[name]+''' , '+CAST(CAST([fs].[CurrentSizeMB] - [fs].[FreeSpaceMB] + @limit AS DECIMAL(18,0)) AS VARCHAR(15))+'); RAISERROR(''Done'',0,0) WITH NOWAIT END GO ' AS auto_action FROM sys.master_files F JOIN sys.databases d ON d.[database_id] = f.[database_id] JOIN [#dbs] [d2] ON [d2].[database_name] = d.[name] LEFT JOIN [#FileSize] fs ON [fs].[FileName] = f.name AND [fs].[dbName] = d.[name] WHERE f.type_desc IN ( 'ROWS' --mdf --, --'LOG' ) --AND [F].[physical_name] LIKE 'd:%' AND d.[is_read_only] = 0 AND [d].[source_database_id] IS NULL --AND d.[is_auto_shrink_on] = 0 --AND d.name='sl2007' AND d.name NOT IN ( 'master' ,'tempdb' ,'model' ,'msdb' ) ORDER BY [d2].[row_size_mb] DESC, d.name DESC, f.name ASC ; --ORDER BY [fileSpaceUsedPercent] ASC; --free disk space SELECT CONVERT(VARCHAR(512), [b].[volume_mount_point]) AS [volume_mount_point], CONVERT(VARCHAR(512), [b].[logical_volume_name]) AS [logical_volume_name], CONVERT(DECIMAL(18, 1), ROUND(((MAX(CONVERT(FLOAT, [b].[available_bytes])) / MAX(CONVERT(FLOAT, [b].[total_bytes]))) * 100), 1)) AS [percent_free], CONVERT(BIGINT, ROUND(((MAX([b].[available_bytes]) / 1024.0) / 1024.0 / 1024.0), 0)) AS [free_gb], CONVERT(BIGINT, ROUND(((MAX([b].[available_bytes]) / 1024.0) / 1024.0), 0)) AS [free_mb], CONVERT(BIGINT, ROUND(((MAX([b].[total_bytes]) / 1024.0) / 1024.0 / 1024.0), 0)) AS [total_gb], CONVERT(BIGINT, ROUND((((MAX([b].[total_bytes] - [b].[available_bytes])) / 1024.0) / 1024.0 / 1024.0), 0)) AS [used_gb], CONVERT(BIGINT, ROUND(((MAX([b].[total_bytes]) / 1024.0) / 1024.0), 0)) / 100 * 5 / 1024.0 AS [5% space in Go is], CONVERT(BIGINT, ROUND(((MAX([b].[total_bytes]) / 1024.0) / 1024.0), 0)) / 100 * 10 / 1024.0 AS [10% space in Go is], CONVERT(BIGINT, ROUND(((MAX([b].[total_bytes]) / 1024.0) / 1024.0), 0)) / 100 * 15 / 1024.0 AS [15% space in Go is], CURRENT_TIMESTAMP AS now, REPLACE(@@SERVERNAME, '\apssql', '') AS srvName FROM sys.master_files AS [a] CROSS APPLY ( SELECT [x].[database_id], [x].[file_id], [x].[volume_mount_point], [x].[volume_id], [x].[logical_volume_name], [x].[file_system_type], [x].[total_bytes], [x].[available_bytes], [x].[supports_compression], [x].[supports_alternate_streams], [x].[supports_sparse_files], [x].[is_read_only], [x].[is_compressed], ROW_NUMBER() OVER (PARTITION BY [x].[volume_mount_point] ORDER BY [x].[volume_mount_point] DESC) AS rnk FROM sys.dm_os_volume_stats(a.database_id, a.[file_id]) x ) b WHERE [b].[rnk] = 1 GROUP BY [b].[logical_volume_name], [b].[volume_mount_point] ORDER BY [b].[volume_mount_point] ASC;