How to check whether old MDF and LDF files are referenced

Question

I inherited a setup, and I'm rather new with DB work. I've got a drive on my production server that was created during a big crisis a few years ago at my company. Crisis was navigated and everything restored. But this drive on my has 70 LDF and MDF files sitting in it from that recovery. Is there a quick and easy way to determine if any of these things are being referenced or used? I just don't know what the last DBA here might have used them for afterward.

The Date Modified dates are all from the crisis period, early 2016. Nothing since.

Thanks.

Answer 1

Use this: Find-DbaOrphanedFile

Its a PowerShell command, but it sure beats testing a ton of files manually.

This command searches all directories associated with SQL database files for database files that are not currently in use by the SQL Server instance.

By default, it looks for orphaned .mdf, .ldf and .ndf files in the root\data directory, the default data path, the default log path, the system paths and any directory in use by any attached directory.

You can specify additional filetypes using the -FileType parameter, and additional paths to search using the -Path parameter.

Answer 2

Use SysInternals' Handle utility to view open handles to the file. That will tell you succinctly and definitely if any process running on the machine in question has those files open.

As an example, on my dev machine, if I run this in a command-prompt:

C:\Users\xxxx\Downloads\Handle_> handle D:\SQLServer\MV2012\Data\CharSizeTest.mdf

I see this output:

Handle v3.51
Copyright (C) 1997-2013 Mark Russinovich
Sysinternals - www.sysinternals.com

sqlservr.exe       pid: 4028   type: File      B2C: D:\SQLServer\MV2012\Data\CharSizeTest.mdf

In the above, you can see sqlserver.exe has an open handle to the .mdf

Notice, this doesn't guarantee you can delete the file; it will simply show you if anything has the file in question open.

Prior to deleting anything, I take a backup of the file in question so that I can recover them if needed. I scheduled that backup for deletion in, say, 6 months.

You can check for databases or individual files that are offline via the SQL Server instance, using this query:

SELECT  DatabaseName = d.name
    , DatabaseState = d.state_desc
    , FileName = mf.name
    , FileState = mf.state_desc
    , FilePath = mf.physical_name
FROM sys.master_files mf 
    INNER JOIN sys.databases d ON mf.database_id = d.database_id
WHERE mf.state_desc <> 'ONLINE'
    OR d.state_desc <> 'ONLINE'
ORDER BY d.name
    , mf.name;

For an offline database, the output looks like:

╔══════════════╦═══════════════╦══════════════════╦═══════════╦═══════════════════════════════════════════════╗
║ DatabaseName ║ DatabaseState ║     FileName     ║ FileState ║                   FilePath                    ║
╠══════════════╬═══════════════╬══════════════════╬═══════════╬═══════════════════════════════════════════════╣
║ CharSizeTest ║ OFFLINE       ║ CharSizeTest     ║ ONLINE    ║ D:\SQLServer\MV2012\Data\CharSizeTest.mdf     ║
║ CharSizeTest ║ OFFLINE       ║ CharSizeTest_log ║ ONLINE    ║ D:\SQLServer\MV2012\Logs\CharSizeTest_log.ldf ║
╚══════════════╩═══════════════╩══════════════════╩═══════════╩═══════════════════════════════════════════════╝

Answer 3

Run a query like this on SQL:

SELECT database_id, DB_NAME(database_id) AS DBName,
name AS LogicalName,
physical_name, convert(numeric(10,1),(size/128.0/1024.0)) SizeGB
FROM master.sys.master_files

This should return the path of all files in use by the SQL instance.

Anything not on that list is not currently in use (by that instance).