SQL Server Performance

SQL 2005 Maint Plans Best Practice

Discussion in 'SQL Server 2005 General DBA Questions' started by cramlow, Aug 30, 2007.

  1. cramlow New Member

    Greetings SQL Gurus.
    My question is: What is the best practice SQL 2005 Maintenance plan for a sql server hosting about 10 databases, all sizes between 25 megs and 2 gigs?
    What I already think I know ;)
    1. Recovery model should be full for production server.
    2. Daily full backup
    3. Hourly Log backup
    4. Clean up plan to delete baks and trns older than 5 days.
    What I don't know and and can't seem to find a book to put it all together for me is what else should I include in a maint plan and why. I've read a lot about indexing, but don't completely grasp it yet. I've read about database shrinking but that sounds potentially dangerous. What else do you recommend and why, please?
    Thanks,
    SQL guru -in- training ;)

    cramlow, Aug 30, 2007
    #2

  2. Luis Martin Moderator

    From backup point of view sound good to me.
    Now, you have to reindex and update statistics frequently in windows scheduled jobs.
    Take a look some articles in our forum.
    Luis Martin, Aug 30, 2007
    #3

  3. ghemant Moderator

    welcome aboard!!
    in addition to what Luis has suggested
    refer below links for backup strategy http://support.microsoft.com/kb/329833 , http://articles.techrepublic.com.com/5100-1035_11-1043671.html, http://www.dbazine.com/sql/sql-articles/larsen12 and http://databases.about.com/od/sqlserver/a/disaster.htm
    SQL Server Administration Best Practice http://vyaskn.tripod.com/sql_server_administration_best_practices.htm
    and here are some Best Practice reference material FYR http://technet.microsoft.com/en-us/sqlserver/bb331794.aspx
    ghemant, Aug 31, 2007
    #4

  4. satya Moderator

    Most of the links provided by Hemant refers to the DBA admin type of tasks, as you said there isn't any specific link talks about BP on MP.
    Shrinking of database can only be approached when you have major disk free space issues, otherwise I wouldn't recommend to go there even. Shrinking transaction log can be periodically but again you need to consider the processes & scheduled jobs that are contributing to its size, then setup a size in order to keepup the backups too.
    http://sqlserver-qa.net/blogs/tools/rss.aspx?Tags=best practices&AndTags=1 Lookat this to download best practices analyzer that will give more information on your environment.
    satya, Aug 31, 2007
    #5

Share This Page