Purge Data Older Than 6 Months
|Article:HOWTO9537|||||Created: 2008-11-18|||||Updated: 2008-11-18|||||Article URL http://www.symantec.com/docs/HOWTO9537|
How can I remove data older than 6 months from the Application Metering SQL database tables?
SQL Database Administrators can write a variety of SQL statements to help purge the Application Metering database. The method below is one recommendation. A backup copy of your database is recommended before attempting to run the SQL statements below.
First determine the timeframe that is necessary to delete. In this example 6 months, or 180 days, from the current date has been chosen (as set by the very last line).
/** Show how much app metering data is older than 180 days **/
from Inv_AeX_AM_Monthly_Summary am
join resourceupdatesummary rus
on am._resourceguid = rus.resourceguid
where InventoryClassGuid = ( SELECT Guid from DataClass
WHERE DataTableName = 'Inv_AeX_AM_Monthly_Summary')
and modifieddate < getdate() - 180
Once the total number of rows has been identified, adjust the row count number in the SQL below and perform the deletion:
/**Run me first to delete the first million rows **/
set rowcount 1000000 delete from Inv_AeX_AM_Monthly_Summary_spt where _resourceguid in (select resourceguid from resourceupdatesummary where InventoryClassGuid = ( SELECT Guid from DataClass WHERE DataTableName = 'Inv_AeX_AM_Monthly_Summary') and modifieddate < getdate() - 180)
/**Run the next SQL statement after the delete query above has finished **/
set rowcount 0
Article URL http://www.symantec.com/docs/HOWTO9537