Database Maintenance Essentials – Resources
I told people in New York at SQL in the City that I’d post some resources on the blog from my talk. My apologies for not getting it done over the weekend, but during a little downtime in Austin I’m getting it done.
From the last slide, a checklist of things for you to look at on your instances.
- Backups scheduled on all database (full and log)
- DBCC CHECKDB running regularly on all databases
- Test restores scheduled
- Manage mdf/ndf/ldf file sizes
- Proactively monitor and maintain indexes and statistics
- Monitor jobs and set up alerts
At work, someday soon, but in the next 30 days, go through the checklist on your important servers, or all your production servers, and assess your maintenance.
From the slide deck, which will come soon in email. These are a list of links and resources from the talk.
- Fragmentation Recommendations from Paul Randal - http://www.sqlskills.com/BLOGS/PAUL/post/Where-do-the-Books-Online-index-fragmentation-thresholds-come-from.aspx
- Backup Script from Ola Hallengren - ola.hallengren.com
- How to Monitor Your SQL Server for Performance and High Availability
- How to Defragment Indexes for Peak Performance
- Inside the SQL Server Transaction Log
- 2012 Diagnostic Queries (Glenn Berry) – http://sqlserverperformance.wordpress.com/category/sql-server-2012/
- SQLFool Missing Indexes – http://sqlfool.com/2009/04/a-look-at-missing-indexes/
- http://sqlblog.com/blogs/elisabeth_redei/archive/2009/03/01/lies-damned-lies-and-statistics-part-i.aspx (part II, part III)
- Multiple log files and why they’re bad – SQLskills, Paul Randal
- Reorganizing and Rebuilding Indexes – http://msdn.microsoft.com/en-US/library/ms189858%28v=SQL.90%29.aspx
- Restore with Verify Only – http://msdn.microsoft.com/en-us/library/ms188902.aspx
- Database Tuning Advisor Overview – http://msdn.microsoft.com/en-us/library/ms173494%28v=sql.105%29.aspx
- Transaction Log File VLFs – http://sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
- Duplicate Indexes – http://www.sqlskills.com/BLOGS/KIMBERLY/post/UnderstandingDuplicateIndexes.aspx