SQL Database Error 924 – Database is already open and can only have one user at a time

[probert94]

Driver version

Since 6.3.3 Preview Release (also testet with 7.3.0 Preview Release)
Worked well with 6.3.2 Preview Release and earlier.

SQL Server version

Microsoft SQL Server 2017 (RTM-CU14-GDR) (KB4494352) - 14.0.3103.1 (X64) Mar 22 2019 22:33:11 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 17763: )

also tested with Microsoft SQL Server 2014

Client Operating System

Windows 10

JAVA/JVM version

1.8.0 and also tested with java 11 (open jdk)

Problem description

In an application I am working on, I have an updater, which updates the db structure whenever a new version is started for the first time.
This updater is executed in single user mode, to ensure no one is working on the db in the meantime.
During this update, I request meta data using SQLServerDatabaseMetaData#getImportedKeys and SQLServerDatabaseMetaData#getExportedKeys.
This call sometimes causes a com.microsoft.sqlserver.jdbc.SQLServerException with the message Database is already open and can only have one user at a time (SQLServer Errorcode 924).

Stacktrace:

com.microsoft.sqlserver.jdbc.SQLServerException: Die Test000-Datenbank ist bereits geöffnet und kann jeweils nur einen Benutzer haben.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:255)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1621)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:592)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:522)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7194)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2942)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:248)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:223)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:444)
	at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.executeSPFkeys(SQLServerDatabaseMetaData.java:919)
	at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getCrossReference(SQLServerDatabaseMetaData.java:814)
	at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getImportedKeys(SQLServerDatabaseMetaData.java:886)

This exception only seems to occur when the database is on a slow drive (HDD). The same database works fine, when I test it on my SSD.

I did some research and I guess the error was introduced with #490.

JDBC trace logs

https://gist.github.com/Springrbua/8760cf873cc337c189fc2e1d47f7682b#file-log-txt

[rene-ye]

Hi @Springrbua, the driver will internally use the same connection the user created when calling these APIs. If your Updater class only establishes 1 connection at a time to the database and no other parts of your program creates simultaneous connections, the driver will use that connection when retrieving DatabaseMetadata. This seems more like an issue with the program logic itself as SINGLE_USER is a database side property, and the user application must properly manage its resources with SINGLE_USER restrictions in mind. The only change I can see regarding these APIs is that they've taken a performance hit in favor of conforming to JDBC Specs, which will not be changed. The APIs before #490 also had similar logic with regards to contacting the server, the only difference is it was faster. I don't believe this is a driver regression.

You mentioned the updater is executed in SINGLE_USER mode on purpose to ensure others are not working on the database. A solution here would be to properly handle this error as it's an error thrown from the SQL Server when multiple users are attempting to access the DB while it's in SINGLE_USER mode. You can simply catch the SQLException and verify the error, and continue with some kind of retry logic until the database is no longer in use/SINGLE_USER mode.

[probert94]

@rene-ye thak you for the quick reply.
We just had a look at the SQL-Profiler and it does not log any connection other then the one we are opening.
Is it possible, that the SQL Server opens a new connection internally, which is causeing this error?

[rene-ye]

Hi @Springrbua, the DatabaseMetadata APIs mentioned in the thread does not open new connections internally. If possible, can you provide a standalone java project which exhibits this behavior?

[probert94]

You can simply catch the SQLException and verify the error, and continue with some kind of retry logic until the database is no longer in use/SINGLE_USER mode.

We tryed to implement this workaround. Our current solution is to catch the Exception, wait for 3 seconds and retry the statement. We have to retry it about 5 times on average until the statement works.

the DatabaseMetadata APIs mentioned in the thread does not open new connections internally

The profiler in the SQL Server Management Studio didn't log any new connection, so the second connection does not seem to come from our logic or from the driver. Thats why my guess is, that the SQL Server (not the driver) opens a new connection internally or works asynchronously and therefore reports the error, when executing the next statement.

If possible, can you provide a standalone java project which exhibits this behavior?

The project, in which we are facing this issue is pretty big. The database contains about 1000 tables, I don't think that the issue is reproduceable in a small standalone project.

Since this problem occures only with driver versions > 6.3.3 and the error reported comes from the getImportedKeys/getExportedKeys method, which has been changed in 6.3.3, it has to be related to that new statements.
I am not saying, that it's a driver problem, but it seems like the SQL Server has some problems with those statements.

[rene-ye]

Hi @Springrbua, I attempted to reproduce the error message mentioned in the thread, but was unable to do so. Attempting to create a new connection to a SINGLE_USER database with an existing connection simply returns a "connection failed..." error. There's not too much we can do from the driver side for now, but the stored procedure changes from #490 will not be reverted as they align the driver with JDBC Specifications.

[probert94]

First of all, thank you for your effort!

I attempted to reproduce the error message mentioned in the thread, but was unable to do so. Attempting to create a new connection to a SINGLE_USER database with an existing connection simply returns a "connection failed..." error.

This probably reinforces my presumption, that the second connection comes from the SQL Server (not the driver) itself.

There's not too much we can do from the driver side for now, but the stored procedure changes from #490 will not be reverted as they align the driver with JDBC Specifications.

I understand that those changes won't be reverted. I was just hoping for some advice on how to track down the cause of this Issue, since the workaround (wait and retry) seems a bit hacky.

