Tranlog in simple recovery mode
-
Hello. In a SIMPLE recovery DB I tried to delete too many rows from a table at one time, leading to:
The transaction log for database is full due to 'ACTIVE_TRANSACTION'.
No problem, I'm on my dev machine and have since made some space on the tranlog drive. But DBCC Loginfo() is showing 97% of this log in use, so shrinking it won't do much.
How do I reclaim space in this log with these 'uncommitted' deletions pending? I suppose I could copy the good data off to another table and truncate the original. Presumably that will fee up the log. Or at worst stop and restart the SQL services.
Thanks,
Ken
-
You've got an active transaction. A session somewhere has run a BEGIN TRANSACTION and has not either committed or rolled the transaction back.
Commit or roll back the open transaction, then do your delete in batches (delete top (10000) ....)
No, copying the data and truncating the table would not clear the log, in fact the copying part would just fill it further.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I didn't start a transaction so there's nothing to roll back. Must have been one of those implicit transactions.
Anyhow, when I checked this morning everything had cleared out of the log. If I recall, Checkpoint comes along and does this. Maybe it was delayed yesterday.
Ken
-
And I don't know why you're looking at DBCC LogInfo() for this anyway.
Much easier is DBCC SQLPERF(LOGSPACE)
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply