Sql-server – When is the SQL Server database ready to accept queries

sql serversql-server-2008startup

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.

Best Answer

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.

Related Question