Solved

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

Posted on 2014-01-12
34
851 Views
Last Modified: 2014-02-25
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
Comment
Question by:marrowyung
  • 19
  • 8
  • 3
  • +2
34 Comments
 
LVL 1

Author Comment

by:marrowyung
ID: 39775713
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
 
LVL 24

Assisted Solution

by:chaau
chaau earned 50 total points
ID: 39775730
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39775766
in oracle side you mean ?

any link ?

in Oracel gateway ? any step by step ?
0
 
LVL 24

Expert Comment

by:chaau
ID: 39776013
Yes, this is what I meant. It all depends on how Oracle connected yo MSSQL. Can you provide more details
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39776030
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
 
LVL 42

Expert Comment

by:EugeneZ
ID: 39776188
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39776904
let me try.. thanks EugeneZ,. let me understand it for a while and will be back soon.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39778381
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
 
LVL 42

Expert Comment

by:EugeneZ
ID: 39778892
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39779255
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
 
LVL 42

Expert Comment

by:EugeneZ
ID: 39780670
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
 
LVL 42

Expert Comment

by:EugeneZ
ID: 39780676
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 100 total points
ID: 39781021
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
 
LVL 35

Expert Comment

by:David Todd
ID: 39781024
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 50 total points
ID: 39781050
>> 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
 
LVL 1

Author Comment

by:marrowyung
ID: 39781066
What time zone you are ? what time is it now? appreciated for your help. this is our morning just start work.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39781072
Hi,

In New Zealand its nearly mid-afternoon.

Regards
  David
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39781100
Tuesday evening here.
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 39781166
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39784478
"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
 
LVL 1

Author Comment

by:marrowyung
ID: 39784893
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39784906
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39785009
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
 
LVL 42

Accepted Solution

by:
EugeneZ earned 300 total points
ID: 39787202
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39792307
""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
 
LVL 1

Author Comment

by:marrowyung
ID: 39792310
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
 
LVL 42

Expert Comment

by:EugeneZ
ID: 39792532
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39793559
this time no error, let me test it.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39796008
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39796009
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
 
LVL 42

Expert Comment

by:EugeneZ
ID: 39796389
what is the problem?
did you run this proc ?
did you get error?
if 'yes' - please post it
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39796533
"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
 
LVL 1

Author Comment

by:marrowyung
ID: 39798930
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39884941
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now