<div>
I will be expecting a cursor to loop out record of that query, right? 
 
then how can I return more that one result(variable) by the cursor ? 
 
or that query has to change with one more where condition to check for total executing time? (my best guess) 
 
is it a better thing to make the sP has a time input that, any process that execute longer than that input time will be killed?
</div>

<div>
in oracle side you mean ? 
 
any link ? 
 
in Oracel gateway ? any step by step ?
</div>

<div>
Yes, this is what I meant. It all depends on how Oracle connected yo MSSQL. Can you provide more details
</div>

<div>
what kind of detail you need? I don't know oracle gateway at all and I hope there are guidline for the oracle DBA to follow &nbsp;! they said they can't control how the gateway operate. 
 
how to set the timeout ?
</div>

<div>
you cab create Sql agent job that will run every e.g. 5 min to check your query condition 
 
 
and act as you need ( just test it) 
 
--- try 
 
Create proc dbo.usp_checkTransandResolve 
--you can modify it as you need &nbsp;e.g. add @loginame 
as 
Declare @statement &nbsp;Varchar(4000) , @host &nbsp;Varchar(50), @programname Varchar(50), @loginname Varchar(50), @msg Varchar(8000), @spid int 
 
IF Exists (SELECT 1 FROM master..sysprocesses WHERE loginame= 'ixapi' and open_tran &gt;&nbsp;0 and DATEDIFF (DD, last_batch,GETDATE())&gt;1) 
 
 
Begin 
 
set @spid = SELECT Top 1 &nbsp;spid FROM master..sysprocesses WHERE loginame= 'ixapi' and open_tran &gt;&nbsp;0 and DATEDIFF (DD, last_batch,GETDATE())&gt;1 
 
 
SELECT @loginname= loginame, @programname= program_name, @host=hostname ,@loginnamelogin_time, @statement=(SELECT text FROM sys.dm_exec_sql_text(sql_handle)) 
FROM master..sysprocesses WHERE loginame= 'ixapi' and open_tran &gt;&nbsp;0 and DATEDIFF (DD, last_batch,GETDATE())&gt;1 and @spid=spid 
 
set @msg= ' The ' + @spid + &nbsp;' Process &nbsp;was killed. Details: login - ' + @loginname &nbsp; --- etc. 
 
exec (Kill @spid) 
 
exec EXEC msdb.dbo.sp_send_dbmail &nbsp;-msg 
 
End 
 
exec
</div>

<div>
let me try.. thanks EugeneZ,. let me understand it for a while and will be back soon.
</div>

<div>
this is what i have modified and I am not sure if it is correct or not: 
 

<pre><code class="code-10 nolinks" id="code-20-39778381-1">SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create proc dbo.usp_checkTransandResolve @Total_time_Executed datetime 
as
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

Declare @statement Varchar(4000) , @host Varchar(50), @programname Varchar(50), @loginname Varchar(50), @msg Varchar(8000), @spid int

IF Exists (SELECT 1 FROM master..sysprocesses WHERE loginame= 'ixapi' and open_tran &gt; 0 and DATEDIFF (DD, last_batch,GETDATE())&gt;1)

Begin

set @spid = SELECT Top 1 spid FROM master..sysprocesses WHERE loginame= 'ixapi' and open_tran &gt; 0 and DATEDIFF (DD, last_batch,GETDATE())&gt;1 


SELECT @loginname= loginame, @programname= program_name, @host=hostname ,@loginnamelogin_time, @statement=(SELECT text FROM sys.dm_exec_sql_text(sql_handle))
FROM master..sysprocesses WHERE loginame= 'ixapi' and open_tran &gt; 0 and DATEDIFF (DD, last_batch,GETDATE())&gt;1 and @spid=spid

set @msg= ' The ' + @spid + ' process was killed. Details: it is executed by the login: ' + @loginname + 'via the applicaiton' + @programname + 
'as this SQL query has ran' +

exec (Kill @spid)

exec EXEC msdb.dbo.sp_send_dbmail -msg

End
go
</code></pre>
 
question: 
 
