SQL 2008 Transaction Logs for Database with Recovery Model

Question

SQL 2008 Transaction Logs for Database with Recovery Model

9denis

SSC Journeyman

Points: 80
More actions
February 14, 2012 at 9:33 pm

#251558

We have a Test Database on SQL 2008. I have set recovery model for it to be 'Simple'. I have got a standard pre-defined maintenance plan which backups the database every night (along with few other tasks like reorganize Index, rebuil index, update statistics and maintenance cleanup).
Now, the problem with database is the transaction log file keeps on growing everyday and eventually fills up the disk. The database is of ~18GB. And the transaction log file keeps growing and fills up to 225GB of disk space.
With Simple Recovery Model, it should flush out all the transaction logs, when the database is backed up. But, it still keeps on growing. Even if I shrink the Log files, it frees up upto 10% space at a time, but it grows by approx 20GB everyday and fills up the disk. Also, due to this, the database backup size keeps growing everyday. And in the end I have to forcefully replace the log file and delete the old file.
I am not sure, why it is doing that. Can anybody throw some light on it?
Thanks in Advance..

Viewing 4 posts - 1 through 4 (of 4 total)

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

In simple recovery the log is marked reusable when a checkpoint occurs, not a backup. There are all sorts of things that can prevent a log from been reused even in simple recovery.

Please read through this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/

Also Managing Transaction Logs[/url]

p.s. Deleting the log file is a good way to destroy the entire database beyond recovery. Unless you like gambling with your data, stop deleting it.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

9denis SSC Journeyman Points: 80 More actions · Answer 2

Hi Gail,

Thanks for your valued reply.

I am a SysAdmin and not an expert with SQL or any other database system. Also, I checked a few things mentioned on the web-link provided by you but, didn't help in resolving the issue. Below are some outputs:

DBCC OPENTRAN

==================================

Transaction information for database 'MyDatabase'.

Replicated Transaction Information:

Oldest distributed LSN : (0:0:0)

Oldest non-distributed LSN : (1084779:8459:1)

SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases

=================================================================================

MyDatabaseSIMPLEREPLICATION

Also, I tried changing to "Full" recovery mode and ran manual backup of database and transaction logs. Still couldn't free up space in Transaction Log file. And it still keeps on growing.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Please read the first article again and look at the section on replication waits. It links to a blog post as well. Read that.

Switching to full recovery just makes the log less likely to be reused. In simple a checkpoint is enough if nothing else is holding the log active. In full recovery a log backup is needed. However when something is holding the log active (as in your case) that root cause must be fixed first.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass