Disclaimer: I'm not a DBA and am asking this question to help research a problem a colleague is having.

We have a database that unintentionally ballooned to 300+ GB. We have turned off the option that caused this in our application, but we want to purge the offending data (in only 1 table) and reclaim the space it was taking up on the hard drive. We have tried truncating the table and then shrinking it on a copy of the production database, but this process takes about a week.

Is there a quicker/better/more efficient way to do this? I have read that shrinking a database is a taboo among DBAs because it introduces index fragmentation.

We are using Microsoft SQL Server.

"Best way" translates to quickest and least likely to cause us issues in the future (like index fragmentation might). We are trying to make this as quick as possible so that our database won't be tied up for several days deleting data and shrinking files.

  • It start with this msdn.microsoft.com/en-us/library/ms189493.aspx I don't think you can shrink a single table file only. When done then rebuild the fractured indexes.
    – paparazzo
    Oct 11 '16 at 21:54
  • Please add version (2008, 2008R2, 2012, etc) and edition (Standard, Enterprise, Azure, etc) Oct 11 '16 at 22:42
  • 1
    I think you'll most likely have to go with Randolph's solution, but you 'might' get some relief if you try SHRINKFILE TRUNCATEONLY - (msdn.microsoft.com/en-us/library/ms189493.aspx) - Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent. Oct 12 '16 at 9:44

There are two options for this kind of scenario:

1) Shrink the data file, and then rebuild the indexes. As you say, this is time consuming and you're largely at the whim of the storage subsystem.

2) Create a new filegroup, and migrate all the data into the new filegroup, one table at a time. Shrink the old filegroup when you're done, and either move everything back again, or leave it as is.

This second option adds some complexity, and additional maintenance for the extra FG, but is probably what you're after, since you can move one table at a time. You would do this by altering each table's clustered index to rebuild in the new filegroup.

Adding a Filegroup:

(See https://msdn.microsoft.com/en-us/library/bb522469.aspx)

USE master  
ALTER DATABASE AdventureWorks2012  

Adding a File to a Filegroup:

ALTER DATABASE AdventureWorks2012   
    NAME = FG2,  
    FILENAME = 'G:\DATA\FG2.ndf',  
    SIZE = 500MB,  
    FILEGROWTH = 250MB  

Rebuilding the clustered index in the new filegroup:

CREATE UNIQUE CLUSTERED INDEX [PK_Example] ON Test.dbo.Example(ExampleID) 

I am pretty sure that truncating is the most efficient way. However, you may want to re-index the table. Hope this helps!

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.