1

I've been experiencing the following issue.

I have a database attached to a remote sql server. All needed impersonation actions are done - in other words I have all needed access to both file system and sql server.

Let's say I have FileStreanDB1 sql database:

\\server\C$\MSSQL\Data\FileStreamDB1.mdf
\\server\C$\MSSQL\Data\FileStreamDB1_log.ldf
\\server\C$\MSSQL\Data\FileStreamDB1

At some point I would like to drop this database. So I just use the following sql statement (I call this code using c#):

DROP DATABASE [FileStreamDB1]

After that the database is deleted and all files are deleted as well (If I go to that server I don't find them - files and directories are really deleted). But unfortunatelly the following code says to me that \\server\C$\MSSQL\Data\FileStreamDB1 still exists.

new DirectoryInfo(@"\\server\C$\MSSQL\Data\FileStreamDB1").Exists // returns true
Directory.Exists(@"\\server\C$\MSSQL\Data\FileStreamDB1")         // returns true

It looks like that info about the directory cached and I need to clean that cash (SMB2 Directory Cache and I DO NOT WANT TO DISABLE IT)

I've also tried to do that:

new DirectoryInfo(@"\\server\C$\MSSQL\Data\FileStreamDB1").Refresh().Exists // return true

Any ideas how I can clean windows cache about unc paths using c# ?

isxaker
  • 8,446
  • 12
  • 60
  • 87
  • Now, i don't know specific details about how MSSQL handles DB-related files under its control, but how exactly did you look for _FileStreamDB1_ being deleted on your server? It could just have been hidden, thus making it invisible to your eye (and file management tool), while it technically still exists. Did you make sure that whatever tool you used to look for the file does show files with hidden and/or system attributes set? –  May 07 '19 at 13:01
  • 1
    @elgonzo all db files and directories are deleted(i have access to this server i just go to the folder and can't found the db files - before `drop database` I see them and right after `drop database` they are deleted) I guess it's issue connected to access files using `unc` paths – isxaker May 07 '19 at 13:04
  • Do you have the FileStream Feature enabled on the database? – Nik. May 07 '19 at 13:10
  • @NiklasM. yes for sure – isxaker May 07 '19 at 13:11
  • 2
    Try this `Directory.Exists(@"\\server$NOCSC$\C$\MSSQL\Data\FileStreamDB1")`. `$NOCSC$` this will bypass the client side caching. – Matthiee May 07 '19 at 13:11
  • @Matthiee this is genius - exactly what I need. post as answer please ) – isxaker May 07 '19 at 13:20

1 Answers1

2

Problem is that the remote info is cached for a certain amount of time configurable in the registry.

All of the code will first read from the cache, resulting in the File.Exists=true result until the cache is being invalidated.

I found a couple of ways to bypass this cache from code.

Try to access the server unc $NOCSC$, this will bypass the client-side cache. (note: this doesn't work on Windows Server).

Like so: Directory.Exists(@"\\server$NOCSC$\C$\MSSQL\Data\FileStreamDB1").

Also it appears having an FileSystemWatcher looking at the specified folder will bypass caching as well. (Haven't tried this myself so correct me if I'm wrong)

Note: watching the unc path from Windows Explorer also bypasses the cache but only for that window not for any other code running.

Sources:

Matthiee
  • 431
  • 4
  • 14
  • unfortunately `$NOCSC$` is unsupported feature and not all windows systems support this(win server 2016) =( any other ideas ? – isxaker May 09 '19 at 11:46
  • looking for how to clear this cache programmatically using c# – isxaker May 09 '19 at 11:48
  • @isxaker Have you tried the FileSystemWatcher suggestion? Just point it at the directory of the share that you want to bypass the cache. I haven't tried this myself but I came across the suggestion somewhere, let me know if it works. – Matthiee May 09 '19 at 12:10