1) I want to tell SP to clean up any spid that rans for more than an hour, can the input parameter define as @Total_time_Executed datetime? I hope to just input 60 for one hour! so the minutest measure unit is mintues. 
 
how to fix it ? 
 
2) This example assume that when runing my query, there always one rows only, I assume that there are more than one record as the spid returned can gives more than one result. 
 
we should use cursor, right? 
 

<pre><code class="code-1 nolinks" id="code-20-39778381-2">DECLARE @MyCursor CURSOR
EXEC titles_cursor @titles_cursor = @MyCursor OUTPUT
WHILE (@@FETCH_STATUS = 0)
BEGIN
 FETCH NEXT FROM @MyCursor
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
</code></pre>
 
how can I make the cursor, based on my query, returns fields to the hanlder correctly: 
 

<pre><code class="code-1 nolinks" id="code-20-39778381-3">@statement Varchar(4000) , @host Varchar(50), @programname Varchar(50), @loginname Varchar(50), @msg Varchar(8000), @spid int
</code></pre>
 
3) 

<pre><code class="code-1 nolinks" id="code-20-39778381-4">DATEDIFF (DD, last_batch,GETDATE())&gt;1 
</code></pre>
 
this means the current time - last batch time is 1 days ? &nbsp;how to convert to minutes so that I can adjust it? 
 
4) if later on I want to check the login name and input as paramter, how can I add that back to the cursor condition? 
 
5) this one 
 

<pre><code class="code-1 nolinks" id="code-20-39778381-5">exec EXEC msdb.dbo.sp_send_dbmail -msg
</code></pre>
 
should be: 
 

<pre><code class="code-1 nolinks" id="code-20-39778381-6">exec msdb.dbo.sp_send_dbmail -@msg
</code></pre>
 
? 
 
anyway to define the email operator so that it will just works ?
</div>

<div>
1. &lt;hope to just input 60 for one hour! 
please replace &nbsp; DATEDIFF (DD, last_batch,GETDATE())&gt;1 
with 
 
DATEDIFF (hh, last_batch,GETDATE())&gt;1 
 
2. if you 'll set this proc (after you adjust) to run from sql agent job every 5 mins (for example you can set 1 min etc) 
and I assume you do not have too many &nbsp;like this transactions 
 
this part &quot;SELECT Top 1 &nbsp;&quot; &nbsp;will kill 1 &gt;1 hour running desired transaction 
 
/if you expect more transactions - we can check -- note : some transactions maybe in Killed\Rollback mode after kill... &nbsp;- so the best way is to take care the source &nbsp;- from Oracle/ 
 
 
 
3. see #1 
4. the set @msg= ...@loginame &nbsp;+ etc will help you to get info about killed process 
for your email 
 
5. see example (yes you need to set operator) 
about msdb.dbo.sp_send_dbmail 
 
<a href="http://technet.microsoft.com/en-us/library/ms190307.aspx" rel="ugc">http://technet.microsoft.com/en-us/library/ms190307.aspx</a>
</div>

<div>
So 

<pre><code class="code-1 nolinks" id="code-20-39779255-1">DATEDIFF (hh, last_batch,GETDATE())&gt;1
</code></pre>
 
is for one hour? how about 6 hours? 
 

<pre><code class="code-1 nolinks" id="code-20-39779255-2">DATEDIFF (hh, last_batch,GETDATE())&gt;6 
</code></pre>
 ? 
 
so I can set the hour as the parameter as DATEDIFF (hh, last_batch,GETDATE())&gt; @hours, where @hours is the input parameter? 
 
2) it is different from time to time. sometimes 4 x spid can appear from the Oracle gateway ! 
 
so I need a cursor ? 
 

<pre><code class="code-10 nolinks" id="code-20-39779255-3">DECLARE db_cursor CURSOR FOR 
SELECT spid, loginame, program_name, hostname as &quot;From host&quot;, login_time, last_batch, DB_NAME(dbid) AS &quot;Against database&quot; , 
(SELECT text FROM sys.dm_exec_sql_text(sql_handle))as &quot;Query executed&quot; 
FROM master..sysprocesses WHERE loginame= 'ixapi' and open_tran &gt; 0;

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @spid, @loginname , @programname , @host , @startime , @lastudpatetime , @DBName , @query ;
WHILE @@FETCH_STATUS = 0 
BEGIN 
 --Do stuff with scalar values

 FETCH NEXT FROM db_cursor INTO @spid, @loginname , @programname , @host , @startime , @lastudpatetime , @DBName , @query ;
