I just got started using FileTables in SQL Server 2012. I have set everything up on a remote server and got things rolling without any issues. Non-transactional file inserts etc worked absolutely fine.
However, when I attempted to create a new Text-file in the FileTable share directory from Windows Explorer (right-click -> New -> Text Document), it totally froze. Now when I execute queries at the FileTable, the queries just freeze and I get no response. So I figured that my non-transactional insert from the file system had put a lock on the FileTable.
Anyhow, this is what I've tried so far (without success):
Kill ongoing filestream handles (http://msdn.microsoft.com/en-us/library/gg492061.aspx#BasicsKilling) with no results. The executions just freeze.
-- Kill all handles on database
EXEC sp_kill_filestream_non_transacted_handles;
GO
-- Kill handles on filetable
EXEC sp_kill_filestream_non_transacted_handles @table_name = 'dbo.MyFileTable';
GO
-- Kill single handle
EXEC sp_kill_filestream_non_transacted_handles @handle_id = <handle id>;
GO
Take database offline. This results in the error message:
Msg 5061, Level 16, State 1, Line 2
ALTER DATABASE failed because a lock could not be placed on database 'FOO'. Try again later.
Drop database. This results in the error message:
Msg 3702, Level 16, State 4, Line 2
Cannot drop database "FOO" because it is currently in use.
Kill ongoing sessions(?) on my Database:
EXEC sp_who2
KILL <SPID>
As mentioned, I'm unable to interract both Transactionally and Non-transactionally with my FileTable. As you can see, I can't even drop my own Database.
Any suggestions in what may have caused this? Should I be careful using FileTables? Any suggestions on how to solve this?
Solved it by restarting the SQL Server instance. This was the last resort though, since it's a remote instance which is used by my company.
I believe that this problem was caused by the fact that i created a file directly in my FileTable directory. In the documentation (http://msdn.microsoft.com/en-us/library/gg492083.aspx#HowToLoadNew) it is stated the following:
The methods that you can use to load files into a FileTable include the following:
Drag and drop files from the source folders to the new FileTable folder in Windows Explorer.
Use command line options such as MOVE, COPY, XCOPY, or ROBOCOPY from the command prompt or in a batch file or script.
Write a custom application in C# or Visual Basic.NET that uses methods from the System.IO namespace to move or copy the files.
However, it is not stated that creating files may cause problems. Unfortunately, it did in my case. Hopefully this answer can help people with similar issues in the future.