Hello ScaryDBA,

The QueryTimeout hint will help. Another colleague also indicated that he ran into query timeout issues, and was able to resolve them with the following index. This will be added in the next version - I am not sure how I missed it in this release:-)

CREATE NONCLUSTERED INDEX [IX_PolicyHistoryID]
ON [policy].[PolicyHistoryDetail] ([PolicyHistoryID])

BTW - So sorry for the long response time. On vacation basking in the Mexican sun. Too much fun.

Thanks. I'll try the index as well. I just wanted to be sure I hadn't broken anything.

This didnot work with me. I even created the index but no luck. Please find attached the Error snapshot.

Hello,
Setting the QueryTimeout to 0 seconds and even by creating the non clustereed index has NOT resolved my problem. The job step keeps on failing randomly. It is scheduled to run every night at 10 pm. Its passes rarely and fails more oftenly .
Is there any resolution for this issue?

SQLUSER, I probably should have come back and posted more. Come to find out, there's a bug in the InvokeSqlCmd that it doesn't use the setting of zero to mean unlimited. Instead it just uses the default. To get a longer timeout, you have to set the number higher, manually. It's a bug on Connect if you look for it.

I would suggest this problem be fixed as a high priority. I've run the index creation script and changed all timeouts in the the .ps1 to 0 and still get the same results. This is stopping all of my jobs from running - every time.

Clearing the policy. tables lets the jobs run...

I get the errors also, you think if I change to silentlycontinue rather than stop will work?

I guess it makes sense to build a staging database and keep it small for these inserts, the dashboard should read from a master database with a batch process that will take all of the data from staging and populate the master. Next version?

I created a staging database that all policies are running against and an MDW database where all the reports are running against. Every night you can setup a process to export all the new data into MDW and clean out the staging database.

USE EPMDB
GO
ALTER PROCEDURE policy.LoadIntoEPMDB
AS

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET NOCOUNT ON

--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRY

BEGIN TRAN [InsertDeleteEPM]
    INSERT INTO [EPMDB].[policy].[PolicyHistoryDetail]
           ([PolicyHistoryID]
           ,[EvaluatedServer]
           ,[EvaluationDateTime]
           ,[MonthYear]
           ,[EvaluatedPolicy]
           ,[policy_id]
           ,[CategoryName]
           ,[EvaluatedObject]
           ,[PolicyResult]
           ,[ExceptionMessage]
           ,[ResultDetail]
           ,[PolicyHistorySource])
    SELECT 
            [PolicyHistoryID]
           ,[EvaluatedServer]
           ,[EvaluationDateTime]
           ,[MonthYear]
           ,[EvaluatedPolicy]
           ,[policy_id]
           ,[CategoryName]
           ,[EvaluatedObject]
           ,[PolicyResult]
           ,[ExceptionMessage]
           ,[ResultDetail]
           ,[PolicyHistorySource]
    FROM EPMDBStaging.policy.PolicyHistoryDetail

    INSERT INTO [EPMDB].[policy].[PolicyHistory]
           ([EvaluatedServer]
           ,[EvaluationDateTime]
           ,[EvaluatedPolicy]
           ,[EvaluationResults])
    SELECT  [EvaluatedServer]
           ,[EvaluationDateTime]
           ,[EvaluatedPolicy]
           ,[EvaluationResults]
    FROM EPMDBStaging.policy.PolicyHistory

    INSERT INTO [EPMDB].[policy].[EvaluationErrorHistory]
           ([EvaluatedServer]
           ,[EvaluationDateTime]
           ,[EvaluatedPolicy]
           ,[EvaluationResults])
    SELECT
           [EvaluatedServer]
           ,[EvaluationDateTime]
           ,[EvaluatedPolicy]
           ,[EvaluationResults]
    FROM EPMDBStaging.policy.EvaluationErrorHistory

    SAVE TRAN [InsertDeleteEPM] 
        DELETE FROM EPMDBStaging.policy.EvaluationErrorHistory
        DELETE FROM EPMDBStaging.policy.PolicyHistory
        DELETE FROM EPMDBStaging.policy.PolicyHistoryDetail
COMMIT TRAN [InsertDeleteEPM]

END TRY
BEGIN CATCH

SELECT ERROR_MESSAGE() AS ErrorMessage;
ROLLBACK TRAN [InsertDeleteEPM]

END CATCH


SET NOCOUNT OFF
GO

I realize this is an OLD thread, but I am having the same issue as above.

I have not adjusted the timeout parameter for PS, because I would rather get EPM to execute in a nominal time. I have a rather large environment that I am executing policy on (650+ SQL Servers), and I am testing one category at a time.

Two things that may help, I have set up registered servers in a particular hierarchy so that I can run policy against PROD servers and/or Non-PROD servers, or 2000 servers and/or 2005 servers and/or 2008 etc. however, I have not been able to define the groups/sub-groups properly in the PS parameters. If I can define the sub groups properly, it may help reduce execution time, and eliminate time outs. Also, it doesn't seem to be the policies themselves that are timing out, but the insert of the details - by shredding the XML files - that is causing the PS script to time out. I tried disabling the XML indexes while the PS script executed, but that didn't help. I noticed if I executed SELECT * on a table with an XML field in it, even with only 4k rows, the query took around 23 seconds to complete, but if I selected all but the XML columns, it was microseconds.

I looked at the function policy.pfn_ServerGroupInstances, and I will attempt more tests with restricted test servers to see if that helps, but I am suspecting right now, the XML files as a major part of the issue. The biggest XML file I had was only 7 MB, so I don't think it is file size.

I have lots more testing to do, but I wanted to put the word on this thread, in case there are still folks encountering the issue, and to spur conversation if there has been solutions (besides those already mentioned) since the last activity in the thread.