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

Question

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

Viewing 15 posts - 16 through 30 (of 109 total)

You must be logged in to reply to this topic. Login to reply

giles.clapham SSCrazy Points: 2201 More actions · Answer 1

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 SSC Guru Points: 148809 More actions · Answer 2

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 SSCommitted Points: 1779 More actions · Answer 3

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 SSCertifiable Points: 5427 More actions · Answer 4

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 SSC Enthusiast Points: 136 More actions · Answer 5

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.

greg.schmitz SSC Rookie Points: 41 More actions · Answer 6

This article makes an excellent point, and emphasizes well why we don't want to shrink the data file. The name, I think, is a bit misleading however. While you also want to avoid shrinking the log file, the log file is still something that isn't so heavily impacted by SHRINKFILE, and SHRINKFILE isn't such a bad thing. In the case of a log file where we aren't concerned about fragmentation so much, I would think it more efficient to stick with SHRINKFILE over the alternatives. That, of course, with the caveat of ensuring proper planning pre-sizes the log file to an appropriate size, and proper backup plans are in place to prevent abnormal log growth.

greg.schmitz SSC Rookie Points: 41 More actions · Answer 7

The size of the data file will grow based on growth configuration, and the amount of data you push into it. The log file is a different beast all together.

The way the log file grows is based partly on configuration, but is triggered by the recovery mode you're in. If you are in simple recovery mode, the data in the log file only persists until the transaction the data is a part of completes (is committed or rolled back). Full mode keeps everything in the log until a full, differential, or log file backup is run, as the data is necessary to restore from failure. (Bulk is similar to full, but maintains less information than full. Because it behaves similarly, it is omitted from the rest of my response.)

A common misconception many have is that simple recovery mode doesn't log. It does, it just reuses space. If the database reaches the end of the log, it goes back to the beginning and starts rewriting over old data not still held by an open transaction.

In order to reduce log size on a database not in simple recovery, you would want to make sure you have a well planned maintenance plan in place... regularly occurring full and transaction log backups (optionally placing differential backups as well). If the log file is growing under simple recovery, you want to make sure the transactions called against the database are completing (committing or rolling back). The only way the transaction log maintains data in simple recovery is when the data is still needed to complete a transaction.

James Goodwin Hall of Fame Points: 3691 More actions · Answer 8

Shrinking a log file isn't that bad. There is no issue with fragmentation of the log file. The only problem with it is that it doesn't solve the underlying problem. The only times I have needed to shrink log files is:

A) Log backups have failed so the log keeps growing

B) An unusually large data transaction has occurred, usually related to upgrades or mass data copying.

As long as your log file is sized properly there is no reason to not shrink the log file after these kinds of one-off situation. If you are doing this once a month, however, fix the problem.

--

JimFive

giles.clapham SSCrazy Points: 2201 More actions · Answer 9

Thanks Jim Five, that's exactly what we do and will continue to do.

Giles

george25 Mr or Mrs. 500 Points: 538 More actions · Answer 10

Guys

I've yet to see a convincing argument for not shrinking log files for databases using the simple recovery model where the transaction log is not crucial - the databases are largely static and very large, hence the use of simple recovery.

I should also add that I am using DPM to back up the databases.

Regards

George25

Simon Facer SSCertifiable Points: 5427 More actions · Answer 11

chethan.cn (11/8/2010)
hi
Does dbccshinkfile( 'filename',mb) wll take care of fragmentation

Unfortunately, No. The ShrinkFile command almost invariably causes data fragmentation.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

george25 (11/9/2010)
I've yet to see a convincing argument for not shrinking log files for databases using the simple recovery model where the transaction log is not crucial

1) They will just grow again

2) When the do grow, performance will be impacted as SQL has to grow and zero the file

3) If your autogrow increments are not carefully chosen, you will be internally fragmenting the log when it grows - slows down backups (and I mean full backups as well as log backups)

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

sjimmo SSChampion Points: 11139 More actions · Answer 13

Shrinking a database file (either using SSMS or T-SQL code) should be avoided if at all possible. In an ideal situation, it is much better to size the file appropriately in the first place, and then grow it as necessary. Of course, in the real world there are always going to be situations where files are over-allocated, bloated, or data is deleted or compressed, and we need to recover the space from the file. When that happens, we are limited to the SHRINKFILE command

After reading and rereading the article, I am very disappointed in the simplicity that the writer takes in telling us about a very important issue, that of arbitrarily shrinking a data/log file because it became larger than we may want.

There is some talk of what SQL Server does with the data inside the data file, but he does not address the fact that the physical files on the drive also become fragmented.

Index fragmentation can easily be fixed by running things like DBCC INDEXDEFRAG or DBCC REINDEX depending upon the amount of fragmentation. One may even run DBCC UPDATEUSAGE or sp_updatestats and sees a performance increase.

But EVERY TIME that you expand/shrink a database file you are fragmenting the physical file(s) on the hard drive, which also create performance issues. If your database is small enough then you are able to do things like turning off SQL Server and running a process to defrag the drive which will physically rearrange the files on the hard drive in order to make them contiguous. Unfortunately, there are many out there who must get this right the first time because their database or warehouse is so large that running tools such as this are simply not practical.

Non-contiguous physical files are just as problematic as fragmented data in that the heads of the drive are moving more to find/read information from the file than is necessary. The more the heads have to work the slower the performance and as we all know, the slowest place of a database is the hard drives.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

george25 Mr or Mrs. 500 Points: 538 More actions · Answer 14

Guys

I hate to keep banging on about this but my databases use simple recovery. Log backups are, pardon the pun, simply not an issue.

Regards

George25

Simon Facer SSCertifiable Points: 5427 More actions · Answer 15

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:
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.

A couple of quick points-

(1) be careful about how you set the Log file Autogrowth, Make sure your Autogrow increment is appropriate - see http://technet.microsoft.com/en-us/library/ms179355.aspx.

(2) Your Data and Log file disk volumes should be formatted with 64K cluster size, not 8K cluster size. Look here http://msdn.microsoft.com/en-us/library/dd758814(SQL.100).aspx for a primer on Partition Alignment for SQL Server, even though this particular page is written for SQL 2008, it applies to earlier editions as well. The relevant line is just below figure 3, towards the bottom of the page:

The file allocation unit size (cluster size) recommended for SQL Server is 64 KB

If you Bing sql server best practices for disks you will find plenty of other references as well.