sp_kill_filestream_non_transacted_handles (Transact-SQL)

Closes non-transactional file handles to FileTable data.

Syntax

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

Arguments

table_name
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.
handle_id
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

None.

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.

Metadata

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

Security

Permissions

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.

Examples

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.
sp_kill_filestream_non_transacted_handles

-- 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;
GO

Dela via