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


Why SHRINKFILE is a very bad thing, and what to do about it.


Why SHRINKFILE is a very bad thing, and what to do about it.

Author
Message
george25
george25
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 71
I have quite a few very large databases and, like Giles above, I need to shrink the log files after large amounts of data are imported into the databases. All the databases use the simple recovery model. Is it such a bad thing to shrink the log file when using the simple recovery model?
paul.knibbs
paul.knibbs
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2605 Visits: 6232
george25 (11/9/2010)
I have quite a few very large databases and, like Giles above, I need to shrink the log files after large amounts of data are imported into the databases. All the databases use the simple recovery model. Is it such a bad thing to shrink the log file when using the simple recovery model?


It's a bad idea using ANY recovery model. Shrinking the log file will almost certainly mean it just has to grow again, and that just causes internal fragmentation in the log file which slows everything down. So long as you're taking regular backups then the log file should be at the size it needs to be to handle your largest transactions, which is where you want it to be!
george25
george25
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 71
Paul

So, bearing in mind that my log files have all been shrunk, would you advise me to recreate them or just stop shrinking them?

Regards

George25
paul.knibbs
paul.knibbs
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2605 Visits: 6232
I would just stop shrinking them for the moment, and make sure you have regular backups so they don't keep growing out of control.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89323 Visits: 45284
paul.knibbs (11/9/2010)
and make sure you have regular backups so they don't keep growing out of control.


Log backups are not necessary (and won't run) in simple recovery, which George indicated his databases are in.

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


giles.clapham
giles.clapham
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 437
Hi Paul,
I appreciate your comments and advice but just to clarify, and I'm open to being educated on this, my DBs are in Simple recovery they get backed up (Full) frequently.
Occasionally we have the need to import large amounts of data which needs some form of processing and validation and we end up with a bloated transaction log. An empty but bloated transaction log.
This transaction log now gets backed up as part of the Full backup which takes additional time and resources.
So would you still say shrinking the log file back to it's pre mass import size is a bad thing?
Giles
John Mitchell-245523
John Mitchell-245523
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14508 Visits: 15980
giles.clapham (11/9/2010)
Hi Paul,
I appreciate your comments and advice but just to clarify, and I'm open to being educated on this, my DBs are in Simple recovery they get backed up (Full) frequently.
Occasionally we have the need to import large amounts of data which needs some form of processing and validation and we end up with a bloated transaction log. An empty but bloated transaction log.
This transaction log now gets backed up as part of the Full backup which takes additional time and resources.
So would you still say shrinking the log file back to it's pre mass import size is a bad thing?
Giles


Yes, indeed. It's only the data in the file that gets backed up, not the file itself. If your log is empty, backing it up will have no impact, no matter how much space the log file occupies on disk.

John
Rajat Jaiswal-337252
Rajat Jaiswal-337252
SSC-Enthusiastic
SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

Group: General Forum Members
Points: 159 Visits: 398
arr.nagaraj (11/9/2010)


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.



Hi,
But I have problem related performance
that's why i can not reschedule log shipping job it is running in night only.
Simon Facer
Simon Facer
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1741 Visits: 724
I think I missed a very important point in the article - that SHRINKFILE on a Log file ISNT the performance killer that shrinking a data file is. The problems come from moving data around inside the file(s) causing fragmentation.
Shrinking a Log file is a very different operation to shrinking a data file. You can only shrink a Log file down to the boundary of the last used VLF (Virtual Log File). Data (i.e. log records, not data per se) doesn't get moved around in a log file in a shrink operation. You can see this by the fact that a Shrink operation on a Log file often doesn't clear all the space you were hoping for - SQL has identified the last VLF that has an active transaction, and that's how far it will shrink the file to, no matter how much free space there is before that VLF. This is a good explanation about Log file architecture http://technet.microsoft.com/en-us/library/ms179355.aspx.



ron.buchanan.us
ron.buchanan.us
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 39
Joshua T. Lewis-378876 (11/9/2010)
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.


I think these are good suggestions. The only thing I would add to item 2 is that there should be three+ files and filegroups. The first is PRIMARY, and your system objects files. The second is the main data file, and the third is indexes.
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