SQL 2005 Maintenance Tasks

[Aoreugif]

Hi! We have one workstation running SQL 2005 that the database got corrupt somewhere down the road, We have daily backups but unfortunately the corruption was on the backups as well. Im new to SQL and did some research online to look for best practices for daily,weekly & monthly maintenance and this is what I found if there is anything else I could add please let me know. Thank you for your time

Daily Routine Maintenance Tasks
• Check that all required SQL Server services are running.
• Check Daily Backup logs for success, warnings, or failures.
• Check the Windows Event logs for errors.
• Check the SQL Server logs for security concerns such as invalid logins.
• Conduct full or differential backups.
• Conduct Transaction Log backups on databases configured with the Full or Bulk-Logged recovery model.
• Verify that SQL Server jobs did not fail.
• Check that adequate disk space exists for all database files and transaction logs.
• At least monitor processor, memory, or disk counters for bottlenecks

Weekly Routine Maintenance Tasks
• Conduct full or differential backups.
• Review Maintenance Plan reports.
• Check database integrity.
• Shrink the database if needed.
• Compact clustered and nonclustered tables and views by reorganizing indexes.
• Reorganize data on the data and index pages by rebuilding indexes.
• Update statistics on all user and system tables.
• Delete historical data created by backups, restores, SQL Server agent, and maintenance plan operations.
• Manually grow database or transaction log files if needed.
• Remove files left over from executing maintenance plans.

Monthly or Quarterly Maintenance Tasks
• Conduct a restore of the backups in a test environment.
• Archive historical data if needed.
• Analyze collected performance statistics and compare them to baselines.
• Review and update maintenance documentation.
• Review and install SQL Server patches and service packs (if available).
• Test failover if running a cluster, database mirroring, or log shipping.
• Validate that the backup and restore process adheres to the Service Level Agreement defined.
• Update SQL Server build guides.
• Update SQL Server disaster recovery documentation.
• Update maintenance plan checklists.
• Change Administrator passwords.
• Change SQL Server service account passwords.

 

[k1pp3r]

• Update SQL Server build guides.
• Update SQL Server disaster recovery documentation.
• Update maintenance plan checklists.
• Change Administrator passwords.
• Change SQL Server service account passwords.

I didn't really read all your post since i handle mine completely different.

But i will note that changing admin passwords and SQL SA passwords, while its a good idea, it normally breaks a lot of stuff. So make sure you have a list of ALL programs that use the admin accounts so you can fix stuff when it breaks

 

[bigdogchris]

Do not allow sql logs to pile up, using up all your disk space, downing your webserver every 10 minutes until you figure out what the hell is going on

 

[Aoreugif]

What about "Shrink the database if needed"? I found conflicting info on this.