sp_kill_filestream_non_transacted_handles (Transact-SQL)

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Closes non-transactional file handles to FileTable data.


sp_kill_filestream_non_transacted_handles [[ @table_name = ] ‘table_name’, [[ @handle_id = ] @handle_id]]  


The name of the table in which to close non-transactional handles.

You can pass table_name without handle_id to close all open non-transactional handles for the FileTable.

You can pass NULL for the value of table_name to close all open non-transactional handles for all FileTables in the current database. NULL is the default value.

The optional ID of the individual handle to be closed. You can get the handle_id from the sys.dm_filestream_non_transacted_handles (Transact-SQL) dynamic management view. Each ID is unique in a SQL Server instance. If you specify handle_id, then you also have to provide a value for table_name.

You can pass NULL for the value of handle_id to close all open non-transactional handles for the FileTable specified by table_name. NULL is the default value.

Return Code Value

0 (success) or 1 (failure)

Result Set


General Remarks

The handle_id required by sp_kill_filestream_non_transacted_handles is not related to the session_id or unit of work that is used in other kill commands.

For more information, see Manage FileTables.


For information about open non-transactional file handles, query the dynamic management view sys.dm_filestream_non_transacted_handles (Transact-SQL).



You must have VIEW DATABASE STATE permission to get file handles from the sys.dm_FILESTREAM_non_transacted_handles dynamic management view and to run sp_kill_filestream_non_transacted_handles.


The following examples show how to call sp_kill_filestream_non_transacted_handles to close non-transactional file handles for FileTable data.

-- Close all open handles in the current database.  

-- Close all open handles in myFileTable.  
sp_kill_filestream_non_transacted_handles @table_name = ’myFileTable’  

-- Close a specific handle in myFileTable.  
sp_kill_filestream_non_transacted_handles @table_name = ’myFileTable’, @handle_id = 0xFFFAAADD  

The following example shows how to use a script to get a handle_id and close it.

DECLARE @handle_id varbinary(16);  
DECLARE @table_name sysname;  

SELECT TOP 1 @handle_id = handle_id, @table_name = Object_name(table_id)  
FROM sys.dm_FILESTREAM_non_transacted_handles;  

EXEC sp_kill_filestream_non_transacted_handles @dbname, @table_name, @handle_id;  

See Also

Manage FileTables
Filestream and FileTable Dynamic Management Views (Transact-SQL)
Filestream and FileTable Catalog Views (Transact-SQL)
sp_filestream_force_garbage_collection (Transact-SQL)