Link to home
Start Free TrialLog in
Avatar of IntercareSupport
IntercareSupport

asked on

Discover which DB .ldf belongs to?

I have a database log file that is pretty large, but I can't see what database owns it.  I've looked at all the existing databases, and none of them seem to use it.  I can't rename it, either because it's locked.  Anyone know a good way to discover which DB this .ldf belongs to?  Thanks.
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

check this script results:
exec sp_msforeachdb 'select * from ?.sys.files '

Open in new window

Avatar of IntercareSupport
IntercareSupport

ASKER

I get this:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'master.sys.files'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'tempdb.sys.files'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'model.sys.files'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'msdb.sys.files'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'CM.sys.files'.

...And so on.  I tried some different variations of the sp, but nothing.
is this then sql 2000, actually?
exec sp_msforeachdb 'select * from ?.dbo.sysfiles '

Open in new window

Oh sorry.  Yes and no.  It's 2005 that has is running in 2000 mode because of the application that uses it.
>>I have a database log file that is pretty large, but I can't see what database owns it. <<
It should be pretty easy to find out which one:  It is the Transaction Log you have failed to backup, that is why it is so big.
Well, I've looked at all the databases and no luck.  Everything looks like it backed up.  Help.  I thought the first idea was on to something.
maybe you have another instance of sql server running?
Nope.  I just double checked.
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ok, that was a brilliantly simple solution.  And guess what?  Nothing is locking that file anymore.  That is very annoying.  There was just a big fuss over disk space here and I got sloppy.  Thanks!
Now that you know which database it belongs to, make sure you start doing regular backups on the Transaction Log or it will continue to grow until you run out of space and then you will have to post another question here.