Explorer not updating after creating directories with SQL Server 2012 FileTable

Question

I am trying to create directories in a SQL Server FileTable via a stored procedure where I call the following code:

INSERT INTO tblMyFiles (name, is_directory, is_archive) 
VALUES ('foldername', 1, 0); 

After calling this code I can see the row sitting in the File Table if I select the rows in SQL Server Management Studio but if I explore to the File Table directory via Windows Explorer I can not see the newly created directory. If I refresh Windows Explorer then the directory still does not show up. If I create a file in the File Table directory in Explorer then it seems to wake Explorer up and then all my directories that were created via the stored procedure appear.

I do not run into this problem when running this on the same SQL box and for a different database and I have compared all the properties and they appear identical but I must be missing something and don't know what? Any help would be much appreciated. Thanks in advance

Answer 1

I've seen this same issue - there seems to be a problem with Explorer caching the network share directory structure indefinitely. I also noticed that it won't refresh until I create a file and then delete it - then magically the cache refreshes. Deleting a file must force a directory cache refresh.

This cache problem seems to be SMB related - I tried both SMB 2 (Windows 7) and SMB 3 (Windows 8) and experienced this same issue. SQL 2012 FileTables are supposed to support SMB 3.

After following this TechNet forum post, disabling the SMB directory cache makes it refresh every time (verified on Win7 and Win8). I'm guessing it could be SQL Server not sending the proper SMB messages or a bug in the SMB client stack. Either way - Microsoft needs to address it since they manage both the client and server design.

Disabling the SMB Directory Cache

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanWorkstation\Parameters] "DirectoryCacheLifetime"=dword:00000000