Hello Everyone,
I am wondering what do other people put in their SQL Maintenance plan for SharePoint Databases. What operations do you do, in what order?
For Example,
- Check for and repair consistency errors by using dbcc checkdb
- Full Backup
- Measure and Reduce Fragmentation
- Fine tuning index performance by setting fill factor
- Shrinking data files
What’s on your plan?
Although previous versions of SharePoint Products and Technologies required manual intervention to perform index defragmentation and statistics maintenance, several SharePoint Health Analyzer rules automate this process in SharePoint 2010 and 2013 These rules evaluate the health of database indexes and statistics daily, and automatically address those items. Specifically
-
Check database integrity.
-
Defragment indexes by reorganizing them or rebuilding them.
-
Set the fill factor for a server.
This would suggest that a full data backup with a full transaction Log backup with truncation would be an efficent Maintenance plan.
I would also consider dropping the “Shrink Logs” part of the routine. The transation logs are likely to just grow again, so why shrink it consistently? Besides shrink-grow-shrink-grow causes file-system level fragmentation. Just shrink them once after the first full backup with truncation.
/Colin
Reference: http://technet.microsoft.com/en-us/library/cc262731(v=office.14).aspx
