1

yesterday i had several Problems in a Windows Service with a Function that already works and runs several Months without a Problem.

But yesterday i got alot of Errors in my Service. The Errormessages were a Combination of mostly these Twos.

The connection does not support MultipleActiveResultSets.
Connection Timeout Expired.  The timeout period elapsed during the post-login phase.

I did not have changed anything in the past 2 Months and had never Problems with this Service.

While searching the Problem i stumbled over these SQL-Server Log Messages

Parallel redo is shutdown for database '' with worker pool size [1].

Parallel redo is started for database '' with worker pool size [1].


Starting up database ''

Theses Messages appeared also in the Past, periodically they occured.

But yesterday they happened in a extremly high Frequency. They have started around 5 Hours after the Backup Process and lasts for about 7 Hours.

I'll had nearly 300'000 Events. These 3 Messages together occured around 3-4 Times per Second and suddenly they stopped.

Im not sure anymore if they stopped after i ended the Service, but as this Message have stopped to occur in this High Frequency, my Service also had no Problems anymore. Im not sure if these 2 Instances (Sql-Server and Windows Service) were rocking each other up.

I'll kinda understand what leads to this Messages, but not what why it were spammed like this.

Any Ideas on how to prevent this and / or ways of investigating this further?

All the Best Daniel

Josh Darnell
  • 30,133
  • 5
  • 70
  • 124
climbah
  • 13
  • 1
  • 3

1 Answers1

2

Those "Starting up database" messages occur when the database starts up (is opened / accessed by a user) for the first time. Normally you'll only see this message once (when the instance starts up), or if something happens to cause the database to shut down / close (like an availability group failover).

If you are seeing that message a lot, one likely reason is that the database AUTO_CLOSE option is set to ON. This means the database is shut down / closed very quickly once there are no connections to that database.

Your service probably uses best practice patterns of opening a connection, doing some work, and then closing the connection. This would lead to the database shutting down / starting up a lot if that's the only thing accessing the database.

You can, and probably should, turn this off to make sure the database remains open and online even when it's not being actively used:

USE master;
GO
ALTER DATABASE YourDatabaseName SET AUTO_CLOSE OFF;
GO

The parallel redo messages are also normal, and generally only occur when the database is starting up. See this Q&A for more information on that one: Messages about parallel redo

You mentioned this problem is intermittent. It's possible some other person or process is changing this setting periodically. Or that the service application you mentioned has somewhat variable performance that might lead to more or less DB shutdowns (depending on how quickly it opens and closes connections).

Josh Darnell
  • 30,133
  • 5
  • 70
  • 124