Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


log backups not clearing transaction log - no open transactions found


log backups not clearing transaction log - no open transactions found

Author
Message
Tim-153783
Tim-153783
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 679
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?
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: Administrators
Points: 35979 Visits: 18727
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Tim-153783
Tim-153783
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 679
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.
Tim-153783
Tim-153783
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 679
I must have been close to the VLF boundry.
The log finally shrunk after some more activity.
Got to love that DBCC LOGINFO command
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: Administrators
Points: 35979 Visits: 18727
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

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
MyDoggieJessie
MyDoggieJessie
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4856 Visits: 7363
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; They'll drag you down to their level and beat you with experience" ;-)
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47147 Visits: 44346
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

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


Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24157 Visits: 37929
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
MyDoggieJessie
MyDoggieJessie
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4856 Visits: 7363
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; They'll drag you down to their level and beat you with experience" ;-)
hkamal.infoedge
hkamal.infoedge
SSC-Enthusiastic
SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)

Group: General Forum Members
Points: 192 Visits: 311
Switch the full recovery mode to simple recovery mode and then switch back to full mode and take the full back up.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search