How can I find when a SQL Server setting was changed?

Question

Our DBAs have changed the 'Cost Threshold for Parallelism'. Is there an audit that will give me a timestamp of this change, so I can correlate it with our APM (Application Performance Monitoring)?

Answer 1

You can right click on the SQL Server instance in SQL Server Management Studio and then navigate down to:

Server Context Menu -- Reports -- Standard Reports -- Configuration Changes History 

If any changes were made in the near past, then they should be displayed similar to the following report summary, showing that somebody changed the media retention value in the server's configuration:

The picture displays the following columns:

• Configuration Option
• Old Value
• New Value
• Time
• User (who changed the value)

...otherwise, you will receive the following informational message:

The report provides a history of all sp_configure and Trace Flag changes recorded by the Default Trace.

Configuration Changes History (Since ). Shows changes in server configuration and flags. Currently, the report has no data to show because the default trace does not contain relevant information.

Answer 2

maybe

Depending on how long ago it was changed, you may be able to find those details either in the default trace or the error log. I couldn't find it in the system health extended event quickly, but I didn't spend a ton of time on it.

err

In the error log, you'll see lines like this when settings are changed. Whether the change is in there or not will depend on how long ago the change happened, and what your error log retention is set to.

default trace

Borrowing some code from Aaron Bertrand, you can search through whatever data is available in the default trace. Same caveats as above, it may not be in here if it happened a long time ago.

WITH 
    p AS
(
  SELECT 
      [path] = 
          REVERSE(SUBSTRING(p, CHARINDEX(N'\', p), 260)) + N'log.trc'
  FROM 
  (
       SELECT 
           REVERSE([path]) 
       FROM sys.traces WHERE is_default = 1
  ) s (p)
)
SELECT 
   t.TextData,
   t.DatabaseID,
   t.HostName,
   t.ApplicationName,
   t.LoginName,
   t.SPID,
   t.StartTime,
   t.DatabaseName,
   t.SessionLoginName
FROM p 
CROSS APPLY sys.fn_trace_gettable(p.[path], DEFAULT) AS t
WHERE t.TextData LIKE N'%cost threshold for parallelism%'
AND   t.TextData NOT LIKE N'WITH%p%'
ORDER BY t.StartTime DESC;

The Configuration Changes History report, available from SSMS Object Explorer by right-clicking on the instance and selecting Reports-->Standard Reports-->Configuration Changes History, uses the default trace as the source.

Answer 3

If it's important enough for you, restore your master DB backups one by one on a temporary SQL Server until you find the previous value.