SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Shrink Data file with truncateonly


Shrink Data file with truncateonly

Author
Message
mwagh
mwagh
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 31
Hi,
I wanted to shrink a data file with the truncate only clause.Will i require to rebuild my indexes?
SQLisAwE5OmE
SQLisAwE5OmE
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2982 Visits: 3075
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.
mwagh
mwagh
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 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. Unsure
GilaMonster
GilaMonster
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218843 Visits: 46279
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


mwagh
mwagh
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 31
Thanks!! works in my case as i have a lot initially allocated space which was never touched.
SQLisAwE5OmE
SQLisAwE5OmE
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2982 Visits: 3075
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.
anthony.green
anthony.green
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23768 Visits: 6519
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
When a question, really isn't a question - Jeff Smith
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


SQLisAwE5OmE
SQLisAwE5OmE
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2982 Visits: 3075
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.
mwagh
mwagh
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 31
Hi,

It is still there in Shrink File .I use 2008 R2.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218843 Visits: 46279
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search