Siusic Dot Com

In a recent problem that I was trouble shooting with, I was told that a store procedure which had been running well for a long time was

taking exceptionally long time to run and experienced “lock timeout“. When I used the sp_who or sp_who2 system store procedure to look at the running processes one told me that the store procedure was blocked by the AutoShrink process and the other one told me the store procedure was blocking the AutoShrink process. Looks like the process of the store procedure and the AutoShrink process was in dead lock.

It hung the database which the store procedure was running against. When I kill the process that ran the store procedure I saw the same message, “Estimated Rollback Completion: 0%. Estimated Time Remaining: 0 Seconds“, which I talked about in my previous post. However no matter how long I wait this killed process was not able to finish the rollback process. So I wanted to killed the AutoShrink process. When I did that it said that I can not kill a system process. Looks like restarting SQL Server was the only way to solve the problem. In fact I don’t think there is way to kill a AutoShrink process. If you find one please let me know.

However when I look into the code of the store procedure to try to find out what the store procedure was trying to do I found some statements like the following.

OPENDATASOURCE (’SQLOLEDB’,'Data Source=ServerName;User ID=CheckUser;Password=Password’).databasename.ownername.tablename

It uses the OPENDATASOUCE function to open an other connection to the server itself and try to read a big table in another database using a different login. When I ran sp_who again I found there were process running under the login CheckUser and the host that initiated the process was the server itself. All of a sudden I understood what happened. Even though the process of running the store procedure was killed it was still waiting for the process initiated by OPENDATASOURCE function to finish its job. And killing a parent process will not automatically kill the child process initiated by the parent. I went ahead and kill the process running under CheckUser and the parent process that ran the store procedure disappear in the sp_who result. That means the process was finally stopped and the database was back to normal.

Maybe I should question why to use OPENDATASOURCE to connect to the server itself. There might be all kinds of historical reason for a specific environment.

Related Posts:

Estimated Rollback Completion: 0%. Estimated Time Remaining: 0 Seconds.

Error 1222 Lock Request Time Out Period Exceeded When Set up Replication

Hard to Kill Malware: Wintems.exe, Hldrrr.exe and Random Number.exe

MySQL Replication and MS SQL Log Shipping

Errors in T-SQL Batches and the GO Statement in ETL process