September 8, 2008 at 5:31 pm
Hi,
One of the tables in the database got locked. when a select statement is issused, it shows that the query is executing but doesnot produce any results. Have to stop the query execution. It is not a very large table, the # of records are around 9000.
Can somebody suggest me how to remove the lock for this table.
Thanks
September 8, 2008 at 7:59 pm
Only way I know how is to run SP_who2 and see what connection is actually blocking the query. Then killing the connection based on the spid.
Gary Johnson
Sr Database Engineer
September 9, 2008 at 6:42 am
You can't just remove a lock. That's data being manipulated and stopping the manipulation mid-stream will break the integrity of the data in the table, possibly losing that data, the table or the entire db.
You can kill the process, as stated above, but it's going to go into a rollback that can be as long as the original query or sometimes longer. If you have to, you can shut down your server, but the start-up will still have to rollback that open transaction, not to mention you'll be closing all the other connections. I have seen long running rollbacks perform quicker during recovery, but it does come at the cost of down time.
"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
September 9, 2008 at 10:39 am
Thanks for your replies. but now i am a little bit confused. So when i kill the process which is causing a lock on the table, are there any chances that I would lose the data. Would it be for that table or for the db. also should I do a backup of the data for all the other tables?
I cannot restart the server. I have not done anything of this kind. So can you please suggest what I should do so that I dont mess it up.
September 9, 2008 at 12:17 pm
I'd start by attempting to kill the process. That should initiate a rollback, meaning whatever changes that process had been making will go away, but you shouldn't have problems with the rest of your data.
"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
September 9, 2008 at 12:51 pm
Thanks Gary,
It worked. Was able to kill the process and the table is no longer locked.
This is a real good forum. Very helpful for newbies like me..:)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply