Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Is there any way to know the source of a LDF file. I have a giant LDF file but I dont know which database its coming from. I couldnt find any database with same name as LDF file. If I try to delete the file it gives me error that another program is using the file.

Please Help.

Cheers.

share|improve this question

2 Answers 2

up vote 2 down vote accepted

Run the following and you should be able to track down the database associated with the file. It will list all files associated with all your databases.

First create a temp table in master:

create table tempsystemfiles (
  fileid smallint,
  groupid smallint,
  size bigint,
  maxsize bigint,
  growth float,
  status int,
  perf int,
  name sysname,
  filename sysname,
  db_name sysname

);

Insert file information into the new table:

EXEC sp_Msforeachdb "use [?];insert master.dbo.tempsystemfiles select *,db_name() from sysfiles"

Then just select it out

select * from master.dbo.tempsystemfiles

Tada.

share|improve this answer
    
I've just edited my post - my original "solution" wasn't quite up to par. Instead of having to go through each database and run TSQL this lets you do it on everything at once. –  Dave Holland Sep 18 '10 at 4:12
    
I just edited it once again to include the database name as part of the list and query, just to make it even easier. –  Dave Holland Sep 18 '10 at 4:16
select db_name(database_id) as DBName, * 
from sys.master_files 
where physical_name = 'C:\path to your file\yourlogfile.ldf'
share|improve this answer
    
This also works - I was trying to give a solution that was overkill I'm sure... but it's what I use for reporting on files/sizes. –  Dave Holland Nov 4 '10 at 5:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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