SQL Server service does not start automatically on system power cycle.

Category: sql server dbengine

Question

IPSingh on Wed, 12 Oct 2016 11:25:11


Hi,

We are noticing a strange behavior of SQL Server (2014) database engine service, that it does not start automatically when system is power cycle. The system is running on Win10 pro 64 bit upgraded from Win 8.1.  The Windows Event Viewer says;

<<The description for Event ID 17137 from source MSSQL$SQLEXPRESS cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.

If the event originated on another computer, the display information had to be saved with the event.

The following information was included with the event: 
AppDatabase>>

When i searched for this issue on internet I came to know from few forums that it is something to do with a bug in Win 10. And on changing the service startup type from Automatic to Automatic-Delay it will work. Although this solves our problem, but still wants to understand is it really a bug in win 10? If Yes, by when we can have a solution for this as this si the workaround which we did to make our applications work.



Replies

Erland Sommarskog on Wed, 12 Oct 2016 11:48:48


You don't see any message about the service starting in a timely fashion? Such a message would from Service Control Manager, not SQL Server.

The default waiting time for starting a service is 30 seconds, but if a lot happens on the machine when it boots, 30 seconds may not be enough. I had quite some problems with this on an earlier machine that I had which had spinning disks, and I had to set it to delayed start. When I changed my system disk to an SSD, the problem went away!

Message 17137 is "Starting up database '%s'.", so that is not related to the boot problems.

IPSingh on Wed, 12 Oct 2016 12:07:23


Thanks for your response...

The systems are normal testing systems with good specs. I have checked & there is no other main apps are running except SQL server 2014. We just have MS Office & SQL installed on these machines.

S_NO on Wed, 12 Oct 2016 12:13:55


windows 10 bug might be windows team can help,but is that event ID 17137 doest not an error & it reported as information-can you cross check?.

Also some of the links says that it might be-

"It is related to to AUTO_CLOSE setting of database in MSSQL$MICROSOFT##SSEE. If you have SSMS installed right click each database and on Options Psge set Auto Close to false. Alternatively you can do it with ALTER DATABASE T-SQL statement".

but I would say that as soon as you powered on the windows 10 - do you see any resource contention issues like CPU,Mem or disk i/o issues, there is also quite performnace degrade with battery saver too..but for the sql to get start it has to do with many things with windows registry,files,systemdatabase,authentication,ports lot more..any one of quite shakes may differ.

it is also worth to check the sql error log once you powered on see- anything in errorlog (or) check the eventlogs too..

Erland Sommarskog on Wed, 12 Oct 2016 12:49:32


Yeah, but there may still be a lot going on at startup. Do the machines have SSD or spinning disk for the system disk?

IPSingh on Thu, 13 Oct 2016 05:00:50


Thanks for your response...

The error message which I mentioned earlier is from Event logs. I'll try to get hold of SQL error logs as well & will share in the post.

I've seen the AUTOCLOSE property of the database mentioned in the Windows Event log message <ATAMDesktop>, when I changed the property to FALSE, the service start problem was still there. But when i changed the service startup type to Automatic delay, it worked fine.

Do you have any idea about the root cause of this issue?

Erland Sommarskog on Thu, 13 Oct 2016 10:43:42


Do you have any idea about the root cause of this issue?

As I said: there is a lot happening when Windows starts up. Did you ever answer the question if you have an spinning system disk (as opposed to SSD)?

The autoclose effect should not have any effect in this case, since SQL Server can come online, even if all databases have not been opened or recovered.

IPSingh on Mon, 17 Oct 2016 16:56:52


The disk on the system is an Spinning Disk & there are not much apps on the system. The system always takes a long time during startup because of apps like Dropbox & Skype.

Erland Sommarskog on Mon, 17 Oct 2016 21:33:51


The disk on the system is an Spinning Disk & there are not much apps on the system. The system always takes a long time during startup because of apps like Dropbox & Skype.

As I suspected. If you don't like the Delayed start, I can only recommend you to switch to SSD for the system disk. I did that a couple of years ago, when my machine started to take 15-20 minutes from starting until I could use it.

There is a registry setting to instruct Services Control Manager to wait more than 30 seconds, but I was never really entirely successful with it. As long delayed start works for you, stick to that.