Post reply

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

  • October 14, 2009 at 11:47 am

    #210957

    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.

  • May 5, 2010 at 9:23 pm

    #1162931

    Did you figure out the error?

  • September 6, 2010 at 6:34 am

    #1216903

    any solutions?

  • September 6, 2010 at 3:41 pm

    #1217053

    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"

  • February 17, 2011 at 11:03 am

    #1288406

    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

  • February 17, 2011 at 1:38 pm

    #1288495

    Take a look at this link:

    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"

  • August 2, 2012 at 3:20 pm

    #1521159

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply