The story was that after installing the regular Windows Updates for Windows Server 2003 on their SQL Server machine, the customer rebooted to complete the update installation. When the server came back online, no-one could log into Microsoft Dynamics GP anymore.
You might ask "What had Windows Update done to break Microsoft Dynamics GP?" Well, the answer to that question was "nothing at all".
As this was affecting all users (including the Microsoft Dynamics GP client on the SQL Server), we decided that the problem was with the SQL Server. Looking in the Application logs with the Event Viewer we found a number of errors of the form:
Event Type: Error
Event Source: MSSQL$SQLEXPRESS
Event Category: (2)
Event ID: 5118
Time: 8:02:04 PM
The file "C:\Program Files\Microsoft SQL Server\MSSQL\Data\GPSTWOLog.ldf" is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.
So why would we get a SQL Server Error 5118 about data files being compressed when no-one had turned compression on for that server? There was plenty of hard disk space available, so there was no need to use compression.
Looking at the Advanced Properties of the files in the C:\Program Files\Microsoft SQL Server\MSSQL\Data\ folder by right mouse clicking and selecting Properties and then clicking Advanced showed us that the files had the Compress contents to save disk space option selected and so were indeed compressed and that the error message from SQL Server was the exact issue.
To make it easier to see what files were compressed we turned on the option in File Explorer to display compressed files in a blue colour. From the File Explorer Select Tools >> Folder Options (or Organize >> Folder and search options) and click on the View Tab and make sure the Show encrypted or compressed NTFS files in color option is selected.
Once this option was enabled, we were able to see that many files in the system had been compressed.
So the next question is "Why would a Windows Update compress the files?" and the answer is "It didn't".
The files were compressed at some stage prior to the Windows Update. It was the reboot that caused the failure as this would have restarted the SQL Server service. When the SQL Server service starts up and attaches the databases, it would have identified that the database and log files were compressed and generated the errors.
So "What could compress files without the user knowing?" and the answer here is "The Disk Cleanup utility!!".
The Disk Cleanup utility has an option to Compress old files and this could compress files that should not be compressed, such as the SQL Server data files. I would recommend un-checking this option as it will cause problems if SQL Server files are compressed and could affect performance elsewhere in the system. Hard Disk space is cheap now and so compression is not as needed as in the past.
To summarize: below are the events that caused the problem:
- A Disk Cleanup was run at some stage which compressed the SQL Server data files.
- The machine was not restarted and so SQL Server continued to work with the now compressed data files.
- Later, Windows Updates were installed which requested the machine be restarted.
- The machine was restarted after the Windows Updates forcing all services to start again.
- SQL Server service checked the files before attaching them and generated the error.
- Users could not log into Microsoft Dynamics GP.
The fix for this issue was fairly simple.
- We ran the Disk Cleanup utility and made sure that the Compress old files option was not selected. This will be remembered and so the option will not be used accidentally in the future.
- We clicked Start >> Control Panel >> Administrative Tools >> Services and stopped the SQL Server related services.
- We selected the C:\Program Files\Microsoft SQL Server\ folder with a right mouse click, selected Properties and clicked Advanced. The Compress contents to save disk space option was not selected. We checked the option, clicked OK and then on the Properties window clicked OK. On the Confirm Attribute Changes window, we selected Apply changes to this folder only (see screenshot below).
- We then selected the C:\Program Files\Microsoft SQL Server\ folder again with a right mouse click, selected Properties and clicked Advanced. The Compress contents to save disk space option was now selected. We unchecked the option, clicked OK and then on the Properties window clicked OK. On the Confirm Attribute Changes window, we selected Apply changes to this folder, subfolders and files (see screenshot below).
This method then made sure all the files were no longer compressed and when we restarted the SQL Server services, the database files were successfully attached.
All users were now able to log into Microsoft Dynamics GP. Problem solved.
Note: After we confirmed that the issue with Microsoft Dynamics GP was resolved, we repeated the process to fix the compressed files from the root C:\ folder of the machine as well. We ignored the files that were locked, but this did remove the compression from most of the system.
Hope you find this article helpful.