Well, that doesn't help either...(I have the latest service pack).
What I wound up doing was to create the following stored procedure:
<xmp>
/****************** KILL HANGING TRANSACTIONS *****************************/
CREATE PROCEDURE [dbo].[KILL_HANGING_TRANSACTIONS]
AS
BEGIN
DECLARE @SPID UNIQUEIDENTIFIER,
@sql nvarchar(128)
SET @SPID = '00000000-0000-0000-0000-000000000000'
WHILE @SPID IS NOT NULL
BEGIN
SET @SPID = (
SELECT TOP 1 request_owner_guid
FROM sys.dm_tran_locks
INNER JOIN KILL_LIST ON request_owner_guid = spid
WHERE request_session_id = -2
and request_owner_guid <> '00000000-0000-0000-0000-000000000000'
)
IF @SPID IS NOT NULL
BEGIN
set @sql = 'KILL ''' + cast( @SPID as nvarchar(64)) + ''''
exec (@sql)
END
END
-- CLEAR OUT KILL_LIST TABLE
DELETE FROM KILL_LIST
-- INSERT ANY CURRENT -2 / 0000... SESSIONS
INSERT INTO KILL_LIST
SELECT DISTINCT request_owner_guid
FROM sys.dm_tran_locks
WHERE request_session_id = -2
and request_owner_guid <> '00000000-0000-0000-0000-000000000000'
END
</xmp>
Which requires that this table exist:
/****************** KILL_LIST TABLE *****************************/
CREATE TABLE [dbo].[KILL_LIST](
[SPID] [uniqueidentifier] NULL
)
I have scheduled this stored procedure to run every minute, and if it finds one of these transactions have been orphaned for more than a minute, it kills them. I'd love to know of a better way if anyone knows of one.
[Message sent by forum member 'christopherrued' (christopherrued)]
http://forums.java.net/jive/thread.jspa?messageID=353943