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 123»»»

log backups not clearing transaction log - no open transactions found Expand / Collapse
Author
Message
Posted Saturday, February 26, 2011 7:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, October 11, 2014 7:56 AM
Points: 113, Visits: 617
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?
Post #1070027
Posted Saturday, February 26, 2011 8:55 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:11 PM
Points: 31,368, Visits: 15,837
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
Post #1070038
Posted Saturday, February 26, 2011 9:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, October 11, 2014 7:56 AM
Points: 113, Visits: 617
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.
Post #1070039
Posted Saturday, February 26, 2011 9:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, October 11, 2014 7:56 AM
Points: 113, Visits: 617
I must have been close to the VLF boundry.
The log finally shrunk after some more activity.
Got to love that DBCC LOGINFO command
Post #1070042
Posted Saturday, February 26, 2011 9:48 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:11 PM
Points: 31,368, Visits: 15,837
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
Post #1070043
Posted Monday, January 2, 2012 8:10 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:46 AM
Points: 4,031, Visits: 7,172
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"
Post #1228918
Posted Monday, January 2, 2012 8:27 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: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
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 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 #1228921
Posted Monday, January 2, 2012 11:36 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:15 AM
Points: 20,861, Visits: 32,888
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.



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)
Post #1228972
Posted Tuesday, January 3, 2012 8:55 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:46 AM
Points: 4,031, Visits: 7,172
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"
Post #1229328
Posted Tuesday, January 3, 2012 9:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 17, 2014 1:56 PM
Points: 192, Visits: 217
Switch the full recovery mode to simple recovery mode and then switch back to full mode and take the full back up.
Post #1229345
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse