April 22, 2009 at 12:21 pm
I have a database in SQL server 2005 (64 bit) server. The recovery mode is set to simple recovery, and the transaction get bigger and bigger every day until I perform database backup or shrink log file. Why is this happening? How can I solve this issue?
April 23, 2009 at 1:59 am
Query sys.databases. What does the log_reuse_wait_descr column say for that database?
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
January 3, 2013 at 8:04 am
Hi Gail
Sorry to be a pain but what should the log_reuse_wait_desc value be?
I have been told that that even in simple recovery mode I may still need to clear transaction logs outside of the DB backup. This point seems to stack as I have a DB which had issues when the transaction log backup schedule failed to run and the transaction log itself was full.
The transaction log backup schedule I refer to was set up with a snap drive manager due to the server being on VMware. The problem I have with this is that it can only be scheduled at given time intervals using the snap manager. Affectively we would be putting the server to more work than needs be by scheduling frequent Tlog backups, when I know sql server can deal with it as and when required.
I have just gone to set up a Tlog backup within the management studio itself, using the maintenance plan wizard, and when I go to select the databases to backup it displays a warning stating 'Databases with simple recovery will be excluded'.
I am now a bit confused. Should the Tlog actually ever fill if in simple recovery mode or is there something else I am missing?
Any help would be greatly appreciated
January 3, 2013 at 8:41 am
craighiggins (1/3/2013)
Hi GailSorry to be a pain but what should the log_reuse_wait_desc value be?
Nothing
Anything other than that and something is preventing log reuse, meaning the log may grow if the condition persists
I have been told that that even in simple recovery mode I may still need to clear transaction logs outside of the DB backup.
Garbage. Besides, full backups don't clear the log anyway
I have just gone to set up a Tlog backup within the management studio itself, using the maintenance plan wizard, and when I go to select the databases to backup it displays a warning stating 'Databases with simple recovery will be excluded'.
Yup. simple recovery = no log backups, no point in time recovery possible. If you need to be able to restore to a point in time, then you need full recovery and log backups.
Should the Tlog actually ever fill if in simple recovery mode or is there something else I am missing?
It can. Not due to lack of log backups of course, but there are a whole bunch of other things that can prevent log reuse.
Maybe take a read through these: Managing Transaction Logs[/url], http://www.sqlservercentral.com/articles/Transaction+Log/72488/
p.s. Please in future post new questions in a new thread, not one 3 years old.
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
March 20, 2013 at 8:11 am
If your transaction logs are getting big even in SIMPLE Recovery Mode, could it possibly be that you have a great deal of DML occuring without commits? INSERTs, UPDATEs and DELETEs for example. If so then performing commits after a certain amount of DML activity could help.
Just out of curiosity.....how were you planning to do Transaction Log backups while the database is running in the SIMPLE Recovery Model?
The transaction log stills plays a part in a database running in the simple recovery model. Why? Because even in this model it is still possible to rollback uncommitted transactions and these transaction are stored in the transaction log until the commit occurs. Once they are committed they are no longer in the log. Hence the reason I wrote the first paragraph.....
And please learn how the transaction log and recovery models work......
March 20, 2013 at 12:19 pm
kevaburg (3/20/2013)
If your transaction logs are getting big even in SIMPLE Recovery Mode, could it possibly be that you have a great deal of DML occuring without commits? INSERTs, UPDATEs and DELETEs for example. If so then performing commits after a certain amount of DML activity could help.
Only if they're starting explicit transactions and never committing. SQL by default is in autocommit mode, meaning every operation is in its own transaction that commits automatically when the statement completes.
Because even in this model it is still possible to rollback uncommitted transactions and these transaction are stored in the transaction log until the commit occurs. Once they are committed they are no longer in the log.
Not quite.
Log entries are marked inactive by the checkpoint process in simple recovery model once all the log records in a particular virtual log file are not needed for anything, where anything includes active transaction, replication, database backup, database recovery and maybe a couple other things. It is not the commit that marks the log records as inactive.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply