ansaurus

Question

Physical Database and Log File location

Answer 1

+1 A:

SELECT * FROM sys.database_files

Will A 2010-08-16 23:38:49

Doesnt show where the log file is when I run this, only the filename path of the mdf file?

Conor 2010-08-16 23:44:15

Opps you are correct I was querying the sysdatabases, sorry!!Thanks

Conor 2010-08-16 23:45:38

Interesting - this includes log files for me, and the docs (http://msdn.microsoft.com/en-us/library/ms174397.aspx) don't imply otherwise.

Will A 2010-08-16 23:45:45

No problem - enjoy the system views!

Will A 2010-08-16 23:46:01

Answer 2

+1 A:

Here is another way

select * from sys.sysaltfiles

SQLMenace 2010-08-16 23:45:06

cool thanks very much!!

Conor 2010-08-16 23:48:44

+1 - but watch out for this disappearing in a future version, sys.master_files is the recommended alternative to this view.

Will A 2010-08-16 23:51:45

Answer 3

+2 A:

You should be using sp_helpdb or sp_helpfile for this.

e.g:

sp_helpdb 'master'

JimG 2010-08-16 23:47:52

Why? Admittedly this provides some additional, potentially useful, information - but there's nothing wrong with querying the system views.

Will A 2010-08-16 23:50:00

sp_helpdb reports wrong info against a snapshot, see here http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/MSSQLServerAdmin/running-sp_helpdb-against-a-snapshot-sho-2008

SQLMenace 2010-08-16 23:51:00

Nothing wrong, until they move stuff around... recently saw a problem where a query broke due to differences in 2000 and 2005. Using the procedures would have avoided that problem. Interesting about the snapshot breakage though.

JimG 2010-08-16 23:58:47

ansaurus

tags:

views:

answers:

Physical Database and Log File location

related questions