<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Backups
»
log backups not clearing transaction log - no...
25 posts, Page 1 of 3
1
2
3
»
»»
log backups not clearing transaction log - no open transactions found
Rate Topic
Display Mode
Topic Options
Author
Message
Tim-153783
Tim-153783
Posted Saturday, February 26, 2011 7:32 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Saturday, May 18, 2013 4:42 PM
Points: 108,
Visits: 573
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
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Saturday, February 26, 2011 8:55 AM
SSC-Dedicated
Group: Administrators
Last Login: Today @ 8:32 AM
Points: 31,433,
Visits: 13,747
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
Tim-153783
Tim-153783
Posted Saturday, February 26, 2011 9:14 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Saturday, May 18, 2013 4:42 PM
Points: 108,
Visits: 573
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
Tim-153783
Tim-153783
Posted Saturday, February 26, 2011 9:31 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Saturday, May 18, 2013 4:42 PM
Points: 108,
Visits: 573
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
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Saturday, February 26, 2011 9:48 AM
SSC-Dedicated
Group: Administrators
Last Login: Today @ 8:32 AM
Points: 31,433,
Visits: 13,747
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
MyDoggieJessie
MyDoggieJessie
Posted Monday, January 02, 2012 8:10 AM
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 2:19 PM
Points: 2,037,
Visits: 3,761
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
GilaMonster
GilaMonster
Posted Monday, January 02, 2012 8:27 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 37,739,
Visits: 30,012
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
Lynn Pettis
Lynn Pettis
Posted Monday, January 02, 2012 11:36 AM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 21,625,
Visits: 27,471
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
MyDoggieJessie
MyDoggieJessie
Posted Tuesday, January 03, 2012 8:55 AM
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 2:19 PM
Points: 2,037,
Visits: 3,761
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
hkamal.infoedge
hkamal.infoedge
Posted Tuesday, January 03, 2012 9:12 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, November 09, 2012 2:08 PM
Points: 142,
Visits: 152
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 »
25 posts, Page 1 of 3
1
2
3
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