END;
CLOSE db_cursor;
DEALLOCATE db_cursor;
GO
</code></pre>
? 
 
4) basically what is the @msg &nbsp;you are referring to ? it is my &quot;Query executed&quot; ?
</div>

<div>
1. 6 hrs or 1 hour - it is as you need 
2. yes, cursor may work 
&nbsp;you just need to test 
3. 
&nbsp; 
@msg &nbsp;is the string that you can get for your email (see my above post) 
for example : 
 
EXEC msdb.dbo.sp_send_dbmail 
&nbsp; &nbsp; @profile_name = 'Adventure Works Administrator', 
&nbsp; &nbsp; @recipients = 'danw@Adventure-Works.com', 
&nbsp; &nbsp; @body = 'Alert: the long running transactions were killed', 
&nbsp; &nbsp; @subject = @msg
</div>

<div>
check an another code example from <a href="http://connectsql.blogspot.com/2012/09/sql-server-automatically-detect-and.html" rel="ugc">http://connectsql.blogspot.com/2012/09/sql-server-automatically-detect-and.html</a>
</div>

<div>
PS Results from the above code
<pre><code class="code-10 nolinks" id="code-20-39781024-1"> 
----------------------- -----------------------
2007-12-31 23:59:59.000 2008-01-01 00:00:00.000

Years
-----------
1

Quarters
-----------
1

Months
-----------
1

Weeks
-----------
0

Days
-----------
1

Hours
-----------
1

Minutes
-----------
1

Seconds
-----------
1
</code></pre>
 
Datetimes that were actually only 1 second different, gave 1 as a results for everything except weeks ...
</div>

<div>
What time zone you are ? what time is it now? appreciated for your help. this is our morning just start work.
</div>

<div>
Hi, 
 
In New Zealand its nearly mid-afternoon. 
 
Regards 
&nbsp; David
</div>

<div>
marrowyung: 
do not be confused of the friendly EE chat &nbsp;:) 
 
select what is the best for you hours\minutes\days - 
 
check BOL DATEDIFF <a href="http://technet.microsoft.com/en-us/library/ms189794.aspx" rel="ugc">http://technet.microsoft.com/en-us/library/ms189794.aspx</a> 
 
we are talking just about the main concept to catch your many hours running undetected processes that you wish to kill and it is your call to do one or another way. 
You are the main person in this case - we are just advisors 
 
 
In some cases it will not be so easy- you may see &nbsp;Killed\Rollback process that can be killed just by server restart. &nbsp;Thus I would suggest to check the source of your issue as well - the &nbsp;Oracle processes, what can be done to stop\commit them from Oracle and avoid the killing from sql server that, as I said is not always a 100% solution. 
 
But as replacement of your manual &quot;bad&quot; &nbsp;process resolution the above code can help you. 
You are doing just fine. 
 
good luck!
</div>

<div>
&quot;we are talking just about the main concept to catch your many hours running undetected processes that you wish to kill and it is your call to do one or another way. 
You are the main person in this case - we are just advisors &quot; 
 
yeah, I knew. 
&nbsp;I will have a test and make sure it is., 
 
&quot;In some cases it will not be so easy- you may see &nbsp;Killed\Rollback process that can be killed just by server restart. &nbsp;Thus I would suggest to check the source of your issue as well - the &nbsp;Oracle processes, what can be done to stop\commit them from Oracle and avoid the killing from sql server that, as I said is not always a 100% solution.&quot; 
 
yes, one of hte solution is to see the blocking head, right? other then sp_who2, what should I use to see the blocking head? 
 
then I kill it first !! 
 
&quot;But as replacement of your manual &quot;bad&quot; &nbsp;process resolution the above code can help you. 
You are doing just fine.&quot; 
 
yes, you are rightg !!
</div>

<div>
one thing, in the code above: 
 
 

