November 26, 2007 at 3:29 pm
I believe I am running into a timing issue where my .NET service is attempting to connect to my database which houses configuration values. The service does have a dependency on MSSQLSERVER. However, the database name is still not available at the time the connection is attempted. This causes the connection to fail and prevents the service from starting. The problem is intermittent and is probably due to workload at startup. However, if I change the code to continually loop and attempt the connection every so often it eventually works. But I'd like to avoid this hack if possible.
In looking at the ERRORLOG file I have seen that some system databases have the entry:
Starting up database 'master'.
Starting up database 'model'.
But then I see my database indicate the following:
Skipping startup of clean database id 6
My question is: Is it possible that the failure to connect to the my database is caused because of it not "starting up", and is skipping "starting up"? I don't really understand what this means. As such, I have put this in the Newbie forum... My hope is that there is a way to ensure the database is started up when all the system ones are too... or does this not make sense?
Any help would be greatly appreciated.
November 26, 2007 at 10:25 pm
Once connected to SQL Server you can run.
select name
from master.dbo.sysdatabases
where has_dbaccess(name) = 1 --look at databases to which you have access
AND NAME = 'YourDatabaseNameHere'
OR
SELECT HAS_DBACCESS('YourDatabaseNameHere')
Of course the above two you would still have to check SQL Server.
At some point you will have to loop if it is in the startup script or any other connection. So why not just loop in the service startup?
November 27, 2007 at 7:32 am
Thanks for your reply. What you stated is what I have found out, regarding looping.
I guess my confusion came in when I assumed that when I set MSSQLSERVER as a dependency on my service starting that I wouldn't need to loop. My assumption was that when MSSQLSERVER notified the "services manager" that it was finished that my service could now start and make a connection to any database with no problems.
It appears though that the following timeline occurs instead:
00:00 MSSQLSERVER starts initializing
10:00 MSSQLSERVER notifies services manager it is complete
10:00 - 10:03 - MSSQLSERVER is making databases available for connections.
10:01 'MyService' starts
10:02 'MyService' attempts to connect to one of the databases.
10:02 Connection fails as not available for login yet.
10:03 Connection retried, fails as not available for login yet.
10:04 Connection retried, connection made.
So my theory is that MSSQLSERVER says it finished but in the background it is still making databases available to something that would be requesting it.
Question now is, Why is having MSSQLSERVER as a dependency unreliable as a method for ensuring a database is started and a connection can be made right away?
November 27, 2007 at 10:27 am
This is because the SQL Server service is started and once it is started it starts the recovery of the databases. The databases are a part of SQL Server but are not part of the service itself. If that make sense.
November 27, 2007 at 11:27 am
Yeah, I understand your point. That makes sense. There must be more issues behind why it wasn't implemented to at least try starting up each database so a connect would work before saying the service is complete. I guess that could've opened a pandora's box with a myriad of differing complexities. Oh well!
But with that, looping is what I will do and have done. I have provided two settings which are configurable by the user in the event they have too much processing after a restart as their PC comes online. This seems to be one cause of the issue. Too many services starting, slow PC in general, etc. The settings are for number of retry attempts before giving up and the other for the time in milliseconds before reattempting a reconnect. This should meet the demands of all customers once the settings are good for their machine. The defaults I am providing should catch all but the really worst case scenarios.
Thanks for your responses. I appreciate the guidance dealing with the MSSQLSERVER service and what happens when it starts and reports finished!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply