SQL 2008 R2 Log Files Grow Despite Being In Simple Recovery

Even in simple mode the transaction is used while a transaction is "in flight". A database will always need the ability to roll back if a transaction fails part way through.

It just means that there is a very large transaction that happens at some point between your shrinks.

How often you do a FULL backup and T-log backup after that? T-log backup is where the log space is released back for usage and until then it will only grow.

my bad...you are under simple recovery mode so SQL doc says:
 
"No log backups required.
Automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space."

Still you need to do a FULL db backup (daily) and I suggest use command below to reclaim log space:

USE YourDB
GO

DBCC SHRINKFILE (N'YourDB_log' , 0, TRUNCATEONLY);
GO

Icohan, thx.

It's in simple recovery and it has daily full backups. So I should probably check tomorrow to see if the log files have shrunk.

It doesn't seem that I'd need to use the DBCC command if SQL is suppose to recover the space automatically, or am I wrong about that? I had thought the whole idea of placing a db in simple mode was to not have to manage the log files.

SQL Server will not recover the space unless the database is set to auto shirnk (Word of advise, dont use autoshrink it will constantly fragment your database)

The database files / log files will not shrink after a full backup.

DBCC SHRINKFILE / SHRINKDATABASE are the commands used to shrink the database / log files (Even if you use right click shrink, this is what goes on under the hood). Unfortunately it doesnt matter how many times you shrink them if you have a transaction running that requires the space it will grow the log file or fail.

If you want to stop the log file from growing then you will need to find & minimize this specific transaction. Failing this, you shouldnt really shrink the log file as if in the mean time other files take the space required by the file growth operation your transaction will then fail and roll back which could obviously cause bigger problems.

@lcohan:

From MDSN library:

"TRUNCATEONLY(in DBCC SHRINKFILE) is applicable only to data files. The log files are not affected."

Oh, yeah, that's right--backups just truncate the files, correct? Files won't shrink unless you use the SHRINKDATABASE or SHRINKFILE command.

I could create an alert to shrink the log files automatically if over xx MB's via a job. Any harm in that?

No transaction log backups empty the log files of committed and written transactions. Database backups have no effect on the transaction log at all apart from the fact that it runs a checkpoint which indirectly effects it.

That may be an option, but in all honesty i would do 1 of 2 things

1) Find the transaction that is growing the log and try to minimize the impact it is having (For example if it is emptying a table completely by running a delete statement, change it to a truncate).

2) Leave it alone. You are causing yourself more hassle than you need to as it will always grow anyway.

Just to clarify, if the database is in simple recovery mode then the log file is automatically shrunk after the transactions are comited. The only concern is that the hard drive where the database log file is has to have enough room for the log to grow on huge transaction.

If the database is in full recovery mode that you have to do regularly transaction log backups which will release the hold on the space inside the file and if the log file is too big than it can be shrunk:

DBCC SHRINKFILE(log_file_name, 2)

>> I run a shrink db job weekly. <<

That in itself could be increasing the log size, possibly a log for a very large db.

It also fragments tables, sometimes *very severely*.

So don't do that.

You might need to shrink the log once and reallocate it to get fewer VLFs.


See how many rows this command returns:

DBCC LOGINFO



To see how much free space is in your db, you can issue this command:

DBCC SHOWFILESTATS

If that shows that much of the files are used, there isn't much point in shrinking anyway.

Just to clarify, if the database is in simple recovery mode then the log file is automatically shrunk after the transactions are comited.

This is wrong, the space used so that the transaction is able to roll back is removed from the transaction log yes, but the log file is not shrunk unless the database is set to autoshrink which is not set by default and is a bad thing to do anyway.

Hi

actually if you check your log file will have free space, as other experts noted you have some heavy transaction which grows your file between shrinks. and regular shrinking and growing of log file is not good for performance, check the auto growth option on your log file, and make it something like 10-30% to minimize how many times file will grow. after this you can shrink your file more often, but 2 GB log file is not a very bad thing

>> check the auto growth option on your log file, and make it something like 10-30% <<

I disagree with that just because from experience a % growth amount always seems to perform more slowly.  Also, as the log grows, so do the size of every addition, and this can get out of hand.

I suggest specifying a specific growth amount for the log, such as 200MB (or whatever amount you prefer, but not too small).

If fast file initialisation is enabled then it speeds up all growth activities.

You can enable fast file initialisation by add the SQL Server service account to the "Perform Volume Maintenance Tasks" under "User Rights Assignment" in your local security policy.

>> If fast file initialisation is enabled then it speeds up all growth activities. <<

No; it does NOT speed up log growth.  NOTHING can prevent SQL from having to pre-initialize log growth space.

I stand corrected, i thought it was applicable to all files.

http://blogs.msdn.com/b/sql_pfe_blog/archive/2009/12/23/how-and-why-to-enable-instant-file-initialization.aspx

Btw, did you ever run these commands I suggested earlier:

DBCC LOGINFO

DBCC SHOWFILESTATS