Oct 25
SQL Server Management and Maintenance Plans

Management Plans

  • Check Database Integrity Plan
    • Do this regularly
  • Cleanup Maintenance Plan
  • Daily Differential Backup Plan
  • Weekly Full and Transaction Log Maintenance Plan
    • Sun @ 4am
    • All DB's
    • \\<location>\File\SharePoint\DataBase
    • Verify Back up Integrity
    • Execute this to test, if it fails you will need to add the [SA]
      • Under SQL Server Agent and the named Maintenance Plan, right click properties.
      • Change the Owner to [SA]
    • Re-Run the Plan

Reorganize and Rebuild Index Plan

  • Rebuild Indexes that has more fragmentation, as a rule of thumb >30%
  • Rebuild and Reorganize Indexes to eliminate fragmentation dailyCheck
  • Reorganize the indexes that has lower fragmentation levels, it really depends on your system as how much you can accept but as a rule of thumb >10% and  <30%.
  • Between 10-70% do nothing and If its greater than 70% defragmentation then perform the operation
  • Shrink DB Size - Do not do!!!
  • Do not use Repair_Allow_Data_Loss
    • This is not a supported state according to Microsoft
  • Only shrink the content DB if free space is greater than 50%
    • Do not perform this as a maintenance plan
    • This is not a supported state according to Microsoft
  • Log Shipping
    • Enable backup compression for SQL server 2008
    • When using full backup model for point in time recovery choose to truncate transaction logs file!!!
  • Perform multiple differentials or log backup's multiples times a day
  • Setup Alerts
    • DB Mail Alerts, etc...
         

    Turn OFF Auto Update Statistics ON, obviously it is a overhead on the server, if you are updating statistics every night OR schedule a job to Update stats manually.

Comments

There are no comments for this post.

 ‭(Hidden)‬ Blog Tools