0

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:

  1. 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.
  2. 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.
  3. We had been installing SQL Server 2016, but we're starting to upgrade users to SQL Server 2022. This seems to help to.
  4. 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?

adamjhilton
  • 362
  • 3
  • 9
  • are you running on single sql server or multiple? The part about restarting SQL server instance is a bit unclear? – siggemannen May 17 '23 at 15:56
  • 1
    Your changes are just making things worse by papering over the cracks. 1. 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. 2. Killig the instance was probably the wrong thing, it's probably just stuck on a query taking a long time. 3. The version wouldn't make such a difference, but you may get a different query plan which doesn't get stuck going slow. 4. `READ UNCOMMITTED` is a really bad idea, don't do it unless you understand the implications of incorrect data. – Charlieface May 17 '23 at 20:09
  • Things to 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. Then set the database so it doesn't `AUTOCLOSE`, and set the instance to not auto-shutdown, see https://stackoverflow.com/questions/14153509/how-to-prevent-sql-server-localdb-auto-shutdown – Charlieface May 17 '23 at 20:13
  • @siggemannen I'm not sure that's relevant to LocalDB. Running these commands in a Command window will restart the instance: sqllocaldb stop MSSQLLocalDB sqllocaldb delete MSSQLLocalDB sqllocaldb create MSSQLLocalDB – adamjhilton May 18 '23 at 16:46
  • @Charlieface Papering over the cracks is an apt statement. I'm trying to resolve the underlying issues now. 1. All connections were being properly disposed of. 2. I don't *think* a query in one connection would have locked a query in another connection on another thread. Especially since I have all connections start by setting READ UNCOMMITTED. But I'm not an expert here. 4. I'm aware of the implications. I may revisit this at some point. – adamjhilton May 18 '23 at 16:52
  • @Charlieface Interesting points on LocalDB startup time and auto-closing. I'll set the instance to not auto-close and see what happens. – adamjhilton May 18 '23 at 16:54
  • So it's one user only. Strange, these errors look like you either exhaust your connections or you're running low on computer resources, the last might explain why you have different problems on different computers. Perhaps your pool is leaking? Is there a lot of multi-threading going on in the app? Or do you do stuff synchroniously? – siggemannen May 18 '23 at 17:06
  • @siggemannen Yes, it's a single-user desktop application. But it does talk to other desktop applications to justify certain data. So, there is a lot of mutli-threading going on. But most of that communication is on background threads and pipelined. Even before I put all the reads on a single static connection, I doubt there was ever more than a dozen simultaneous connections. Although, there could be a thousand or more read/writes that happen sequentially. – adamjhilton May 19 '23 at 12:42
  • I remoted into a few of my problem children machines and configured their LocalDBs to not auto-close. I also modified my connection string to include "Min Pool Size=12". At note from https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/sql-server-express-user-instances says, "If Min Pool Size is used in the connection string with a value greater than zero, the connection pooler will always maintain a few opened connections, and the user instance will not automatically shut down.". So, I'll see how things go over the weekend. – adamjhilton May 19 '23 at 17:14
  • So far, so good. The databases have been rock solid. The only downside that I'm seeing is that the "SQL Server Windows NT - 64 Bit" tasks are basically maxing out their allowed memory. The default is ~2,147 MB, and that's basically what they grow to in a day or so. Not really a deal breaker, but I just wasn't expecting it. – adamjhilton May 26 '23 at 12:50
  • @Charlieface, alright, I'm calling this fixed. Making sure the database doesn't auto-shutdown was the secret sauce. Would you like to officially answer this question to get the credit? Thanks! – adamjhilton May 31 '23 at 14:08
  • **"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."** This means, "we totally refactored our data layer." It might make sense to figure out how to load-test your software, especially after making big changes. – O. Jones May 31 '23 at 14:36

1 Answers1

1

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.
Charlieface
  • 52,284
  • 6
  • 19
  • 43