Avatar of marrowyung
marrowyung asked on

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

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:

SELECT spid, loginame, program_name, hostname as "From host", login_time, last_batch, DB_NAME(dbid) AS "Against database" , 
(SELECT text FROM sys.dm_exec_sql_text(sql_handle))as "Query executed" 
FROM master..sysprocesses WHERE loginame= 'ixapi' and open_tran > 0

Open in new window


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.
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008

Avatar of undefined
Last Comment
marrowyung

8/22/2022 - Mon
ASKER
marrowyung

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?
SOLUTION
chaau

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
marrowyung

in oracle side you mean ?

any link ?

in Oracel gateway ? any step by step ?
chaau

Yes, this is what I meant. It all depends on how Oracle connected yo MSSQL. Can you provide more details
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
marrowyung

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  ! they said they can't control how the gateway operate.

how to set the timeout ?
EugeneZ

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  e.g. add @loginame
as
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 > 0 and DATEDIFF (DD, last_batch,GETDATE())>1)


Begin

set @spid = SELECT Top 1  spid FROM master..sysprocesses WHERE loginame= 'ixapi' and open_tran > 0 and DATEDIFF (DD, last_batch,GETDATE())>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 > 0 and DATEDIFF (DD, last_batch,GETDATE())>1 and @spid=spid

set @msg= ' The ' + @spid +  ' Process  was killed. Details: login - ' + @loginname   --- etc.

exec (Kill @spid)

exec EXEC msdb.dbo.sp_send_dbmail  -msg

End

exec
ASKER
marrowyung

let me try.. thanks EugeneZ,. let me understand it for a while and will be back soon.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
marrowyung

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

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 > 0 and DATEDIFF (DD, last_batch,GETDATE())>1)

Begin

set @spid = SELECT Top 1  spid FROM master..sysprocesses WHERE loginame= 'ixapi' and open_tran > 0 and DATEDIFF (DD, last_batch,GETDATE())>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 > 0 and DATEDIFF (DD, last_batch,GETDATE())>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

Open in new window


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?

DECLARE @MyCursor CURSOR
EXEC titles_cursor @titles_cursor = @MyCursor OUTPUT
WHILE (@@FETCH_STATUS = 0)
BEGIN
   FETCH NEXT FROM @MyCursor
END
CLOSE @MyCursor
DEALLOCATE @MyCursor

Open in new window


how can I make the cursor, based on my query, returns fields to the hanlder correctly:

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

Open in new window


3)
DATEDIFF (DD, last_batch,GETDATE())>1 

Open in new window


this means the current time - last batch time is 1 days ?  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

exec EXEC msdb.dbo.sp_send_dbmail  -msg

Open in new window


should be:

exec msdb.dbo.sp_send_dbmail  -@msg

Open in new window


?

anyway to define the email operator so that it will just works ?
EugeneZ

1. <hope to just input 60 for one hour!
please replace   DATEDIFF (DD, last_batch,GETDATE())>1
with

DATEDIFF (hh, last_batch,GETDATE())>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  like this transactions

this part "SELECT Top 1  "  will kill 1 >1 hour running desired transaction

/if you expect more transactions - we can check -- note : some transactions maybe in Killed\Rollback mode after kill...  - so the best way is to take care the source  - from Oracle/



3. see #1
4. the set @msg= ...@loginame  + 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

http://technet.microsoft.com/en-us/library/ms190307.aspx
ASKER
marrowyung

So
DATEDIFF (hh, last_batch,GETDATE())>1

Open in new window


is for one hour? how about 6 hours?

DATEDIFF (hh, last_batch,GETDATE())>6 

Open in new window

?

so I can set the hour as the parameter as DATEDIFF (hh, last_batch,GETDATE())> @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 ?

DECLARE db_cursor CURSOR FOR 
SELECT spid, loginame, program_name, hostname as "From host", login_time, last_batch, DB_NAME(dbid) AS "Against database" , 
(SELECT text FROM sys.dm_exec_sql_text(sql_handle))as "Query executed" 
FROM master..sysprocesses WHERE loginame= 'ixapi' and open_tran > 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

Open in new window

?

4) basically what is the @msg  you are referring to ? it is my "Query executed" ?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
EugeneZ

1. 6 hrs or 1 hour - it is as you need
2. yes, cursor may work
 you just need to test
3.
 
@msg  is the string that you can get for your email (see my above post)
for example :

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Adventure Works Administrator',
    @recipients = 'danw@Adventure-Works.com',
    @body = 'Alert: the long running transactions were killed',
    @subject = @msg
EugeneZ

SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
David Todd

PS Results from the above code
                        
----------------------- -----------------------
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

Open in new window


Datetimes that were actually only 1 second different, gave 1 as a results for everything except weeks ...
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
marrowyung

What time zone you are ? what time is it now? appreciated for your help. this is our morning just start work.
David Todd

Hi,

In New Zealand its nearly mid-afternoon.

Regards
  David
Anthony Perkins

Tuesday evening here.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
EugeneZ

marrowyung:
do not be confused of the friendly EE chat  :)

select what is the best for you hours\minutes\days -

check BOL DATEDIFF http://technet.microsoft.com/en-us/library/ms189794.aspx

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  Killed\Rollback process that can be killed just by server restart.  Thus I would suggest to check the source of your issue as well - the  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 "bad"  process resolution the above code can help you.
You are doing just fine.

good luck!
ASKER
marrowyung

"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 "

yeah, I knew.
 I will have a test and make sure it is.,

"In some cases it will not be so easy- you may see  Killed\Rollback process that can be killed just by server restart.  Thus I would suggest to check the source of your issue as well - the  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."

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 !!

"But as replacement of your manual "bad"  process resolution the above code can help you.
You are doing just fine."

yes, you are rightg !!
ASKER
marrowyung

one thing, in the code above:


exec EXEC msdb.dbo.sp_send_dbmail  -msg

Open in new window


still correct?

I think it should be ony one exec, right ?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
marrowyung

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 ?
ASKER
marrowyung

if I change the SP like that:

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 "From host", login_time, last_batch, DB_NAME(dbid) AS "Against database" , 
(SELECT text FROM sys.dm_exec_sql_text(sql_handle))as "Query executed" 
FROM master..sysprocesses WHERE loginame= 'ixapi' and open_tran > 0 and DATEDIFF (minute, last_batch, GETDATE()) > @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

Open in new window


is it making sense that if I found 2 spid from the cursor, then it will  kill it one by one and send out 2 x email "?

why it gives error:

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 ';'.

Open in new window

ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
marrowyung

""try to use login_time instead of last_batch with DATEDIFF"


yeah, I think so, this should be the only thing we can do.

let me try.

but it seems the error gives is :

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 '+'.

Open in new window

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
marrowyung

can't do this :

    @body = 'The long running transactions were killed and that make tempdB full, and the query is: ' + @query,

Open in new window


?

SET NOCOUNT ON added to prevent extra result sets from

Open in new window


this is a comment ?
EugeneZ

try this one and adjust for your needs:

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)  --<<EZ


DECLARE db_cursor CURSOR FOR 
SELECT spid, loginame, program_name, hostname as "From host", login_time, last_batch, DB_NAME(dbid) AS "Against database" , 
(SELECT text FROM sys.dm_exec_sql_text(sql_handle))as "Query executed" 
FROM master..sysprocesses WHERE loginame= 'ixapi' and open_tran > 0 and DATEDIFF (minute, login_time, GETDATE()) > @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))   --<<EZ
exec(@kill)                                        --<<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 

Open in new window

ASKER
marrowyung

this time no error, let me test it.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
marrowyung

now, I think something is wrong:

as we do this now:

set @query ='The long running transactions were killed and that make tempdB full, and the query is: ' + @query 

Open in new window


but what we can see is this one:

 @body = @query,

Open in new window


bring up the REAL body like that:

FETCH API_CURSOR000000000000F3EA

Open in new window


which partially correct but the characters all gone, any reason for that you can see ?
ASKER
marrowyung

The first time it runs it even show the followings:

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 > 0 THEN 20 + syscolumns.scale ELSE 19 END ELSE prec END AS COLUMN_SIZE, CASE I

Open in new window


you will just don't know why and you run again, it give the first message I show you above.
EugeneZ

what is the problem?
did you run this proc ?
did you get error?
if 'yes' - please post it
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
marrowyung

"The first time it runs it even show the followings:"

yes, I ran it but the email body don't show the complete message:

The long running transactions were killed and that make tempdB full, and the query is: FETCH API_CURSOR000000000000F3EA 

Open in new window


it just show:

FETCH API_CURSOR000000000000F3EA

Open in new window


any reason for this?

once it go inside the cursor loop it forget this string ?
ASKER
marrowyung

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

set @query ='The long running transactions were killed and that make tempdB full, and the query is: ' + @query 

Open in new window


let see tonight if the same thing happen.
ASKER
marrowyung

one thing,  as from time to time we see that the query we trying to kill is "FETCH API_CURSOR00000000000000AF"

someone said this statement is a cursor, and he/she suggest me to use:

SELECT c.session_id as "session id", c.name, c.properties as "The cursor properties", c.creation_time as "Cursor creation time", c.is_open as "is the Cursor opened" , t.text as "The SQL Query"

FROM sys.dm_exec_cursors (53) c

CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t 

Open in new window


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?

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 

Open in new window


?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.