Freigeben über

Shrinking All Database Files in SQL Server

  • Artikel

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

  • Anonymous
    May 30, 2008
    PingBack from http://blogs.msdn.com/jjameson/archive/2008/05/30/truncating-all-transaction-logs.aspx

  • Anonymous
    June 09, 2008
    MS SQL Scripts I Use constantly

  • Anonymous
    June 09, 2008
    MS SQL Scripts I Use constantly

  • Anonymous
    November 28, 2008
    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)

  • Anonymous
    December 01, 2008
    The comment has been removed

  • Anonymous
    March 11, 2009
    <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> ...