January 16, 2018 at 6:49 pm
There is a open transaction created due to bulk loading of large tables.Transaction log got full as well. When I do DBCC OPENTRAN, I get the following. It's been couple almost close to 4 hours and I can't seem to kill this transaction by KILL 59.
What are other ways I can kill this transaction? Thanks in advance.
Transaction information for database 'DataBase'
Oldest active transaction:
SPID (server process ID): 59
UID (user ID) : -1
Name : BULK INSERT
LSN : (70:17550:51)
Start time : Jan 16 2018 4:03:37:037PM
SID : 0x010500000000000515000000fe26c64878006d1f43170a32690e0200
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
January 16, 2018 at 8:08 pm
What does it say when you run "kill 59"?
_____________
Code for TallyGenerator
January 17, 2018 at 1:38 am
Hi,
what is the output if you try this command:
KILL xxx WITH STATUSONLY
January 17, 2018 at 5:59 am
Are there blocked processes on the machine? You may be waiting on something else entirely. Also, how long was it running before you rolled it back? I've seen the rollback take longer than the initial data load, regularly. So if the data load itself takes 3 hours, a 5 hour rollback, while a royal pain, is not crazy.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 17, 2018 at 9:49 am
The rollback itself will definitely take much longer than the initial transaction. It will require additional log space as well. If the transaction log disk partition is full, you will need to add a log file on a disk partition with ample space. If you don't let it run its course, you may end up with a corrupt database. So unless you can afford that, don't mess around with a rollback process.
January 17, 2018 at 9:58 am
RandomStream - Wednesday, January 17, 2018 9:49 AMIt will require additional log space as well.
Are you sure about that - isn't sufficient space for a rollback allocated in the log at the start of a transaction?
John
January 17, 2018 at 10:41 am
John Mitchell-245523 - Wednesday, January 17, 2018 9:58 AMRandomStream - Wednesday, January 17, 2018 9:49 AMIt will require additional log space as well.Are you sure about that - isn't sufficient space for a rollback allocated in the log at the start of a transaction?
John
I'm speaking from experience. If it did, it certainly didn't work as intended for me. Let's see if OP can share the result.
January 17, 2018 at 2:42 pm
John Mitchell-245523 - Wednesday, January 17, 2018 9:58 AMRandomStream - Wednesday, January 17, 2018 9:49 AMIt will require additional log space as well.Are you sure about that - isn't sufficient space for a rollback allocated in the log at the start of a transaction?
Yes, it is.
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
January 17, 2018 at 2:45 pm
RandomStream - Wednesday, January 17, 2018 9:49 AMIt will require additional log space as well. If the transaction log disk partition is full, you will need to add a log file on a disk partition with ample space. If you don't let it run its course, you may end up with a corrupt database.
No, and no.
If other stuff is running, that other stuff will also be using the log file and hence growing it, and it can't be marked reusable while there's a rollback in progress, but log space is reserved when a transaction runs and so the rollback itself won't need extra space.
Stopping a kill can only be done by restarting SQL. It won't corrupt the DB, it'll just mean that SQL rolls that transaction back with the entire DB unavailable.
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
January 17, 2018 at 4:45 pm
GilaMonster - Wednesday, January 17, 2018 2:45 PMRandomStream - Wednesday, January 17, 2018 9:49 AMIt will require additional log space as well. If the transaction log disk partition is full, you will need to add a log file on a disk partition with ample space. If you don't let it run its course, you may end up with a corrupt database.No, and no.
If other stuff is running, that other stuff will also be using the log file and hence growing it, and it can't be marked reusable while there's a rollback in progress, but log space is reserved when a transaction runs and so the rollback itself won't need extra space.
Stopping a kill can only be done by restarting SQL. It won't corrupt the DB, it'll just mean that SQL rolls that transaction back with the entire DB unavailable.
Thanks, Gail, for correcting my poor understanding.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply