Sql-server – One of the Disaster recovery server every day sp_configure values are changing

sql server

We have a DR server, Daily databases are restoring from Production to DR server.

From last week onward, the Sp_Configure Value (Show Advanced) is changing.

How do I find out from where these values are coming from, they are different from the Production server Sp_Configure values.

Best Answer

This will be in your Log File.

For example, if I run:

EXEC sys.sp_configure @configname = 'Show Advanced', @configvalue = 0
RECONFIGURE WITH OVERRIDE


EXEC sys.sp_configure @configname = 'Show Advanced', @configvalue = 1
RECONFIGURE WITH OVERRIDE

It shows up in my Error Log like this:

If you want to search through your error log for other instances of this occurring, you can click filter, and then fill out a search condition.

If you want to dig a little bit deeper, you can check the default trace for events as well (adapted from Aaron's code here):

DECLARE @path NVARCHAR(260);

SELECT @path = REVERSE(SUBSTRING(REVERSE(path), CHARINDEX('\', REVERSE(path)), 260)) + N'log.trc'
FROM   sys.traces
WHERE  is_default = 1;

SELECT   TOP 1000 TextData,
                  NTUserName,
                  NTDomainName,
                  HostName,
                  ApplicationName,
                  LoginName,
                  ServerName,
                  DatabaseName,
                  SessionLoginName
FROM     sys.fn_trace_gettable(@path, DEFAULT)
WHERE    EventClass = 22
AND TextData LIKE '%Configuration%'
ORDER BY StartTime DESC;

Related Solutions

Sql-server – Historical reporting from the database with Reporting Services

If you have lost the historical data (i.e. it gets overwritten rather than stored as a transaction history) then you can't reconstruct the historical state - full stop. However, there are a few approaches to dealing with this.

Build an automated process that queries the system through SSRS or some other mechanism and saves the historical reports to an archive somewhere. SSRS exports a web service API that lets you run reports (paramaterised if necessary) and then save the output to a file. This can be operated by anything capable of consuming it.
Build an ETL process that takes a snapshot of the data and compares it with the previous snapshot position. Where changes are detected they can be written out to a historical table. If you have the option of putting triggers on the source database then you can make triggers that write out the audit logging information instead.
If you need something more elaborate then you could build a data mart - do a google search on 'type 2 slowly changing dimensions' for some ideas on how to implement this. You can use a change capture mechanism like that described in (2) to source the data as necessary. Either you can store the transaction history and reconstruct the status in the reports, or you can make a periodic snapshot of the data with its status at that point.

Note that Standard Edition doesn't support changed data capture.

Sql-server – Selecting values that are not associated with one side of a many to many relationship

You need all combinations of Category and Value (a CROSS JOIN) except those that appear in the CategoryValue. This can be done with a LEFT JOIN / IS NULL

SELECT 
    c.CategoryID, v.ValueID, v.ValueName
FROM 
    Category AS c
  CROSS JOIN
    Value AS v
  LEFT JOIN
    CategoryValue AS cv
      ON  cv.CategoryID = c.CategoryID
      AND cv.ValueID = v.ValueID
WHERE
   cv.CategoryID IS NULL ;

or a NOT EXISTS:

SELECT 
    c.CategoryID, v.ValueID, v.ValueName
FROM 
    Category AS c
  CROSS JOIN
    Value AS v
WHERE
   NOT EXISTS
     ( SELECT *
       FROM CategoryValue AS cv
       WHERE cv.CategoryID = c.CategoryID
         AND cv.ValueID = v.ValueID 
     ) ;

or an EXCEPT solution:

SELECT 
    c.CategoryID, v.ValueID, v.ValueName
FROM 
    Category AS c
  CROSS JOIN
    Value AS v

EXCEPT

SELECT 
    cv.CategoryID, v.ValueID, v.ValueName
FROM 
    CategoryValue AS cv
  JOIN
    Value AS v
      ON  cv.ValueID = v.ValueID ;

Best Answer

Related Solutions

Sql-server – Historical reporting from the database with Reporting Services

Sql-server – Selecting values that are not associated with one side of a many to many relationship

Related Question