Post reply

shrinking data file

  • January 5, 2021 at 2:04 am

    #3827397

    Hello all,

    I am struggling with shrinking of a big database in TB. I read on many links and it is recommended not to shrink the file because it may cause defragmantation. In my case it is absolutely needed.  I went through this article and did some shrinking on some dev DB and it worked fine.

    here is the script used. My question is should I build reindex after the file is shrinked? I see the file is shrinked with script below but what next step I need to take to make sure the performance in sql is not degraded?

    https://www.sqlshack.com/shrinking-your-database-using-dbcc-shrinkfile/

    DECLARE @FileName sysname = N'fundamentals_log';

    DECLARE @TargetSize INT = (SELECT 1 + size*8./1024 FROM sys.database_files WHERE name = @FileName);

    DECLARE @Factor FLOAT = .999;

    WHILE @TargetSize > 0

    BEGIN

    SET @TargetSize *= @Factor;

    DBCC SHRINKFILE(@FileName, @TargetSize);

    DECLARE @msg VARCHAR(200) = CONCAT('Shrink file completed. Target Size: ',

    @TargetSize, ' MB. Timestamp: ', CURRENT_TIMESTAMP);

    RAISERROR(@msg, 1, 1) WITH NOWAIT;

    WAITFOR DELAY '00:00:01';

    END;

    Any help is appreciated.

    Thanks,

    Rohit

  • January 5, 2021 at 1:34 pm

    #3827564

    The deal with shrinking databases is not the simple act of a one-time shrink:

    "Oops, we had a bad data load. Gotta shrink the database."

    Despite the dire warnings you get all over the place, this is not a big deal. The issue is:

    "Oops, we had our third bad data load of the day and our 473 this week. Gotta shrink the database, again. Good thing I have a script to automate database shrinking because we have that one that keeps almost filling the disk, so we shrink it five times a day, every day"

    This is what leads to all sorts of bad outcomes. Performance will be atrocious, disk management gets harder, disk failure become more likely as you stress the technologies ability to hop all over the place (even an SSD starts to feel the pain after a while).

    So, you shrank the database. Fine. No big deal. Yes, defrag the indexes. That's a very good idea. Now, don't do it again for a long time (at least a year). If you find you're in the "well, we just have to keep shrinking because XXXXXX" camp, stop. Get more storage or fix whatever issues you're hitting.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply