Home Forums SQL Server 2005 Administering DBCC CHECKDB - how use it in a maintenance plan ? RE: DBCC CHECKDB - how use it in a maintenance plan ?

  • 2.Rebuild Index [Expect System Database]

    3.Shrink Database [Expect System Database]

    That's a combination I generally refer to as a waste of time. The shrink will cause massive fragmentation of your indexes, easily up to 90%. You're spending time, CPU and IOs to put the indexes in order (possibly growing the database) and then you're spending more time, cpu and IOs shrinking the file and shuffling the indexes. Next time any data is added, SQL will spend even more time, CPU and IOs growing the file so it has space to work.

    Also, repeated shrinks and grows can easily cause fragmentation at a file system level, requiring a disk defragment 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