using SQL Server DMV sys.dm_server_memory_dumps to scan for memory dumps

Whilst I was waiting for some very long running reporting code to run recently (a 365 million row table in tempdb was never going to be a good thing, but it wasn’t my doing, the business logic was forced upon me!) I was browsing various SQL 2012 items of interest and noticed that there was a new DMV to query the memory dumps produced by SQL Server


When looking into it further I realised that it had been introduced in SQL 2008 R2 as well and I’d never even noticed! This is ironic as in all the courses and talk I do on troubleshooting I’m always nagging people to check their error log directory in case they have 10000 mini dumps flooding their disks (insert large number of your choice here but I think that 10000 was the largest amount that I saw over my time at Microsoft). Now I just need to build this check into automated scripts. I guess the natural way would be to add it into the perfstats scripts or create a hybrid of that, which is something to add to my ever growing to-do list 🙂

I guess another nice way to do it would be to create a job which emailed the DBA when dumps were encountered (although you could argue a good DBA would already have noticed!)

By Graham Kent

Code Club teacher, IT Director, currently living in London, misses being in Stockholm