October 29, 2010 at 2:26 am
Hi,
I have a problem on one of our SQL databases in that there is a locking process that is occurring that is preventing other users from using the database. Using the sysprocesses view I have discovered that the lock is occurring when waiting for an extended stored procedure to finish. Below is an example of what I am seeing :
spidkpidblockedwaittypewaittimelastwaittypewaitresourcedbiduidcpu
81689200x00C7295140 MSQL_XP 8 1 31
When this type of lock occurs the only way I can clear this is to restart the SQL Service and it kicks back into life. I tried using KILL and that left it in a rollback state but never rolled back. I had a situation this week where restarting the service didn’t work and I was forced to reboot the server entirely. Although the databases are only serving about 70 users, this is still not a good thing to happen and I would like to get it resolved. I am quite lucky in the fact this occurs once every 1-2 weeks.
What I would like to know is there a way of killing whatever process the SQL server is waiting for so that other users can access the system? With regards to the external DLL the process is running I know what that is I just need to know how to kill it safely.
October 29, 2010 at 7:07 am
There just isn't any magic way to kill a process. If you know where the problem is, I'd concentrate on fixing it. If a transaction is opened and running, when you kill it, it must go into rollback, no options.
"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
October 29, 2010 at 7:44 am
I was afraid someone would say that! Even though I was the one that developed the DLL, debugging it is going to prove really tricky. The DLL is used as some integration logic to an existing Visual FoxPro app we have. The problem is I cant reproduce the problem on our test environment. Even in the live environment once the lock occurs and I restart the sql service, if I run the same T-SQL statement that caused the lock, it runs fine.
October 29, 2010 at 7:46 am
Then you'd probably be better off doing the integration some other way, call both databases from the app instead of one database from the other database, something along those lines.
"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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply