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 - 46 through 60 (of 109 total)

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

sjimmo SSChampion Points: 11139 More actions · Answer 1

Paul - here is another link that may explain the recoveryinterval better:

http://msdn.microsoft.com/en-us/library/ms191154(SQL.90).aspx

The recovery interval doesn't necessarily state how often that checkpoint will run but to have it run often enough so that a database will recover on startup within the time established by the recovery interval.

In my case, I have the recoveryinterval set to 5 on several large and busy databases. This means that in theory SQL Server will recover the database within 5 minnutes. As a result, the checkpoint will occur frequently enough, based upon the number of transactions, to ensure that it can do this.

There are the occaisions where something has run for a very long time (ie 1 hr) and has not yet committed. If something happens to SQL Server causing it to go down during this time you will find that the transactio log may have grown to a considerable size and teh recovery period seems longer because SQL Server will roll back any uncommitted transactions.

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 2

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?

In simple recovery the log file is checkpointed for re-use after each transaction is completed (commit or rollback), no backup is required. The common reasons for large log files in simple recovery are:

1. Large data import.

2. Large data deletes.

Each of which happens within a single transaction.

--

JimFive

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 3

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?

My recommendation would be to learn how to do "minimally logged" imports. See Books Online for how to do such a thing. BCP and BULK INSERT are both capable of such a thing if you meet all the conditions outlined in Books Online.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 4

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

Actually, it doesn't. It's a major cause of index and data (clustered index) fragmentation not to mention the fragmentation that can occur on the physical disk (depending on your disk system).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

satalaj SSC Veteran Points: 268 More actions · Answer 5

satalaj

SSC Veteran

Points: 268

November 9, 2010 at 9:23 am

#1248377

Is it bad for shrinking log file?

Thanks,Satalaj.

SQLMD Right there with Babe Points: 764 More actions · Answer 6

Coming form more of a development background I enjoyed seeing a basic article on why SHRINK is bad. A quick Google search will show you that it is considered bad practice, but rarely says why. Of course Most of the folks searching for it are trying to figure out how to make their 20GB log file smaller so they can get the database started again and when we...um...they are in that situation they don't care why it is bad as long as it isn't going to make things worse. Our log files are set to a size that will handle our largest imports and fortunately we have enough disk to support this. This means that a lot of the time they are about 90% empty, but at least there is no growth when the imports do happen. I've come from other environments (SMB) where we would shrink the log file on a regular basis because we paid for every GB on our offsite server.

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 7

satalaj (11/9/2010)
Is it bad for shrinking log file?
Thanks,Satalaj.

See Gilamonster's post earlier. You should not be regularly shrinking the log.

RD Francis SSCarpal Tunnel Points: 4867 More actions · Answer 8

I have a (somewhat) similar situation to that several others have described.

In my production environment, I have an 80GB DB file. Throughout the course of the week, a 5GB log file is more than enough for our activity.

However, when our weekly optimization process is run, that log file does grow - the weekend, it got to 35 GB.

I have enough space in production to allow for this, as is appropriate. However, I backup our database nightly and restore it to create a QAS DB, and a Reporting DB. On the QAS and Reporting servers, I don't have enough space for a 35GB file with 30GB of free space.

So, I am (trying) to shrink our log files before taking the full backup after the optimization job is complete.

The problem I run into is that DBCC SHRINKFILE on Log files doesn't seem to work in a particularly straightforward fashion. As often as not, I ask if to shrink the file down to 5GB of free space - and the file's still 30GB. [Note: I actually have two log files, but have been considering dropping one; I may look at the idea proposed here, setting the main log file to fixed size, and leaving the other variable, and then shoving all the transactions to the fixed file and shrinking the variable one. I'd want to keep track of how often this needed to happen, so I'd know when to look at growing the fixed one].

So, our DBA team (who are responsible for getting the job run) go ahead and shrink the file to minimum size - which works, but then we do usually need to grow the file at some point before that optimization job.

Thanks for the various links - I'll be reading the related articles on log file structure, to see if there's anything I've missed.

R David Francis

DeusExDatum Ten Centuries Points: 1144 More actions · Answer 9

