Shrink Data file with truncateonly

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Shrink Data file with truncateonly

Shrink Data file with truncateonly

Rate Topic

Display Mode

Topic Options

Author

Message

mwagh

Posted Tuesday, February 19, 2013 2:30 AM

Forum 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

SQLCrazyCertified

Posted Tuesday, February 19, 2013 2:36 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 312, Visits: 1,868

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.

Post #1421490

mwagh

Posted Tuesday, February 19, 2013 2:44 AM

Forum 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. Unsure

Post #1421494

GilaMonster

Posted Tuesday, February 19, 2013 2:51 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 37,739, Visits: 30,013

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

mwagh

Posted Tuesday, February 19, 2013 2:55 AM

Forum 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

SQLCrazyCertified

Posted Tuesday, February 19, 2013 5:13 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 312, Visits: 1,868

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.

Post #1421570

anthony.green

Posted Tuesday, February 19, 2013 5:15 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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.

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

SQLCrazyCertified

Posted Tuesday, February 19, 2013 5:17 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 312, Visits: 1,868

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.

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

Got it. Thank you.

SueTons.

Post #1421579

mwagh

Posted Tuesday, February 19, 2013 5:23 AM

Forum 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

GilaMonster

Posted Tuesday, February 19, 2013 6:54 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 37,739, Visits: 30,013

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 »

Permissions