Post reply

DBCC SHRINKFILE Aborted. Does it matter ?

  • December 30, 2008 at 7:24 pm

    #199551

    If I run this, which reorganizes data, and is cancelled before completion because the business day is approaching, will it cause any problems ??

    I want to free up space on the primary data file and get it smaller. It won't need to regrow because it's got a max size and other data files (My_Data1, My_Data2) can take the growth.

    DBCC SHRINKFILE (N'My_Data' , 600000)

    I'm thinking of running it on New Years Day when there's no activity, but if it runs into Friday morning, I don't want to impact production.

  • December 30, 2008 at 9:01 pm

    #919420

    Hello,

    According to SQL 2005 BOL: “DBCC SHRINKFILE operations can be stopped at any point in the process, and any completed work is retained”.

    I believe the same is true for SQL 2000 as well. Which version are you using?

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • December 31, 2008 at 12:33 am

    #919472

    Be sure to rebuild all of your indexes afterwards. Shrink badly fragments indexes.

    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

  • December 31, 2008 at 8:12 am

    #919666

    I'm running 2000 SP4

  • December 31, 2008 at 8:22 am

    #919679

    GilaMonster (12/31/2008)

    Be sure to rebuild all of your indexes afterwards. Shrink badly fragments indexes.

    Does that still apply if the indexes are on another drive ? I'm thinking of shrinking Data.mdf in the Primary filegroup on the F drive and most of my big indexes are in a different filegroup called Indexes on the G drive (physically different drive)

  • December 31, 2008 at 8:27 am

    #919685

    homebrew01 (12/31/2008)

    I'm thinking of shrinking Data.mdf in the Primary filegroup on the F drive and most of my big indexes are in a different filegroup called Indexes on the G drive (physically different drive)

    So what's in the primary filegroup? The tables themselves? If they are clustered indexes, then yes, the fragmentation issue still applies. If they are all heaps, then they'll still get fragmented (extent fragmentation), but it can't be fixed.

    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

  • December 31, 2008 at 8:52 am

    #919701

    I agree with Gail. The clustered indexes are probably more important, and you should have them on your tables, so you'll need to rebuild.

    And you'll need space to rebuild.

  • December 31, 2008 at 8:59 am

    #919702

    For some reason the application that we bought does not use clustered indexes. Most of the non-clustered have been moved to the other file group on G.

    Some unique primary key indexes are still on the primary filegroup, but I assume dropping & recreating them should be done in off-hours ? Otherwise a duplicate row could sneak in ?

  • December 31, 2008 at 1:07 pm

    #919809

    Hello again,

    I have not (yet) seen it written in any MS documentation, but I was led to understand that the quote from BOL goes for SQL 2000 as well as 2005.

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

Viewing 9 posts - 1 through 8 (of 8 total)

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