SqlException when the database is not started but SQL Server is ready to connect
I am dealing with a rather long delay between the SQL ready and "ready to connect" state and the time my database starts up. Sometimes it takes more than 2 minutes. This is the reason why I sometimes try to connect to the database before looking it up online. Do you know how to shorten the time between "ready to connect" and started database? Or how can I determine that the db is connected to it from my C # application?
Basically I have some kind of "cache" of connections by connection string. This happens for the first connection, of course, so my code for the connections is pretty simple:
var result = new SqlConnection();
result.ConnectionString = "some connection string here";
try
{
result.Open()
}
catch (Exception ex)
{
//some logging stuff here
throw;
}
This is C # exception:
System.Data.SqlClient.SqlException: Cannot open database "XXX" requested at login. Login failed.
This is the SQL Server log:
2014-03-31 08: 21: 05.65 - SQL Server is now ready for client connections. This is an informational message; no user action is required.
2014-03-31 08: 21: 09.21 - Restore completed for database model (database ID 3) in 1 second (parsing 234 ms, retry 0 ms, undo 514 ms). This is an informational message only, no user action required.
2014-03-31 08: 21: 11.52 - Error: 18456, Severity: 14, State: 38.
2014-03-31 08: 21: 11.52 - Login failed for user "YYY". Cause: The explicitly specified database could not be opened. [CUSTOMER:]
2014-03-31 08: 21: 13.88 - Cleaning up tempdb.
2014-03-31 08: 21: 21.38 - Restore completed for database msdb (database ID 4) in 2 seconds (parsing 327 ms, retry 0 ms, undo 468 ms.) This is informational only, No user action required ...
2014-03-31 08: 21: 32.98 - Starting the "tempdb" database.
2014-03-31 08: 21: 40.30 - Service Broker pass through is disabled or not configured.
2014-03-31 08: 21: 40.41 - Database Mirroring Protocol Transport is disabled or not configured.
2014-03-31 08: 21: 41.50 - Restoration completed. This is an informational message. No user action is required.
2014-03-31 08: 21: 41.52 - The service broker manager started launching.
2014-03-31 08: 23: 41.87 - Starting the database "XXX".
source to share
Based on this link , the best practice is to query the value of the Collation property.
A database that has just appeared on the Internet is not necessarily ready to connect. To determine when a database can accept connections, query the collation_name column for sys.databases or the Collation property for DATABASEPROPERTYEX.
Therefore, when the Collation property is not null, the database is ready to connect. The request looks like this:
SELECT DATABASEPROPERTYEX('MyDatabase', 'Collation')
source to share