Rajendra Gupta
SQL Server FILETABLE objects

SQL Server FILETABLE Use Cases

March 28, 2019 by

SQL Server FILETABLE provides benefits over SQL FILESTREAM available from SQL Server 2012. We can manage unstructured objects in the file system using SQL Server. It stores metadata in particular fixed schema tables and columns. It provides compatibility between an object in SQL Server table and Windows.

In my previous article, we explored that you can work with SQL Server FILETABLE directory in a similar way of a regular file or folder. You can do copy-paste, drag-drop files and it inserts data into FILETABLE as well using the Windows API functions and extended functions of SQL Server. It allows updating objects similar to a transaction table. SQL Server also maintains the critical information of files such as file type, extension, create date, modified date, and file properties (read-only, hidden, archive).

In this article, we will explore a few user cases related to SQL FILETABLE.

Prerequisites

  • A SQL Server instance with FILESTREAM feature and configured filestream_access_level
  • A SQL Server database with SQL FILETABLE

SQL FILETABLE User Cases

Scenario 1: Cancel a running transaction for a SQL FILETABLE

Suppose you are trying to insert or update records in SQL Server FILETABLE in a transaction using t-SQL. You need to cancel this transaction while it is still not completed. Usually, in a regular transaction table on SQL Server, if we cancel any query, it goes through a rollback process and UNDO any changes made so far. Once the rollback is finished, it should not contain records that are not committed. If there are many transactions, it might take longer for the recovery to take place.

In SQL FILETABLE transaction, SQL Server maintains transaction consistency as well. If we remove any files from FILETABLE directory, it removes that particular row from SQL Server FILETABLE as well. If we cancel any transaction in SQL FILETABLE, it should remove any inserted objects as part of the rollback process. We should not have record in FILETABLE as well as in the FILETABLE directory.

Let us look in the current environment set up for SQL FILETABLE. We have the following records in existing FILETABLE. In the following query, we selected a few columns from FILETABLE for demonstration purpose.

We have 14 records in SQL FILETABLE including files and folders in FILETABLE directory.

SQL Server FILETABLE objects

In the following screenshot, you can see FILETABLE directory right click on table and click on Explore FILETABLE directory option)

SQL Server FILETABLE directory

Now, execute following query to insert an object into SQL Server FILETABLE. We are using BEGIN TRAN to start a transaction. We are using any commit transaction in this query.

We have not committed this transaction yet. Once we have inserted a record into SQL Server FILETABLE, try to access the records from it in SSMS. It keeps executing the select statement and does not return any result. In the following screenshot, you can see query is running from more than 6 minutes. It does not give any error message as well. It will continue to run forever without any error message.

SQL Server FILETABLE access objects

Execute the following query to check why it is not giving any records. In this 66 is the session id in which we try to access records from FILETABLE.

We have a blocking session, and it shows block by session id 88. We are running an insert statement using session id 88.

sp_who2 output for blocking in SQL Server FILETABLE

We can see newly inserted object into FILETABLE directory.

SQL Server FILETABLE directory

If we try to access this file as well, it does not open. It is an image file. Therefore, OS try to open it in Windows Photo Viewer.

I get following error message that file is being edited in another program.

error in accessing SQL Server FILETABLE

Now, let us cancel or kill SPID for the select statement that was running for long. We can access this FILETABLE using NOLOCK hint in SQL Server. It allows reading and showing uncommitted data as well. It shows the FILETABLE record quickly using NOLOCK hint.

It gives a result for our recently inserted record in SQL FILETABLE with Begin Tran statement.

SQL Server FILETABLE record

We do not want to commit this transaction, therefore, execute command rollback transaction in same transaction window. It performs a rollback of the particular transaction. Let us view records in FILETABLE. It rollbacks the transaction, and we do not see any records in SQL Server FILETABLE for our inserted object. We do not need to use NOLOCK hint because rollback is already finished.

We can verify object in FILETABLE directory as well. SQL Server removes the file as part of the Rollback process. SQL Server FILETABLE objects in directory

SQL Server maintains transactional consistently in SQL FILETABLE as well. However, we need to ensure that no transactions should be left open. We should either Commit or Rollback any transaction; otherwise, it might cause an issue to access FILETABLE due to blocking. It is always beneficial to keep transaction smaller to avoid any issues.

Scenario 2: Update a record in SQL FILETABLE and accessing an object from the directory

Let us look at another scenario for SQL Server FILETABLE. Suppose you are doing an update for an existing object in FILETABLE using SSMS. Execute the following code to update FILETABLE.

We can see update filename in FILETABLE directory, note that we have not committed update query yet.

Updated file in SQL Server FILETABLE directory

Now, try to open this document using Microsoft Word, and you get an error message that file is currently in use.

Error in accessing SQL Server FILETABLE from OS

