Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
readysetstop
Posting Yak Master
123 Posts |
 Posted - 2008-11-12 : 07:27:33
|
| I have a SQL2005 SP2 server with a strange issue. There is a process that holds page locks on a DB, with a SPID of '-2'. There's (obviously) no corresponding process to kill, and no open transactions on that DB. DBCC comes up perfectly clean on that DB, as well as master. No memory issues that I am aware of.Is there a way to kill these locks?Any and all help is appreciated.-D.(Cross posted to dbforums - http://www.dbforums.com/showthread.php?t=1635511.)____________________________________________________________________________________"Believe in those who are seeking the truth. Doubt those who say they have found it." -Andre Gide |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-12 : 07:35:43
|
| http://manicprogrammer.com/cs/blogs/michaelruminer/archive/2008/11/03/if-you-get-a-sql-server-2005-spid-blocked-by-a-spid-of-2.aspx |
 |
|
|
readysetstop
Posting Yak Master
123 Posts |
Posted - 2008-11-12 : 07:55:47
|
| Thanks, sodeep. I have forwarded that info to the web developers to see if they can confirm that there are corresponding IIS errors on their side.____________________________________________________________________________________"Believe in those who are seeking the truth. Doubt those who say they have found it." -Andre Gide |
|
|
|
readysetstop
Posting Yak Master
123 Posts |
Posted - 2008-11-12 : 10:10:13
|
Found out from a co-worker that you can kill processes by UOW as well as by SPID. Just incase anyone else runs into this, here's a script generator for you for this specific issue. As always, make sure there are no open transactions associated with these before you start killing./* Generate a script to kill orphaned DTC Transactions. */SELECT 'KILL ' + CAST(request_owner_guid AS CHAR(36))FROM sys.dm_tran_locksWHERE request_session_id = -2 AND request_owner_guid <> '00000000-0000-0000-0000-000000000000' Thanks again for the help.-D.____________________________________________________________________________________"Believe in those who are seeking the truth. Doubt those who say they have found it." -Andre Gide |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-12 : 10:20:02
|
| Glad it worked out. |
|
|
|
|
|
|
Process Long Gone, Lock still causing trouble?