Database has grown very large with Notification Server 6 SP2

book

Article ID: 179882

calendar_today

Updated On:

Products

Management Platform (Formerly known as Notification Server)

Issue/Introduction

 

Resolution

Problem
The database has grown very large; how can I fix it?

Environment
Notification Server 6.0 SP2 and earlier

Cause
Notification Server SP2 and earlier does a bulk delete on the tables when they are purged. When the delete command runs longer than the timeout value, the delete is ended. This happens when either the tables get extremely large before Purge Maintenance is enabled or the Purge Settings are changed to much smaller values. The result is that no entries are deleted.

Resolution

Short Term resolution:

To resolve this in the near future, you must manually trim the Purge Settings back using the Stored Procedures that are timing out. After the tables are trimmed down, the Purge Maintenance will begin working again.

The first step is to determine which tables are large. These are usually Evt_ Tables.  Utilize the SQL script found in KB Article 21310 to determine the table sizes.

To sort the results properly, the " KB" needs to be edited off each entry. Choose Edit > Replace from the menu, or press Ctrl + H. Search for " KB" (space KB), and then click Replace All. After this, you can sort the data by table size. With the list sorted, you can pick out the largest tables. Look for tables named Evt_*. These are the tables that Purge Maintenance is failing to trim down. Find the Evt* Table with the Largest Row count, and make a note of the number.

Purge Maintenance is accountable for keeping the following Tables trimmed back:

Evt_NS_Security_Descriptor_Change
Evt_Push_Agent_Install_Service
Evt_AeX_Client_LogOn
Evt_AeX_Package_Server_IIS_Status
Evt_AeX_Package_Server_Package_Event
Evt_AeX_Push_Status
Evt_AeX_RapidInstall
Evt_AeX_SWD_Execution
Evt_AeX_SWD_Package
Evt_AeX_SWD_Status
Evt_Client_Transport_Status
Evt_NS_Client_Config_Generation
Evt_NS_Client_Config_Request
Evt_NS_Client_Pkg_Info_Request
Evt_NS_Event_History
Evt_NS_Event_Machine_Usage_Summary
Evt_NS_Event_Primary_User_Summary
Evt_NS_Event_Queue_Status
Evt_NS_Item_Management
Evt_NS_Log
Evt_NS_New_Computer_Discovered
Evt_NS_Package_Server_Config_Warning
Evt_NS_Process_Statistics
Evt_NS_Product_Installation
Evt_NS_Report_Run
Evt_NS_Resource_Discovery
Evt_NS_Scheduled_Event
Evt_NS_Security_Permission_Demand
Evt_NS_Security_Privilege_Demand

To trim these tables down, you will run the attached Purge Script.txt in Query Analyzer. Open the script, and copy it in to Query Analyzer but don't run it yet. 

Pay attention to the following section of the script:

------------------------------------------------------------------------
------------------------------------------------------------------------
-----***            
-----***      ENTER HOW MANY DAYS WORTH OF DATA TO DELETE 
-----***            
------------------------------------------------------------------------
------------------------------------------------------------------------
 
select @DAYStoKEEP = 400  -- ENTER HOW MANY DAYS TO KEEP IN THE TABLE
select @ROWStoKEEP = 800000 -- ENTER HOW MANY ROWS TO KEEP IN THE TABLE

These two variables, DAYStoKEEP and ROWStoKEEP represent the desired Purge Maintenance > Purge Resource Events settings for Keep data for XX Days, and Row Count.  Make a note of your Purge Maintenaintenance > Purge Resource Events settings. 

Note: The time variable needs to be converted to Days in order to use the Script accurately.

What you'll do is run the script many times, trimming down the tables a little at a time. It is recommended to take a couple of days to get the database down to the size that is required. The script may run for many minutes on each iteration. It's optimum to run the script when the server is not busy.

Make the ROWStoKEEP variable the number found above that represents the Largest Row Count for an Evt* Table. Leave the days at 400, and run the script the first time. Now run the script by decreasing the days by 50 each time, until you get to the desired days. 

Now run the "Database Table Size Query.txt" script again, to find our new Largest Evt Table Row Count. Now make the ROWStoKEEP variable the new Largest Row Count number, minus 50,000 and Run the Script. Drop the number by 50,000 to 100,000 each time until you reach the number specified in the Purge Maintenance.

At this point, you've trimmed the days and row count down to the Purge Settings level. They should be able to keep up in the future, but it's worth checking the "Database Table Size Query.txt" script every week for the next month, to make sure that they are doing their job. Once confidence is built that it is working, it may still be worth it to check back every month or two. If Purge Maintenance Settings are ever lowered, this procedure will need to be followed again.

Long Term resolution:

Service Pack 3 still has issues with Purge Maintenance.  Please see KB 31988 for further resolution.

Attachments

Purge Script.txt get_app