This article will cover corruption and recovery scenarios in the context of SQL Server FILESTREAM including missing data, incompatible files types, DBCC checks, orphan files and garbage collection

It is the primary responsibility of the DBA is maximum database uptime. Database corruption can be very problematic because you cannot predict data corruption and, therefore, prepare the solution for it proactively. Most of the time database corruption occurs ia due to external reasons such as power failure, storage corruption etc. With FILESTREAM, we are dealing with SQL Server database files (MDF, LDF) along with file system objects as well. Therefore, we need to be aware of the possible scenarios that may occur in the FILESTREAM database wich can cause database ccorruption. In this article, my focus is to highlight such common scenarios specific for the FILESTREAM database and solutions for it.

Before we move further, I will assume that you have a FILESTREAM database and the objects in the FILESTREAM container. You can take reference from the articles in the TOC section (see bottom) if you need to build your example environment to follow along

Checking for corruption

Below is the screenshot for our example FILESTREAM database table.

There are 12 files in the FILESTREAM container.

We can use the DBCC CHECKDB command to detect any corruption or consistency issues. It can check the physical and logical consistency error on the database. Let’s run this command, and in the following screenshot, you can notice that we do not have any consistency issues for the FILESTREAM database.

“CHECKDB found 0 allocation errors and 0 consistency errors in database ‘FileStreamDemoDB_test’.”

Missing or deleted files from a FILESTREAM container

Let’s view some FILESTREAM database corruption scenarios.

Note: We might perform steps to deliberately corrupt the FILESTREAM database, you should not run these examples in the production database.

We should not modify or delete any files in the FILESTREAM container directly at the OS level. If we want to do the changes or delete the object, we should do it from the query. SQL Server removes the physical file once the garbage collector process runs depending upon the recovery model and the transaction log backups.

Let us remove the highlighted file manually from the FILESTREAM container.

We are not going to delete this file. Instead, we will move this file to a backup folder so that it can be used later.

Let us try to access the database. We cannot access it now, below is the error message.

We cannot access the database now, let us try to run the DBCC CHECKDB on this database and observe the output.

DBCC CHECKDB reported consistency error in table ‘Tbl_Support_Document. It also gives a message that cannot find the FILESTREAM file. You can see in the following screenshot that the error is for the same file we moved from the FILESTREAM container earlier.

Recovering the corrupt FILESTREAM database

We can recover this FILESTREAM database with consistency error in the following ways

1. Recover the deleted object from the previous backup

   If we are taking regular backups, we can restore the FILESTREAM database in another location and replace the problematic file. In this example, let us consider that you found the file by restoring the FILESTREAM database backup so that we will copy that file into FILESTREAM container.

   

   We have all the files in the FILESTREAM container let us perform the DBCC CHECKDB again. We do not get any consistency error in the FILESTREAM database.

   

2. Recover the FILESTREAM database without a backup of the removed object

   In the previous step, we recovered the database by replacing the problematic file in the FILESTREAM container. Suppose we do not have any backup for the FILESTREAM database or the file is not present in the backup as well. We can still recover the FILESTREAM database by running DBCC CHECKDB with repair_allow_data_loss. It will delete the corresponding row from the FILESTREAM table.

   We already recovered the database in the previous step, therefore, remove the same file again from the FILESTREAM container manually.

   We need to put the database in the single user mode using the Alter Database command.

   1 2	ALTER DATABASE FileStreamDemoDB_test SET SINGLE_USER  WITH ROLLBACK IMMEDIATE   GO

   This command kills all of the existing connections in the database and put the database into an existing mode.

   

   In the single user mode, only one connection can be active at a time. Therefore, we need to run a further command in the same connection window. If you try to open another connection, you get the below error message.

   

   Now, run the below command in a previous connection window to repair the database using the DBCC CHECKDB. You get the following output of this command execution.

   1	DBCC CHECKDB('FileStreamDemoDB_test', repair_allow_data_loss)

   

   Let us understand this output message. In first part, it deletes the row from the FILESTREAM table for the deleted file.

   In the second step, it successfully rebuilds the non-clustered index as well.

   

   In the bottom part of the message, you can see that the database repaired the consistency error successfully.

   

   Let us put the database in the MULTI_USER using the below command.

   1 2	ALTER DATABASE FileStreamDemoDB_test SET MULTI_USER   GO

   We can access the database now. Previously we were having 12 rows while the number of rows in the FILESTREAM table is 11, it shows that row is deleted by the DBCC CHECKDB command.

   

Orphan files in a FILESTREAM container

We should not use the FILESTREAM container for storing files other than the FILESTREAM objects. In the following image, you can see the file in the container that is not part of the FILESTREAM data.

Now we can run the DBCC CHECKDB in the FILESTREAM database, and we get the error message as following.

We get the consistency error again in the FILESTREAM database, and it gives the message that the file ‘Rajendra.PNG’ is not a valid FILESTREAM file in the container.

FILESTREAM container files do not have any extension. It also has the name in the form of metadata. Now let us rename the existing file ‘Rajendra.PNG’ in the FILESTREAM format. I renamed the file to the highlighted name ‘0000002d-00000368-000c’. We can note here that it does not have any extension with its name.

Let us rerun the DBCC CHECKDB command and observe the error message. It again complains about the consistency error message however, there is a difference in the error message.

• Previous error message: Table error: The file “\fea3feda-924f-4aee-b320-e33669d27d18\rajendra.png” in the rowset directory ID 0b05ad18-6dc6-4391-b644-413b27535e16 is not a valid FILESTREAM file in container ID 65537.
• Current error message: The orphaned file “0000002d-00000368-000c” was found in the FILESTREAM directory ID fea3feda-924f-4aee-b320-e33669d27d18
  Container ID 65537 for object ID 1237579447, index ID 0, partition ID 72057594043695104, column ID 4.

We can fix this error message by removing the file from the FILESTREAM container. We cannot fix it by running the DBCC CHECKDB with repair_allow_data_loss.

However, let us try it. In the following image, we get the same error message. It does not fix the error.

Therefore, we need to remove the unwanted file from the FILESTREAM container and execute the DBCC CHECKDB again. We did not get any consistency error message after removing the file manually from the FILESTREAM container.

Removing the Garbage files from the SQL Server FILESTREAM container

We can do DML (insert and update) activity in the FILESTREAM table. Consider we have performed an update on the FILESTREAM table, SQL Server does not remove the old file from the container immediately. SQL Server removes these files once the garbage collector process runs and it removes the files if it is not required in the recovery process.

In the following query, we replaced one image in the existing FILESTREAM table. The old image file becomes a garbage file.

In the following image, the highlighted file is the garbage file.

Let us remove this garbage file manually and run the DBCC CHECKDB command. We do not receive any consistency error message despite by removing the garbage file manually.

We can access the FILESTREAM database without any issues however, I would recommend not doing any changes in the FILESTREAM container directly at the OS level. It might cause serious corruption issues for the database that might impact the database availability.

Conclusion:

In this article, we wrote about the various consistency issues for the SQL Server FILESTREAM and the solution of them to fix various FILESTREAM related corruption issues.

Table of contents

• Author
• Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

• Understanding PostgreSQL SUBSTRING function - September 21, 2024
• How to install PostgreSQL on Ubuntu - July 13, 2023
• How to use the CROSSTAB function in PostgreSQL - February 17, 2023