<pre><code class="code-1 nolinks" id="code-20-39784893-1">exec EXEC msdb.dbo.sp_send_dbmail -msg
</code></pre>
 
still correct? 
 
I think it should be ony one exec, right ?
</div>

<div>
hi, the query problems come again: 
 
this logic can't detect it as we need to take into account the login time? 
 
now 2x query like this come to our MS SQL serve and one of them has login_time: 2014-01-14 10:10:37.007 and last batch is 2014-01-16 10:28:47.9210, already 2 days ? 
 
it makes our tempdB keep growthing. 
 
any suggestion ?
</div>

<div>
if I change the SP like that: 
 

<pre><code class="code-10 nolinks" id="code-20-39785009-1">SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


Create proc dbo.check_GatewayOracle_longrunningQueries @Total_time_Executed datetime 
as
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

Declare @spid int, @loginname Varchar(50), @programname Varchar(50), @host Varchar(50), @startime datetime, @lastudpatetime datetime , @DBName Varchar(50), 
@query Varchar(4000) , @msg Varchar(8000) ;


DECLARE db_cursor CURSOR FOR 
SELECT spid, loginame, program_name, hostname as &quot;From host&quot;, login_time, last_batch, DB_NAME(dbid) AS &quot;Against database&quot; , 
(SELECT text FROM sys.dm_exec_sql_text(sql_handle))as &quot;Query executed&quot; 
FROM master..sysprocesses WHERE loginame= 'ixapi' and open_tran &gt; 0 and DATEDIFF (minute, last_batch, GETDATE()) &gt; @Total_time_Executed ;

OPEN db_cursor;

set @msg = 'Alert:A long running transactions were killed and that make tempdB full';

FETCH NEXT FROM db_cursor INTO @spid, @loginname , @programname , @host , @startime , @lastudpatetime , @DBName , @query ;
WHILE @@FETCH_STATUS = 0 
BEGIN 
 exec (Kill @spid);

	 EXEC msdb.dbo.sp_send_dbmail
 @profile_name = 'sdfdsfd',
 @recipients = 'itsupport@dddd.com',
 @body = 'The long running transactions were killed and that make tempdB full, and the query is: ' + @query,
 @subject = @msg ;

 FETCH NEXT FROM db_cursor INTO @spid, @loginname , @programname , @host , @startime , @lastudpatetime , @DBName , @query ;
END;
CLOSE db_cursor;
DEALLOCATE db_cursor;
GO
</code></pre>
 
is it making sense that if I found 2 spid from the cursor, then it will &nbsp;kill it one by one and send out 2 x email &quot;? 
 
why it gives error: 
 

<pre><code class="code-1 nolinks" id="code-20-39785009-2">Msg 156, Level 15, State 1, Procedure check_GatewayOracle_longrunningQueries, Line 32
Incorrect syntax near the keyword 'Kill'.
Msg 102, Level 15, State 1, Procedure check_GatewayOracle_longrunningQueries, Line 37
Incorrect syntax near '+'.
Msg 102, Level 15, State 1, Procedure check_GatewayOracle_longrunningQueries, Line 43
Incorrect syntax near ';'.
</code></pre>
</div>

<div>
&quot;&quot;try to use login_time instead of last_batch with DATEDIFF&quot; 
 
 
yeah, I think so, this should be the only thing we can do. 
 
let me try. 
 
but it seems the error gives is : 
 

<pre><code class="code-1 nolinks" id="code-20-39792307-1">Msg 102, Level 15, State 1, Procedure check_GatewayOracle_longrunningQueries, Line 4
Incorrect syntax near 'added'.
Msg 102, Level 15, State 1, Procedure check_GatewayOracle_longrunningQueries, Line 39
Incorrect syntax near '+'.
</code></pre>
</div>

<div>
can't do this : 
 

<pre><code class="code-1 nolinks" id="code-20-39792310-1"> @body = 'The long running transactions were killed and that make tempdB full, and the query is: ' + @query,
</code></pre>
 
? 
 

<pre><code class="code-1 nolinks" id="code-20-39792310-2">SET NOCOUNT ON added to prevent extra result sets from
</code></pre>
 
