February 3, 2010 at 9:06 pm
Comments posted to this topic are about the item What Do You Include in Your Database Maintenance Plans?
Brad M. McGehee
DBA
February 4, 2010 at 4:09 am
I have a simple maintenance job that runs at Midnight to cycle the SQL Error Log. That way a Log covers a Maximum 24 Hr Period.
February 4, 2010 at 7:06 am
I always check available disk space. Several times I have had temp db go wild, or someone/something steal it away. Rapid decrease merits further investigation.
Performance metrics.
Failed jobs.
Open transactions
Check Database Statuses and Recovery Plans - Make sure nobody has changed them.
Occasionally I check identity columns, to see if they are close to their limit.
February 4, 2010 at 7:44 am
Find all Untrusted but Enabled constraints
Estimate the amount of time each will take to test for existing rows in violation of the constraint (DBCC CHECKCONSTRAINTS)
Determine if that will go past the maintenance window or if it will take more time than any one operation is allowed; if it will go past, proceed to the next untrusted but enabled constraint
Execute DBCC CHECKCONSTRAINTS, if no rows, proceed, otherwise, record the rows (all of them).
Estimate re-trust (ALTER... WITH CHECK CHECK... which would render the DBCC a complete waste of time if it didn't batch abort when it finds a row in violation) time
Determine timing as above
Re-trust the constraint.
Time estimation done based on parms passed in until enough of a given specific operation has been done to use a cumulative average with a fudge factor.
This runs daily with tight time limits (no more than a few minutes total maintenance window, no more than a few seconds per operation) to reduce contention and locking possibilities within the maintenance window, and weekly with much more generous limits within a much larger maintenance window.
P.S. How many others actually set up jobs that stay within a known maintenance window (i.e. they're designed to do what they can within a window of time, but not exceed the window)?
February 4, 2010 at 10:24 am
Separate from a maintenance plan, but still running regularly, I have included these items:
- scan of backup files. Gather size, compare to previous size. If > 20% larger, raise a flag
- job scan for failures, notify someone if there's an error
- scan available disk space, if < 25%, let someone know
- load sp_configure options, compare with previous values. Note any differences. Store new values
Just realized I should have been scanning for new logins as well.
March 31, 2011 at 3:48 pm
Our agency is just now upgrading from SQL Server 2005 to SQL Server 2008 R2. I took this change as an opportunity to finally read your book. And I couldn't be more grateful. Thank you! for taking the time to write it.
******************
QUESTION:
Maintenance of our server has been a discipline that has grown in fits and starts over the years. After reading your book, I realized that we are missing a rather important piece: Other than setting up a Clean Up History task (if that counts), I do not think we are doing anything to maintain the system databases. I volunteered to take on this piece.
What isn't clear to me, is what the best practices for maintaining system databases would be. I'm not confused about frequency, but about which maintenance tasks apply to system databases. The book mentions that practices would be different for system vs user databases, but all the examples appear to be for user databases.
This entry in SQL Server Central seems to say that only backing up and doing a database check are what are needed for system databases. I did a search on the internet to try to get this question answered and found two conflicting answers, both by MVPs, on this topic.
My question is: Do I understand you correctly about only needing the 2 tasks (backup and check db) for maintaining system DBs? And if so, why wouldn't one also rebuild indexes?
Thanks again for providing this book. I doubt I would have been able to read it any other way.
April 1, 2011 at 10:15 am
JJ B (3/31/2011)
QUESTION:Maintenance of our server has been a discipline that has grown in fits and starts over the years. After reading your book, I realized that we are missing a rather important piece: Other than setting up a Clean Up History task (if that counts), I do not think we are doing anything to maintain the system databases. I volunteered to take on this piece.
What isn't clear to me, is what the best practices for maintaining system databases would be. I'm not confused about frequency, but about which maintenance tasks apply to system databases. The book mentions that practices would be different for system vs user databases, but all the examples appear to be for user databases.
This entry in SQL Server Central seems to say that only backing up and doing a database check are what are needed for system databases. I did a search on the internet to try to get this question answered and found two conflicting answers, both by MVPs, on this topic.
My question is: Do I understand you correctly about only needing the 2 tasks (backup and check db) for maintaining system DBs? And if so, why wouldn't one also rebuild indexes?
Thanks again for providing this book. I doubt I would have been able to read it any other way.
Most of the DBAs I know only perform daily full backups and daily DBCC CHECKDB on system databases as part of their regular backup routine, and the cleanup tasks on the MSDB (perhaps weekly or monthly). If other DBAs perform other tasks, it would be interesting to hear what they do, how often, and why.
Brad M. McGehee
DBA
April 1, 2011 at 10:22 am
Thanks for taking the time to reply!
I think you have already answered this next question, but in case you have more to say, I'll go ahead and ask it:
I came across an article that talks about the importance of backing up the hidden Resource database:
"...in SQL Server 2005 and higher versions there is a new hidden read only system database named Resource database which was introduced by Microsoft. ... Resource database basically contains copies of all system objects that are shipped with SQL Server 2005 and SQL Server 2008. In order to take the backup of Resource Database you need to perform a file based or a disk based backup of mssqlsystemresource.mdf and mssqlsystemresource.ldf files, by treating the files as if they were like any other binary files, instead of a database file. In Maintenance Plan as there is no other task available which ... The command used for copying mssqlsystemresource.mdf and mssqlsystemresource.ldf files is mentioned below. ..."
Do you have any opinions on this? I've never heard of this before, but it sounds important.
April 1, 2011 at 10:48 am
JJ B (4/1/2011)
I think you have already answered this next question, but in case you have more to say, I'll go ahead and ask it:
I came across an article that talks about the importance of backing up the hidden Resource database:
"...in SQL Server 2005 and higher versions there is a new hidden read only system database named Resource database which was introduced by Microsoft. ... Resource database basically contains copies of all system objects that are shipped with SQL Server 2005 and SQL Server 2008. In order to take the backup of Resource Database you need to perform a file based or a disk based backup of mssqlsystemresource.mdf and mssqlsystemresource.ldf files, by treating the files as if they were like any other binary files, instead of a database file. In Maintenance Plan as there is no other task available which ... The command used for copying mssqlsystemresource.mdf and mssqlsystemresource.ldf files is mentioned below. ..."
Do you have any opinions on this? I've never heard of this before, but it sounds important.
The resource database doesn't need to be backed up. It it should every become corrupt, it can be easily replaced.
Brad M. McGehee
DBA
April 1, 2011 at 10:50 am
The resource database doesn't need to be backed up. It it should every become corrupt, it can be easily replaced.
Makes sense. Thanks again! I'm good to go now.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply