3

I have found a mysterious NDF file in my DB. Can't tell if it contains valid data or if the file is dead.

I'd like to remove it but I cannot tell if the file is in use by SQL Server. Windows Last Modified Date is 2 years ago, but for SQL files that is not reliable because SQL uses it's own drivers to access its files.

How can I tell what the last access time (either to read or to write) is for particular NDF file in a db?

Thanks!

3
  • 2
    What do you mean "in your DB"? Did you find this file on the file system, or is it actively referenced in yourDB.sys.database_files? Apr 13 '16 at 16:26
  • You may be interested in reading this before deleting the file.
    – mustaccio
    Apr 13 '16 at 16:30
  • FYI - there are no special "drivers" used to access the files. All file access is done through Windows APIs like CreateFile, WriteFile, WriteFileGather, ReadFileScatter, etc. the exception to this, of course, is filestream which can utilize a filter driver. Apr 14 '16 at 1:36
1

Another option is to use sysinternals process utilities especially :

make sure you use the filter button to filter out unwanted things :-)

Process Explorer Find out what files, registry keys and other objects processes have open, which DLLs they have loaded, and more. This uniquely powerful utility will even show you who owns each process.

or

Process Monitor Monitor file system, Registry, process, thread and DLL activity in real-time.

1

I'm assuming you have one or more instances on the server where the NDF file is located.

You can tell which if any databases are using that file with the following query:

SELECT DB_NAME(database_id),* FROM sys.master_files
WHERE physical_name = 'MyFileName'

If there are a small number of databases on your server you might be better off running it without the WHERE clause and manually searching for the file name but that depends on your comfort level.

You'll have to run that query on each instance installed on that server. If you don't find it on any of them then it's probably safe to try to delete it.

This won't tell you the last time it was touched but it will tell you what database it belongs to. (if any)

-1

Try deleting the file. If you are able to, SQL is not using it. A file that is part of an attached/online database cannot be deleted.

3
  • 2
    Or renaming might be safer. Apr 13 '16 at 16:24
  • Yes, that does sound safer, but honestly questioning...what could go wrong? Bjorn wants to delete the file if it is not in use.
    – tpet
    Apr 13 '16 at 16:29
  • 5
    Perhaps, but currently he doesn't even know what the file is for. What if he finds out 10 minutes from now that the file was part of an important (but currently detached or offline) database? Just because he wants to delete it does not mean that's the safest method of discovery. This is like dropping an egg on the floor to see if your mop works. Apr 13 '16 at 16:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.