CODEWITHSUNDEEP
sql-serversql-server-2008
PositiveGuy
PositiveGuy

Reputation: 47743

Query Transaction Started but never Ends

I run a query sometimes and it just hangs. I go look at processes in the Activity Monitor (SQL Server 2008 R2) and it shows it and just that the transaction is open and it hoses the entire server.

This makes no sense, even if this was a long running query it should at some point time out one would assume.

I do a trace on it and it shows this:

enter image description here

Any suggestions?

Upvotes: 0

Views: 1667

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294287

BEGIN TRANSACTION
INSERT INTO ... VALUES ...
GO

Forgetful Fred has opened a transaction and went to lunch. There is nothing to timeout as there is no request active. The user must explcitly commit or rollback, eventually. An adminsitrator can forcefully terminate the offending session using KILL. Such a session/transaction can stay open for years if left on its own devices (assuming you doin't run out of log and you don't restart the server).

More convoluted scenarios can occur even when the user did issue a COMMIT, if distributed transactions are involved, see How It Works: Orphan DTC Transaction (Session/SPID = -2).

Upvotes: 1

Related Questions