sql-server sql-server-2008 add tag
ahmed (imported from SE)
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 (imported from SE)
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

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.