Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

What Do You Include in Your Database Maintenance Plans? Expand / Collapse
Author
Message
Posted Wednesday, February 3, 2010 9:06 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 10, 2014 7:20 AM
Points: 175, Visits: 723
Comments posted to this topic are about the item What Do You Include in Your Database Maintenance Plans?

Brad M. McGehee
Microsoft SQL Server MVP
Director of DBA Education, Red Gate Software
www.bradmcgehee.com
Post #859279
Posted Thursday, February 4, 2010 4:09 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, September 19, 2014 1:45 AM
Points: 432, Visits: 313
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.
Post #859492
Posted Thursday, February 4, 2010 7:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 10, 2013 11:12 AM
Points: 20, 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.
Post #859635
Posted Thursday, February 4, 2010 7:44 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:52 AM
Points: 876, Visits: 2,421
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)?
Post #859665
Posted Thursday, February 4, 2010 10:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:59 PM
Points: 31,082, Visits: 15,529
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
Post #859785
Posted Thursday, March 31, 2011 3:48 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 10:57 AM
Points: 266, Visits: 2,598
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.
Post #1087161
Posted Friday, April 1, 2011 10:15 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 10, 2014 7:20 AM
Points: 175, Visits: 723
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
Microsoft SQL Server MVP
Director of DBA Education, Red Gate Software
www.bradmcgehee.com
Post #1087593
Posted Friday, April 1, 2011 10:22 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 10:57 AM
Points: 266, Visits: 2,598
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.
Post #1087599
Posted Friday, April 1, 2011 10:48 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 10, 2014 7:20 AM
Points: 175, Visits: 723
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
Microsoft SQL Server MVP
Director of DBA Education, Red Gate Software
www.bradmcgehee.com
Post #1087618
Posted Friday, April 1, 2011 10:50 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 10:57 AM
Points: 266, Visits: 2,598
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.
Post #1087619
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse