Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

sp_kill_filestream_non_transacted_handles (Transact-SQL)

SQL Server 2012

Closes non-transactional file handles to FileTable data.

sp_kill_filestream_non_transacted_handles [[ @table_name = ] ‘table_name’, [[ @handle_id = ] @handle_id]]
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.

0 (success) or 1 (failure)

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

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.

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
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.