SQL Agent Job "hangs" due to a wait type PREEMPTIVE_OS_PIPEOPSWe 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_cmd. Shell. Originally, these were DTS packages in SQL 2. The default t DTExec used will be the 64-bit version, but if you have elected to install the 32-bit runtime as well, you can use the 32-bit version of DTExec. This allows you to access 32-bit only resources such as Jet again. See C:\Program Files (x86)\Microsoft SQL. Recently we upgraded to SQL 2. DTS packages to SSIS 2. Win 2. 00. 8, and are running in a 6. The SP still executes the packages via xp_cmdshell but now uses the dtexec. About 9. 0% 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 2. Windows 2. 00. 3 environment leaning toward an issue with our new environment.**Our environment is SQL 2. I posted it here for now.
I think its issue with the account that you use while executing from the proc using xp_cmdshell. You need to give it access to location where you place excel file.SQL Server MVP.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
November 2016
Categories |