Sql-server – What’s the most efficient way to reclaim unused DB hard drive space

shrinksql servertruncate

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.

Best Answer

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:\DATA\FG2.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

Related Solutions

Sql-server – Most cost efficient way to page through a poorly ordered table

Essentially, you are asking if you can perform a single ordered scan through the data overall, while making no copies of the data, and returning 'x' disjoint sets of rows from the full set on each call. This is exactly the behaviour of an appropriately-configured API cursor.

For example, using the AdventureWorks table Person.EmailAddress to return sets of 1,000 rows:

DECLARE 
    @cur integer,
    -- FAST_FORWARD | AUTO_FETCH | AUTO_CLOSE
    @scrollopt integer = 16 | 8192 | 16384,
    -- READ_ONLY, CHECK_ACCEPTED_OPTS, READ_ONLY_ACCEPTABLE
    @ccopt integer = 1 | 32768 | 65536, 
    @rowcount integer = 1000,
    @rc integer;

-- Open the cursor and return the first 1,000 rows
EXECUTE @rc = sys.sp_cursoropen
    @cur OUTPUT,
    N'
    SELECT *
    FROM AdventureWorks2012.Person.EmailAddress
        WITH (INDEX([IX_EmailAddress_EmailAddress]))
    ORDER BY EmailAddress;
    ',
    @scrollopt OUTPUT,
    @ccopt OUTPUT,
    @rowcount OUTPUT;

IF @rc <> 16 -- FastForward cursor automatically closed
BEGIN
    -- Name the cursor so we can use CURSOR_STATUS
    EXECUTE sys.sp_cursoroption
        @cur, 
        2, 
        'MyCursorName';

    -- Until the cursor auto-closes
    WHILE CURSOR_STATUS('global', 'MyCursorName') = 1
    BEGIN
        EXECUTE sys.sp_cursorfetch
            @cur,
            2,
            0,
            1000;
    END;
END;

Each fetch operation returns a maximum of 1,000 rows, remembering the position of the scan from the previous call.

Sql-server – When Should You Shrink Your Database

There are basically two things to consider:

You cause fragmentation when shrinking the database
If the database grew yesterday, there's a good chance it will grow again tomorrow.

To address 1, you need to defrag indexes once you've shrunk the database. The database will grow when you do this, although probably not back to the original size.

To address 2, ask yourself why the database grew. If you know it was a one time thing, then you're probably OK to shrink it. If you know it's a recurring thing, then it's best not to shrink. If you're not sure, do it once manually, but keep a close eye on things and make sure you don't make a habit of it.

Regularly shrinking is a waste of time, will cause you performance issues and one day you'll run out of space without realising it because the database grows overnight but you didn't realise it was at 99% capacity because it was scheduled to shrink everyday before you got into the office.

Keep in mind that the shrink should be carefully planned maintenance on live servers, it'll conflict with things like backups and might put significant load on the server.

Best Answer

Related Solutions

Sql-server – Most cost efficient way to page through a poorly ordered table

Sql-server – When Should You Shrink Your Database

Related Question