May 31, 2006 at 10:47 am
Hello
Had a SQL 2000 server (running Macola) that was fixing to be upgraded to a new server, three of the 12 databases on the server were involved in a snapshot replication plan. In the process of trying to remove the subscription to one of the replication plans so that replication could be removed entirely before the move, the EM interface hung and finally bombed out. According to the activity monitor, 2 open transactions remained (and still remain) in the master database.
In the meantime, a Macola consultant showed up to perform the upgrade and detached the databases and moved them to the new server. I was able to run sp_removedbreplication on the three databases to remove the orphaned replication properties so that the database upgrades would work. The master database was not moved from the old server. The old server was renamed and the new server was created with the same name as the old server.
However, the two open transactions appeared in the master database on the new server. They have been there for a week now and don't seem to be affecting anything other than my nerves.
When I run 'kill 63 with statusonly', I receive the following output:
Server: Msg 6120, Level 16, State 1, Line 2
Status report cannot be obtained. Rollback operation for Process ID 63 is not in progress.
The line in the 'process info' gui shows the following:
status: runnable, command: select into, application: MS SQLEM, wait type: not waiting
I have now noticed the same 'process info' line showing up on several other of our SQL servers with the same information as displayed above. This seems more than slightly weird to me. Why should these transactions 'float' around to other SQL servers?
How can I get rid of the two orphaned transactions ? Or should I not worry about them ?
Thanks in advance
June 1, 2006 at 6:20 am
Are you using Enterprise Manager to view processes? A process with two open transactions is normal. Also, KILL 63 with statusonly will display a status report only when a transaction is currently being rolled back. The message you're receiving is normal. You would have to run KILL 63 first to terminate the process.
June 1, 2006 at 8:55 am
Thanks for the reality check on this - looks like the two transactions are normal through EM when I thought they were a carryover from the bombed process. I hadn't noticed them before...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply