0

A long running query was being run on a SQL Server 2005 database which referenced tables on a SQL Server 2000 database. Both instances of SQL Server are running on the same machine. It was only a select query, it did not alter any records. It had run for about 2 hours before it was killed. The process has been in a "KILLED/ROLLBACK" state for over 6 hours.

The CPU Time of the process keeps going up (12827264 as of this message), whilst the Disk IO remains low and constant (8).

We could restart the server and this would probably fix the issue, but I'm keen to know what the cause of this is. What exactly is it rolling back? What is it doing to use up so much CPU? Why might it be taking so long?

Evil Pigeon
  • 103
  • 3

2 Answers2

1

One possibility: SQL Server 2005's created a temp table to cache the results of the remote query on 2000, and the remote query is actually returning a large amount of data. So the rollback may be SQL Server 2005 cleaning up the local tempdb. A fair indicator for this would be seeing the transaction log file for tempdb having grown to unexpected proportions.

Chris J
  • 1,218
  • 18
  • 32
1

SQL Server isn't great at killing out-of-process things like linked server calls (which use OLEDB/COM). xp_cmdshell can also be a problem. I've seen calls to ADSI freeze up and never come back. Try shutting down the application or recycling the IIS application, rather than trying to kill it from the back end.

Darin Strait
  • 2,012
  • 12
  • 6