SQL Server LDF file

Question

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.

Answer 1

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.

Answer 2

select db_name(database_id) as DBName, * 
from sys.master_files 
where physical_name = 'C:\path to your file\yourlogfile.ldf'