Problem

Retention of records and files, aka delete old logs and data

Solution

Regular Schedule: Everyday at 2AM

Solution: Native T-SQL and CMD

Delete old logs

1 CommandLog table

Delete records that are older than 30 days from DBA.dbo.CommandLog table

2 Output file

Delete CommandLogCleanup*.txt files that are older than 30 days from

S:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\

3 Backup history

Delete backup history records that are older than 60 days

4 Job history

Delete purge history records that are older than 60 days

5 Agent Error Log

Cycle agent error logs

6 Whoisactive data

Purge whoisactive data

7 Backup testing data

Delete backup data collected for custom backup configurations

8 Audit file retention

Delete old audit files