SQL Server Job – How to Create Job to Set Auto Update Statistics On

jobssql serversql-server-2012

I am using SQL Server 2012. The vendor wants Auto Update Statistics set to false, but my boss wants it set to true.

If I set it to true, after a while it goes back to false by itself. I think the vendor has something to switch it to false in their application.

I want to create a job which checks the whether Auto Update is set false or true. If it is to false, it will turn it to true. If it is true it should leave it true.

This is the script I am going to use in the job:

USE master
GO
ALTER DATABASE AdventureWorks2008 
SET AUTO_UPDATE_STATISTICS ON WITH NO_WAIT;
GO

But I don’t want the job to do anything when it is already true.

Do I need to change the script or run the way it is?

Best Answer

You need to have a check using: 

SELECT name, is_auto_update_stats_on, is_auto_update_stats_async_on 
FROM sys.databases

For example:

IF 
(
    SELECT COUNT(*) 
    FROM [sys].[databases]
    WHERE [name] = 'AdventureWorks_2005' 
    AND [is_auto_create_stats_on] = 0
) = 0
BEGIN
    ALTER DATABASE [AdventureWorks_2005] 
    SET AUTO_UPDATE_STATISTICS ON
END
ELSE
PRINT 'The auto update stats is ON ... no need to worry !'
Related Question