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

Question

Post reply

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

Viewing 15 posts - 31 through 45 (of 109 total)

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

george25 (11/9/2010)

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.

And?

Everything that I stated in my reply to your question applies no matter what recovery model you 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

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

george25

Mr or Mrs. 500

Points: 538

November 9, 2010 at 6:48 am

#1248240

Simon

I've read loads of stuff about appropriate autogrowth increments. I get the fact that you should not use percentages but have never come to a proper conclusion about appropriate increments. What is your take on this?

Regards to all who are contributing to this

George25

magasvs SSCertifiable Points: 7660 More actions · Answer 3

You can actually improve Transaction Log's performance by shrinking it and resizing it properly (when database has many VLFs). Kimberly Tripp has great blog about Log's performance: http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx and

http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx

Simon Facer SSCertifiable Points: 5427 More actions · Answer 4

sjimmo (11/9/2010)

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,

you make a very good and often forgotten point about File Fragmentation, however AutoGrow and File Fragmentation wasn't what I was trying to convey. I was simply trying to raise awareness about a common and often misunderstood command - the SHRINKFILE command as it relates to Data Files (not Log Files - see a previous post of mine in this thread).

And I would also counter that if you are in an Expand / Shrink cycle with any database, you should look at your maintenance procedures to determine why you are doing that, and also check to see if you have AutoShrink turned on (and then turn it off if so).

Simon Facer SSCertifiable Points: 5427 More actions · Answer 5

george25 (11/9/2010)

Simon

I've read loads of stuff about appropriate autogrowth increments. I get the fact that you should not use percentages but have never come to a proper conclusion about appropriate increments. What is your take on this?

Regards to all who are contributing to this

George25

Unfortunately, that's a YMMV (Your Mileage May Vary) question. Its dependant on several things - the update (insert / update / delete) load you will get on the database, how often you purge data, how much disk space you have, etc. The best answer I can give is for you to try to avoid AutoGrow events by sizing the Files appropriately. If I don't have good growth projections, I tend to use a simple MB increment of multiples of 64MB (64, 128, 256, 512, 1024, 2048, 4096) depending on the size of the database, generally never more than 10% of the original size.

osgcurt SSC Enthusiast Points: 164 More actions · Answer 6

Good Article. It's one of the on going arguments as to how to handle Large transaction log files.

Problems come though when hosting companies charge clients by the megabyte for disk space.

Or Virtual servers are not provisioned with proper Disk configurations.

I have received many calls from "Administrators" that a SQL based Application is crashing and very often the disk are out of space because of a almost empty Transaction log that is 20GB is size and the MDF is 500MB and it's on the system partition.

At that point BOL says to shrink it.

Let's face it. Bottom liners will mess up a SQL server more often than not.

/:<

sjimmo SSChampion Points: 11139 More actions · Answer 7

Simon

I was simply trying to raise awareness about a common and often misunderstood command - the SHRINKFILE command as it relates to Data Files (not Log Files - see a previous post of mine in this thread).

And I would also counter that if you are in an Expand / Shrink cycle with any database, you should look at your maintenance procedures to determine why you are doing that, and also check to see if you have AutoShrink turned on (and then turn it off if so)

Simon, it was a good article, and maybe should have been part 1 of multiple parts. Unfortunately, from many threads on this very subject here on SSC you see many of the same questions/arguments. After reading your article, again you see many of the same arguments with more who just seem to dig in their heels.

IMO if you are going to address the subject, and talk about ome of the impact that you need to address all of the impact. Then there are those exceptions to the rule, maybe naming a few examples. (I know, it is easy to critique/criticize others.) Many come here to learn something new or maybe they are having an issue and are looking for an answer. Your article may come up in a search on this problem yet doesn't address the issue.

As you have stated, "a common and often misunderstood command", you have the perfect opportunity here. You are the subject expert.

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

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

george25 (11/9/2010)

Simon

I've read loads of stuff about appropriate autogrowth increments. I get the fact that you should not use percentages but have never come to a proper conclusion about appropriate increments. What is your take on this?

Regards to all who are contributing to this

George25

