Post reply

Shrink Data file with truncateonly

  • February 19, 2013 at 2:30 am

    #270989

    Hi,

    I wanted to shrink a data file with the truncate only clause.Will i require to rebuild my indexes?

  • February 19, 2013 at 2:36 am

    #1589078

    mwagh (2/19/2013)

    Hi,

    I wanted to shrink a data file with the truncate only clause.Will i require to rebuild my indexes?

    Shrinking data file is not recommended as it will create fragmentation and it affects the performance. What's the reason you are trying to shrink?

    SueTons.

    Regards,
    SQLisAwe5oMe.

  • February 19, 2013 at 2:44 am

    #1589081

    thanks for your reply.I know it is not recommended as it will cause fragmentation of indexes but i need to free up some space.

    As per http://msdn.microsoft.com/en-us//library/ms189493.aspx

    the TruncateOnly clause "Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file"

    So i was hoping that this should not create fragmentation as there is no movement of pages. :unsure:

  • February 19, 2013 at 2:51 am

    #1589083

    Correct, shrink with truncate only doesn't fragment indexes, but it also may not reduce space usage if the space and the end of the file is in use.

    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

  • February 19, 2013 at 2:55 am

    #1589088

    Thanks!! works in my case as i have a lot initially allocated space which was never touched.

  • February 19, 2013 at 5:13 am

    #1589142

    mwagh (2/19/2013)

    Thanks!! works in my case as i have a lot initially allocated space which was never touched.

    Hi, sorry, I was not aware of this, anyway, with truncate only option only available for sql 2005 right? because I know they took away this option for 2008 and above. Just curious which version you are using.

    SueTons.

    Regards,
    SQLisAwe5oMe.

  • February 19, 2013 at 5:15 am

    #1589145

    SQLCrazyCertified (2/19/2013)

    mwagh (2/19/2013)

    Thanks!! works in my case as i have a lot initially allocated space which was never touched.

    Hi, sorry, I was not aware of this, anyway, with truncate only option only available for sql 2005 right? because I know they took away this option for 2008 and above. Just curious which version you are using.

    SueTons.

    TRUNCATE ONLY been removed from the BACKUP LOG command, but not from DBCC SHRINKFILE

  • February 19, 2013 at 5:17 am

    #1589148

    anthony.green (2/19/2013)

    SQLCrazyCertified (2/19/2013)

    mwagh (2/19/2013)

    Thanks!! works in my case as i have a lot initially allocated space which was never touched.

    Hi, sorry, I was not aware of this, anyway, with truncate only option only available for sql 2005 right? because I know they took away this option for 2008 and above. Just curious which version you are using.

    SueTons.

    TRUNCATE ONLY been removed from the BACKUP LOG command, but not from DBCC SHRINKFILE

    Got it. Thank you.

    SueTons.

    Regards,
    SQLisAwe5oMe.

  • February 19, 2013 at 5:23 am

    #1589152

    Hi,

    It is still there in Shrink File .I use 2008 R2.

  • February 19, 2013 at 6:54 am

    #1589190

    SQLCrazyCertified (2/19/2013)

    Hi, sorry, I was not aware of this, anyway, with truncate only option only available for sql 2005 right? because I know they took away this option for 2008 and above.

    SQL 2000, SQL 2005, SQL 2008, SQL 2008 R2, SQL 2012, not on the deprecation list, so 2 more versions at least, personally I doubt it'll be removed, too useful and no good reason to remove it. Maybe earlier too, never worked on SQL 7 though.

    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 10 posts - 1 through 9 (of 9 total)

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