The script below will scan through all files in all the databases on a given instance and identify the files with the most free space. It will shrink that file down in a small increment and then identify the next file with the most free space to shrink (might be that same file still).
I use this script after a big release to identify excess free space and remove it.
It also comes in hand when a drive is out of space and you need to clean it up fast.
SET NOCOUNT ON
DECLARE @dbname sysname
DECLARE @stmt varchar(1000)
DECLARE @shrink_increment integer
DECLARE @volume char(1)
DECLARE @space_to_free integer
DECLARE @FileID integer
DECLARE @TotalMB integer
SET @shrink_increment = 250 --Increment which you would like to shrink files in.
SET @space_to_free = 100000 --Total amount of space you want to free.
SET @volume = null --If you would like to shrink a particular drive just enter it here. If you want to do all drives set it to null.
IF EXISTS (SELECT 1 FROM tempdb.dbo.sysobjects WHERE name like '##data%')
DROP TABLE ##data
CREATE TABLE ##data (
Fileid int NOT NULL,
FileGroup int NOT NULL,
TotalExtents int NOT NULL,
UsedExtents int NOT NULL,
Name sysname NOT NULL,
FileName varchar(300) NOT NULL,
DatabaseName sysname NULL,
TotalMB integer NULL,
UsedMB integer NULL)
DECLARE db_curs CURSOR FOR
SELECT name
FROM master.sys.databases
WHERE state = 0 --Only get ONLINE databases
and database_id > 4
OPEN db_curs
FETCH NEXT FROM db_curs INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @stmt = 'USE ' + @dbname + char(10) + 'DBCC showfilestats with no_infomsgs'
INSERT INTO ##data (Fileid, FileGroup, TotalExtents, UsedExtents, Name, FileName)
EXECUTE(@stmt)
UPDATE ##data
SET DatabaseName = @dbname
WHERE DatabaseName is null
SET @stmt = 'USE ' + @dbname + char(10) + 'dbcc sqlperf(logspace) with no_infomsgs'
FETCH NEXT FROM db_curs INTO @dbname
END
CLOSE db_curs
DEALLOCATE db_curs
UPDATE ##data
SET TotalMB = (TotalExtents * 64.0)/ 1024.0,
UsedMB = (UsedExtents * 64.0)/ 1024.0
WHILE @space_to_free > 0
BEGIN
SELECT TOP 1
@FileID = FileID,
@TotalMB = TotalMB - @shrink_increment,
@dbname = DatabaseName
FROM ##data
WHERE substring(FileName, 1, 1) = @volume or @volume is null
and totalmb > 1000
ORDER BY (cast(UsedMB as float)/cast(TotalMB as float))
SET @stmt = 'USE ' + @dbname + char(10) + 'DBCC SHRINKFILE(' + cast(@FileID as varchar) + ', ' + cast(@TotalMB as varchar) + ')'
PRINT @stmt
execute(@stmt)
Update ##data
SET TotalMB = @TotalMB
WHERE FileID = @FileID
and DatabaseName = @dbname
SET @space_to_free = @space_to_free - @shrink_increment
END