I managed to create a "patched" version of the 6.3.3 driver, without the changes of #490 by following this steps:

1. Clone this repository using git
2. Hard reset to the revision edc794c
3. Revert the 2 changes from 7fb24f2
4. Build the Jar using mvn install -Pbuild42

With this "patched" driver version, I can't reproduce the error, so it must be somehow related to #490.

I also noticed, that the issue only occures, when the updater changes the structure of the database (for example by using some ALTER TABLE statements). If the database structure is not modified, the error does not seem to occure.

I did some research regarding the single user mode and found this article, which contains the folliwng prequisite:

Before you set the database to SINGLE_USER, verify that the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. When this option is set to ON, the background thread that is used to update statistics takes a connection against the database, and you will be unable to access the database in single-user mode. For more information, see ALTER DATABASE SET Options (Transact-SQL).

The flag is not turned on in our case, so thats not the issue, but this prequisite clearly states, that if the SQL Server (again, not the driver) does some asynchronous jobs, the database cannot be in single user mode.
So my assumption is, that some statements are executed asynchronously by the SQL Server (not the driver), causing subsequent statements to fail, until the statement is done.
This would also explain, why the error is not reproduceable on fast SSD drives.

Since I am not an expert, I would like to hear your opinion on my assumption.
If you can imagine this to be true, I should probably try to report it to the SQL Server team.

[rene-ye]

Hi @Springrbua, feel free to share this issue w/ the SQL Server team. I don't really know enough about the underlying operations SQL Server is performing to comment on whether the assumptions here are correct. I can only confirm that the driver is not opening new connections against the database when these APIs are called.

[probert94]

@rene-ye thak you again for your help, I'll report it to the SQL Server team and update this thread, if I get some feedback.
Btw. do you know the correct channel to report such issues to the SQL Server team?

[rene-ye]

Hi @Springrbua, I believe the public forum for SQL Server is: https://feedback.azure.com/forums/908035-sql-server

[probert94]

@rene-ye I openend a thread on the public forum.
If it's okay, I would like to get back to you, if more informations about the exact statements are needed.
Thank you for your help!

[ulvii]

Hi @Springrbua,
We will close the issue for now, as there is no action item on the driver side. Please feel free to re-open it if you have any questions.

[probert94]

@rene-ye @ulvii I posted this problem on the msdn forums and one of the users was able to create a minimal reproduction of the problem.
This allowed him to analyse what's going on and his conclusion was this:

when your process is running normally, it holds a shared lock on the database. Any other process that wants to come in to the database, attempts to take X lock on the database (because it is in single-user mode.). So far, so good. But when you submit a batch that creates a temp table or declares a table variable, your process also wants an X lock on the database. It may be queued up behind the other X request. And its request may time out before the other does. That's when you get error 924.

(Original Post)

He also listed some possible solutions:

• Run the update with RESTRICTED_USER instead of single user. The RESTRICTED_USER would only allow the admin user(s) to access the db but since our application is using the same user for the update and the application itself, this is not a solution for our case. Changing it to use 2 different db users would take quite some time implementing and would increase the support effort for every installation, so that's something we would like to avoid.
• Open a case with Microsoft and hope that there is a secret flag or that they agree it's a bug and fix it.
• Get rid of the operation, that invokes the call to sp_cursorprepexec. This operation is probably coming from the driver and if it is possible to get rid of it, it might solve the issue.

So it would be cool if you could take another look at it, since our workaround (wait and retry) does not seem to solve the problem, but just reduces it's frequency.

[ulvii]

Hi @Springrbua,

Based on the logs, looks like you are setting selectMethod=cursor; in your connection string, which forces the driver to use sp_cursorprepexec. If you remove selectMethod or set selectMethod=direct;, sp_executesql will be used instead.

I would also suggest to update the thread with the reproduction steps.

[probert94]

@ulvii thank you for the information, I just tried it again using selectMethod=direct but the error still seems to occur.
I will update the thread in the MSDN Forum with logs using selectMethod=direct.

I would also suggest to update the thread with the reproduction steps.

The reproduction does actually come from that thread, the user Erland Sommarskog was able to reproduce the bug:

Here is a repro, based on Northwind, but just replace the table name to any of yours. Set Northwind in single user from one window and stay in Northwind in that window. In a second window run:

USE tempdb
go
DECLARE @error bit
Again:
BEGIN TRY
SELECT @error = 0
EXEC('USE Northwind')
END TRY
BEGIN CATCH
WAITFOR DELAY '00:00:00.050'
SELECT @error = 1
END CATCH
IF @error = 1 GOTO Again

That is, this window attempts to sneak in to Northwind.

In the first window run:

EXEC sp_executesql N'CREATE TABLE #t (a int NOT NULL)
SELECT * FROM dbo.Orders WHERE CustomerID = @custid', N'@custid nchar(5)', N'ALFKI'

Run this manually a couple of time until you get error 924.

[ulvii]

My apologies, I meant to update your initial thread.

We were forced to use temporary tables to join sp_fkeys and sys.foreign_keys, to make DatabaseMetaData.getImportedKeys() JDBC-compliant without introducing serious performance degradation. Please see #467, #490, #677 for more details. Because the incident seems to be happening on server side, I don't think there is anything we can do in the driver without impacting rest of the users. Please feel free to let me know if you have driver change suggestions that can resolve the issue, I will definitely take a look.