SQL Server does not allow opening this file using Windows application because it is being locked by the Update transaction.

Let us look at this situation in a different way. Before we move ahead, execute a Rollback Transaction to be a previous state of SQL FILETABLE.

Let us modify the highlighted file. Right-click on the file and click on Edit. It opens the document in Microsoft Word file. Edit SQL Server FILETABLE object

Once the file is open for modification, access the FILETABLE records using NOLOCK hint. We can see a new record in SQL Server FILETABLE. When we open any file in Windows, it creates a hidden file, and you can see the entry for a hidden file in the table.

SQL Server FILETABLE object using NOLOCK hint

Kill Open handles for SQL FILETABLE

Suppose a large number of users used to access FILETABLE directory and make changes to it. You might have faced this situation in regular files in Windows. In this case, you need to check processes causing a lock on a particular file and kill that process manually to release locks.

If you face this situation in SQL FILETABLE, do you still have to go through windows tools and kill the process?

No, SQL Server allows monitoring the open handles associated with each object in SQL Server FILETABLE. We can use dynamic management view sys.dm_filestream_non_transacted_handles. We get one row per open file handle using this DMV. If there is any handle get closed and you rerun DMV to check open handle for FILETABLE, particular entry is removed from the output.

Execute the following code to view current open associated handles in our SQL FILETABLE database.

We get following output from this DMV.

DMV sys.dm_filestream_non_transacted_handles outout for SQL Server FILETABLE

In the following screenshot, you can see all columns in the output of this DMV.

DMV sys.dm_filestream_non_transacted_handles outout for SQL Server FILETABLE

We can see the following important columns in this output.

  • database_id: SQL Server FILETABLE database ID
  • object_id: SQL Server FILETABLE ID
  • handle_id: It is a unique handle ID
  • State: It shows the current state of the handle. You can have an active, closed or killed state
  • opened_file_name: It gives the source file name along with its FILETABLE directory path
  • Database_directory_name: We get FILETABLE directory name
  • Login_name: We get the principal name that is the owner of Open handle
  • read_access: it shows it the file is opened for reading access
  • Write_access: it shows if the file is opened for write access

We have checked open handle associated with SQL FILETABLE. We can close non-transactional file handles to SQL Server FILETABLE using stored procedure sp_kill_filestream_non_transacted_handles. We need to pass FILETABLE name to close all open file handles in SQL Server.

Suppose we want to close all associated open handles, therefore, give the FILETABLE in this stored procedure. Execute the following code in SQL FILETABLE database.

In the output, you can see it killed three non-transactions FILESTREAM handles for SQL Server FILETABLE database.

Kill open handles using  sp_kill_filestream_non_transacted_handles for SQL Server FILETABLE

If we have multiple non-transactional handles for a FILETABLE object, we can close a particular file handle as well. We need to pass handle_id in a stored procedure in the FILETABLE database.

Suppose, we have following open handles, and we want to close a particular file handle id 653.

View curent open handles for SQL Server FILETABLE

Execute the following code, and in the output, you can see it killed one non-transactional handle.

Kill open handles using  sp_kill_filestream_non_transacted_handles for SQL Server FILETABLE

It kills open handle for FILETABLE however we can still see an entry for a hidden file in SQL Server FILETABLE. It does not impact accessing FILETABLE however we can remove this particular row using delete statement.

Left over record after Killing an open handles using  sp_kill_filestream_non_transacted_handles for SQL Server FILETABLE

Conclusion

In this article, we explored user cases associated with SQL FILETABLE. It is an important aspect to know how SQL Server FILETABLE works in case of any transactional failure or open file handles. We will continue covering more on SQL FILETABLE in my next article.

Table of contents

FILESTREAM in SQL Server
Managing data with SQL Server FILESTREAM tables
SQL Server FILESTREAM Database backup overview
Restoring a SQL Server FILESTREAM enabled database
SQL Server FILESTREAM database recovery scenarios
Working with SQL Server FILESTREAM – Adding columns and moving databases
SQL Server FILESTREAM internals overview
Importing SQL Server FILESTREAM data with SSIS packages
SQL Server FILESTREAM queries and Filegroups
Viewing SQL Server FILESTREAM data with SSRS
SQL Server FILESTREAM Database Corruption and Remediation
Export SQL Server FILESTREAM Objects with PowerShell and SSIS
SQL FILESTREAM and SQL Server Full Text search
SQL Server FILESTREAM and Replication
SQL Server FILESTREAM with Change Data Capture
Transaction log backups in a SQL FILESTREAM database
SQL FILESTREAM Compatibility with Database Snapshot, Mirroring, TDE and Log Shipping
SQL Server FILETABLE – the next generation of SQL FILESTREAM
Managing Data in SQL Server FILETABLEs
SQL Server FILETABLE Use Cases

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
624 Views