If you have enabled the PRO integration with operations manager in your VMM environment, then this blog post might be for you. If your VMM Administrator Console is taking too long to load and the memory footprint of the vmmadmin.exe is too large (i.e. the vmmadmin.exe is taking up too much RAM), then it is possible that you have a lot of PRO tips in the VMM Database and they need to be purged. During the initial load of the Administrator Console, we also load all PRO tips in the UI, both active and completed ones, causing this side effect.

Even though our Jobs view has a built-in grooming cycle, the PRO tips table does not. if you would like to purge some of the completed PRO tips, you can do that using the SQL script in this blog post.

  • You can modify the NumberOfDays variable to choose how many days of PRO tips to leave back in the database. Currently the script is configured to purge any PRO tip older than 30 days.
  • This script is provided "AS IS" with no warranties, and confers no rights. You assume all risk for your use. Please back up your VMM Database before executing the above SQL script.
  • Once you successfully execute the script, you can restart the Administrator Console and see the improved performance

<<

/*

Query deletes all ProTips which

- are closed (@ProTipStateClosed = 7)

- are created @NumberOfDays before today

- don't have a entry in jobs table

*/

DECLARE @NumberOfDays INT, @ProTipStateClosed INT

SET @NumberOfDays = 30;

SET @ProTipStateClosed = 7;

DELETE FROM tbl_PRO_PROTip WHERE

    CreationTime <= GETDATE() - @NumberOfDays AND

    ObjectState = @ProTipStateClosed

    AND

    ObjectId NOT IN

    (

        -- ProTips that have a job trail.

        SELECT Tasks.PROTipID FROM tbl_TR_TaskTrail AS Tasks INNER JOIN tbl_PRO_PROTip AS Protips

        ON Tasks.PROTipID = Protips.ObjectId

    )

>>