7

Is there a way to tell who changed SQL server's max memory after a several SQL reboots?. I looked at the default trace log for event category 81 (memory configuration changes) but could not find anything. SQL server -->reports--> configuration report changes is also empty. Please suggest.

2
  • 3
    Not unless you have server side trace running with proper auditing enabled. You can find When were the sp_configure options last changed?
    – Kin Shah
    Jun 9, 2014 at 22:17
  • How many people have the ability to change these settings? Why? (Especially if you can ask them and nobody admits it - maybe they shouldn't have these privileges.) Jun 10, 2014 at 4:08

2 Answers 2

5

YES, there is a way to find out Who did it.

On SQL Log file viewer, on left panel select Windows NT to expand and select Application to display.

You can see details of date, User, computer, etc:

message:
Date        8/02/2014 
Log     Windows NT (Application)

Source      MSSQLSERVER
Category        (2)
Event       2342
User        ???????   THIS IS WHO  "OFFENDER"
Computer    THIS IS THE HOST where he did it

Message
Configuration option 'min server memory (MB)' changed from 2048 to 512. Run the RECONFIGURE statement to install.

Good luck.

1
  • 4
    Just for clarity -- if user using SQL Authentication e.g. sa changes the max memory, then the User: will show as N/A. Only if you use windows authentication, the user will be shown as User: domain\username. The default trace with eventclass=81 - server memory change will show the SPID and the starttime.
    – Kin Shah
    Aug 19, 2015 at 16:57
0

You can find the log from event Viewer Log in Windows..

1: Open Event Viewer: Click Start, point to All Programs, point to Administrative Tools, and then click Event Viewer.

2: In Event Viewer, in the console tree, click Application. In the details pane you can find the logs..

Event log

Thanks..

1
  • Does it tell you who did the change?
    – Andriy M
    Nov 8, 2016 at 6:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.