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.
|
1 2 3 4 5 6 7 |
SELECT [name] ,[file_type] ,[cached_file_size] ,[creation_time] ,[last_write_time] ,[last_access_time] FROM [SQLFileTable].[dbo].[SQLShackDemoDocuments] |
We have 14 records in SQL FILETABLE including files and folders in FILETABLE directory.
In the following screenshot, you can see FILETABLE directory right click on table and click on Explore FILETABLE directory option)
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.
|
1 2 3 4 5 6 7 8 |
BEGIN TRAN INSERT INTO [dbo].[SQLShackDemoDocuments] ([name],[file_stream]) SELECT 'DSC_0312.JPG', * FROM OPENROWSET(BULK N'c:\SQLShack\DSC_0312.JPG', SINGLE_BLOB) AS FileData GO |
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.
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.
|
1 |
sp_who2 66 |
We have a blocking session, and it shows block by session id 88. We are running an insert statement using session id 88.
We can see newly inserted object into 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.
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.
|
1 2 3 4 5 6 7 8 |
SELECT [name] ,[file_type] ,[cached_file_size] ,[creation_time] ,[last_write_time] ,[last_access_time] FROM [SQLFileTable].[dbo].[SQLShackDemoDocuments] (nolock) where name= ‘DSC_0312.JPG' |
It gives a result for our recently inserted record in SQL FILETABLE with Begin Tran statement.
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 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.
|
1 2 |
BEGIN TRAN UPDATE [SQLFileTable].[dbo].[SQLShackDemoDocuments] SET name='UpdateFILETABLE_new.docx' where name='UpdateFILETABLE.docx' |
We can see update filename in FILETABLE directory, note that we have not committed update query yet.
Now, try to open this document using Microsoft Word, and you get an error message that file is currently in use.
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.
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.
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.
|
1 2 3 4 5 |
SELECT * FROM sys.dm_filestream_non_transacted_handles WHERE fcb_id IN ( SELECT request_owner_id FROM sys.dm_tran_locks ); GO |
We get following output from this DMV.
|
1 2 |
SELECT * FROM sys.dm_filestream_non_transacted_handles; GO |
In the following screenshot, you can see all columns in the output of this DMV.
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.
|
1 2 |
EXEC sp_kill_filestream_non_transacted_handles @table_name = 'SQLShackDemoDocuments'; GO |
In the output, you can see it killed three non-transactions FILESTREAM handles for SQL Server FILETABLE database.
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.
Execute the following code, and in the output, you can see it killed one non-transactional handle.
|
1 2 |
EXEC sp_kill_filestream_non_transacted_handles @handle_id = 653; GO |
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.
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
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
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023