SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What Do You Include in Your Database Maintenance Plans?


What Do You Include in Your Database Maintenance Plans?

Author
Message
bradmcgehee@hotmail.com
bradmcgehee@hotmail.com
Mr or Mrs. 500
Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)

Group: General Forum Members
Points: 598 Visits: 730
Comments posted to this topic are about the item What Do You Include in Your Database Maintenance Plans?

Brad M. McGehee
DBA
Paul Smith-221741
Paul Smith-221741
Mr or Mrs. 500
Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)

Group: General Forum Members
Points: 500 Visits: 334
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.
Wyatt Eurich
Wyatt Eurich
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 216
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.
Nadrek
Nadrek
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1850 Visits: 2726
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)?
Steve Jones
Steve Jones
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: Administrators
Points: 61415 Visits: 19097
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
JJ B
JJ B
Mr or Mrs. 500
Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)

Group: General Forum Members
Points: 501 Visits: 2859
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.
bradmcgehee@hotmail.com
bradmcgehee@hotmail.com
Mr or Mrs. 500
Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)

Group: General Forum Members
Points: 598 Visits: 730
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
JJ B
JJ B
Mr or Mrs. 500
Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)

Group: General Forum Members
Points: 501 Visits: 2859
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. ..."
from: http://www.sql-server-performance.com/articles/dba/Backup_System_Databases_Using_Maintenance_Plans_p3.aspx

Do you have any opinions on this? I've never heard of this before, but it sounds important.
bradmcgehee@hotmail.com
bradmcgehee@hotmail.com
Mr or Mrs. 500
Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)Mr or Mrs. 500 (598 reputation)

Group: General Forum Members
Points: 598 Visits: 730
JJ B (4/1/2011)
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. ..."
from: http://www.sql-server-performance.com/articles/dba/Backup_System_Databases_Using_Maintenance_Plans_p3.aspx

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
JJ B
JJ B
Mr or Mrs. 500
Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)

Group: General Forum Members
Points: 501 Visits: 2859
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search