Tuesday, May 15, 2007

depending on loading Sql Server 2000

My C# Service depends on MS SQL 2000 service and my service is starting to use database immediatly during loading. Everything ok, service is starting and stopping untill... restart.
On some computers, my service is not run automatically after computer restart because database not ready yet.
(I got error: Could not find database ID 2. Database may not be activated yet or may be in transition)

After investigation of the sql server I understand that server starting listening clients before, than starts up my database... How it can be??? Ok, there are milliseconds difference, but my service already tried to access db, and exited because it cannot. On other computers, where service is starting, I see in the log that my database was started before than server starts listening clients.

My database is not marked "Auto Close". Sql Server configuration is exactly same on computers, SP4 installed. So I don't have idea, why some sql servers change their loading priority...

Well, I found some things that may fix this.
I unchecked autostart MSDTC service. Anyway this service is loading when DTC transaction initiated by somebody. And it helps

Interesting, service started, but it is not solution, I don't know, what settings user will have.. I And I suspect, it is just loading time issue.. Because log still shows starting database only after starting listening, not before.

2. I found at microsoft if I will use trace flag 3614, the startup sequence of SQL Server 2000 will be slightly different. And they rules the world with such phrases like "slightly different"?
I checked checkbox "Autostart MSDTC" back and added parameter -T3614. Ooops, service started... Let's see sql server log.Yes! My database is loaded before than service started to listen clients.

Nice. Solution may be used for workaround in the case of problem. But for my serenity, I added to OnStart method of my service check for accessibility of my database, and in the negative case sleep service for some time before continue..
Now it works.

However, I don't have real solution or understanding, why it is happens when Sql Server put user databases to load after starting to accept requests.. Question is opened for while.
It looks like this article have similar situation, but it is not enough to understand.