Solved

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

Posted on 2014-02-19
12
720 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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 27

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

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

695 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