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.
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.
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 '
ASKER
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.
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.
ASKER
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?
ASKER
Nope. I just double checked.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Open in new window