Clever Geek Handbook

Create File in Windows Explorer Lock FileTable

I just started using FileTables in SQL Server 2012. I configured everything on a remote server and got everything going without any problem. Non-transactional file inserts, etc. They worked absolutely fine.

However, when I tried to create a new text file in the FileTable shared folder in Windows Explorer (right click -> New -> Text Document) it froze completely. Now when I execute requests on the FileTable, the requests just freeze and I get no response. So I figured that my non-transactional insert from the filesystem had placed the lock on the FileTable.

Anyway, this is what I have tried so far (no success):

  • Kill the current thread handles ( http://msdn.microsoft.com/en-us/library/gg492061.aspx#BasicsKilling ) with no results. The executions are simply frozen.

    -- 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 the database offline. As a result, an error message appears:

    Msg 5061, Level 16, State 1, Line 2
ALTER DATABASE failed because a lock could not be placed on database 'FOO'. Try again later.

  • Database folder. As a result, an error message appears:

    Msg 3702, Level 16, State 4, Line 2
Cannot drop database "FOO" because it is currently in use.

  • Kill the current sessions (?) In my database:

    EXEC sp_who2
KILL <SPID>

As mentioned, I cannot change the transaction and not the transaction using the FileTable. As you can see, I can't even leave my own database.

Any suggestions on what could have caused this? Should I be careful about using FileTables? Any suggestions on how to solve this?

+3


source to share


1 answer


Decided it to restart the SQL Server instance. It was a last resort though, as this is a remote instance that my company uses.

I believe this problem was caused by the fact that I created the file directly in my FileTable directory. The documentation ( http://msdn.microsoft.com/en-us/library/gg492083.aspx#HowToLoadNew ) states the following:



The methods you can use to upload files to the FileTable include the following:

  • Drag the files from the original folders to the new FileTable folder in Windows Explorer.

  • Use command line options such as MOVE, COPY, XCOPY or ROBOCOPY from the command line or in a file or script package.

  • Write your own C # or Visual Basic.NET application that uses methods from the System.IO namespace to move or copy files.

However, it is not stated that creating files can cause problems. Unfortunately this happened in my case. Hope this answer can help people with similar problems in the future.

+1


source






More articles:


All Articles