SQL Agent Job "hangs" due to a wait type PREEMPTIVE_OS

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » SQL Server Agent » SQL Agent Job "hangs" due to a wait type...

SQL Agent Job "hangs" due to a wait type PREEMPTIVE_OS_PIPEOPS

Rate Topic

Display Mode

Topic Options

Author

Message

SMAAG

Posted Wednesday, October 14, 2009 11:47 AM

SSC 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

erichjm

Posted Wednesday, May 05, 2010 9:23 PM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, February 23, 2013 6:38 PM
Points: 1, Visits: 162

Did you figure out the error?

Post #916638

Cem Uney

Posted Monday, September 06, 2010 6:34 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, January 04, 2013 8:13 AM
Points: 45, Visits: 128

any solutions?

Post #981081

Joie Andrew

Posted Monday, September 06, 2010 3:41 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, May 06, 2013 5:58 AM
Points: 535, Visits: 1,010

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

dprathimacs

Posted Thursday, February 17, 2011 11:03 AM

Forum 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

Joie Andrew

Posted Thursday, February 17, 2011 1:38 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, May 06, 2013 5:58 AM
Points: 535, Visits: 1,010

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

deanroush

Posted Thursday, August 02, 2012 3:20 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 02, 2013 9:10 PM
Points: 26, Visits: 263

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 »

Permissions