Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

5 Answers 5

up vote 23 down vote accepted

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.

share|improve this answer
    
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
2  
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
    
Additionally, as was pointed out to me, sys.master_files is a great, flexible option that allows you greater control over the process. –  swasheck Mar 14 '12 at 15:47

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.

share|improve this answer

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'
share|improve this answer
    
"quickly shrink all the files, both log and data" –  dezso Feb 14 '13 at 22:23
    
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

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
share|improve this answer

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)'
share|improve this answer
2  
+1. Interesting blog post. –  Larry Coleman Jan 7 '11 at 10:52
3  
A single line of code is not necessarily better if it might not work correctly. Please also read these posts, since sp_msforeachdb can skip databases and not warn you: sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/… and mssqltips.com/sqlservertip/2201/… –  Aaron Bertrand Mar 14 '12 at 15:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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