Environment
Situation
Resolution
The DeleteOldArchiveData stored procedure that ships with AppManager 7.0.1 and higher, has 4 variables. The following are the four variables, and their default values:
@days INT = 90,
@DataID INT = 0,
@MaxRows INT = 10000,
@MaxLoop INT = 100
Definitions of the variables:
@days - Determines how many days worth of data to retain. So using the default value of '90', and data found to be older than 90 days old, is subject to being deleted.
@DataID - If you wish to delete ONLY data that has a specific DataID value, you can enter it in the second variable's position. If you want to delete ALL data, regardless of DataID, use the default value of '0'.
@MaxRows - This is how many rows of ArchiveData to delete per loop.
@MaxLoop - This is haw many times to loop the process per iteration.
The maximum number of rows that may be deleted in a single iteration of the stored procedure is calculated as:
@MaxRows x @MaxLoop = Max rows to be deleted
Using the default values:
10000 (MaxRows) x 100 (MaxLoop) = 1,000,000 rows
By default, if you use the following command:
exec dbo.deleteoldarchivedata
The SQL Query that is executed will use the above listed defaults, and would effectively be:
deleteoldarchivedata 90,0,10000,100
This translates as:
- Delete the first ArchiveData found that is:
- More than 90 days old
- Regardless of DataID value
- In sets of 10,000 rows
- Loop 100 times.
If you wish to delete more than 1,000,000 rows per iteration of the stored procedure, you can either increase the number of rows to delete per loop, or increase the number of loops per iteration. It is recommended that you increase the number of loops, as this will task the SQL Server's resources less than adding to the number of rows per loop. However, increasing either value will increase the amount of time it takes for the stored procedure to complete.
Management Server Considerations:
Keep in mind that while this task is executing, your Management Servers will likely find themselves having to store data locally in their IOC Data Queues, until the stored procedure completes.
If you expect to run this stored procedure frequently, or on a very short iteration schedule, you will want to consider how long it takes the Management Servers that are tied to the afffected QDB to catch up on delivering data to the QDB, and fit that amount of time into your iteration schedule.
To see how long it takes for the MSs to get caught up on data insertion after this stored procedure has completed an iteration, you can launch Perfomance Monitor on the Management Servers, and load the following counter:
Object: NetIQms.exe
Counter: IOC Data Queue
Once you have the counter loaded, execute the stored procedure one time using the default values, and when the stored procedure completes its iteration, watch how long it takes for the IOC Data Queue to catch up.
You will want to take that amount of time into consideration when deciding how frequently to execute this stored procedure, so that your Managemetn Servers have time to get fully caught up before the stored procedure kicks off another iteration.