Solved

The SQL query from Oralce Gateway machine can hangs the MS SQL as the tempdB full

Posted on 2014-02-19
12
683 Views
Last Modified: 2014-02-25
right now our company has another company connect to our MS SQL server from Oracle gateway, but from time to time we see this kind of query

FETCH API_CURSOR00000000000000AF

Open in new window


stuck to the MS SQL and make the tempdB full as this code runs so long.

But it don't always kill the tempdb but from time to time, anyone know what it is and how to solve it?

Oracle DBA do'nt konw what it is as it is not part of their code.

we are running SQL server 2008R2 with SP2 and CU4, it seems that it is not happening on SQL server 2005 With SP2.
0
Comment
Question by:marrowyung
  • 8
  • 2
  • 2
12 Comments
 
LVL 15

Accepted Solution

by:
Aaron Shilo earned 250 total points
ID: 39872682
probaly means that application running query or stored procedure that using a cursor.
You can identify a session id of running this command

DBCC INPUTBUFFER (53) returned FETCH API_CURSOR00000000000000AF

---Then

SELECT c.session_id, c.properties, c.creation_time, c.is_open, t.text

FROM sys.dm_exec_cursors (53) c

CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39872715
but once this statement, FETCH API_CURSOR00000000000000AF, disappear from MS SQL memory, we can't see this by:

SELECT c.session_id, c.properties, c.creation_time, c.is_open, t.text

FROM sys.dm_exec_cursors (53) c

CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t 

Open in new window



any more ?

Waht c.is_open will show ? t.text is the content of the query ?

the 53 is the spid and I think it is? or what you mean is once we run:

DBCC INPUTBUFFER (53)  and it return  FETCH API_CURSOR00000000000000AF?

and then just input the spid to sys.dm_exec_cursors (spid) to that query ?
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 39872727
hi

you stated : "stuck to the MS SQL and make the tempdB full as this code runs so long."

Waht c.is_open will show ? will show if the cursor is open
t.text is the content of the query ? "yes it shoud"
nd then just input the spid to sys.dm_exec_cursors (spid) to that query ?yes
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39872839
"
you stated : "stuck to the MS SQL and make the tempdB full as this code runs so long.""

yes, and ?

how can I show how long this process has been running ? which host it comes from and what is the user account that execute that ?

Right now I use this SP to check the query ran over 30 minutes by the account ixapi:

CREATE 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.
	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), 
@servername varchar(100)
 
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;

set @msg = 'Alert: A long running transactions from gateway were killed that can potentially make tempdB full';
set @servername= CAST((select SERVERPROPERTY ('ServerName') ) as varchar (100));

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

  set @query ='The long running transaction(s) from gateway were killed on ' + @servername+ ' as it ran for more than ' + cast(@Total_time_Executed as varchar(5)) + ' minutes and the query is: ' + @query 

  set @kill = 'Kill ' + cast(@spid as varchar(5)) + ';';
  exec(@kill)  ;

	EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Telarix',
    @recipients = 'myung@pccwglobal.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


any idea to match both query to one to fully make use the whole thing ?
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39873629
I recomman you to use Adam Mechanic's stored procedure sp_whoisactive:

http://sqlblog.com/tags/Who+is+Active/default.aspx

You can install it(just execute the script you can download from the link above) directly on your master database or in a dedicated dba database, if you have one, and use it whenever you need. Is a well know procedure used widely by many DBAs around the world. Basically if you just run the procedure like

EXEC sp_whoisactive

will return a result set with details about that are actually running at that moment. It has multiple parameters that can be used depending on what you are after. The first column in that result set is the duration of the process. Also it can give you the execution plan, the actual command/query is running.

You can also set it up to run multiple times and put the result in a table that it creates on the fly. Look into the documentation.

In regards to the tempdb being filled up it is because teh process probably makes use of temp tables or does some sorting in the tempdb. It can also be caused by the setting SET ALLOW_SNAPSHOT_ISOLATION ON, which creates multiple versions of data in the tempdb database. You should also get in touch with the other side, Oracle guys, and let them know about this issue.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39875900
"You can install it(just execute the script you can download from the link above) directly on your master database or in a dedicated dba database, if you have one, and use it whenever you need. Is a well know procedure used widely by many DBAs around the world. Basically if you just run the procedure like

EXEC sp_whoisactive"

yeah, I heard about this but didn't dig into detail yet.

thanks .

"In regards to the tempdb being filled up it is because teh process probably makes use of temp tables or does some sorting in the tempdb."

of course, basically everything will stay in tempdb for a while as it need data rendering !

" You should also get in touch with the other side, Oracle guys, and let them know about this issue. "

done this one, someone here suggest me that let them change the TIMEOUT parameter of the Oracle. but our ORACLE DBA (many here! but just one MS SQL DBA) don't know what it is and someone even say what is inside tthat FETCH_API statement, but I said it is what is in the memory and it is the content !

I can tell you that this seems not cause any problme for MS SQL 2005 with SP2 but alert from SQL server 2005 just starting !!

I think I will also try yours script too to make it easlier for me at this time then sp_whoisactive !!

that SP for SQL server 2012 too and 2014 CTP2 ?

for the  SET ALLOW_SNAPSHOT_ISOLATION ON, how long the snapshot stay in tempdB? it will not last until restart, rihgt ?
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 1

Author Comment

by:marrowyung
ID: 39875904
here we go:

Released: Who is Active v11.11 


It's been several months since the last Who is Active fix, so I thought I'd call this one out specifically via a blog post.

v11.11 contains a few minor fixes and enhancements, which you can read about on the download page.

This will (I believe) be the last release that is compatible with SQL Server 2005 and 2008. v11.xx has been quite a stable release in general, with very few bugs found in the 11 months since I've released it--I do not expect to need to release any more fixes.


In the meantime, I have started work on a new version that will take advantage of several SQL Server 2012 features, as well as some SQL Server 2008 features that I was unable to previously leverage due to my efforts to keep the procedure backward-compatible with SQL Server 2005. The new version will be released in a few months, once I've had time to both implement and thoroughly test the new functionality.

Open in new window

0
 
LVL 26

Assisted Solution

by:Zberteoc
Zberteoc earned 250 total points
ID: 39876441
Yes, what is the problem? I am using this version on both SQL 2008 and 2012.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39879280
I will try this one and see what it return.

SELECT c.session_id, c.properties, c.creation_time, c.is_open, t.text

FROM sys.dm_exec_cursors (53) c

CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t  

Open in new window

0
 
LVL 1

Author Comment

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

Author Comment

by:marrowyung
ID: 39885029
one thing, today I found something of the same statement by that SINGAL spid, but when I do this:

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 (54) c

CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t 

Open in new window


I see it return 2x line by the same session_id, how it possible ?

I see from cursor properties:

1 returned result)  API | Snapshot | Read Only | Global (0)
2 returned result) API | Dynamic | Read Only | Global (0)

what is that mean ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39885032
as there are 2 x result, how can I return each of them without using Cursor again ?

while loop ? how to use it ?
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup

746 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

10 Experts available now in Live!

Get 1:1 Help Now