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.
TopAnswers
Databases
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