this is a comment ?
</div>

<div>
try this one and adjust for your needs: 
 

<pre><code class="code-10 nolinks" id="code-20-39792532-1">alter proc dbo.check_GatewayOracle_longrunningQueries @Total_time_Executed int
as
BEGIN
SET NOCOUNT ON ---added to prevent extra result sets from
	-- interfering with SELECT statements.
	--dbo.check_GatewayOracle_longrunningQueries 60
	

Declare @spid int,
@loginname Varchar(50), 
@programname Varchar(50),
@host Varchar(50),
@startime datetime, 
@lastudpatetime datetime ,
@DBName Varchar(50), 
@query Varchar(4000) , 
@msg Varchar(8000) ,
 @kill varchar(50) --&lt;&lt;EZ


DECLARE db_cursor CURSOR FOR 
SELECT spid, loginame, program_name, hostname as &quot;From host&quot;, login_time, last_batch, DB_NAME(dbid) AS &quot;Against database&quot; , 
(SELECT text FROM sys.dm_exec_sql_text(sql_handle))as &quot;Query executed&quot; 
FROM master..sysprocesses WHERE loginame= 'ixapi' and open_tran &gt; 0 and DATEDIFF (minute, login_time, GETDATE()) &gt; @Total_time_Executed 

OPEN db_cursor


FETCH NEXT FROM db_cursor INTO @spid, @loginname , @programname , @host , @startime , @lastudpatetime , @DBName , @query 
WHILE @@FETCH_STATUS = 0 
BEGIN 

set @msg = 'Alert:A long running transactions were killed and that make tempdB full'
set @query ='The long running transactions were killed and that make tempdB full, and the query is: ' + @query 
set @kill = 'Kill ' + cast( @spid as varchar(5)) --&lt;&lt;EZ
exec(@kill) --&lt;&lt;EZ

	 EXEC msdb.dbo.sp_send_dbmail
 @profile_name = 'sdfdsfd',
 @recipients = 'itsupport@dddd.com',
 @body = @query,
 @subject = @msg 

 FETCH NEXT FROM db_cursor INTO @spid, @loginname , @programname , @host , @startime , @lastudpatetime , @DBName , @query 
END
CLOSE db_cursor
DEALLOCATE db_cursor

END 
</code></pre>
</div>

<div>
this time no error, let me test it.
</div>

<div>
now, I think something is wrong: 
 
as we do this now: 
 

<pre><code class="code-1 nolinks" id="code-20-39796008-1">set @query ='The long running transactions were killed and that make tempdB full, and the query is: ' + @query 
</code></pre>
 
but what we can see is this one: 
 

<pre><code class="code-1 nolinks" id="code-20-39796008-2"> @body = @query,
</code></pre>
 
bring up the REAL body like that: 
 

<pre><code class="code-1 nolinks" id="code-20-39796008-3">FETCH API_CURSOR000000000000F3EA
</code></pre>
 
which partially correct but the characters all gone, any reason for that you can see ?
</div>

<div>
The first time it runs it even show the followings: 
 

