SQL Server refusing to start

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)

SQL Server 2008 Express Edition

I've just installed SQL Server 2008 Express Edition.

Thought #1 : It rocks.

I found SQL Server 2005 (particularly the SQL Server 2005 Management Studio Express) to be VERY slow - you know, 10 second delay here, 10 second delay there. And btw, I'm using a very powerful machine.

None of that ridiculous stuff (so far) from SQL Server 2008 - bam! Responsive. Fast. I like.

Thought #2 : It's extremely unintuitive figuring out how to install SQL Server 2008 Management Studio Express.

SQL Server 2008 Express Edition installed with my version of .NET Studio 2008 Express. But no Management Studio. I find Management Studio particularly useful, so I wanted to install it.

Problem #1 : It's now called Management Studio Basic instead of Management Studio Express.

Problem #2 : With SQL Server 2005 Express Edition, you could download and install the Management Studio separately. Turns out that option ain't available with SQL Server 2008 Express Edition. You actually have to download a 200MB+ install package that includes both SQL Server 2008 Express Edition and the Management Studio. That's not obvious, but once you know it, ok, you can move on...

Problem #3 : When you launch this installer, it is extremely unintuitive finding how to install just the Management Studio, given that (if you're in the case I was in) you already have SQL Server 2008 Express Edition installed and just want the Management Studio to be installed.

Many many MANY thanks to these guys who wrote an article which gave me just enough information that, with a bit of trial and error, I finally managed to triumph. If you're in the same boat as me, go to Installing SQL Server Express 2008 and it shows you the things you need to know. Kudos!