Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 758
  • Last Modified:

SQL 2008 R2 Log Files Grow Despite Being In Simple Recovery

I had thought placing a database in simple recovery would manage the log file growth to keep them small, but I've noticed databases growing to over 2+ GB in simple recovery. There's no replication or log shipping going on. I've never had extraordinary log file growth in SQL 2005 editions.

I run a shrink db job weekly. Other than that, how do you manage the file growth to keep the files small. I've already checked out http://msdn.microsoft.com/en-us/library/ms365418.aspx.

Thanks
0
barnesco
Asked:
barnesco
  • 6
  • 5
  • 2
  • +3
1 Solution
 
EvilPostItCommented:
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.
0
 
lcohanDatabase AnalystCommented:
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.
0
 
lcohanDatabase AnalystCommented:
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
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
barnescoAuthor Commented:
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.
0
 
EvilPostItCommented:
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.
0
 
ZberteocCommented:
@lcohan:

From MDSN library:

"TRUNCATEONLY(in DBCC SHRINKFILE) is applicable only to data files. The log files are not affected."
0
 
barnescoAuthor Commented:
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?
0
 
EvilPostItCommented:
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.
0
 
ZberteocCommented:
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)
0
 
Scott PletcherSenior DBACommented:
First you need to see if you have any open trans on that db, or something else preventing SQL from re-using existing log space:

USE [<your_db_name>]

SELECT log_reuse_wait_desc, *
FROM sys.databases
WHERE
    name = DB_NAME()

DBCC OPENTRAN


What you want to see is "NOTHING" in the log_reuse_wait_desc: if it's something else, post that result here.

Also, you want to see no open trans.  If you have any open, they could be forcing the log to be unavailable for reuse and thus to keep growing.



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

That's false.  In simple mode, after commit, the related log records are immediately marked as available for reuse **IF NO CURRENTLY ACTIVE TRANSACTION IS USING THEM**.

The log file is not shrink automatically, only when SQL is "told" to shrink it (either via a SQL setting or a SQL command).
0
 
Scott PletcherSenior DBACommented:
>> 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.
0
 
EvilPostItCommented:
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.
0
 
Giorgi_KhurtsilavaCommented:
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
0
 
Scott PletcherSenior DBACommented:
>> 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).
0
 
EvilPostItCommented:
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.
0
 
Scott PletcherSenior DBACommented:
>> 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.
0
 
EvilPostItCommented:
0
 
Scott PletcherSenior DBACommented:
Btw, did you ever run these commands I suggested earlier:

DBCC LOGINFO

DBCC SHOWFILESTATS
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 6
  • 5
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now