Post reply

runnig Log Shopping, but want to shrink that database without braking log shipping

  • March 19, 2010 at 1:33 pm

    #142079

    Hi,

    I am in SQL sever 2005 shop and runnig Log Shopping every 15min, but want to shrink the database without braking the log shipping.

    There is any option that I can pick?

  • March 19, 2010 at 3:40 pm

    #1137060

    Shrinking a database does not break the log chain, ever. There is no option to ShrinkFile or ShrinkDatabase that will break the log chain and hence log shipping.

    That said, why do you want to shrink? Databases tend to grow as more data gets put in them. It's in their nature.

    Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.

    See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/

    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

  • March 22, 2010 at 7:09 am

    #1137564

    Thanks for the reply, I have the shrink the database because we upgrade the application and lot of data add and deleted also when I recreate the index my log file grow very big, how to control this? that the two reason I want to shrink the database.

  • March 23, 2010 at 4:41 am

    #1138079

    Shrinking a database will have no impact on the log space used when you rebuild an index. If you are rebuilding a large index you will use a lot of log space.

    One of the best things to limit the impact of index rebuilds is to only rebuild the indexes that need rebuilding. Many people just rebuild all indexes, and this is guaranteed to maximise the size of your database and log space.

    Google can find you a number of routines published that can choose which indexes need rebuilding, so you can pick the one you like the most.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Viewing 4 posts - 1 through 3 (of 3 total)

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