-- 查找运行中的事务,输出其内容,并将其杀死
IF NOT EXISTS (
SELECT
*
FROM
tempdb.dbo.sysobjects
WHERE
id = object_id(N'tempdb..#OpenTranStatus')
AND type = 'U'
)
BEGIN
CREATE TABLE #OpenTranStatus (
ActiveTransaction VARCHAR (25),
Details sql_variant
)
END
ELSE
BEGIN
DELETE #OpenTranStatus
END -- Execute the command, putting the results in the table.
INSERT INTO #OpenTranStatus EXEC (
'DBCC OPENTRAN WITH TABLERESULTS, NO_INFOMSGS'
);
-- Display the results.
DECLARE
@transpid sql_variant DECLARE
@spid INT
SET @transpid = (
SELECT
Details
FROM
#OpenTranStatus
WHERE
ActiveTransaction = 'OLDACT_SPID'
) --select @transpid sid_num
SET @spid = CAST (@transpid AS INT)
IF NOT EXISTS (
SELECT
*
FROM
tempdb.dbo.sysobjects
WHERE
id = object_id(N'tempdb..#InputBuffer')
AND type = 'U'
)
BEGIN
CREATE TABLE #InputBuffer (
EventType nvarchar (30),
Parameters SMALLINT,
EventInfo nvarchar (4000)
)
END
ELSE
BEGIN
DELETE #InputBuffer
END INSERT INTO #InputBuffer EXEC (
'dbcc inputbuffer(' + @spid + ')'
);
SELECT
EventInfo
FROM
#InputBuffer
EXEC ('kill ' + @spid);