Unexpected File Id Errors

jrbudnack

I have a database with multiple file groups that I am trying to compress wth SQL Storage Compress v6. I also tried with v5.x, but received the same result. SQL Server is 2008R2 Enterprise Edition 64-bit, installed on Windows Server 2008R2 Standard SP1. The backup was created using RedGate SQL Backup 6.4. It is a compressed and encrypted backup of our Production database.

I see the files being created in the proper directories, but the restore fails with the following error:

SQL error 5161: An unexpected file id was encountered. File id 16 was expected but 4 was read from "<path info removed for post>". Verify that files are mapped correctly in sys.master_files.

The database remains in "Restoring..." mode. When I query sys.master_files, the component files for the database are not listed.

I can do a successful RedGate restore of the backup on this exact same server, I just can't do a SQL Storage Compress restore for whatever reason. I have also tried backing up the database after the restore and tried using the resulting backup file with the same results.

Next, I will try to back up the database without encryption and try restoring it to see if that works. Anyone have any other ideas?

jrbudnack

I tried using a backup with no compression or encryption and received the same results.

The database has 2 files for the transaction log. Every time I try to recover the newly restored and compressed database, I get an OS error indicating an end of file on the 2nd transaction log file. SQL also indicates that it cannot find this file, even though the path is correct in sys.master_files. I tried backing up the database after truncating and shrinking the transaction log, but I receive the same error.

I will next look into logging a support ticket. Any new ideas are welcome.

javen

Are you using the latest version of SSC (5.4), this is available on the website now

jrbudnack

Changing the drive to which the logs were restored seemed to do the trick. The puzzling thing is that there was more than enough space to store these logs twice over on the drive. In addition, the newly compressed logs take up 24MB (not GB, MB), so it does not make sense to me why it works on a much larger partition when it had plenty of room on the smaller one.

Uncompressed, the total log file size for both files comes to 35GB, with 46GB as the total max size. The partition size is 79GB. I wonder: Do you actually inflate to the max size before performing compression? What is the generally accepted method of computing the required disk space to use SQL Storage Compress?