When is the SQL Server database ready to accept queries?

Question

In the SQL Server error log file I found the following lines:

2018-02-22 14:10:58.95 spid17s     Starting up database 'msdb'.
2018-02-22 14:10:58.95 spid16s     Starting up database 'ReportServer'.
2018-02-22 14:10:58.95 spid18s     Starting up database 'ReportServerTempDB'.
2018-02-22 14:10:58.95 spid19s     Starting up database 'XYZ'.

If I check for the status of the database XYZ before this time, it is ONLINE using the following statement:

SELECT state_desc FROM sys.databases WHERE name='XYZ'

...but when I try to connect to this database using a C# application, it can't connect to the database.

The error is:

Login failed for user 'asd'.
Reason: Failed to open the explicitly specified database.

I tried three different users (Windows user, sa, SQL Server user defined for the application). The problem happens when I run the application in the start up of the OS, but if I start it manually after the start up, no errors happen, so I think all SQL Server settings and firewall settings are correct.

I also checked before this that the service status is running.

What else should I check to make sure the database is actually online and ready for queries?

I'm looking for a key that tells me it's ok to query the database, instead of delaying for a time (even not based on a clear reason).

I thought of scanning the error log for the text "Starting up database 'XYZ'", but this means I have to add a setting for the application for the path of the SQL Server error log. It also means reading the file many times until I find this phrase.

Answer 1

The SQL Server database is ready to accept queries as soon as:

SELECT DATABASEPROPERTYEX(N'database name', 'Collation')

does not return NULL.

From the documentation for DATABASEPROPERTYEX (Transact-SQL):

Note: The ONLINE status may be returned while the database is being opened and is not yet recovered. To identify when a database can accept connections, query the Collation property of DATABASEPROPERTYEX. The database can accept connections when the database collation returns a non-null value. For Always On databases, query the database_state or database_state_desc columns of sys.dm_hadr_database_replica_states.

Answer 2

SQL Server databases start up, but then they need to analyze the transaction log to roll transactions forward & backward. This process can take anywhere from milliseconds to hours (even days!) in the event of a long-running transaction, many (think thousands) of user databases, or databases with lots (think tens of thousands) of virtual log files.

If you only want the app to get in when recovery is done and the database is ready, have the application retry its connection.

If you want the app to be able to get into SQL Server immediately, but maybe not be able to run any queries yet, set its default database to TempDB instead of a user database. It'll likely be online right away even when user databases are unavailable.