Update – December 2020 – Feature no longer supported by Microsoft. It was good while it lasted.
I was VERY excited when I read the following tweet (below) from Bob Ward regarding SQL Server Diagnostics capability. What is it you are asking? It is an extension to SQL Server Management Studio (SSMS) where it gives you the ability to Upload / Analyse dump files created by SQL Server.
This is HUGE news, I could not resist writing up on it ( sorry the Cloud series will be delayed for a few more days) because last month I had a really strange Dump file produced by SQL Server. These files are located in the \LOG\ folder of your install. The information for my instance was as below.
Dark Clouds Are Ahead
There is a log file that holds the actual stack trace, if you remember an older post of mine I was able to use WinDBG (in the past) to at least get human readable text, but for this case I couldn’t.
Believe me, this was a really weird one.
The Tool
So let’s put the new tool into action! You can find it here. https://www.microsoft.com/en-us/download/confirmation.aspx?id=55516
It is a classic installer, just be sure to read the privacy agreements. This is on my local machine so I am happy with it.
Keep in mind the SSMS 2017 requirement as shown below.
The install takes 10 seconds, if that. So next I head straight into my SSMS 2017 for the tools menu.
This is the main analysis screen.
Notice the region to upload – If you are using a work machine I would suggest getting authorisation. The great thing here is that this is GDPR compliant.
Once ready hit the upload button, it goes through 3 phases. Upload, Analysis and a recommendation.
The Silver Lining
Give or take 10 minutes here is the final output:
The recommendations seem to be a list of KB articles, reading through them they are references to bugs. I have been messaging a member from the Diagnostics team at Microsoft and these are in ranked order. So the top one is most relevant to my issue and I should be installing the CU referenced within that specific KB link.
I was also told that going forward there will be 3 recommended fixes rather than 10.
Here are some screen shots of its findings.
It’s better than having
At the main screen of analysis you may notice a delete option. This does NOT delete the upload. The actual dump uploaded will be persisted for 90 days, then after that an automatic purge will occur.
By the way, if you entered your email address you will be sent an email From Microsoft Enterprise Concierge Service with a summary of findings.
Takes self-diagnostics to a new level. What I like about this tool is that it sheds some light when things are dark and confusing. I guess I will be patching this instance up, next I will be looking at my other server which has 77 dump files!
Thanks to the Diagnostic Team and Bob Ward at Microsoft with the detailed replies to my queries – much appreciated.
Pingback: Self-Analysis Of SQL Server Dump Files – Curated SQL
I assume that this will soon be baked into newer versions of SSMS?
LikeLike
It’s a good question and I am not sure. It’s definitely worth asking, I will find out over the next few days.
LikeLike
Pingback: Using Get-SQLDiagFix to get information from the SQL Server Diagnostic API with PowerShell | SQL DBA with A Beard
The note says “This extension requires Microsoft SQL Server Management Studio (SSMS) 2016 or a later version.”
But it doesn’t appear to work with SQL Server Management Studio 2017.1. I have 2017.1 installed, but when I run the msi, I get a modal dialog with “You need SQL Server Management Studio (SSMS) 2016 or higher to continue installing SQL Server Diagnostics extension.” Only option is OK, and then setup ends.
(OS is Win2012R2 Standard if that matters, running in a VM with SQL 2016 installed as a server, plus SSMS 2017. I don’t have SSMS 2016 on there.)
Please let us know what is the fix for this issue ?
LikeLike
We had a known issue for this which we have fixed in the latest installer published at https://www.microsoft.com/en-us/download/details.aspx?id=55516. Could you please try this out and let us know in case you face any issues
LikeLike
I’ve installed it, but I’m curious about where my uploaded dumps go and who is reviewing them? GDPR applies to the EU; not the US. What assurances do we have for privacy and security of our uploaded server data? Thanks.
LikeLike
I tried uploading a sql dump but after the analysis i got a status of “failed”. anyone know why?
LikeLike
Hi – Microsoft do not monitor this website. Try post it here : https://blogs.msdn.microsoft.com/sql_server_team/sql-server-diagnostics-preview/
thanks.
LikeLike
https://ecs.microsoft.com is no longer active
LikeLike
I really appreciated this thorough writeup and got my hopes up. Unfortunately, after getting everything installed, I found out that it has been discontinued (https://docs.microsoft.com/en-us/archive/blogs/sql_server_team/sql-server-diagnostics-preview). It would be helpful to post a note at the top of the post letting others know so they don’t spend their time trying to get this all installed and running. Thanks.
LikeLike
that is fair, I will update the post. thanks
LikeLike