Post
Ask Question

SharePoint Backup During Midnight is not Recommended

SharePoint site collection backup or SQL database backup for SharePoint databases are not recommended to schedule at midnight as it causes skipping of database statistics update.

Microsoft SharePoint is scheduled to run database statistics update (proc_updatestatistics) for every database at midnight.

When we run database backup either full or incremental, it enables skipping for statistics update.
When you check with below query, it would give you the result as updating statistics but it would not actually do.

  1. EXECUTE sp_msforeachdb   
  2. 'USE [?];  
  3. IF DB_NAME() NOT IN(''master'',''msdb'',''tempdb'',''model'')  
  4.    begin   
  5.         SELECT  ''CHECKING STATS FOR '' + DB_NAME() AS ''DATABASE NAME''  
  6.         SELECT   OBJECT_NAME(A.OBJECT_ID) AS ''TABLE NAME''  
  7.                , A.NAME AS ''INDEX NAME''  
  8.                , STATS_DATE(A.OBJECT_ID,A.INDEX_ID) AS ''STATS LAST UPDATED''  
  9.           FROM   SYS.INDEXES A  
  10.           JOIN   SYS.OBJECTS B  
  11.             ON   B.OBJECT_ID = A.OBJECT_ID   
  12.          WHERE   B.IS_MS_SHIPPED = 0  
  13.          ORDER   BY OBJECT_NAME(A.OBJECT_ID),A.INDEX_ID  
  14.      end'  
To overcome this issue, create a SQL agent job that runs at least daily and runs the following script.
  1. EXECUTE sp_msforeachdb   
  2. 'USE [?];  
  3. IF DB_NAME() NOT IN(''master'',''msdb'',''tempdb'',''model'')  
  4.      begin   
  5.           print ''updating statistics in database  ==> '' + db_name()  
  6.           if exists (select 1 from sys.objects where name = ''proc_updatestatistics'')  
  7.              begin  
  8.                   print ''updating statistics via proc_updatestatistics''  
  9.                   exec proc_updatestatistics   
  10.              end  
  11.          else   
  12.              begin   
  13.                   print ''updating statistics via sp_updatestats''  
  14.                   exec sp_updatestats  
  15.              end  
  16.     end'  

To repair the same with out of box features at SharePoint Central Admin: 

  1. Verify that the user account that is performing this procedure is a member of the Farm Administrators group.

  2. On the Central Administration Home page, click Monitoring.

  3. On the Monitoring page, in the Health Analyzer section, click Review rule definitions.

  4. On the Health Analyzer Rule Definitions – All Rules page, in the Category: Performance section, click the name of the rule.

  5. In the Health Analyzer Rule Definitions dialog box, click Edit Item.

  6. Select the Repair Automatically check box, and then click Save.