Simon Facer (11/9/2010)
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.

I totally agree Simon, the 64KB cluster at the physical disk level is ideal; This allows 8 SQL I/O writes per Disk I/O, and prevents bottlenecks in the disk queue. My point regarding 8KB divisibility in the auto-growth increment is to prevent SQL from triggering auto-growth of a file halfway through a page write. This may seem insignificant, but if you have OLTP system with a high write transaction load, this page segmentation can build up very quickly, requiring more intense and frequent maintenance to maintain high availability.

Josh Lewis

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 10

GilaMonster (11/9/2010)
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

don't know if it's a bug but we have to run backup log with no_log because we've had log growth even with most of our databases on the simple recovery model

eram50 Grasshopper Points: 21 More actions · Answer 11

What about tempdb?

I've tried the shrink but needed to restart the server for "full" effect

Cliff Jones SSChampion Points: 10547 More actions · Answer 12

Simon Facer (11/9/2010)
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?

I agree Simon, I thought this was a good article and provoked a great discussion.

DeusExDatum Ten Centuries Points: 1144 More actions · Answer 13

alen teplitsky (11/9/2010)
GilaMonster (11/9/2010)
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
don't know if it's a bug but we have to run backup log with no_log because we've had log growth even with most of our databases on the simple recovery model

Alen, a few people on this thread have explained how a simple recovery model work, but I'll reiterate really quick:

A DB in a Simple Recovery Model does in fact still have and use a log file. The log is written from begining to end, and when the end of file is reached, it moves back to the begining of the file and overwrites where ever data is not being held by an open transaction, again moving from being to end.

If you are in a simple recovery model and are still experiencing this kind of growth, I can think of two possible culprits:

1.) If the system / application that writes to this DB is explicitly managing transactions (BEGIN TRAN / COMMIT TRAN) rather than letting SQL manage transactions with auto-COMMIT, you potentially have a bug in code that is not properly commiting transactions. As the log is cycled through, more and more transactions build up until the log file has no re-usable space, because all space is held by open transactions. The log file is then forced to grow for the DB to continue accepting writes.

2.) Do you have any large Import / Archive operations that run against this system? If you have a large transaction that exceeds the amount of re-usable space in the log, the log will be forced to grow because all space has already been allocated to the open transaction, and growth must occur.

Regardless the recovery mode, if auto-grow options are configured, the log will grow when required. As a solution to your problem, either find the bug in code, or if the large transaction scenario seems more likely, note how large the log file grows to, and then modify the starting size of the log to that size + a fixed amount that is roughly 10% of DB size. The transaction can then process without growth occurring, and there is some breathing room incase the transaction becomes larger, or other smaller transactions are also processing.

Josh Lewis

Simon Facer SSCertifiable Points: 5427 More actions · Answer 14

eram50 (11/9/2010)
What about tempdb?
I've tried the shrink but needed to restart the server for "full" effect

You can shrink TempDB; but I would recommend agqainst it if the server is very active, I have (personal experience) encountered performance issues while shrinking TempDB. On the other hand, it doesn't suffer from the same level of problems as User databases after a shrink. Restarting SQL will reset TempDB back to the defined sizes for data and log files. TempDB is cleared and recreated on each SQL Startup.

TempDB has a unique set of options - the best performance for TempDB is to create multiple equally-sized MDF / NDF files, up to 1 file per processor core for SQL, with each file on it's own disk volume (we don't have that luxury, like most DBA's, so all our TempDB files are on one or at best two volumes per server), with identical autogrow settings.

That's a whole different discussion though. BING search on 'SQL Server TempDB Performance', or start here http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-%281230%29-tempdb-should-always-have-one-data-file-per-processor-core.aspx

gsprague SSC Rookie Points: 39 More actions · Answer 15

I have a default filegroup with 6 files (all the same size). I want to empty the last file by migrating the data from that 6th file to the other 5 files:

1) will the data distribute evenly across the other 5 files

2) will the data be fragmented in the remaining 5 files (i.e. should I rebuild all the clustered/non-clustered indexes)?

I'm assuming the answer to both question is "yes", but would like a second opinion.