ahmed imported from SE
sql-server sql-server-2008
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.
Top Answer
Paul White
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)][1]:

>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`.

  [1]: https://docs.microsoft.com/en-us/sql/t-sql/functions/databasepropertyex-transact-sql
When is the SQL Server database ready to accept queries?

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.