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?

4 Answers 4


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;

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 ;-)

  • 1
    I would add the special case that if you deleted a lot of data from a heap (especially from the middle of the heap) you won't be able to get that space back until you add a clustered index to it (hopefully forever), and then drop the clustered index after (turning it back into a heap). Of course if the heap is truncated regularly then no concerns. But still worth mentioning. May 31, 2017 at 14:29
  • Can someone explain the implication of NOTRUNCATE AND TRUNCATEONLY, apparently the latter does not rearrange pages and therefore does not cause index fragmentation? May 23, 2018 at 22:54
  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, and that means gigabytes less in case of a 12GB mathematical shrink!
  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

    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
        SET @FullTbName = 'dba.' + @TbName
        IF (@ixName IS NULL)
            PRINT 'Reindexing Table ' + @FullTbName
            DBCC DBREINDEX(@FullTbName, '', 0)
             PRINT 'Reindexing Table ' + @FullTbName + ', Index ' + @IxName
             DBCC DBREINDEX(@FullTbName, @IxName, 0)
    FETCH NEXT FROM myCursor INTO @TbName, @ixName
CLOSE 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.


You have heard all of the warnings about shrinking databases and they are all true. It will fragment your indexes and is in general, muck up your database and shouldn't be done on a production system.

But, I generally do so on a weekly basis when I restore a backup on my workstation due to space on my SSD drive. Mind you, I didn't write this script but found it years ago. On other databases [250 GB], I created an SSIS package that will transfer the tables that I need and then recreate the indexes for that oh so fresh index feel.



DECLARE @ShrinkIncrementMB INT

SET @DBFileName = 'Set Name of Database file to shrink'

-- Set Desired file free space in MB after shrink

SET @TargetFreeMB = 500
-- Set Increment to shrink file by in MB
SET @ShrinkIncrementMB = 100

SELECT [FileSizeMB] = convert(NUMERIC(10, 2),
round(a.size / 128., 2)),

[UsedSpaceMB] = convert(NUMERIC(10, 2),

round(fileproperty(a.NAME, 'SpaceUsed') / 128., 2)),

[UnusedSpaceMB] = convert(NUMERIC(10, 2),

round((a.size - fileproperty(a.NAME, 'SpaceUsed')) / 128., 2)),

[DBFileName] = a.NAME

FROM sysfiles a


SELECT @SizeMB = size / 128.
FROM sysfiles

SELECT @UsedMB = fileproperty(@DBFileName, 'SpaceUsed') / 128.

SELECT [StartFileSize] = @SizeMB
    ,[StartUsedSpace] = @UsedMB
    ,[DBFileName] = @DBFileName

WHILE @SizeMB > @UsedMB + @TargetFreeMB + @ShrinkIncrementMB

    SET @sql = 'dbcc shrinkfile ( ' + @DBFileName + ', ' + convert(VARCHAR(20), @SizeMB - @ShrinkIncrementMB) + ' ) '

    PRINT 'Start ' + @sql
    PRINT 'at ' + convert(VARCHAR(30), getdate(), 121)

    EXEC (@sql)

    PRINT 'Done ' + @sql
    PRINT 'at ' + convert(VARCHAR(30), getdate(), 121)

    SELECT @SizeMB = size / 128.
    FROM sysfiles
    WHERE NAME = @DBFileName

    SELECT @UsedMB = fileproperty(@DBFileName, 'SpaceUsed') / 128.

    SELECT [FileSize] = @SizeMB
        ,[UsedSpace] = @UsedMB
        ,[DBFileName] = @DBFileName

SELECT [EndFileSize] = @SizeMB
    ,[EndUsedSpace] = @UsedMB
    ,[DBFileName] = @DBFileName

SELECT [FileSizeMB] = convert(NUMERIC(10, 2), round(a.size / 128., 2))

    ,[UsedSpaceMB] = convert(NUMERIC(10, 2), round(fileproperty a.NAME, 'SpaceUsed') / 128., 2))

,[UnusedSpaceMB] = convert(NUMERIC(10, 2), round((a.size - fileproperty(a.NAME, 'SpaceUsed')) / 128., 2))

,[DBFileName] = a.NAME

FROM sysfiles a

This quote below is directly from Microsoft (applies to versions 2008-2016), and gives guidance on if/when and how you should use the DBCC SHRINKFILE command.


Best Practices

Consider the following information when you plan to shrink a file:

  • A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
  • Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.
  • A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.
  • Shrink multiple files in the same database sequentially instead of concurrently. Contention on system tables can cause delays due to blocking.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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