Shrinking All Database Files in SQL Server
Here is another SQL script that I keep handy in my toolbox: Shrink All Database Files.sql. Unlike the script that I shared in my previous post that simply truncated all transaction logs to free up disk space, this script is suitable for running in a Production environment (PROD), as well as in non-production environments, such as a shared Development environment (DEV).
Here is the script:
DROP TABLE #CommandQueue
CREATE TABLE #CommandQueue
(
ID INT IDENTITY ( 1, 1 )
, SqlStatement VARCHAR(1000)
)
INSERT INTO #CommandQueue
(
SqlStatement
)
SELECT
'USE [' + A.name + '] DBCC SHRINKFILE (N''' + B.name + ''' , 1)'
FROM
sys.databases A
INNER JOIN sys.master_files B
ON A.database_id = B.database_id
WHERE
A.name NOT IN ( 'master', 'model', 'msdb', 'tempdb' )
DECLARE @id INT
SELECT @id = MIN(ID)
FROM #CommandQueue
WHILE @id IS NOT NULL
BEGIN
DECLARE @sqlStatement VARCHAR(1000)
SELECT
@sqlStatement = SqlStatement
FROM
#CommandQueue
WHERE
ID = @id
PRINT 'Executing ''' + @sqlStatement + '''...'
EXEC (@sqlStatement)
DELETE FROM #CommandQueue
WHERE ID = @id
SELECT @id = MIN(ID)
FROM #CommandQueue
END
As you can see, this script follows the same pattern that I described in my previous post.
I have found this script to be especially useful when working with Microsoft Office SharePoint Server (MOSS) 2007, because I sometimes migrate large amounts of content when working on certain features (particularly Search) but later decide to remove the content and need to recover the disk space on my VM.