Technology Toolbox

Your technology Sherpa for the Microsoft platform

Jeremy Jameson - Founder and Principal

Search

Search

Shrinking All Database Files in SQL Server

Note
This post originally appeared on my MSDN blog:

Since I no longer work for Microsoft, I have copied it here in case that blog ever goes away.

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.

Comments

  1. # re: Shrinking All Database Files in SQL Server

    November 28, 2008 10:53 AM
    m155698

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

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

    :o/

    ;o)

  2. # re: Shrinking All Database Files in SQL Server

    December 1, 2008 10:12 AM
    Jeremy Jameson
    Gravatar

    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.

    From http://msdn.microsoft.com/en-us/library/ms186986.aspx:

    "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 (http://msdn.microsoft.com/en-us/library/ms130214.aspx).

  3. # Random Musings of Jeremy Jameson : Truncating All Transaction Logs

    May 30, 2008 9:53 AM
    blogs.msdn.com
  4. # MS SQL Scripts I Use constantly

    June 9, 2008 11:52 AM
    blog.mikedopp.com
  5. # MS SQL Scripts I Use constantly

    June 9, 2008 11:54 AM
    mikedopp.com
  6. # Shrinking alle databaser.

    March 11, 2009 10:31 AM
    newcomtech.dk

Add Comment

Optional, but recommended (especially if you have a Gravatar). Note that your email address will not appear with your comment.
If URL is specified, it will be included as a link with your name.

To prevent spam from being submitted, please select the following fruit: Apple

Strawberry
Cherries
Grapes
Apple
Watermelon
Pear
 
Please add 8 and 3 and type the answer here: