Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Transaction Log Expand / Collapse
Author
Message
Posted Wednesday, April 22, 2009 12:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 3:25 PM
Points: 14, Visits: 77
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?
Post #702611
Posted Thursday, April 23, 2009 1:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:06 PM
Points: 42,332, Visits: 35,383
Query sys.databases. What does the log_reuse_wait_descr column say for that database?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #702944
Posted Thursday, January 3, 2013 8:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 3, 2013 10:00 AM
Points: 1, Visits: 10
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
Post #1402403
Posted Thursday, January 3, 2013 8:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:06 PM
Points: 42,332, Visits: 35,383
craighiggins (1/3/2013)
Hi Gail

Sorry 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, 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 2008, MVP
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

Post #1402415
Posted Wednesday, March 20, 2013 8:11 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 6:17 AM
Points: 306, Visits: 473
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......
Post #1433241
Posted Wednesday, March 20, 2013 12:19 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:06 PM
Points: 42,332, Visits: 35,383
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 2008, MVP
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

Post #1433402
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse