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 12345»»»

Why SHRINKFILE is a very bad thing, and what to do about it. Expand / Collapse
Author
Message
Posted Monday, November 8, 2010 9:21 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 8:40 AM
Points: 1,568, Visits: 666
Comments posted to this topic are about the item Why SHRINKFILE is a very bad thing, and what to do about it.


Post #1017583
Posted Monday, November 8, 2010 11:59 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 9, 2010 10:08 PM
Points: 133, Visits: 35
hi
Does dbccshinkfile( 'filename',mb) wll take care of fragmentation
Post #1017626
Posted Tuesday, November 9, 2010 12:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 8:31 AM
Points: 110, Visits: 310
Simon Facer (11/8/2010)
Comments posted to this topic are about the item <A HREF="/articles/SHRINKFILE/71414/">Why SHRINKFILE is a very bad thing, and what to do about it.</A>


Hi Simon,
Thanks for such a nice explaination. I have some diffrent case hope you will give suggestion for this.
I did logShipping for a large database now the file size increased vastly so i have used shirnkfile with truncate only option so that my logshipping will not disturb. but size of ldf file increase up to 40 to 60 GB.
Any idea or guidance to reduce the file size without affecting LogShipping.

Thanks
Raj

Post #1017627
Posted Tuesday, November 9, 2010 12:15 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 11:30 AM
Points: 587, Visits: 145
In an effort to curb the necessity of shrinking, I have always taken a few steps whenever setting up a DB:

1.) Always separate by partition (or even physical disk and controller) the three key portions of a database; Data, indexes, and logs. Also, if you have the resources, put the tempdb on a separate partition; This can frequently be the culprit of disk space issues, and the only way to shrink is a restart of the SQL Server.

2.) Using the separated partition schema, build a secondary file and file group in the DB, intended strictly for indexes. When building any nonclustered indexes, always build them to this filegroup. CAUTION: If you make the mistake of building a primary key or clustered index to your "Indexes" file group, you will not only build the index there, but will also cause all data in the object the index is built on to move into the secondary file group.

3.) Build two ldf files on any DB running in a "Full" Recovery model. The first file is set at a fixed size with no auto-growth allowed, and the second is configured very small, but with auto-growth allowed. Monitor your logs through a full business cycle, and you will have an idea how large your fixed file should be resized to. When you see the second log file begin to grow again, you know that your transaction load has increased, and the fixed file needs to grow again.

As a side note that applies to all of this, when configuring auto-growth, I would always recommend using a fixed MB as opposed to a percentage, and further, make sure that your fixed MB growth size is divisible by 8KB (This is the size of a single SQL I/O write). This should prevent page segmentation due to auto-growths.

Just my two cents, but in my experience, having everything distributed and compartmentalized like this makes managing growth of a DB much less cumbersome, and also has a positive impact on performance.


Joshua T. Lewis
Post #1017631
Posted Tuesday, November 9, 2010 12:21 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 11:30 AM
Points: 587, Visits: 145
Simon,

Just as an FYI, Microsoft has depreciated the TRUNCATE_ONLY option in SQL 2008, and you have to use a different option in the BACKUP LOG command:

BACKUP LOG <database> TO DISK='NUL:'

There is an upside, but also a huge risk. The upside is after the backup to DISK='NUL:', you will not receive the log backup failures until a new full backup is taken. The huge risk is that log backups will continue to succeed, but you will lose the ability to recover within the timeframe the DISK='NUL:' backup was taken for, until another full backup is taken, so it effectively invalidates all transactional backups that follow it.


Joshua T. Lewis
Post #1017634
Posted Tuesday, November 9, 2010 1:44 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:12 AM
Points: 1,610, Visits: 5,482
Joshua T. Lewis-378876 (11/9/2010)
Simon,

Just as an FYI, Microsoft has depreciated the TRUNCATE_ONLY option in SQL 2008, and you have to use a different option in the BACKUP LOG command:


He didn't use TRUNCATE_ONLY as part of a BACKUP command, he used it in a DBCC SHRINKFILE--I can't find any mention that using it in that way is deprecated.
Post #1017664
Posted Tuesday, November 9, 2010 1:50 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:03 PM
Points: 42,485, Visits: 35,554
Joshua T. Lewis-378876 (11/9/2010)
Simon,

Just as an FYI, Microsoft has depreciated the TRUNCATE_ONLY option in SQL 2008, and you have to use a different option in the BACKUP LOG command:

BACKUP LOG <database> TO DISK='NUL:'


The replacement for Backup log ... truncate only is a switch to simple recovery model, not a backup to the nul device.

A backup to the nul device is completely equivalent to taking a log backup and then deleting the backup file. It is NOT a replacement for backup log ... truncate only and quite honestly I would question the logic of anyone using that



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 #1017670
Posted Tuesday, November 9, 2010 1: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:03 PM
Points: 42,485, Visits: 35,554
paul.knibbs (11/9/2010)
He didn't use TRUNCATE_ONLY as part of a BACKUP command, he used it in a DBCC SHRINKFILE--I can't find any mention that using it in that way is deprecated.


It's not deprecated.

It is, however, only applicable to data files, not to log files.



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 #1017671
Posted Tuesday, November 9, 2010 2:22 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 25, 2014 9:41 AM
Points: 66, Visits: 336
I've come from the school of thought that shrinking a database is only putting it into a state where it has to grow again and hence should be avoided, so your article was very interesting and important reading.

However I've occasionally run DBCC SHRINKFILE on the Log file, usually after a large data migration.
So what considerations should I take into account if I run DBCC SHRINKFILE on the Log File, other that it having to grow again?

Thanks
Giles
Post #1017679
Posted Tuesday, November 9, 2010 3:02 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: 2 days ago @ 8:39 AM
Points: 488, Visits: 1,334


Hi Simon,
Thanks for such a nice explaination. I have some diffrent case hope you will give suggestion for this.
I did logShipping for a large database now the file size increased vastly so i have used shirnkfile with truncate only option so that my logshipping will not disturb. but size of ldf file increase up to 40 to 60 GB.
Any idea or guidance to reduce the file size without affecting LogShipping.

Thanks
Raj



Taking frequent t-log backups using log shipping should reduce/stop ldf growth.
Shrink of log file doesnt distrub log shipping.


Regards,
Raj

Strictlysql.blogspot.com
Post #1017701
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse