All Apps and Add-ons

How to track memory dumps in sql server?

Engager

Hi all, I'm a beginner and I am wondering if anyone who uses Splunk to monitor SQL server has successfully set up tracking for memory dumps.

As you may know, when a memory dump occurs in SQL Server, a file is created in the root of the SQL server log directory as a .mdmp or .dmp. All we would like to do is be able to keep track of when this memory dump happens and on what server, as indicated by the existence of these files. However, as far as I know, Splunk would not be able to track these files, since it would be scanning a folder and not indexing a log file that is then searched on.

We have indexes set up for wineventlog, perfmon, and mssql, but to my knowledge, a SQL server memory dump event is not actually logged in any of the related sources types like the general SQL server error log (a related event might, but it would not indicate itself as being related to a memory dump). I might be wrong about this though, and perhaps someone can correct me that this is logged somewhere common that Splunk would be able to consume.
I am wondering how the community has approached this problem, any input appreciated, thank you!

0 Karma

Builder

Splunk base has an SQL addon(https://splunkbase.splunk.com/app/2648/) to deploy at sql servers where collects a several sourcetypes, including the memory.
here is the metric that collects from memory, I did not remember is this sourcetype can capture memory dumps, so I suggest you can install it on a SQL dev server and test it.

Memory % Committed Bytes In Use; Pages/sec; Available Mbytes; Pages perfmon:sqlserverhost:memory

https://docs.splunk.com/Documentation/AddOns/latest/MSSQLServer/About

https://docs.splunk.com/Documentation/AddOns/released/MSSQLServer/Datatypes

There is also an SQL app that you can deploy to the search head with dashboards to report the information you are indexing with SQL addon

https://splunkbase.splunk.com/app/1524/

Engager

Thank you, I appreciate the response. We are using the sql addon on all of our sql servers and do have those source types you mentioned available. However, I am not sure if a proper indicator for sql server memory dumps are logged anywhere, but instead just the event the is associated with it (but with no indication that a memory dump occurred). On thoughts on what field to be searching on for this, if it even is tracked?

Otherwise, I am unsure what to do. As mentioned, .dmp files are created but since they are new files and not a single log we can index, that doesn't do much good.

0 Karma

Builder

One alternative can be you index this file, but use the regex expression to find/index the information you need to for memory.
There is a potential that SQL save this type of information in a sys.dm table.
sys.dm_os_sys_info
sys.dm_os_sys_memory
sys.dm_os_process_memory

Here is a link with some tables that can you can search to check if the dump is also stored in one of those tables. If so, you can use the dbconnect to read this table and index this particular information.
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-...

0 Karma

Engager

Thanks for the response again.

As far as indexing the file, the issue seems to be that every time a memory dump occurs, a new file is created in the log root folder, rather than there being a single file that I can index. I don't think splunk has a way to ingest new files of a certain type just for showing up in a folder and turn that into a single index that will stay the same for a search string?

As far as the dmv approach, good call, there is even one specifically for this, sys.dm_server_memory_dumps. However in this case I would either need to set up a job on all servers that creates an output file that I then index, or use the sql db connect splunk plugin. The problem with this I am told is that it doesn't use connection pools, which means it would have too many connections in this use scenario.

0 Karma

Builder

thanks for the feedback

0 Karma