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.

This question is asked in various forms here but the question boils down to:

I know shrinking a database is risky. In this case, I've removed so much data and I'll never use it again.

  • How can I shrink my database? What files do I shrink?
  • What should my considerations be while doing this?
  • Should I do anything after?
  • What if it is a large database? Can I shrink it in smaller increments?
share|improve this question
1  
I struggled with this some time ago: dba.stackexchange.com/questions/47310/… I tried to summarize my experience in my answer –  Csaba Toth Nov 26 '13 at 19:58

2 Answers 2

Some Initial Caveats:

  1. It is generally known as a worst practice to ever shrink a production database or data file (Log files are another issue as this question talks about). I advise people to not shrink their databases in blog posts like this where I talk about "right-sizing" and good planning. I'm not alone there (Paul Randal, Brent Ozar, just to provide a couple more links). Shrinking a data file or database fragments indexes, is slow and laborious on your resources, can be a drain on your system and is just a bad thing to do, generally
  2. In this case, we all know the risk is there, we are prepared to deal with it, but we freed a lot of space that we know we'll never ever need again. So in this specific type of case - shrinking makes a lot of sense as one of our options.

If you've read about the concerns and risks and you still need to do this shrink because you freed a significant amount of space, hopefully the rest of this answer will help you out. But do consider the risks.

There are two main approaches two consider here:

1.) Shrink Yes, do the actual shrink - Consider using DBCC SHRINKFILE instead of DBCC SHRINKDATABASE, you have more control over what gets shrunk and how. This will cause some performance degradation for sure - it is a large operation doing a lot of IO. You can potentially get away with repeated shrinks to a target size that gets progressively smaller.

This is the "A.)" example in the above DBCC SHRINKFILE link.. A datafile is being shrunk to 7MB target size in this example. This format is a good way to shrink repeatedly as your downtime window allows. I would do this in testing on development to see how the performance looks and how low/high you can go of an increment and to determine the expected timing in production. This is an online operation - you can run it with users in the system accessing the database being shrunk, but there will be performance degradation, almost guaranteed. So monitor and watch and see what you are doing to the server, pick a downtime window or period of lighter activity, ideally.

USE YourDatabase;
GO
DBCC SHRINKFILE (DataFile1, 7);
GO

Always Remember: - every time you shrink you fragment your indexes and should do an index rebuild if you are going to shrink in chunks over a prolonged period of time. You are now incurring that cost each time if you can't get it done all in one window.

2.) New Database - You could create a new database and migrate data to it. You would have to script the empty database out and all of it's keys, indexes, objects, procs, functions, etc. and then migrate data to it. You could write scripts for this or you could use a tool like SQL Data Compare from Red Gate or other vendors with similar tools. This is more setup work on your side, more development and testing, and depending on your environment may also blow out your downtime window also but an option to consider.

When I am forced to shrink a Database If this were my environment, I'd look to leave a fair/hefty amount of white space in the data file because I like being a disk hog and like to be prepared for future/unexpected growth. So I would be okay giving space back if we just deleted a majority of the space, but I'd never trust those saying "but it will never grow again" and still leave some white space. The route I'd probably go with (sigh) is the shrink approach if I had smaller downtime windows and didn't want to incur the complexity of creating an empty DB and migrating data to it. So I would shrink it a bunch of times incrementally (based on how many times I thought I needed to based on my testing in dev and the desired size. progressively choosing a smaller file size)and then rebuild the indexes.. And then I'd never tell any one that I shrank my database ;-)

share|improve this answer
  1. How can I shrink my database? What files do I shrink?: You can shrink the files individually by the DBCC SHRINKFILE command you mention. It depends on your server how many files your database consists of. A simple database has one database file and one transaction log file.
  2. What should my considerations be while doing this?: the shrink affects your index fragmentation, see 3rd point. Also note that you don't want to shrink the database file to a size which is the minimal possible, because in a real-world environment it'll grow anyway. So I would tune the size (in your example you gave 7 megabytes) in a manner that you'd leave 10%-20% free space within the database file, because it'll be filled anyway in the production environment, and you can save some auto-growth cycles that way. So the actual number needs careful calculation. Also note that the "big space freeup" you performed would bloat up the transaction log file even more than the space you gained within the DB file. Also, the actual space gain you may experience will be less than what you mathematically expect! So let's say you mathematically freed up 12 gigs, then you maybe able to shrink only
  3. Should I do anything after?: As I mentioned earlier, you want to reindex those indexes which fragmentation got distorted as a result of the SHRINK's changes. I haven't experimented enough if you need to do anything special about query statistics.
  4. What if it is a large database? Can I shrink it in smaller increments? The SHRINK operation can be interrupted any time, and you can continue later. I'd advise to perform it on an off-line database if possible. By interrupting and contuniung it'd would go forward the same shrink size though. Theoretically you can shrink in smaller increments by specifying a less tight target size instead of 7 megabytes, but I'd say that if you are performing it in production, then just give it one go. As you see there are issues with the index fragmentation and possible transaction log growth. So I'd go through this just one time.

We all know that it is not advised to do SHRINK regularly anyway. I try to leave out all the warnings and disclaimers you probably know anyway. Backup, and don't do this at home if possible :)

Bonus: in replication environment if you perform this on the publisher database, it won't cause the subscriber databases to shrank (which may have the size problem because they are Express editions).

Finally, my reindex script:

USE YourDBName

DECLARE @TbName VARCHAR(255)
DECLARE @FullTbName VARCHAR(255)
DECLARE @IxName VARCHAR(255)
DECLARE myCursor CURSOR FOR
    SELECT OBJECT_NAME(dmi.object_id) AS TableName,i.name AS IndexName
    FROM sys.dm_db_index_physical_stats(14, NULL, NULL, NULL , 'LIMITED') dmi
    JOIN  sys.indexes i on dmi.object_id = i.object_id and dmi.index_id = i.index_id
    WHERE avg_fragmentation_in_percent > 30
    ORDER BY avg_fragmentation_in_percent
OPEN myCursor
FETCH NEXT FROM myCursor INTO @TbName, @ixName
WHILE @@FETCH_STATUS = 0
BEGIN
    IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_SCHEMA = 'dba' AND TABLE_NAME = @TbName)
BEGIN
        SET @FullTbName = 'dba.' + @TbName
        IF (@ixName IS NULL)
        BEGIN
            PRINT 'Reindexing Table ' + @FullTbName
            DBCC DBREINDEX(@FullTbName, '', 0)
        END
        ELSE
        BEGIN
             PRINT 'Reindexing Table ' + @FullTbName + ', Index ' + @IxName
             DBCC DBREINDEX(@FullTbName, @IxName, 0)
        END
    END
    FETCH NEXT FROM myCursor INTO @TbName, @ixName
END
CLOSE myCursor
DEALLOCATE myCursor

The only variable in this is the 14, which can be obtained by issuing select DB_ID('YourDBName'), and the script assumes that you are interested only in the tables in the dba.* schema.

share|improve this answer

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.