sql-server localdb sql-server-2016-localdb

SQL Server Local DB Stability Issues (Spring 2023)

We have a WPF .NET desktop application that uses SQL Server Local DB as its database. Our application is installed on hundreds of computers and has been released for about six years now.

We started running into multiple issues this Spring with database stability. Everything seemed to be fine last year. Admittedly, 80% of our app's usage is in the Spring. So, we could have been running into issues in the Fall of 2022 and not been aware of it. The only thing that really changed in our software was that we moved the app away from ADO and over to explicitly concatenated SQL statements. We've been seeing these issues on both Windows 10 and Windows 11 machines. The app could have been running for hours or even days without issue before running into a brick wall of database instability. Alternatively, sometimes the app won't even start because it can't connect to the database without a computer restart. Although, some installs of the app seem to have more issues than others. Some installs of the app have never encountered a single issue. It's completely random.

I've tried out various things to address the issues:

The app was creating a new connection every time it needed to communicate. I switched this over to a static connection for reads and instance connections for transactions. This seemed to alleviate most of the issues, but not all.
When a statement failed, I added logic to kill the SQL Server instance itself and manually recreate it. Then retry the statement. This 'second chance' execution fixed more issues, but the app logs can sometimes show an excessive amount of SQL Server instance bounces.
We had been installing SQL Server 2016, but we're starting to upgrade users to SQL Server 2022. This seems to help to.
I added a 'connection test' to every caller needing a database connection. I have the app run "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;" to make sure that the connection is open. If not, I go into the second change logic from item 2.

We seem to see two errors at startup. The first is significantly more common.

Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=12085; handshake=0;
  at System.Data.ProviderBase.DbConnectionpool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultileObjectTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error 50 - Local Database Runtime error occurred. Error occurred during LocalDB instance startup: SQL Server process failed or start.)
  at System.Data.ProviderBase.DbConnectionpool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultileObjectTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)

During runtime, we see these a lot. The first two are by far the most common, but the others pop up from time to time as well.

The wait operation timed out.

Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

A transport-level error has occurred when receiving results from the server. (provider: Session Provider, error: 19 - Physical connection is not usable)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

ExecuteReader requires an open and available Connection. The connection's current state is open.
   at System.Data.SqlClient.SqlConnection.GetOpenConnection(String method)

The ConnectionString property has not been initialized.
   at System.Data.SqlClient.SqlConnection.PermissionDemand()

Thoughts?

Solution

Your changes are just making things worse by papering over the cracks.

You are supposed to make a new connection when needed, and then dispose with a using, not have a static connection object. You should rely on connection pooling to handle it.
Killing the instance was probably the wrong thing, it's probably just stuck on a query taking a long time. You should investigate the relevant query plan and try improve it
The version by itself wouldn't usually make such a difference, but you may get a different query plan which doesn't get stuck going slow.
READ UNCOMMITTED is a really bad idea, don't do it unless you understand the implications of incorrect data.

Things you should check:

LocalDB does not run the whole time by default. Try starting it manually at application startup, then wait at least 5 seconds for it to be ready.
Alternatively set it to autostart using a batch file on user login.

Set the database so it doesn't AUTOCLOSE

ALTER DATABASE YourDb SET AUTO_CLOSE OFF;

LocalDB is normally set to auto-shutdown. Set the instance to only auto-shutdown after a long time, see also How to prevent SQL Server LocalDB auto shutdown?
```
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
```
```
EXEC sp_configure 'user instance timeout', 65535;
RECONFIGURE;
```
Consider using a full installation of SQL Server Express instead, to avoid most of the above problems.
It looks like you also need to set the max server memory, see the documentation.