Vryjdfkk

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.

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  

GO  

ALTER DATABASE AdventureWorks2012  

ADD FILEGROUP NewFG;  

GO  

Adding a File to a Filegroup:

ALTER DATABASE AdventureWorks2012   

ADD FILE   

(  

    NAME = FG2,  

    FILENAME = 'G:DATAFG2.ndf',  

    SIZE = 500MB,  

    MAXSIZE = UNLIMITED,  

    FILEGROWTH = 250MB  

)  

TO FILEGROUP NewFG;  

GO  

Rebuilding the clustered index in the new filegroup:

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

WITH (DROP_EXISTING = ON) ON [NewFG]

GO

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