Should schedule during off hours.

Type: Transaction-SQL script (T-SQL)

Database: master

Command:

DECLARE @SQL nVARCHAR(1000)  
DECLARE @DB sysname  

DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR  
    SELECT  [name]
    FROM    master.sys.databases
    WHERE   [name] NOT IN ( 'model', 'tempdb' )
            AND state_desc = 'ONLINE'
			AND is_read_only = 0 
            AND [name] NOT IN (
				--Index/stats updates should be skipped on non-primary replicas
				SELECT  dc.database_name
				FROM    sys.availability_replicas r
						JOIN sys.availability_databases_cluster dc ON dc.group_id = r.group_id
						LEFT JOIN sys.dm_hadr_availability_group_states gs ON gs.group_id = r.group_id
																  AND gs.primary_replica = r.replica_server_name
				WHERE   gs.primary_replica IS NULL
				AND r.replica_server_name = @@SERVERNAME )
    ORDER BY [name] 
     
OPEN curDB  
FETCH NEXT FROM curDB INTO @DB  
WHILE @@FETCH_STATUS = 0  
   BEGIN  
       SELECT @SQL = 'USE [' + @DB +']' + CHAR(13) + 'EXEC sp_updatestats ''resample''' + CHAR(13)  
       PRINT @SQL  
       EXEC sp_executesql @SQL
       FETCH NEXT FROM curDB INTO @DB  
   END  
    
CLOSE curDB  
DEALLOCATE curDB
Last modified: August 13, 2020

Author

Comments

Write a Reply or Comment