February 26, 2011 at 7:32 am
I have a database in full recovery mode.
I normally take log backups every two hours.
However, recently, I have a log that will not clear when running a log backup.
I have checked for open transactions with
dbcc opentran() and have none.
I have checked to see if anything is causing this by lookiing at
select name, log_reuse_wait_desc
from sys.databases
and all databases show NOTHING in the reuse column.
I have run checkpoint in the database about 5 times and tried rerunning the t-log backup
but dbcc sqlperf(logspace) is still showing 9% used (about 2 GB out of 20 GB file)
Any ideas on how to clear the log back to a reasonable 1-2% usage?
February 26, 2011 at 8:55 am
replication enabled? If not, I might suspect that you have a large VLF that's active. The log is divided up, and depending on how it was sized and grown, you might have VLFs that are large or small. You must have one active, and if it's large, then you can't clear that VLF until it's filled and the next one becomes active.
Why do you want to get to 1-2% usage? Is there something wrong with 9% usage? I might worry if it were up at 80% usage and I couldn't clear it, but not at that level.
February 26, 2011 at 9:14 am
I'v been doing some checking.
No mirroring or replication going on.
I do have 1400 VLF's
The reason I want it small is that I am reindexing some large tables and am taking a log backup after each one which typically clears it out. The last one did not clear it out.
February 26, 2011 at 9:31 am
I must have been close to the VLF boundry.
The log finally shrunk after some more activity.
Got to love that DBCC LOGINFO command
February 26, 2011 at 9:48 am
LOL, glad it worked. Note, that you shouldn't be shrinking the log regularly. If you need that space for index rebuilds, leave the log where it maxes and let is remain there.
If you have a lot of VLFs, what I'd do is shrink the log down the a minimal size, then grow it in increments. The forumla for VLFs and some recommendations are here: http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
January 2, 2012 at 8:10 am
I've got a log reuse type of LOG_BACKUP, but there's no transaction log backup running. I've run a maintenance job to see if that would clear out my LDF file but nothing's changing. It's still full...
Server: Microsoft SQL Server 2005 - 9.00.5057.00 (Intel X86) Mar 25 2011 13:50:04 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 2, 2012 at 8:27 am
MyDoggieJessie (1/2/2012)
I've got a log reuse type of LOG_BACKUP, but there's no transaction log backup running.
Please post new questions in a new thread. Thank you.
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 2, 2012 at 11:36 am
GilaMonster (1/2/2012)
MyDoggieJessie (1/2/2012)
I've got a log reuse type of LOG_BACKUP, but there's no transaction log backup running.Please post new questions in a new thread. Thank you.
I agree with Gail that this should be posted in a new thread, but based on what you posted it soulds like your database is using the bulk_logged or full recovery model and you have no transaction log backups running against the database. The only way to clear the t-log is to run transaction log backups or switch your database to the simple recovery model.
For more help, please start another thread.
January 3, 2012 at 8:55 am
I agree with Gail that this should be posted in a new thread, but based on what you posted it soulds like your database is using the bulk_logged or full recovery model and you have no transaction log backups running against the database. The only way to clear the t-log is to run transaction log backups or switch your database to the simple recovery model.
Sorry, didn't think a new thread was needed - didn't see the point of creating a new post for the same type of question... but, will definitely do should this occur again.
Lynn, as an FYI the DB is in FULL mode with TLog backups running every 15 minutes (successfully) - I was just confused that the log_reuse_wait_desc
still said LOG_BACKUP, even when nothing was running...
I'll open a new thread
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 3, 2012 at 9:12 am
Switch the full recovery mode to simple recovery mode and then switch back to full mode and take the full back up.
January 3, 2012 at 9:39 am
hkamal.infoedge (1/3/2012)
Switch the full recovery mode to simple recovery mode and then switch back to full mode and take the full back up.
No, no, no, no, no!
And on a day where the headline article was 'Managing Transaction Logs.'
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, 2012 at 9:44 am
MyDoggieJessie (1/3/2012)
I was just confused that the log_reuse_wait_desc still said LOG_BACKUP, even when nothing was running...
A log backup wait reason means a log backup is needed, not that one is running.
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, 2012 at 9:47 am
GilaMonster (1/3/2012)
hkamal.infoedge (1/3/2012)
Switch the full recovery mode to simple recovery mode and then switch back to full mode and take the full back up.No, no, no, no, no!
And on a day where the headline article was 'Managing Transaction Logs.'
Well duh! What else did you expect :-D.
January 3, 2012 at 9:53 am
Ninja's_RGR'us (1/3/2012)
GilaMonster (1/3/2012)
hkamal.infoedge (1/3/2012)
Switch the full recovery mode to simple recovery mode and then switch back to full mode and take the full back up.No, no, no, no, no!
And on a day where the headline article was 'Managing Transaction Logs.'
Well duh! What else did you expect :-D.
I'm rapidly reaching the point of 'why the hell do I bother'
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, 2012 at 10:01 am
GilaMonster (1/3/2012)
Ninja's_RGR'us (1/3/2012)
GilaMonster (1/3/2012)
hkamal.infoedge (1/3/2012)
Switch the full recovery mode to simple recovery mode and then switch back to full mode and take the full back up.No, no, no, no, no!
And on a day where the headline article was 'Managing Transaction Logs.'
Well duh! What else did you expect :-D.
I'm rapidly reaching the point of 'why the hell do I bother'
because. (nothing else to say!)
When's the revised version coming out?
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply