How to manually create a Microsoft SQL Job to Age your Statistical and Auditing data. (4319283)

If your database is getting to large and you would like to get rid of some data but don't have sysadmin rights on the SQL Server to deploy the jobs through the MessageStats console you can give the Resolution to your Database Administrator to remove the data.

How to manually create a Microsoft SQL Job to Age Stats and Audits:

1. Open SQL Management Studio
2. Expand Databases | MessageStats | Table and Right click on the T_Configuration table and select Open Table or Edit Top 200 Rows in SQL 2008
3. Change the StatsRetentionDays to 365 and press enter and change the the AuditRetentionDays to 365 and press enter. And close out the Tab.
4. Expand SQL Server Agent and Right click on Jobs and Select New Job.
5. In the General section For the Name: put QMDataAge .
6. For the Owner: make sure it is an account that is a sysadmin on the SQL server.
7. In the General section for the Category: Select Database Maintenance.
8. On the Steps Tab Select New
9. In the General section The Step name should be QMSDataAge_SQL_Step0
10. The Type should be Transact-SQL script (T-SQL)
11. The Database should be MessageStats
12. And in the Command section put EXEC PR_DataAgingDelStats and Click OK
13. On the Steps Tab Select New
14. In the General section The Step name should be QMSDataAge_SQL_Step1
15. The Type should be Transact-SQL script (T-SQL)
16. The Database should be MessageStats
17. And in the Command section put EXEC PR_DataAgingDelAudits and Click OK
18. On the Schedules Tab please select New
19. For the Name Select QMS_SCHEDULE
20. For the Schedule Type Select Recurring and check the Enabled box.
21. For the Frequency we recommend setting this to run daily (Schedule at a time when the MessageStats Tasks are not running) And Click OK then OK again and the job has been created.