How to Handle SQL Server Error 7904

Problem:

The SQL Server error 7904 occurs With SQL Server 2008, a new database storage system was introduced that is termed as FILESTREAM. This allows saving the structured data into the DB and along with it, the unstructured database(the images, videos, audios etc.) will directly get saved into the NTFS file system. In this case, the actual data is stored outside the NTFS file system, but still the T-SQL statements can be used to manage the unstructured database (like INSERT, UPDATE etc.). And the good part is, the transactional consistency between both type of databases (structured and unstructured) will be maintained. The FILESTREAM feature is not enabled by default. It has to be enabled at Windows and SQL Server level.

Situation 1:

Error: 7904, Severity: 16, Table error: Cannot find the FILESTREAM file “%.*ls” for column ID %d (column directory ID %.*ls container ID %d) in object ID %d, index ID %d, partition ID %I64d, page ID %S_PGID, slot ID %d.

Reason:

The error is encountered because the column value of partition cannot find a FILESTREAM file that corresponds to the FILESTREAM column directory.

Solutions:

Check Out for Hardware Failure

The very first step to be taken when the error 7904 is encountered is to run hardware diagnostics and find out if there is any problem with the hardware where server is working. In addition to this, check out the application and SQL Server log that will inform if the error has occurred as a result of hardware failure. If any of the issue is caught through the log, fix it.

In case the problem occurs frequently, then it is suggested to go for hardware swapping. Take care of the fact that the system does not have the requirement for enabling the write-caching for the disk controller. It is important that you contact the hardware vendor if write-caching is considered a problem. However, in the entire process, remember that hardware replacement calls for disk reformatting and re-installation of the Operating System.

(1) Restore Database from Backup

If the problem is not linked with the hardware, then the database can be restored from the last updated backup plan. Backup is the last resort to deal with the error because this error cannot be fixed by running the DBCC CHECKDB repair command.

Situation 2:

Filestream corruption – missing files, SQL Error 7904.

Reason:

The error generally occurs in a race condition where in some of the transaction log files are skipped from the backup process. At the time, when the file is an active transaction, a series of number is allocated to the file. It is recorded in create LSN column is created. The log sequence number that is recorded in the create LSN column is always lesser in number than the last LSN column. The FSLOG entry will not be marked in the $FSLOG folder considering the file to be skipped by the in process transaction log. The backup processed later will not backup the skipped files and thus the interrupted chain will result in SQL error 7904.

Recommended Actions:

In this case, the database is fine, but there is a problem with the transaction log while backup restoration. Restoring database without transactions is manually not possible and thus the only idea is to bank upon SQL database repair tool
to remove SQL Server Error 7904. All you need to do is add the MDF files (in any state) and the database will be restored efficiently.