July 25, 2008 at 9:46 am
Hi,
My transaction log is full and it has occupied all the space in the disk. Can somebody please, suggest me the best way to empty it.
Thanks a lot
July 25, 2008 at 10:07 am
Is the database in full recovery mode?
Do you have transaction log backups running?
Do you have replication running?
What is the recovery requirements for this DB in the case of failure? (ie, how much data loss is acceptable)
What do you get for the DB in question if you run the following query?
select name, recovery_model_desc, log_reuse_wait_desc from sys.databases
For some background info, read this - Transaction logs and recovery mode[/url]
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
July 25, 2008 at 10:22 am
Yes,It is a full recovery model. The transaction log backups and replication are running.
Thanks
July 25, 2008 at 10:29 am
Jr.DBA (7/25/2008)
Yes,It is a full recovery model. The transaction log backups and replication are running.Thanks
Hmm. Check that the tran log backups really are running. Check that the replication log reader is up to date.
What does the query I gave you return?
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
July 25, 2008 at 10:35 am
Hi Gail,
For two of my databases, it is giving full recovery model and log backup and for others its giving simple and nothing.
Thanks a lot
July 25, 2008 at 10:39 am
Is one of those full and log backup the DB with the really large log file? If so, the reason that the log has grown to that size if that the log backups aren't running. Either the job isn't running or its running and failing.
Check the transaction log backup jobs, make sure that both the DBs in full recovery are included in the log backup and make sure that the log backups aren't failing.
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
August 9, 2008 at 4:17 am
I had a similar problem with a couple of databases where the transaction would not shrink even after a successful transaction log backup.
Untill i find out what ic causing the issue I do the following.
once a week after full backup and transaction log backup
1) change the recovery model to Simple
2) shrink the transaction log
3) then change the recovery model back to full.
good luck.
August 9, 2008 at 5:02 am
tazza_no1 (8/9/2008)
I had a similar problem with a couple of databases where the transaction would not shrink even after a successful transaction log backup.
Transaction log backups aren't supposed to shrink the file. They just truncate the inactive portion of the log and allow the space within the file to be reused. It is not recommended to regularly shrink your transaction log. It will have to grow again and that growth will cause slow downs, log fragmentation (lots of little virtual log files) and possibly file system fragmentation.
The best thing to do with the transaction log is to size it for the amount of entries that need to go in between log backups, then leave it alone.
once a week after full backup and transaction log backup
1) change the recovery model to Simple
2) shrink the transaction log
3) then change the recovery model back to full.
Then take a full database backup since changing the mode to simple broke the log chain and any log backups take after that are useless. Until you take a full backup you are not able to do point in time recovery. If you lose the database you will only be able to restore to the last full backup.
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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply