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
mostly agree with your list.
But I would shrink logs only together with an intelligent sizing afterwards. (what I mean is, resizing the files to prevent autogrowth as much as possible)
If you’re using availability groups, you HAVE to be on FULL on all databases!
We backup logs every 15 minutes or so, so our logs are never that big. (relatively)
I’d thus remove step 3, and if you are rebuilding indexes why would you reorganize them? (Unless they’re different indexes.)
As you say, space can be an issue, but unless you’re willing to loose data I wouldn’t set the mode to simple, or not have hourly (at a minimum transaction log backups.)
If you use Availability Groups, all your databases are in Full Recovery mode. That means logs get pretty big.
If you never Shrink, log files will get huge.. and unfortunately for some companies, space is an issue.
I am trying to find a correct order in which to do them.. What do you think of this? (Every Night)
1. Check Database Integrity.
2. Full Backup.
3. Shrink Logs.
4.Reorganize Index
5. Rebuild Index.
6. Maintenance Cleanup Task.
Adapted from this link: http://social.technet.microsoft.com/wiki/contents/articles/13956.sharepoint-2010-how-to-create-a-sql-server-2008-r2-maintence-plan-for-sharepoint.aspx
