Sql-server – How to check whether old MDF and LDF files are referenced

filessql server

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.

Best Answer

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 ║
╚══════════════╩═══════════════╩══════════════════╩═══════════╩═══════════════════════════════════════════════╝
Related Question