<pre><code class="code-1 nolinks" id="code-20-39796009-1">SELECT TABLE_CATALOG AS TABLE_CAT, SCHEMA_NAME(schema_id) AS TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, CAST (CASE INFORMATION_SCHEMA.COLUMNS.DATA_TYPE WHEN 'xml' THEN -152 WHEN 'sql_variant' THEN -150 WHEN 'uniqueidentifier' THEN -11 WHEN 'ntext' THEN -10 WHEN 'sysname' THEN -9 WHEN 'nchar' THEN -8 WHEN 'bit' THEN -7 WHEN 'tinyint' THEN -6 WHEN 'tinyint identity' THEN -6 WHEN 'bigint' THEN -5 WHEN 'bigint identity' THEN -5 WHEN 'image' THEN -4 WHEN 'binary' THEN -2 WHEN 'timestamp' THEN -2 WHEN 'text' THEN -1 WHEN 'char' THEN 1 WHEN 'numeric' THEN 2 WHEN 'numeric() identity' THEN 2 WHEN 'decimal' THEN 3 WHEN 'money' THEN 3 WHEN 'smallmoney' THEN 3 WHEN 'decimal() identity' THEN 3 WHEN 'int' THEN 4 WHEN 'int identity' THEN 4 WHEN 'smallint' THEN 5 WHEN 'smallint identity' THEN 5 WHEN 'float' THEN 6 WHEN 'real' THEN 7 WHEN 'date' THEN 91 WHEN 'time' THEN 92 WHEN 'datetime2' THEN 93 WHEN 'datetime' THEN 93 WHEN 'smalldatetime' THEN 93 WHEN 'datetimeoffset' THEN 93 WHEN 'varchar' THEN CASE length WHEN -1 THEN -1 ELSE 12 END WHEN 'varbinary' THEN CASE length WHEN -1 THEN -4 ELSE -3 END WHEN 'nvarchar' THEN CASE length WHEN -1 THEN -10 ELSE -9 END END as smallint) AS DATA_TYPE, CASE INFORMATION_SCHEMA.COLUMNS.DATA_TYPE WHEN 'varchar' THEN CASE length WHEN -1 THEN 'text' ELSE INFORMATION_SCHEMA.COLUMNS.DATA_TYPE END WHEN 'varbinary' THEN CASE length WHEN -1 THEN 'image' ELSE INFORMATION_SCHEMA.COLUMNS.DATA_TYPE END WHEN 'nvarchar' THEN CASE length WHEN -1 THEN 'ntext' ELSE INFORMATION_SCHEMA.COLUMNS.DATA_TYPE END WHEN 'numeric' 	THEN CASE c.is_identity WHEN 1 THEN 'numeric() identity' ELSE INFORMATION_SCHEMA.COLUMNS.DATA_TYPE END WHEN 'decimal' 	THEN CASE c.is_identity WHEN 1 THEN 'decimal() identity' ELSE INFORMATION_SCHEMA.COLUMNS.DATA_TYPE END WHEN 'bigint' 	THEN CASE c.is_identity WHEN 1 THEN 'bigint identity' ELSE INFORMATION_SCHEMA.COLUMNS.DATA_TYPE END WHEN 'int' 	THEN CASE c.is_identity WHEN 1 THEN 'int identity' ELSE INFORMATION_SCHEMA.COLUMNS.DATA_TYPE END WHEN 'smallint' 	THEN CASE c.is_identity WHEN 1 THEN 'small identity' ELSE INFORMATION_SCHEMA.COLUMNS.DATA_TYPE END WHEN 'tinyint' 	THEN CASE c.is_identity WHEN 1 THEN 'tinyint identity' ELSE INFORMATION_SCHEMA.COLUMNS.DATA_TYPE END ELSE INFORMATION_SCHEMA.COLUMNS.DATA_TYPE END AS TYPE_NAME, CASE INFORMATION_SCHEMA.COLUMNS.DATA_TYPE WHEN 'ntext' 			THEN 	CHARACTER_OCTET_LENGTH / 2 WHEN 'text' THEN CHARACTER_OCTET_LENGTH WHEN 'image' THEN CHARACTER_OCTET_LENGTH WHEN 'varchar' THEN CASE length WHEN -1 THEN 2147483647 ELSE length END WHEN 'varbinary' THEN CASE length WHEN -1 THEN 2147483647 ELSE length END WHEN 'nvarchar' THEN CASE length WHEN -1 THEN 1073741823 ELSE length END WHEN 'time' THEN 8 WHEN 'datetimeoffset' THEN CASE WHEN syscolumns.scale &gt; 0 THEN 20 + syscolumns.scale ELSE 19 END ELSE prec END AS COLUMN_SIZE, CASE I
</code></pre>
 
you will just don't know why and you run again, it give the first message I show you above.
</div>

<div>
what is the problem? 
did you run this proc ? 
did you get error? 
if 'yes' - please post it
</div>

<div>
&quot;The first time it runs it even show the followings:&quot; 
 
yes, I ran it but the email body don't show the complete message: 
 

<pre><code class="code-1 nolinks" id="code-20-39796533-1">The long running transactions were killed and that make tempdB full, and the query is: FETCH API_CURSOR000000000000F3EA 
</code></pre>
 
