Post reply

Shrink Database benefit

  • September 30, 2011 at 1:02 pm

    #243956

    Hi,

    We recently removed a large table which freed up about 40% of the database. Considering space is not an issue and we are anticipating for this database to grow, is there a benefit to shrink the DB now and allow it to grow later? Or is it better to just leave it as is and let it to grow "into" the available space?

    Thanks.

  • September 30, 2011 at 1:05 pm

    #1388989

    40% is a lot, but if you have the room to spare, leave it be. Saves you time later and gives you enough room to not need to grow during maintenance and the like. Now would be a good time to defrag your stuff too.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • September 30, 2011 at 1:16 pm

    #1388994

    Evil Kraig F (9/30/2011)

    Now would be a good time to defrag your stuff too.

    Defrag with the overallocated DB? What do you use for defraging and why now be a good time to do it?

  • September 30, 2011 at 1:20 pm

    #1388996

    Lexa (9/30/2011)

    Evil Kraig F (9/30/2011)

    Now would be a good time to defrag your stuff too.

    Defrag with the overallocated DB? What do you use for defraging and why now be a good time to do it?

    Defragging indexes and the like internally to the database. Poke around on google and you'll find a number of things to reference it.

    One of the reasons now is a good time is it usually needs some space to toss things around in to get them re-organized. Since you're sitting on plenty of space, this will give you the room to do it. It'll also let you have enough room to reset fill-factors accurately and the like.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • September 30, 2011 at 8:04 pm

    #1389067

    Basically unless you absolutly need to reclaim space on the disk, I'm of the opinion you should not ever shrink the data files on a database.

  • October 1, 2011 at 7:25 am

    #1389103

    Thanks.

  • October 1, 2011 at 7:53 am

    #1389109

    Evil Kraig F (9/30/2011)

    Lexa (9/30/2011)

    Evil Kraig F (9/30/2011)

    Now would be a good time to defrag your stuff too.

    Defrag with the overallocated DB? What do you use for defraging and why now be a good time to do it?

    Defragging indexes and the like internally to the database. Poke around on google and you'll find a number of things to reference it.

    One of the reasons now is a good time is it usually needs some space to toss things around in to get them re-organized. Since you're sitting on plenty of space, this will give you the room to do it. It'll also let you have enough room to reset fill-factors accurately and the like.

    He means this one most likely 😉

    http://sqlfool.com/2011/06/index-defrag-script-v4-1

  • October 3, 2011 at 10:31 am

    #1389675

    You could set up an auto job to shrink the database(s) when your backups are taken, which would maximise available space at all times.

    I'd only recommend doing this in a situation where you have both full backups in place (full recovery model) and preferably a disk- or server-level backup solution too.

    Just issue DBCC SHRINKDATABASE db_name TRUNCATEONLY; GO

    You can set this up as a regular job or code it directly into a batch file and put it in Scheduled Tasks.

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

  • October 3, 2011 at 10:36 am

    #1389677

    derek.colley (10/3/2011)

    You could set up an auto job to shrink the database(s) when your backups are taken, which would maximise available space at all times.

    I'd only recommend doing this in a situation where you have both full backups in place (full recovery model) and preferably a disk- or server-level backup solution too.

    Just issue DBCC SHRINKDATABASE db_name TRUNCATEONLY; GO

    You can set this up as a regular job or code it directly into a batch file and put it in Scheduled Tasks.

    And while you're at it, start a fire in the server room so the sprinklers start and cool those servers off.

    Auto_shrink is one of the worst ideas in the history of SS. Putting it in a job is not much better.

    Not trying to insult you, nor the idea but this is a definite no-no.

    We appreciate when people come in and give free help. We don't mind the occasional mistakes (I've done more than my share), but this is just flat out dangerous.

  • October 4, 2011 at 12:07 am

    #1389948

    Hah, fair enough! This is not the first time I've heard that opinion aired!

    Out of interest, does anyone know why AUTO_SHRINK is such a bad idea? I know that even the training material for MCTS / MCITP recommends against using it ... but it is because of the performance overhead? Or the risk of data file corruption? Surely the former could be mitigated by running it before/after backups at silly o'clock, and the latter through using DBCC CHECKDB?

    Not wanting to sound naive, but just wanting to understand everyone's apathy to it.

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

  • October 4, 2011 at 12:14 am

    #1389952

    derek.colley (10/4/2011)

    Hah, fair enough! This is not the first time I've heard that opinion aired!

    Out of interest, does anyone know why AUTO_SHRINK is such a bad idea? I know that even the training material for MCTS / MCITP recommends against using it ... but it is because of the performance overhead? Or the risk of data file corruption? Surely the former could be mitigated by running it before/after backups at silly o'clock, and the latter through using DBCC CHECKDB?

    Not wanting to sound naive, but just wanting to understand everyone's apathy to it.

    One reason, no way to control when the auto-shrink runs. Because of a mass delete during the day, it could run at the busiest time for the database causing severe performance issues. In addition, it would fragment indexes adding to the performance problems.

  • October 4, 2011 at 12:55 am

    #1389962

    Another reason is the unnecessary impact when the database needs to grow again

  • October 4, 2011 at 2:59 am

    #1390003

    I prefer manual file management than auto-management (auto-grow, auto-shrink, defrag etc.). The reason is simple ‘I (as DBA) know better when the database must grow / shrink’.

  • October 4, 2011 at 4:12 am

    #1390047

    derek.colley (10/4/2011)

    Out of interest, does anyone know why AUTO_SHRINK is such a bad idea? I know that even the training material for MCTS / MCITP recommends against using it ... but it is because of the performance overhead? Or the risk of data file corruption? Surely the former could be mitigated by running it before/after backups at silly o'clock, and the latter through using DBCC CHECKDB?

    http://sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(930)-data-file-shrink-does-not-affect-performance.aspx

    http://sqlskills.com/BLOGS/PAUL/post/Conference-Questions-Pot-Pourri-10-Shrinking-the-database-before-taking-a-backup.aspx

    http://sqlskills.com/BLOGS/PAUL/post/Auto-shrink-e28093-turn-it-OFF!.aspx

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 14 posts - 1 through 13 (of 13 total)

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