It is YMMV but in general your autogrowth should be set small enough to not overly impact performance and large enough so that you are not constantly growing, which also impacts performance. Ideally, when you see that autogrowth has happened you should determine why and, if necessary, project future growth and manually resize the file to your projected need. You may also want to schedule a maintenance window to allow you to clean up the physical disk to prevent/eliminate file fragmentation at this time.

Autogrow is a convenience feature to prevent unexpected downtime and should be treated like a "Disk 1 is over 80% full." alert. It is a notice to make a plan, not a set and forget item.

--

JimFive

paul.knibbs SSCoach Points: 15320 More actions · Answer 9

GilaMonster (11/9/2010)

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.

I never mentioned log backups. Correct me if I'm wrong, but in simple recovery mode, doing a full backup will checkpoint the log file and allow for re-use. Is there anything else that would cause a checkpoint in simple recovery, or would it just keep growing?

Paulino PP SSC Veteran Points: 292 More actions · Answer 10

giles.clapham (11/9/2010)

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

I have the same scenario as Giles. So, if some one can provide feedback to our question...

Paulino PP

SanDroid SSChampion Points: 10068 More actions · Answer 11

I am really disappointed with this article and it's information.

Lots of it is directly from the BOL or other articles.

All it told me was:

SHRINKFILE BAD. Be afraid, it does things other db maintenance can fix.

NO, wait!

Simon says DO SHRINKFILE like Simon.

SHRINKFILE good like Simon do good.

SHRINKFILE is a low level utilities and it is good when needed and done properly.

We should educate other DBAs on what the tools are for, and how to use them.

sjimmo SSChampion Points: 11139 More actions · Answer 12

Paul

I never mentioned log backups. Correct me if I'm wrong, but in simple recovery mode, doing a full backup will checkpoint the log file and allow for re-use. Is there anything else that would cause a checkpoint in simple recovery, or would it just keep growing?

A checkpoint occurs regularly in SQL Server, regardless of the recovery mode. Checkpoints take all modified data (Insert/Update/Delete) which has been written to the transactin log and publishes it to the database. This is a great feature for performance. Many years ago you read/wrote directly to the database files which became problematic but that's enough history.

When a database dump occurs, it forces all commited transactions in the transaction log to be written to the database. While the database dump occurs transactions continue to be written to the transaction log and once the dump completes all of the newly committed transactions are written to the database dump.

Additionally, you can read more in BOL or at http://msdn.microsoft.com/en-us/library/ccdfc689-ad4e-44c0-83f7-0f2cfcfb6406.aspx as well as many other web sites on the subject.

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

Simon Facer SSCertifiable Points: 5427 More actions · Answer 13

SanDroid (11/9/2010)

I am really disappointed with this article and it's information.

Lots of it is directly from the BOL or other articles.

All it told me was:

SHRINKFILE BAD. Be afraid, it does things other db maintenance can fix.

NO, wait!

Simon says DO SHRINKFILE like Simon.

SHRINKFILE good like Simon do good.

SHRINKFILE is a low level utilities and it is good when needed and done properly.

We should educate other DBAs on what the tools are for, and how to use them.

I'm sorry you didn't like the article, but as I previously stated, my intention was to raise awareness of the issues around the use of the ShrinkFile command, and give some options if you have to use it. It probably is mostly in BOL, but how many of us have read BOL cover-to-cover?

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 14

paul.knibbs (11/9/2010)

I never mentioned log backups. Correct me if I'm wrong, but in simple recovery mode, doing a full backup will checkpoint the log file and allow for re-use. Is there anything else that would cause a checkpoint in simple recovery, or would it just keep growing?

A full backup does a checkpoint before it starts, but in regular operation checkpoint runs automatically every minute or so. Hence in simple recovery there is no dependency on any backups to make log space reusable.

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

paul.knibbs SSCoach Points: 15320 More actions · Answer 15

sjimmo (11/9/2010)

A checkpoint occurs regularly in SQL Server, regardless of the recovery mode.

.

.

.

Additionally, you can read more in BOL or at http://msdn.microsoft.com/en-us/library/ccdfc689-ad4e-44c0-83f7-0f2cfcfb6406.aspx as well as many other web sites on the subject.

Thanks. Seems that the "recoveryinterval" setting is the significant one--apparently by default that'll checkpoint a database approximately every minute; not something I'd ever come across.