Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Shrink Data file with truncateonly Expand / Collapse
Author
Message
Posted Tuesday, February 19, 2013 2:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 3:51 AM
Points: 5, Visits: 31
Hi,
I wanted to shrink a data file with the truncate only clause.Will i require to rebuild my indexes?
Post #1421486
Posted Tuesday, February 19, 2013 2:36 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, December 5, 2014 8:02 PM
Points: 422, Visits: 2,662
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.
Post #1421490
Posted Tuesday, February 19, 2013 2:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 3:51 AM
Points: 5, Visits: 31
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.
Post #1421494
Posted Tuesday, February 19, 2013 2:51 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:55 AM
Points: 40,620, Visits: 37,087
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 2008, MVP
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

Post #1421496
Posted Tuesday, February 19, 2013 2:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 3:51 AM
Points: 5, Visits: 31
Thanks!! works in my case as i have a lot initially allocated space which was never touched.
Post #1421501
Posted Tuesday, February 19, 2013 5:13 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, December 5, 2014 8:02 PM
Points: 422, Visits: 2,662
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.
Post #1421570
Posted Tuesday, February 19, 2013 5:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:45 AM
Points: 5,221, Visits: 5,120
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




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1421576
Posted Tuesday, February 19, 2013 5:17 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, December 5, 2014 8:02 PM
Points: 422, Visits: 2,662
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.
Post #1421579
Posted Tuesday, February 19, 2013 5:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 3:51 AM
Points: 5, Visits: 31
Hi,

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

Post #1421583
Posted Tuesday, February 19, 2013 6:54 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:55 AM
Points: 40,620, Visits: 37,087
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 2008, MVP
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

Post #1421625
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse