Post reply

shrink databases

  • March 12, 2019 at 1:33 am

    #371657

    Hi All,

    Which one is good to use while shrinking ?
    DBCC SHRINKFILE()
    DBCC SHRINKDATABASE()

    I know shrinking is bad, however if I may have to use which one is good and WHY?

    Thanks,

    Sam

  • March 12, 2019 at 3:40 am

    #2024176

    vsamantha35 - Tuesday, March 12, 2019 1:33 AM

    Hi All,

    Which one is good to use while shrinking ?
    DBCC SHRINKFILE()
    DBCC SHRINKDATABASE()

    I know shrinking is bad, however if I may have to use which one is good and WHY?

    Thanks,

    Sam

    Shrinkfile - transaction log.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • March 12, 2019 at 4:50 am

    #2024183

    Shrink file is the better choice because of its scope
    Keep in mind you need to rebuild all indexes in that file after your shrink datafile ( not log ) operation because the shrink only moves stuff together.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • March 12, 2019 at 6:18 am

    #2024197

    Also, in addition to the other suggestion, a very important safety note. If you shrink a database once because of some untoward event, fine. If you're scheduling your shrinks or running the manually and regularly, that's a very big issue and must stop. You are causing major problems on your system with repeated shrinks of the database.

    "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

  • March 15, 2019 at 4:47 am

    #2024542

    ALZDBA - Tuesday, March 12, 2019 4:50 AM

    Shrink file is the better choice because of its scope
    Keep in mind you need to rebuild all indexes in that file after your shrink datafile ( not log ) operation because the shrink only moves stuff together.

    Thanks for all the responses.

    Follow up questions :

    Why should we rebuild indexes if we shrink a data file? Is it going cause any fragmentation ? How does the shrink is gona work behind the scenes?
    As per  my  knowledge goes, shrink will release free space (if any) at the end of file. For example , if I have 10GB data file , out of which 5GB used, and if I use a shrink-file them 5GB will be released to OS. Correct me if I am wrong.

  • March 15, 2019 at 4:48 am

    #2024543

    Grant Fritchey - Tuesday, March 12, 2019 6:18 AM

    Also, in addition to the other suggestion, a very important safety note. If you shrink a database once because of some untoward event, fine. If you're scheduling your shrinks or running the manually and regularly, that's a very big issue and must stop. You are causing major problems on your system with repeated shrinks of the database.

    Sir, what kind of problems we might end up with ? could you please elaborate a little bit?

  • March 15, 2019 at 4:53 am

    #2024545

    vsamantha35 - Friday, March 15, 2019 4:47 AM

    ALZDBA - Tuesday, March 12, 2019 4:50 AM

    Shrink file is the better choice because of its scope
    Keep in mind you need to rebuild all indexes in that file after your shrink datafile ( not log ) operation because the shrink only moves stuff together.

    Thanks for all the responses.

    Follow up questions :

    Why should we rebuild indexes if we shrink a data file? Is it going cause any fragmentation ? How does the shrink is gona work behind the scenes?
    As per  my  knowledge goes, shrink will release free space (if any) at the end of file. For example , if I have 10GB data file , out of which 5GB used, and if I use a shrink-file them 5GB will be released to OS. Correct me if I am wrong.

    You will need to rebuild your indexes because shrinking the data file will cause fragmentation.

    Thanks

  • March 15, 2019 at 4:55 am

    #2024546

    vsamantha35 - Friday, March 15, 2019 4:48 AM

    Grant Fritchey - Tuesday, March 12, 2019 6:18 AM

    Also, in addition to the other suggestion, a very important safety note. If you shrink a database once because of some untoward event, fine. If you're scheduling your shrinks or running the manually and regularly, that's a very big issue and must stop. You are causing major problems on your system with repeated shrinks of the database.

    Sir, what kind of problems we might end up with ? could you please elaborate a little bit?

    As previously mentioned regarding index fragmentation and subsequent rebuilding of indexes. The database will more than likely need to grow again and growth events have a hit on performance. It's far more important to size your database and growth correctly. Try to avoid growth events as much as possible and leave space for your database to grow into.

    Thanks

  • March 15, 2019 at 9:32 am

    #2024595

    NorthernSoul - Friday, March 15, 2019 4:55 AM

    As previously mentioned regarding index fragmentation and subsequent rebuilding of indexes. The database will more than likely need to grow again and growth events have a hit on performance. It's far more important to size your database and growth correctly. Try to avoid growth events as much as possible and leave space for your database to grow into.

    Thanks

    Yep, this.

    Repeated shrinks causes problems because you then have to regrow. There has to be space to rebuild indexes, add new data, etc., etc., shrinking takes that way, causes big issues with your indexes, and then the regrow points cause locking and blocking while that process occurs. It's a mess.

    "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

  • March 16, 2019 at 12:37 am

    #2024682

    Thanks a lot Sir. Got it.

  • March 16, 2019 at 11:08 am

    #2024699

    ALZDBA - Tuesday, March 12, 2019 4:50 AM

    Shrink file is the better choice because of its scope
    Keep in mind you need to rebuild all indexes in that file after your shrink datafile ( not log ) operation because the shrink only moves stuff together.

    Ah... careful now.   You don't actually need to rebuild ALL of the indexes.  Some of them may not have been inverted by the shrink.  You only need to rebuild the ones that actually suffered the inversion.  Just do your normal index maintenance which, hopefully, isn't all of the indexes, fragmented or not.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 16, 2019 at 11:08 am

    #2024700

    NorthernSoul - Friday, March 15, 2019 4:53 AM

    vsamantha35 - Friday, March 15, 2019 4:47 AM

    ALZDBA - Tuesday, March 12, 2019 4:50 AM

    Shrink file is the better choice because of its scope
    Keep in mind you need to rebuild all indexes in that file after your shrink datafile ( not log ) operation because the shrink only moves stuff together.

    Thanks for all the responses.

    Follow up questions :

    Why should we rebuild indexes if we shrink a data file? Is it going cause any fragmentation ? How does the shrink is gona work behind the scenes?
    As per  my  knowledge goes, shrink will release free space (if any) at the end of file. For example , if I have 10GB data file , out of which 5GB used, and if I use a shrink-file them 5GB will be released to OS. Correct me if I am wrong.

    You will need to rebuild your indexes because shrinking the data file will cause fragmentation.

    Thanks

    See my previous post...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 16, 2019 at 11:13 am

    #2024701

    vsamantha35 - Friday, March 15, 2019 4:47 AM

    ALZDBA - Tuesday, March 12, 2019 4:50 AM

    Shrink file is the better choice because of its scope
    Keep in mind you need to rebuild all indexes in that file after your shrink datafile ( not log ) operation because the shrink only moves stuff together.

    Thanks for all the responses.

    Follow up questions :

    Why should we rebuild indexes if we shrink a data file? Is it going cause any fragmentation ? How does the shrink is gona work behind the scenes?
    As per  my  knowledge goes, shrink will release free space (if any) at the end of file. For example , if I have 10GB data file , out of which 5GB used, and if I use a shrink-file them 5GB will be released to OS. Correct me if I am wrong.

    When you shrink a file, it does it page by page from the "end" of the file to the first available freespace at the beginning of the file.  Any indexes that are affected in such a manner by the shrink will be "inverted".  What I mean by that is that a part of the index or even the whole index will be in totally reverse physical order compared to the page logical order.  That causes massive logical fragmentation and, if they occur, any "Read Aheads" will be limited to as little as a single page and that causes a heap of trouble for performance when something needs to be loaded from disk into memory (and everything needs to be loaded into memory before it can be used by code).

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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