Friday, September 6, 2019

SSMS Feature generates Severity 20 Error in the Errorlog.

Yesterday I've needed to use Dedicated Administrator Connection (DAC) once in a while, and because I have all kinds of notifications in my system, I immediately got an "Severity 20" alert.

As you probably know, Severity 20 Errors "Indicate system problems and are fatal errors" (See books online: https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-error-severities?view=sql-server-2017)

Even though "Severity 20" does not indicate any problems with data and belong only to a user process it is still worth to investigate the problem.

I looked at my Log file and found following record:
Error: 17810, Severity: 20, State: 2. 
Could not connect because the maximum number of '1' dedicated administrator connections already exists. 
Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process. [CLIENT: 192.168.234.54]

I knew that I'm using the DAC on that server right now, but it was a surprise for me that "somebody else" is also trying to connect to the same SQL server via DAC.

I've set the simplest extended event session (script is below) to monitor the cause of the problem.
CREATE EVENT SESSION [Tracking DAC Errors] ON SERVER
ADD EVENT sqlserver.error_reported(
    WHERE ([category]=(4) AND [error_number]=(17810) AND [severity]=(20))),
ADD EVENT sqlserver.errorlog_written
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)

GO

And by doing that I discovered a little "Aero" SSMS window, which is driven by IntelliSense, and usually appears for a fraction of a second when you connect to a new server or a database and then disappears. It reads the schema of current database to provide you suggestions. However, I noticed this time that this small popup window did not disappear and it also had an error.

I've tried to hit a "Restart" button and immediately got an error in my extended event's "Live View" window:

As you can see from the screenshot IntelliSense was trying to read the schema of my current database using the same connection string I used to connect to the server, but because I've used DAC, which allows you to have only one that kind of connection IntelliSense got an error.

I've tried to turn IntelliSense off, however it hasn't fixed the issue. Behind the seen SSMS still tried to pull some information from a SQL Server using the same connection string to as my connection in a tab.

At first, my thought was that it might be a bug, but after little thinking I realized that SSMS is not so smart and can't determine correct connection string to the server you want and for simplicity just uses same connection string, which was already provided.
It would be nice though, if SSMS realized that I'm using DAC and did not try to connet to the Server itself and not producing unnecessary error.

What can we learn from that?
If you are at the point when you have to use DAC to connect to your server, you are on your own and SSMS interface become useless. All its reports and GUI features are unavailable and only pure T-SQL can help you.
I'm glad I'm using my own set of scripts embedded into SSMS shortcuts, if you want to check it out, here is the link to a downloadable archive: https://drive.google.com/open?id=0B5yWoyX1eEWqZ3FJUnNHZm80bzQ

No comments:

Post a Comment