it just show: 
 

<pre><code class="code-1 nolinks" id="code-20-39796533-2">FETCH API_CURSOR000000000000F3EA
</code></pre>
 
any reason for this? 
 
once it go inside the cursor loop it forget this string ?
</div>

<div>
I think becaues in my edition, this one is not inside the loop: 
 

<pre><code class="code-1 nolinks" id="code-20-39798930-1">set @query ='The long running transactions were killed and that make tempdB full, and the query is: ' + @query 
</code></pre>
 
let see tonight if the same thing happen.
</div>

<div>
one thing, &nbsp;as from time to time we see that the query we trying to kill is &quot;FETCH API_CURSOR00000000000000AF&quot; 
 
someone said this statement is a cursor, and he/she suggest me to use: 
 

<pre><code class="code-1 nolinks" id="code-20-39884941-1">SELECT c.session_id as &quot;session id&quot;, c.name, c.properties as &quot;The cursor properties&quot;, c.creation_time as &quot;Cursor creation time&quot;, c.is_open as &quot;is the Cursor opened&quot; , t.text as &quot;The SQL Query&quot;

FROM sys.dm_exec_cursors (53) c

CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t 
</code></pre>
 
To find out the content of the cursor, inside that SP, how can I store all those field in to varible in a singal statment ? or many set statement? 
 

<pre><code class="code-1 nolinks" id="code-20-39884941-2">declare A,b,c,d,e,F varchar(10);

SELECT c.session_id into A, c.name into b, c.properties into c, c.creation_time into d, c.is_open into e , t.text into F

FROM sys.dm_exec_cursors (53) c

CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t 
</code></pre>
 
?
</div>

<div>
<div class="content wysiwyg-content">
Dear all, 
 
right now we keep having Oracle query to the MS SQL send from the Oracle gateway, some time it rans fast and sometimes it hold there for serveral days and make the tempdB full and MS SQL operate anymore: 
 
I usually run the follow query to query the sys.sysprocess to see their login time and how long it has been ran. Then if I see a the login used by the oracle to query MS SQL and it takes much longer than expected, I will use the spid to kill that by: 
 

<pre><code class="code-1 nolinks" id="code-10-28336754-1">SELECT spid, loginame, program_name, hostname as &quot;From host&quot;, login_time, last_batch, DB_NAME(dbid) AS &quot;Against database&quot; , 
(SELECT text FROM sys.dm_exec_sql_text(sql_handle))as &quot;Query executed&quot; 
FROM master..sysprocesses WHERE loginame= 'ixapi' and open_tran &gt; 0
</code></pre>
 
Any SP script you guys can introduce so that: 
 
1) to build a SP that run that query by using the script above, and if the time it takes is more then one hour, it look at the SPID. 
2) it create the kill commmand with the spid number as a string. 
3) it execut the whole kill command with taht SPID we need one by one, if there are more than one result from the query about( it is rared to see but it did happen!) 
4) email the statement, host, program name, loginname of each spid killed to the DBA (the operator is functional already) so that they know something anormal was happened. 
 
any example is good for me to do it.
</div>
</div>

Dear all,

right now we keep having Oracle query to the MS SQL send from the Oracle gateway, some time it rans fast and sometimes it hold there for serveral days and make the tempdB full and MS SQL operate anymore:

I usually run the follow query to query the sys.sysprocess to see their login time and how long it has been ran. Then if I see a the login used by the oracle to query MS SQL and it takes much longer than expected, I will use the spid to kill that by:

(CODE)

Any SP script you guys can introduce so that:

1) to build a SP that run that query by using the script above, and if the time it takes is more then one hour, it look at the SPID.
2) it create the kill commmand with the spid number as a string.
3) it execut the whole kill command with taht SPID we need one by one, if there are more than one result from the query about( it is rared to see but it did happen!)
4) email the statement, host, program name, loginname of each spid killed to the DBA (the operator is functional already) so that they know something anormal was happened.

any example is good for me to do it.

SP to kill a process automatically is that process has been running for a hour.

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

Microsoft SQL Server 2005

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.