Purge Data Older Than 6 Months
| Article:HOWTO9537 | | | Created: 2008-11-18 | | | Updated: 2008-11-18 | | | Article URL http://www.symantec.com/docs/HOWTO9537 |
Question
How can I remove data older than 6 months from the Application Metering SQL database tables?
Answer
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 **/
select count(*)
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
Legacy ID
44457
Article URL http://www.symantec.com/docs/HOWTO9537
Terms of use for this information are found in Legal Notices
Thank you.