June 23, 2011 at 2:32 am
I've got the following peace of code that I use to find any transactions running for a long time:
/*Determining the Longest Running Transaction*/
SELECT transaction_id, session_id, elapsed_time_seconds
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC;
What I would like som help with is to achieve the following
1) Filter the query so only transactions running for more than 30 seconds are displayed
2) Pipe those session IDs into a kill kommand such as
kill session_id
Any help will be greatly appreciated.
/Andy
June 23, 2011 at 2:46 am
Andy
(1) Use a WHERE clause
(2) SELECT 'KILL ' + CAST(session_id AS varchar(4))....
Be very careful with (2) - you may find yourself killing stuff that should be allowed to live if you don't review the results before you execute the KILL.
John
June 23, 2011 at 2:58 am
This may help you
DECLARE @session_id int
Declare @command varchar (max)
DECLARE DATABASES_CURSOR CURSOR FOR
select session_id FROM sys.dm_tran_active_snapshot_database_transactions
where elapsed_time_seconds>30
OPEN DATABASES_CURSOR
FETCH NEXT FROM DATABASES_CURSOR INTO @session_id
WHILE @@FETCH_STATUS = 0
BEGIN
SET @command='KILL '+convert( varchar(2),@session_id)
exec (@command)
FETCH NEXT FROM DATABASES_CURSOR INTO @session_id
END
CLOSE DATABASES_CURSOR
DEALLOCATE DATABASES_CURSOR
GO
Ryan
//All our dreams can come true, if we have the courage to pursue them//
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply