Thursday, January 24, 2019

Who changed SQL server's max memory?

I found the max server memory (mb) was misconfigured today on one of my customer's servers.  The server had 32GB physical ram, but SQL Server's max server memory (mb) was set too high at 48GB.  The customer insisted they hadn't changed it, and asked me to find out who had... but how?  Easy peasy.  Using sp_readerrorlog we can search all of the available ERROR LOGs for the 'configuration option' string.  But, if it's not in the available ERROR LOGs, you'll see this:

In that case, we can got to go to the Windows Application Event log and search for the same thing.  Open Event Viewer, Windows Logs, right click Application and choose 'Find'.  Input your string and lookie there!  We found the configuration change string, and can see it was made TWO YEARS ago.  Have to wonder how that misconfiguration has impacted the server memory all this time!  

So, I changed it to 29GB, which is the recommended setting for a dedicated server with 32GB physical, and now we can see it in the current ERROR LOG.

Need to be careful about who is authorized to make changes like this.  And, we have to recognize that people make mistakes.  May be a good idea may be to trigger notifications when configuration changes are made.  Good idea!  ... for a later post.

No comments:

Post a Comment