Customer reported that recently they started to noticed a lot of deadlocks in their NS logs. After enabling SQL flag trace (HOWTO7118), they found out that the deadlocks were caused when sp_Inv_AeX_SWD_Status_Summary_clean was executed. After looking on the stored procedure, it was noticed that the table been used was Inv_Aex_SWD_Status_Summary. In this case it was too large. Purging Maintenance was not enable to purge this table and multiple events were coming from each package that was enable (Software Delivery and Patch Management were the most common ones).
One of the events that causes to overpopulate this table is Verbose Reporting of Package Status Events.
Is there a way to disable the "Verbose Reporting of Package Status Events" in SMP 7 (NS7)?
AeX SWD Package events are sent usually when the following occur:
The AeX SWD Package events are not required and, in environments where the Notification Server has a lot of clients, can cause unneeded burden on the Notification Server. So in some large environments, the AeX SWD Package events are disabled on the Notification Server.
At this point there are two things that we need to consider: One, how we can stop of getting these events sent from any new package enabled? and two, how we can disable this events from all packages already created?.
A) For new packages:
There are two commom settings that we could disable to avoid these events.
1. Under Settings>All Settings>Software>Managed Delivery Settings>Run tab>Reporting section, uncheck 'Enable Verbose Reporting of Package Status Events'. It should stop those verbose reporting events from all the Software Delivery packages.
2. Under Settings>All Settings>Software>Patch Management>Microsoft Settings>Microsoft>Programs tab, uncheck 'Send Package Events'
B) For already created packages:
Run the following query (after making sure that you have a current backup of your database. This query should not be a risk but we suggest to have backups when changes are made directly in the database):
set State = replace( cast([state] as varchar(8000)),'<statusEventsEnabled>True</statusEventsEnabled>','<statusEventsEnabled>False</statusEventsEnabled>'),
ModifiedDate = getdate()
where guid in
select v.guid from vItem v
join class c on v.classguid = c.guid
where c.type = 'Altiris.Inventory.Policies.SoftwareInventoryPolicy'
or c.type = 'Altiris.SoftwareManagementSolution.SoftwareDelivery.ManagedDeliveryPolicy'
or c.type = 'Altiris.PatchManagementCore.Resources.PackageInfo'
and guid not in
where datalength (state) > 8000
Then you should be able to reduce the size of the Inv_AeX_SWD_Package_Summary table by small portions. You can use the following query:
Note: The following is a primitive SQL script can be used on SQL server 2000, 2005 or 2008. You would need to enter the table name directly into the script and replace %TableName%. We recommend that you don't use this query for every table. Some tables contains data that is required for certain processes.You can also change the variable @DaysToKeep to the needed value (default is 90 days) and/or adjust the rowcount value (this value limits the number of rows to delete on each run) to a bigger/ smaller value (default value is 10000) depending on the execution time of each run. You can run this delete script over and over till the results are 0 row affected. Please test the script on a temp table first, so you understand it. You can then use the last query to see how many rows are left in that table.