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 ;)

  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.
  3. ghemant Moderator

  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.

Share This Page