Clear Pending Procedures in the VSA

Question

How do we clear any agent procedures that are currently pending to run on all VSA agents?

Answer

Step 1 

ClearPendingProcedures MD5 Hash: 4d828a98800645e47ec0d1860f153a86

Step 2

Place the file on your SQL server Desktop or other similarly accessible location on the server

Step 3

Double click or "open" the file, this should launch SQL Server Management Studio click "connect" to authenticate.

Step 4

Click "Connect" and Execute the script:

You should see the following message: 

Step 5

Open a "New Query" window: 

And run the following command

exec [dbo].[clearPendingProcedures]

NOTE: This may take a long time to execute, in some cases hours. 

Step 6

Run the following Query:

delete from scriptAssignment
where execscripttime < getdate()
and eventinstanceid = 0

Step 7

Run the following Query

update scriptAssignment set inProc = 0 where inProc = 1