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
    'USE [' + + '] DBCC SHRINKFILE (N''' + + ''' , 1)'
    sys.databases A
    INNER JOIN sys.master_files B
    ON A.database_id = B.database_id
WHERE NOT IN ( 'master', 'model', 'msdb', 'tempdb' )


FROM #CommandQueue

    DECLARE @sqlStatement VARCHAR(1000)
        @sqlStatement = SqlStatement
        ID = @id

    PRINT 'Executing ''' + @sqlStatement + '''...'

    EXEC (@sqlStatement)

    DELETE FROM #CommandQueue
    WHERE ID = @id

    SELECT @id = MIN(ID)
    FROM #CommandQueue

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.

Comments (6)

  1. mikedopp says:

    MS SQL Scripts I Use constantly

  2. mikedopp says:

    MS SQL Scripts I Use constantly

  3. m155698 says:

    I’m relatively new to MS SQL. What does this script actually do step-by-step?

    Starting with .." DROP TABLE #CommandQueue" .. looks frightening!



  4. In SQL Server, tables that begin with ‘#’ are local temp tables. In my script, I am simply using one of these to temporarily store the list of commands to be executed. The script starts by dropping the temp table to handle the scenario where you may want to run the script repeatedly (or run the script again from the beginning after a previous error terminated the script).

    By stuffing the commands into a temp table (i.e. the "command queue"), you can also quickly view or execute the remaining commands in the queue after an error.


    "Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server."

    For more information on what this script does step-by-step, please refer to SQL Server Books Online (

  5. <p>En gang i mellem får man brug for at rydde op i sine databaser, måske fordi en log backup har fejlet, der er for lidt diskplads eller hvad der nu kan hænde.</p> …