SQL Server Express Failing To Start Revisited
David Musgrave posted an interesting article a few days ago where compressed SQL Server database files caused the SQL Server service not to start.
Happily, the event log message tells us exactly the issue and solution and when I read this I noted that this was SQL Server Express being used by the customer.
Beth, one of our authors, noted in an email that "we used to do this all the time without issue".
I thought about it and essentially agreed. We've been using laptops for years as our "primary" machines and not desktop machines. And while hard disk space is cheap, you don't find terabyte drives in laptop machines too often (read: ever). So with having multiple SQL Instances for various versions and hard drives in the range of 100-150 GB or so (my new laptop has a big 250GB drive which really helps) and I can personally attest to having compressed my SQL folders from time to time also.
How do we reconcile this difference? Mistake? Bad memory?
Well, what I recall from my previous experience is that while SQL 2000 used to run compressed OK (for me), when new service packs came out it wouldn't install unless the files were uncompressed.
But it seems to me that a key distinction is that even before Great Plains was acquired by Microsoft, we had a SQL Server site license. Meaning that we could install a paid for version of SQL Server whether it was Enterprise, Standard, Developer, etc. Heck at that time I don't believe Microsoft even offered a free Express version.
So to test this theory, I checked my machine and sure enough I did have a version of SQL Server 2008 Express edition loaded but not running on my machine.
Next, I then used NTFS file compression to compress the entire MSSQL10.SQLEXPRESS folder where the SQL files reside and tried to start the service on my Windows 7 - 64 bit machine. It failed to start with the error:
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
Looking in the application event log, I think I like the customers' version of the error better. The only thing it tells me is:
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.
And no, the useful error message isn't in the details section either but it does show the 3417 number that the first error refers to. I wonder if this is a "Windows 7" thing somehow as the customer did seem to be using the same SQL 2008 Express edition that I was.
Searching the internet, I did see a few reasons why 3417 might occur. File damage, permissions, and compression were all culprints with file compression being the typical issue. So if this happened on my own machine I would have solved it as well by consulting the Wisdom of the 'Net. But I wouldn't have minded a better error message.
OK, after all the above effort I just re-confirmed what we already knew - SQL Server Express doesn't start with compressed files.
What about a retail version of SQL?
As a test, I then stopped my SQL 2008 Developer Edition 64bit Service that runs my GP10 instance and compressed its folder and tried to start the service again. Somewhat to my surprise, I got the same 3417 error that the SQL Express instance gave. On one hand, not surprising because why would one work and not the other? But my thought was perhaps that the Express version might be "limited" and wouldn't run compressed but the Developer Edition wouldn't have this limitation. Guess that wasn't it, huh?
So what about Beth's and my own fuzzy memories of this working? About the only thing I can think of is that we were likely running SQL 2000 and previous versions and that this wasn't an issue then. I considered poking around and finding a SQL 2000 to test with or installing one specifically to test my new theory that the old version allowed this. But I figure - what's the point? Even if the old version allowed this; the new versions don't - buy a bigger hard drive and try to fill it up.
Best Regards,
Patrick Roth
Dev Support