I'm revamping our housekeeping & maintenance scripts. I would be interested to hear what data you record for information-purposes, or variations on housekeeping that you perform.
We have an "Admin" database which looks after this type of thing. That contains all informational data that we record (e.g. database disk file size over time, and when databases were backed up etc), and it also contains Sprocs for the actual database housekeeping & maintenance.
Our Admin Database contains:
Table of database names and the "category" of backup that they need. Any newly created database is automatically added to the table with a "Normal" backup schedule.
High priority = TLog backups every 10 minutes, DIFF at midnight, FULL on Sunday night
Normal priority = same but TLog backup is once per hour
We have an Sproc that makes the database backups, and we EXEC that Sproc whenever we want to take a "manual" backup (e.g. before a risky procedure!). Parameters are: database name (default = current DB), Backup Type (TLog, Diff, Full) and an optional Comment. We don't allow any other means of taking a backup which ensures that the backup files are all in the "normal" folder.
Index Rebuilds and Update Statistics
We have an SProc with a parameter for the name of the Database. Various other parameters available to control the threshold for Fragmentation level etc., but I don't think we ever fiddle with them. This will basically rebuild all indexes that are fragmented in all databases (actually for any index which is not tiny it uses REORGANISE rather than REBUILD). Like the backup we have a High priority and Normal priority and any new database is automatically added at the Normal priority. Normal only runs at weekends, High priority runs every night. We record, historically, how fragmented the indexes were (and whether they were rebuilt, or not)
We don't, currently, have any means of detecting when Statistics are out of date, so when the Index Rebuild SProc runs it Updates Statistics on ALL tables (even if the index was not rebuilt because it was not fragmented). I'd like to improve that and would appreciate any ideas on thresholds for trigger an Update Statistics on a particular table/index.
Information Gathering - collected once Daily
Server Config - gather, and store "historically", various Registry and ServerProperty settings.
File Info - ditto for physical database files. We store the path/filename although, for us, this should never be non-standard, but would be handy for a post mortem if the DB moved somewhere strange! More importantly, for us, is the size of the files and thus the growth-rate-over-time. Always nice to give management early warning of a purchase of more disk space / a new server
Various rows from sys.dm_os_wait_stats
Various rows from sys.dm_db_index_usage_stats
Information Gathering - collected every Ten Minutes
Various rows from sys.dm_os_performance_counters (for some counters a measurement Delta is calculated, and stored, from two readings taken 10 seconds apart)
A Purge routine deletes old/stale historical data (mostly that is anything older than 3 months)