August 10, 2012 at 12:13 pm
Jeff Kelly-310227 (8/10/2012)
The quick (hack/non proper way) way to solve this in the past for me has been to flip to full, backup db to nul, backup log to nul, shrink as needed. (again this for simple recovery DB's that are completely disposable and have no backup plan in place)
The thing is, that can't by itself fix the log.
Now it will run several checkpoints, which may mark the log reusable. The time required for the full backup may also allow things to resolve by themselves, but there is nothing in that set of statements that will mark a log as reusable over what would happen automatically in simple recovery model.
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 10, 2012 at 12:40 pm
The problem is the DB is alredy in simple recovery mode. WHat can we do in that case?
August 10, 2012 at 12:44 pm
Identify why the log is not being reused, resolve whatever the particular cause is. See the article I posted earlier titled "Why is my transaction log full"
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 10, 2012 at 1:11 pm
But Mu DB is alredy in simple recovery
August 10, 2012 at 1:14 pm
GilaMonster (8/10/2012)
Jeff Kelly-310227 (8/10/2012)
The quick (hack/non proper way) way to solve this in the past for me has been to flip to full, backup db to nul, backup log to nul, shrink as needed. (again this for simple recovery DB's that are completely disposable and have no backup plan in place)The thing is, that can't by itself fix the log.
Now it will run several checkpoints, which may mark the log reusable. The time required for the full backup may also allow things to resolve by themselves, but there is nothing in that set of statements that will mark a log as reusable over what would happen automatically in simple recovery model.
Could it of been something like a transaction @ the end of the file, causing the shrink to not work (or rather, not shrink 99% of the log) and the issuing of a checkpoint (in our case by the backup) freed up that trans, now allowing the shrink to do what we are hoping for?
either way, it's been awhile since this has bitten us, and next time i'll investigate further and identify the root cause.
As always, thx for the time/info Gila!
August 10, 2012 at 1:22 pm
na1774 (8/10/2012)
But Mu DB is alredy in simple recovery
Yes, you said so. Steps are the same no matter what the recovery model is. Identify the cause, resolve the problem, see the article I posted earlier.
There are only 2 reasons for the log to not be reusable that are tied to recovery model - log backups and database mirroring. The others can occur in any recovery model at all.
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
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply