Here's a weird one that boggled my brain for a long ol' time.
SQL Server on my development laptop was refusing to start.
I hadn't used it for months, but didn't think I'd changed anything that could affect it.
The error message was plain ol' weird :
"Windows could not start the SQL Server (SQLEXPRESS) on Local Computer. For more information, review the System Event Log. If this is a non-Microsoft service, contact the service vendor, and refer to service-specific error code 3417."
Well, that wasn't weird, but the associated Event Log entry sure was :
"The SQL Server (SQLEXPRESS) service terminated with service-specific error WARNING: You have until SQL Server (SQLEXPRESS) to logoff. If you have not logged off at this time, your session will be disconnected, and any open files or devices you have open may lose data."
Ah - aha? What's all this log off bizzo?
No makey sensey.
Searching the web found only one other website mentioning the problem. One, in the entire world. And their solution was basically to uninstall SQL Server, delete the remnants of the installation folder, and reinstall.
Well, thanks be to God, the answer dawned on me :
To save space on my SSD (where speed was brilliant but size was cramped), I had compressed all my program files.
That works a treat - program files are almost entirely read-only, so both with hard disks and with SSDs of all varieties, compressing program files usually brings a speed improvement, and certainly frees up a lot of disk space.
BUT, I suddenly recalled that SQL Server stores its "master databases" deep inside the Program Files. By itself, that's not a problem. But I also recalled from years ago that SQL Server uses a special type of low-level file system access that is incompatible with NTFS compression. The disk access used by SQL Server is designed to optimise database page caching for uncompressed data files. But it simply cannot work with NTFS-compressed data files. Full stop. (And there are ways to trick it into working, but believe you me, there are substantial write performance implications if you try.)
So I dug into the SQL Server installation folder, and uncompressed the *.mdf and *.ldf files in the Data folder, and all was merriment once more.
For the odd sailor out there cutting close to the wind like me, this might prove helpful. Enjoy! :o)
Subscribe to:
Post Comments (Atom)
17 comments:
Thank you! :) I would never guess it myself.
Cool - Worked Like A Charm :-)
I. Just. Love. You.
This post saved my life. Thanks!
Thanks for the positive feedback all - glad it helped!
Thank you! I *never* would have found the solution to this, and it bit me at a critical time. Thank you very, very much!
Thank you!
Brilliant! My thought when i got the message {...WARNING: You have until SQL Server (SQLEXPRESS) to logoff...} was that it must be time to go home -- i promised my wife I'd be home before Oracle.
You're welcome, you're welcome, you're welcome! Thanks for saying thanks! :o)
LOL @ "home before Oracle" :o)
Uhm.. Now I have gotten the same error message as you. And I have NOT compressed the folder. I guess I have to reinstall SQL server :(
I did find the error message rather amusing though :)
Same here.... unless I am missing something REALLY obvious????
Really trying to avaoid a reinstall...
Same here.... unless I am missing something REALLY obvious????
Really trying to avaoid a reinstall...
We have sql on separate drive letters on a dynamic disk., nothing on the C other than the install, some how the drives got set as read only after a reboot, dont know why yet, in the app log there was an EID 17053 and the others in the system log. 17053 had (the media is write protected)in the error details. To fix, from the command prompt, launch diskpart, then 'list volumes', select the volume where your sql drive is (select volume ) then 'attributes volume', ours had read only, use 'attributes volume clear readonly' to fix. We had to do this for each drive. SQL started right up!
same problem, same reason, same solution! Thank you!
Had same problem. Changed security on ldf and mdf files to allow access to everyone. Services started normally. Thanks for the hint.
You win!!!!! Good call.
Hello, I had the same problem, but my problem was that the server location didn't exist, it was on another location.
Just localhost instead of localhost/msqlserver
Post a Comment