[3417] MSSQL Express 2005 error due to compressed LDF/MDF

Feb 22, 2010 by d0rr    1 Comment     Posted under: Web Development

Today when I tried to start MSSQL EXPRESS 2005 on my laptop in which I have not started the SQL service for quite some time, I received the following error:

Windows could not start the SQL Server (SQLEXPRESS2005) 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.

And I found the following error detail in the Event Viewer (Windows XP: Start > Settings > Control Panel > Administrative Tools > Event Viewer > Application)

“The file ‘ C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mastlog.ldf ‘ is compressed but does not reside in a read-only database or file group. The file must be decompressed.”

Immediately the cause was known. As my laptop disk space did run low quite some time ago, Windows actually helped to compress the files that were not used frequently. MSSQL doesn’t know how to load compressed database files, as simple as that.

I simply browse to ‘ C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\ ‘, right click on ‘ mastlog.ldf ‘, proceed to ‘ Properties > General > Advanced ‘ and uncheck ” Compress contents to save disk space “

If that still does not solve your problem, try to do the same for all the files under that folder, that should do the trick.

Cheers!

Got anything to say? Go ahead and leave a comment!