August 16, 2011 at 5:07 pm
Howdy All -
Has anyone ran into an issue where a log file for a given database will not clear? I'm running 2008 SP1 and I have a DB where the logfile will not clear. Backed up the DB, the log, issued a checkpoint, and even have gone as far as setting it to simple mode and restarting the SQL Server. We've checked the DB and there are no hanging or open transactions. I've done DBCC Loginfo and I'm seeing that the status is set to 2 for all the VLF's but I'm not sure why they are not clearing when backing the log/DB up.
Any ideas?
August 16, 2011 at 9:41 pm
Just so everyone knows, restarting a second time allowed the DB to be shrank. Not sure why it didn't happen with the first reboot.
August 17, 2011 at 12:17 am
next time instead of directly restarting the sql service, you may try first to bring database offline/online
----------
Ashish
August 17, 2011 at 12:28 am
Andrew Theodore (8/16/2011)
Howdy All -Has anyone ran into an issue where a log file for a given database will not clear? I'm running 2008 SP1 and I have a DB where the logfile will not clear. Backed up the DB, the log, issued a checkpoint, and even have gone as far as setting it to simple mode and restarting the SQL Server. We've checked the DB and there are no hanging or open transactions. I've done DBCC Loginfo and I'm seeing that the status is set to 2 for all the VLF's but I'm not sure why they are not clearing when backing the log/DB up.
Any ideas?
This is the first time I am hearing to clear the log files the SQL server had restarted. No need to restart the server you can do this by using alter database.
Could you tell me what steps you did?
Instead of checking everything I can say your first approach will be to check “log_reuse_wait_desc” in sys.database
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
August 17, 2011 at 1:12 am
http://www.sqlservercentral.com/articles/Transaction+Log/72488/
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 17, 2011 at 1:42 am
Andrew Theodore (8/16/2011)
Just so everyone knows, restarting a second time allowed the DB to be shrank. Not sure why it didn't happen with the first reboot.
would of been handy to see the output from dbcc loginfo. Is this database used in any log shipping, replication, etc
Usually at 2 log backups will truncate the file enough and cycle to allow the shrinking of the file (i'm assuming that is what you are ultimately attempting)
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 17, 2011 at 6:37 am
Usually at 2 log backups will truncate the file enough and cycle to allow the shrinking of the file
I second that.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply