Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1010
  • Last Modified:

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.
0
marrowyung
Asked:
marrowyung
  • 19
  • 8
  • 3
  • +2
4 Solutions
 
marrowyungAuthor Commented:
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?
0
 
chaauCommented:
A little bit different approach: have you tried to set up a "Query Timeout" (or equivalent) in your Oracle to MSSQL connection? Many providers have this option.
0
 
marrowyungAuthor Commented:
in oracle side you mean ?

any link ?

in Oracel gateway ? any step by step ?
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
chaauCommented:
Yes, this is what I meant. It all depends on how Oracle connected yo MSSQL. Can you provide more details
0
 
marrowyungAuthor Commented:
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 ?
0
 
Eugene ZCommented:
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
0
 
marrowyungAuthor Commented:
let me try.. thanks EugeneZ,. let me understand it for a while and will be back soon.
0
 
marrowyungAuthor Commented:
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 ?
0
 
Eugene ZCommented:
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
0
 
marrowyungAuthor Commented:
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" ?
0
 
Eugene ZCommented:
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
0
 
Eugene ZCommented:
0
 
David ToddSenior DBACommented:
Hi,

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

Warning, this is dangerous!

Datediff counts edges. To illustrate, run the following:
use tempDb
go

declare @StartDateTime as DateTime
declare @EndDateTime as DateTime

set @StartDateTime = '2007-12-31 23:59:59'
set @EndDateTime = '2008-01-01 00:00:00'

set nocount on
select @StartDateTime, @EndDateTime

select datediff( year, @StartDateTime, @EndDateTime ) as Years
select datediff( quarter, @StartDateTime, @EndDateTime ) as Quarters
select datediff( month, @StartDateTime, @EndDateTime ) as Months
select datediff( week, @StartDateTime, @EndDateTime ) as Weeks
select datediff( day, @StartDateTime, @EndDateTime ) as Days
select datediff( hour, @StartDateTime, @EndDateTime ) as Hours
select datediff( minute, @StartDateTime, @EndDateTime ) as Minutes
select datediff( second, @StartDateTime, @EndDateTime ) as Seconds

Open in new window


I suggest that you use
DATEDIFF (minute, last_batch, GETDATE()) > 60

Open in new window


HTH
  David
0
 
David ToddSenior DBACommented:
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 ...
0
 
Anthony PerkinsCommented:
>> DATEDIFF (hh, last_batch,GETDATE())>1

Warning, this is dangerous!

I was beginning to wonder, if anyone was going to point that out.  Clearly anyone who suggest that the following is a desired result is going to be disappointed:
SELECT DATEDIFF(HOUR, '20140114 08:59', '20140114 09:00')

As suggested previously if you are going to use DATEDIFF() you should use minutes or seconds.
0
 
marrowyungAuthor Commented:
What time zone you are ? what time is it now? appreciated for your help. this is our morning just start work.
0
 
David ToddSenior DBACommented:
Hi,

In New Zealand its nearly mid-afternoon.

Regards
  David
0
 
Anthony PerkinsCommented:
Tuesday evening here.
0
 
Eugene ZCommented:
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!
0
 
marrowyungAuthor Commented:
"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 !!
0
 
marrowyungAuthor Commented:
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 ?
0
 
marrowyungAuthor Commented:
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 ?
0
 
marrowyungAuthor Commented:
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

0
 
Eugene ZCommented:
check this adjusted code and if you need try to use login_time instead of last_batch with DATEDIFF

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
	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) ,
 @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, 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  
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 = '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

END

Open in new window

0
 
marrowyungAuthor Commented:
""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

0
 
marrowyungAuthor Commented:
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 ?
0
 
Eugene ZCommented:
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

0
 
marrowyungAuthor Commented:
this time no error, let me test it.
0
 
marrowyungAuthor Commented:
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 ?
0
 
marrowyungAuthor Commented:
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.
0
 
Eugene ZCommented:
what is the problem?
did you run this proc ?
did you get error?
if 'yes' - please post it
0
 
marrowyungAuthor Commented:
"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 ?
0
 
marrowyungAuthor Commented:
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.
0
 
marrowyungAuthor Commented:
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


?
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 19
  • 8
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now