0

If you look at how to temporarily change the sql server settings in order to do a task and when finished revert back? there is a way to save SQL Server settings before enabling them, so you can disable them if needs be.

Here is the code (partially copied):

IF OBJECT_ID('tempdb.dbo.#Settings') IS NOT NULL
    DROP TABLE #Settings;

CREATE TABLE #Settings
(
    Setting VARCHAR(100),
    Val INT
)
INSERT #Settings (Setting, Val)
SELECT 'show advanced options', cast(value_in_use as int) from sys.configurations where name = 'show advanced options'
UNION
SELECT 'xp_cmdshell', cast(value_in_use as int) from sys.configurations where name = 'xp_cmdshell'
UNION
SELECT 'Ad Hoc Distributed Queries', cast(value_in_use as int) from sys.configurations where name = 'Ad Hoc Distributed Queries'


SELECT * FROM #Settings;

That works fine, if you run one procedure at a time; however, it writes to the SQL Server error log as you can see below:

enter image description here

Is there a way to avoid writing changes of settings to the error log?

Marcello Miorelli
  • 17,274
  • 53
  • 180
  • 320

2 Answers2

4

Much as Remus has stated in the answer you've linked, there is no out of the box functionality to disable these messages from being written to the errorlog.

This doesn't mean it isn't technically possible, it's just not supported. You'd need to either edit the binary yourself to remove the call to logging, inject something that does this dynamically, or write a filter driver to throw away any data you wouldn't want saved in the log.

Note that this only pertains to the errorlog, this would not stop logging in other areas or items such as sql traces or extended events.

Seems to be a bit far to go for informational messages that don't seem to be doing any harm, though.

Sean Gallardy
  • 38,135
  • 3
  • 49
  • 91
3

ERRORLOG is just a text file stored at the configured location.

SELECT SERVERPROPERTY('ErrorLogFileName')

You can edit these text files at your own risk.

screencap showing manually edited SQL Server ERRORLOG file

If you do this in a production system, you are probably bypassing or violating some compliance rule or other.

If you choose to do this anyway, you need to release the lock on the log file in use - either by stopping the SQL Server process or by cycling the file using sp_cycle_errorlog.

This does not stop the message from being written to the log in the first place nor will it stop downstream processes from ingesting the log message (nor should it).

Peter Vandivier
  • 5,485
  • 1
  • 25
  • 49