SQL Server Error 18456 Severity: 14 State: 73

A question asked on one of the forums today wasn’t easily answerable by Googling. Summary of the question “I have error 18456 State 73 – why?”

Google seemed remarkably quiet on the subject of that particular state code. Even Aaron Bertrand’s list of causes of state codes for SQL Server error 18456 missed this one.

However, some searching did find a link to what appears to be some in-depth VMWare VSAN training documentation that includes that error in some logging information, which made me wonder if it was related to the error 18456 state 38 that followed. The log excerpt in question:

2017-08-17 23:16:47.690 spid52 Setting database option SINGLE_USER to ON for database for 'tpcc'.
2017-08-17 23:18:28.490 Logon Error: 18456, Severity: 14, State: 73.
2017-08-17 23:18:28.490 Logon Login failed for user 'SQL2016-SA-03\Administrator'.  Reason: Failed to open the database 'tpcc' configured in the session recovery object while recovering the connection. [CLIENT: ]
2017-08-17 23:18:31.330 Logon Error: 18456, Severity: 14, State: 38.
2017-08-17 23:18:31.330 Logon Login failed for user 'SQL2016-SA-03\Administrator'.  Reason: Failed to open the explicitly specified database 'tpcc'. [CLIENT: ]

The full text of the error in the SQL Server event log is “Failed to open the database ” configured in the session recovery object while recovering the connection”. That, combined with the error state 38 – Failed to open the explicitly specified database – led me to think that a connection had been dropped and the database dropped or made otherwise unavailable before the user tried to reopen the query session.

A test

Fire up SSMS, and create a database:

CREATE DATABASE Test73;
GO

In another query window, run the following:

USE Test73;
SELECT DB_Name(), SPID();

Make a note of that SPID, and feed it into the following SQL that you run in the first window:

KILL 55;
GO
DROP DATABASE Test73;
GO

Now go back to the second query window, and hit F5 to refresh / rerun the batch.

Test73
Oops.

The error text:

TITLE: Microsoft SQL Server Management Studio

The connection is broken and recovery is not possible. The client driver attempted to recover the connection one or more times and all attempts failed. Increase the value of ConnectRetryCount to increase the number of recovery attempts. (Microsoft SQL Server, Error: 0)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476

ADDITIONAL INFORMATION:

Cannot open database “Test73” requested by the login. The login failed.
Login failed for user ‘SomeDomain\RUSHTONT’. (Microsoft SQL Server, Error: 4060)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=4060&LinkId=20476

To confirm this series of errors, check the SQL Server Error Log in SSMS, and you’ll see:
Test73ErrorLog

Suspicion proved!

Now all we have to do is test to see if there are other possibilities for this issue, but I need to step away from the keyboard…