3

TL;DR - MSSQL Service claims to be ready (status = Started) while it's not really ready (see below strike-outed text)

I have the C# written windows service which installs itself as ServiceAccount.LocalSystem and specifies MSSQLSERVER in ServicesDependedOn.

Now I've created MS SQL user, granted it all the necessary permissions & roles (including db_owner on the needed database, and Connect / Login privileges).

When running the service manually (via net start or services.msc manager) it works corretly, i.e. it connects to the database. When I restart the machine, the service throws this exception:

Cannot open database "xxxx" requested by the login. The login failed.
Login failed for user 'xxxxx'.

What have I configured incorrectly? Again, when I run the service manually it works fine!! I've also tried logging in with this account via MS SQL Management Studio - and this works too.

as suggested in comments, I've tried waiting before trying to connect - 20sec Sleep doesn't solve the problem. I'm adding the state of MSSQLSERVER service (checked via ServiceController) to the log, and it is "Running". Everything seems to be fine, except that the Login fails when service is being auto-started

Ok, I've been tracking this down for a few hours. Here's what I've found:

SQL Server service (MSSQLSERVER) claims to be ready (status = Started) quite quickly (about 2-3 seconds after issuing the "net start" command). Unfortunately warming up (starting up databases, recovery and some other stuff) takes place later, and takes up to 2 minutes (120 seconds!!). Of course it rejects connections until it's warmed up.

I've ended up doing 

try 
{
    connect;
} catch {
    RequestAdditionalTime(); // to avoid Windows Service timeout
    Sleep();
}

in a while loop.

I hate this kind of solutions but can't find anything cleaner.

If anyone knows how to do it properly, please answer.

migajek
  • 8,204
  • 14
  • 71
  • 113
  • SQL Server can take a while to start up and have all databases ready for action. It needs to create `tempdb` and run restart recovery on all databases. – Martin Smith Dec 15 '13 at 13:02
  • @MartinSmith Is there any way to get to know if it is already ready to go? Id like to avoid Thread.Sleep as this is rather dirty hack – migajek Dec 15 '13 at 13:24
  • you can use this solution to detect if the service ready or not: http://stackoverflow.com/questions/178147/how-can-i-verify-if-a-windows-service-is-running – yasso Dec 15 '13 at 13:31
  • @yasso I don't think this is the solution, my service depends on MS SQL, so it is supposed to be ran after MS SQL claims to be ready. The problem seems to be MS SQL claiming to be ready while it's not really ready... – migajek Dec 15 '13 at 14:38
  • Following your edit I'd also be inclined to see what it is doing for these 2 minutes and if that can be improved. e.g. Is your `tempdb` data file sized large without instant file initialization enabled? Do any databases have too many VLFs? – Martin Smith Dec 15 '13 at 21:05
  • @MartinSmith MSSQL startup time is quite long here but this is not really the point. This SQLServer instance is just my dev-environment, I believe production db-admin will take care of their's instance. I need to solve the problem as a developer, i.e. how to make sure my C# code waits for SQLServer to be ready. Anyway, there are the screenshots. This time it was "only" 78secs. In the meantime there's one failed attempt to connect, made by my application. http://imgur.com/EFgfC5Y,J1s4PDF#0 – migajek Dec 16 '13 at 14:15
  • Well I suppose for a local server you can poll the log until you see the "Server is listening" message appear. For a remote server nothing springs to mind apart from simply trying to connect and seeing if it succeeds (as you are doing) – Martin Smith Dec 16 '13 at 14:26

2 Answers2

1

The only solution which works for me is not to relay on the service status, just to retry each ~10sec to connect to the server.

migajek
  • 8,204
  • 14
  • 71
  • 113
1

You can use the above approach connecting to master and call the following 

select state, databases.state_desc ,* from sys.databases


/*
0 = ONLINE
1 = RESTORING
2 = RECOVERING
SQL Server 2008 through SQL Server 2014
3 = RECOVERY_PENDING
SQL Server 2008 through SQL Server 2014
4 = SUSPECT
5 = EMERGENCY
SQL Server 2008 through SQL Server 2014
6 = OFFLINE
SQL Server 2008 through SQL Server 2014
7 = COPYING
Azure SQL Database
10 = OFFLINE_SECONDARY
Azure SQL Database
*/

or another approach is call the following checking for non NULL value back, Note that this should return NULL unless database is completely ready.

SELECT DATABASEPROPERTYEX('MyDatabaseName', 'Collation')

AAzami
  • 396
  • 2
  • 3