1

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.

  • Try to move them. If they move, SQL isn't using them. – Erik Darling Mar 27 '18 at 19:17
  • Are they in a directory that is allocated to a specific instance on the machine? Is there more than one instance on the machine? You could use Powershell to build a list of filenames and check against master.sys.master_files. – Aaron Bertrand Mar 27 '18 at 19:17
  • @sp_BlitzErik That and renaming will work with a database that is offline. – Aaron Bertrand Mar 27 '18 at 19:17
  • @AaronBertrand OP didn't mention having any offline databases, but sure. – Erik Darling Mar 27 '18 at 19:20
  • @sp_BlitzErik Well, if he's trying to find out if a database is referenced by an instance, I think you have to consider both online and offline. If you take a move as "success" and then delete the file, then later try to bring it back online... – Aaron Bertrand Mar 27 '18 at 19:26
2

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.

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 ║
╚══════════════╩═══════════════╩══════════════════╩═══════════╩═══════════════════════════════════════════════╝
  • 1
    But like my comment you should also consider that the databases could be offline, or that the SQL Server instance isn't currently running. – Aaron Bertrand Mar 27 '18 at 19:40
  • That comment reminds me of that guy a while ago who wanted to have 10's of thousands of databases running in SQL Server Express via some automated detach-and-attach mechanism. – Max Vernon Mar 27 '18 at 19:44
0

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).

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.