Database Maintenance Plan

  • Let me say I am not a SQL Server administrative guru, actually I am a .NET developer, but I have inherited the DBA tasks for our database.  The problem is, is that there are no current "maintenance" activities taking place other than a nightly database backup.  We have a 30 GB (used space) SQL Server 2000 database and we are already receiving some timeout errors in some of our applications, as well as, some full transaction log errors when we try to manually rebuild indexes (DBCC REIndex).

    • What would be considered the best practice as far as using DBCC ShrinkFile or Database?  I have read articles saying these don't really help matters.
    • We have "Auto Grow" turned on with the 4% option.
    • We have "Auto Shrink" turned on which I believe is counter-productive in conjunction with auto grow. 
    • We don't regularly rebuild indexes or statistics and the database is usually under an above average load with a mix of OLTP and OLAP.

    I guess my question is what would be a good start as far as determining some regular maintenance activities/scripts that need to take place?  I know this is very high level but at the least what should be done on a periodic basis and in what order?  For instance, I know we should rebuid indexes and statistics on a regular basis but should we turn auto shrink off and periodically run DBCC commands to shrink the database and/or log files?  Any help or reference to some type of high level article detailing a generic plan would be greatly appreciated.  Thanks. 

  • I would personally turn off auto shrink as its only real use is on development machines where space is at a premium. You can find numerous articles on why its bad but on production system it should never be on.

    http://blogs.msdn.com/sqlserverstorageengine/archive/2007/03/28/turn-auto-shrink-off.aspx

    Also although auto grow is useful in case you forget to keep an eye on your databases and they run out of space you should again never let it actually happen as again it will really hurt performance as it can take quite a while for the files to grow.

    http://www.sql-server-performance.com/sql_server_performance_audit6.asp

    http://www.sqlskills.com/blogs/kimberly/2007/03/04/InstantInitializationWhatWhyAndHow.aspx

    To be honest the above are likely candidates for your time-out issues and if you do need to shrink databases do it manually

    So basically you should size the database and the transactions logs so that they do not need to auto-grow.

    Indexes are more problematic to advise on because it depends on the acivity in your database when they will need to be rebuilt but there are lots of scripts about which will rebuild them automtically depending on how fragmented they are which are quite useful.

    http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1475

    http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1822

    http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=797

    hth

    David

     

  • Running DBCC CHECKDB is very important. You do not want your db corrupted little by little without knowing it.

  • David...

    Thanks for the reply.  One more quick question.  Let's say I

    • turn auto shrink off
    • run a weekly script that 1) updates statistics and 2) DBCC DBREINDEX (in some fashion, maybe only ones that are > 15% fragmented)

    How should I incorporate shrinking of log files into this since they will become quite large I'm sure ?

    Should I have a script (or collection of) that does the following:

    • Backup log
    • DBCC ShrinkFile on the log file
    • Update Statistics
    • DBCC DBREINDEX

    I am with a smaller start up company that really has no DBA nor any regularly scheduled "maintenance" scripts so I am trying to get a "skeleton" of a plan started as far as what to run and in what order to run them. 

  • If you need to reduce the size of the log after rebuilding the indexes then I would use dbcc shrinkfile and put this as the final step in your job which re-indexes the database.

    If you dont need point in time recovery you could just set the database to simple and this would keep your transaction logs small if you do need point in time recovery then you will need to back them so that they get truncated just doing a db backup won't do it.

    David

  • I would rearrange your list and put DBCC DBREINDEX at the top.  I generates a huge amount of log information, sometimes as large as the database itself.  If you think you'll ever need to shrink the log, it would be right after DBREINDEX (and after a log backup, of course).

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply