CODEWITHSUNDEEP
sql-server-2008t-sql
Franklin
Franklin

Reputation: 905

Find Unused mdf and ldf file locations

We can find mdf and ldf file locations either by using sp_helpdb or using the below command.

SELECT * FROM sys.master_files

But how can we find ldf and mdf files which are not being used at present i.e Let say you restored one database with new mdf and ldf files and not deleted old mdf and ldf files .

Upvotes: 0

Views: 714

Answers (2)

Pintu Kawar
Pintu Kawar

Reputation: 2156

You can try the below way:

IF OBJECT_ID(N'tempdb.dbo.#AllMDFfiles', N'U') IS NOT NULL
BEGIN   DROP TABLE #AllMDFfiles END
CREATE TABLE #AllMDFfiles (mdfFileName VARCHAR(max))

-- Will return all the .mdf and .ldf in C:\ drive, you can change it in ... /d C:\ && dir /b....
INSERT INTO #AllMDFfiles EXEC xp_cmdshell 'cmd /c "cd /d C:\ && dir /b /s | sort | findstr /c:".mdf" /c:".ldf""'

--Will return the mdfs and ldf paths which are not in sys.master_files
select mdfFileName from #AllMDFfiles
Where mdfFileName IS NOT NULL

EXCEPT

SELECT physical_name FROM sys.master_files

Upvotes: 1

Neo
Neo

Reputation: 3399

There isn't a straight forward easy way. You can manually do this by searching the server or there is a more complex way spelled out here:

Finding un-used data files

Upvotes: 1

Related Questions