Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Agent Job "hangs" due to a wait type PREEMPTIVE_OS_PIPEOPS Expand / Collapse
Author
Message
Posted Wednesday, October 14, 2009 11:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 29, 2011 8:15 AM
Points: 41, Visits: 113
We have a custom back end process we use to process batch jobs asynchronously through a queuing table. Many of the jobs that are executed in this process run through a stored procedure that executes a SSIS package using the xp_cmdShell. Originally, these were DTS packages in SQL 2000 and 2005 which executed using the dtsrun executable.

Recently we upgraded to SQL 2008, migrated the DTS packages to SSIS 2008, added a cluster to the mix, upgraded to Win 2008, and are running in a 64 bit environment. The SP still executes the packages via xp_cmdshell but now uses the dtexec.exe (32 bit version). About 90% of the time we have no issues. However, in two instances we have had the SQL Agent job "hang" due to a wait type on the xp_cmdshell. The wait type is PREEMPTIVE_OS_PIPEOPS. Each time we have tried killing the spid, however, this created a rollback that appeared could take hours to complete. The only way to release the pressure and allow users to process again was to fail over the cluster to another node.

The resolution here is not a long term fix obviously. I'm currently trying to determine what is causing this wait type, how to prevent it, etc...I'm leaning toward a concurrency issue and considering making the process run single threaded to try and avoid this. However, since this was never an issue in our SQL 2005 / Windows 2003 environment leaning toward an issue with our new environment.

**Our environment is SQL 2008 but unable to find a forum for the sql agent in the 2008 forums so I posted it here for now.
Post #802987
Posted Wednesday, May 5, 2010 9:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:29 PM
Points: 1, Visits: 209
Did you figure out the error?
Post #916638
Posted Monday, September 6, 2010 6:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 8:35 AM
Points: 45, Visits: 135

any solutions?
Post #981081
Posted Monday, September 6, 2010 3:41 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 7:56 AM
Points: 709, Visits: 1,425
Do you have to execute the SSIS packages with dtexec? Is there a reason you cannot import the packages into SQL and change the job step to SSIS package?

Joie Andrew
"Since 1982"
Post #981252
Posted Thursday, February 17, 2011 11:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 21, 2013 5:47 PM
Points: 1, Visits: 8
I am having the same problem here. I am executing a stored procedure and it is waiting on a wait type called PREEMPTIVE_OS_PIPEOS. Stored procedure call is made by SSIS package as part of a SQL job. Are there any suggestions or solutions?

Please advice.



PDR

Post #1065869
Posted Thursday, February 17, 2011 1:38 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 7:56 AM
Points: 709, Visits: 1,425
Take a look at this link:

[url=http://sqlblogcasts.com/blogs/christian/archive/2008/06/06/new-sql-server-2008-wait-types-preemptive-and-ft.aspx][/url]

It seems that a lot of the preemptive wait stat types have not made it to bol yet. It seems from the name that it is a process that is piped to the OS for processing and it taking a while to finish.


Joie Andrew
"Since 1982"
Post #1065975
Posted Thursday, August 2, 2012 3:20 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 8, 2014 8:18 AM
Points: 26, Visits: 265
I just experienced this wait type as well. I was running a stored procedure in SSMS when I noticed an error in the output window. The error was "An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.". The query indicated that it was still executing. I switched to the SSMS Activity Monitor and there it was "PREEMPTIVE_OS_PIPEOS".

My stored proc repeatedly calls xp_cmdshell which is calling a C# command line utility that loads an image into an existing row/column of type Image. The images are not large, typically 10-15K. About 75 images had been uploaded when the error occurred. The C# utility uses a filestream reader to load a byte array from the disk file. The byte array is then directly assigned to the parameter value in an UPDATE statement passed via System.Data.SQLClient.

The SQL Server is 32-bit SQL 2008 R2 Standard Edition. The server is Windows Server 2003 Standard Edition. 3GB memory. No solution yet, just sharing my pain. Will rerun after an after-hours reboot and see if any better....
Post #1339542
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse