47

In SQL Server (2008 in this case) how can I quickly shrink all the files, both log and data, for all databases on an instance? I could go through SSMS and right click each and choose Tasks -> Shrink, but I'm looking for something faster.

I scripted some "Create database" scripts and forgot they had ballooned sizes for defaults, and don't need quite that much space reserved for these files on this project.

55

When you do "Tasks -> Shrink" from the GUI it actually issues a DBCC SHRINKDATABASE command behind the scenes. Try it. When the dialog box comes up, don't click the "OK" button. Instead, click the "Script" button. You'll see the command in a query window. Combine that with a query on sys.databases (leave out master and msdb), and you can make a script to shrink all of the databases.

For example (taken from jcolebrand's comment):

SELECT 
      'USE [' + d.name + N']' + CHAR(13) + CHAR(10) 
    + 'DBCC SHRINKFILE (N''' + mf.name + N''' , 0, TRUNCATEONLY)' 
    + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) 
FROM 
         sys.master_files mf 
    JOIN sys.databases d 
        ON mf.database_id = d.database_id 
WHERE d.database_id > 4;

Copy the output of that query and run it to shrink all your files.

  • 1
    Ok, I think I have what I want (ugly but does just what I need it to) SELECT 'USE [' + d.name + N']' + CHAR(13) + CHAR(10) + 'DBCC SHRINKFILE (N''' + mf.name + N''' , 0, TRUNCATEONLY)' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) FROM sys.master_files mf JOIN sys.databases d ON mf.database_id = d.database_id WHERE d.database_id > 4 But figuring that out gave me a new problem. Off to post another question. – jcolebrand Jan 6 '11 at 22:09
  • Seriously. Check out @Sandy's answer. the sp_MSForEachDB (there's also a "table" sproc, too) are extremely helpful – swasheck Mar 14 '12 at 15:37
  • 3
    And here's the obligatory reminder to all who are reading this: Shrinking your database is dangerous. – Nick Chammas Mar 14 '12 at 15:40
  • 1
    filter out Offline DB would make it even better. :-) – TiloBunt Nov 21 '14 at 19:27
  • 1
    Agreed with @TiloBunt, the whole condition is better as WHERE d.database_id > 4 AND d.state_desc = 'ONLINE'; – Mauro Jul 13 '18 at 9:15
23

How about one single line of sql statement?

Please read this very interesting blog post before executing the following sql statement.

EXEC sp_MSForEachDB 'DBCC SHRINKDATABASE (''?'' , 0)'
15

DBCC SHRINKDB (and its cousin SHRINKFILE) are extremely slow, because there is a lot of single threaded execution going on in that code.

A much faster way to shrink a database file is this:

  • Allocate a new filegroup to database
  • Make this filegroup as large as it has to be (use sp_spaceused to determine just how large)
  • Rebuild all indexes to this new filegroup
  • Drop the old filegroup

Because index rebuilds are massively parallel, this technique often results in a much faster shrinking of the database. Of course, it does require you to have a bit of extra space for the new filegroup while the process is going on. However, you only need enough space in the new filegroup to hold the largest filegroup in the instance (as you will be reclaiming space as you go along).

This technique also has the added benefit of defragmenting your indexes in the process.

  • You forgot an important part. Rebuilding indexes won't move anything else including such things as stored procedures, views, functions, synonyms, heaps, etc, etc. – Jeff Moden May 29 at 18:01
  • And those take up no space that you should care about. They HAVE to reside in the PRIMARY filegroup too, you can't really move them (and neither should you) – Thomas Kejser Jun 4 at 10:28
13

I tunned up a little the query to shrink only the LOG as it is requested:

set nocount on  
SELECT 
      'USE [' + d.name + N']' + CHAR(13) + CHAR(10) 
    + 'DBCC SHRINKFILE (N''' + mf.name + N''' , 0, TRUNCATEONLY)' 
    + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) 
FROM 
         sys.master_files mf 
    JOIN sys.databases d 
        ON mf.database_id = d.database_id 
WHERE d.database_id > 4 and mf.type_desc = 'LOG'
  • "quickly shrink all the files, both log and data" – dezso Feb 14 '13 at 22:23
  • 2
    I was looking for this, and was about to double post when I saw your answer. Not direct answer, but VERY relevant and spot on for my case. – Gomibushi Dec 17 '13 at 12:28
2

The code below, get a list of non system databases, set the database to readonly and then shrink the file. I have kept this code in a few SQL Server boxes using SQL Agent Job, where space is always an issue. On Sat/Sun night every week, it start running and shrink all the databases within few hours (depending upon the size of databases).

declare @db varchar(255)
declare c cursor for
select name from sys.databases where is_read_only=0 and state=0
  and name not in ('master','model','tempdb','msdb')
open c
fetch c into @db
while @@fetch_status=0
begin
  exec SP_dboption @db,'trunc. log on chkpt.','true' 
  DBCC shrinkdatabase (@db)
  fetch next from c into @db
end
close c
deallocate c
0

Shrink all log files except master, model, msdb:

EXEC sp_MSforeachdb '
DECLARE @sqlcommand nvarchar (500)
IF ''?'' NOT IN (''master'', ''model'', ''msdb'')
BEGIN
USE [?]
SELECT @sqlcommand = ''DBCC SHRINKFILE (N'''''' + 
name
FROM [sys].[database_files]
WHERE type_desc = ''LOG''
SELECT @sqlcommand = @sqlcommand + '''''' , 0)''
EXEC sp_executesql @sqlcommand
END'
0

This one extends the answer above, using a cursor to iterate through the SQL statements one by one. It's not as short as Emrah's answer but it does allow for additional logic within the while loop within the cursor..

SELECT 
    'USE [' 
    + databases.name + N']' 
    + CHAR(13) 
    + CHAR(10) 
    + 'DBCC SHRINKFILE (N''' 
    + masterFiles.name 
    + N''' , 0, TRUNCATEONLY)' 
    + CHAR(13) 
    + CHAR(10) 
    + CHAR(13) 
    + CHAR(10)                                                                  AS sqlCommand
INTO
    #shrinkCommands
FROM 
    [sys].[master_files] masterFiles 
    INNER JOIN [sys].[databases] databases ON masterFiles.database_id = databases.database_id 
WHERE 
    databases.database_id > 4; -- Exclude system DBs


DECLARE iterationCursor CURSOR

FOR
    SELECT 
        sqlCommand 
    FROM 
        #shrinkCommands

OPEN iterationCursor

DECLARE @sqlStatement varchar(max)

FETCH NEXT FROM iterationCursor INTO @sqlStatement

WHILE (@@FETCH_STATUS = 0)
BEGIN
    EXEC(@sqlStatement)
    FETCH NEXT FROM iterationCursor INTO @sqlStatement
END

-- Clean up
CLOSE iterationCursor
DEALLOCATE iterationCursor
DROP TABLE #shrinkCommands
0

We can repeat SHRINKDB and SHRINKFILE for all databases dynamically:

while @DBID<=@MaxDBID
begin
  -- Used Dynamic SQL for all databases.
  Set @SQL ='Use '+@DBName+ ' '+Char(10)
  Set @SQL += 'DBCC SHRINKFILE('+@Filename+',5)' +Char(10)
  Set @SQL += 'DBCC SHRINKDATABASE('+@DBName+')'+Char(10)

  --#6 Increment DBid for looping over all databases
  Set @DBID = @DBID+1
  Select @DBName = DBName, @Filename=DBFileName from #DBNames where [dbid] = @DBID and type_Desc = 'LOG'
  Print (@SQL)
  Exec (@SQL)
end

You can find details in this article.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.