SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
SMAAG
SMAAG
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 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.
erichjm
erichjm
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 209
Did you figure out the error?
Cem Uney
Cem Uney
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 Visits: 157
any solutions?
Joie Andrew
Joie Andrew
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6275 Visits: 2032
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"
dprathimacs
dprathimacs
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 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
Joie Andrew
Joie Andrew
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6275 Visits: 2032
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"
deanroush
deanroush
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 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....
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search