April 30, 2018 at 3:14 am
Hi
I have a transaction running that has filled my log drive completely. It's a delete transaction and I don't mind killing it and rolling back since I can easily change the job and do it in smaller portions. However:
TL:DR. Have transaction that won't be killed and has filled my entire log drive (400 gb).
Any ideas?
April 30, 2018 at 4:27 am
asbjoernbrandt - Monday, April 30, 2018 3:14 AMHi
I have a transaction running that has filled my log drive completely. It's a delete transaction and I don't mind killing it and rolling back since I can easily change the job and do it in smaller portions. However:
- The transaction is still running but doesn't seem to progress over multiple days now - probably due to a full log that it keeps filling.
- I tried stopping the job from the agent with no luck.
- I tried killing the transaction with KILL spid but when I do that it seems it just renames the spid from 59 to 59s (system id) for a bit and then renames it back to 59. I can't query the progress with KILL 59 WITH StatusOnly; since it changes name after it completes the first KILL 59.
- If I could clear the transaction log I suspect it might be able to actually run to completion, but I can't while this transaction is writing to it.
TL:DR. Have transaction that won't be killed and has filled my entire log drive (400 gb).
Any ideas?
Do you have any available storage space where you could add another log file whilst the transaction finishes?
😎
Question, what is the recovery model of that database?
April 30, 2018 at 1:58 pm
Thanks.
I added another log file on another drive and let it complete. Took 4-5 hours. Now I've backed up the database, shrunk the log a bit, and cut the job in smaller portions and run it again - it ran without filling my log space now (am in simple recovery so transaction log is not kept across the smaller deletes).
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply