July 16, 2008 at 7:27 am
Hello All,
I am hoping you can help. I need to find out if it is possible to read the location of the sql logs from the database. I am aware that it is normally saved at a default location, but it also might be that the user who installed it configured it to be different. I would want to write an application that would do a query to the database and get the path to the log files;
Kind Regards.;)
July 16, 2008 at 7:44 am
harriet.louis (7/16/2008)
Hello All,I am hoping you can help. I need to find out if it is possible to read the location of the sql logs from the database. I am aware that it is normally saved at a default location, but it also might be that the user who installed it configured it to be different. I would want to write an application that would do a query to the database and get the path to the log files;
Kind Regards.;)
select physical_name from sys.database_files where type = 1
Regards,
Andras
July 16, 2008 at 8:33 am
Hi,
This is cool, but it is not the one I am looking for. I am looking for
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
Or am I missing something? can the returned *.ldf file be of ue to get this directory:cool:
July 16, 2008 at 8:38 am
Try this (note, credit to Gert Drapers - it was his script initially)
declare @dir nvarchar(4000)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'SQLArg1', @dir output, 'no_output'
if @dir is null
begin
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLDataRoot', @dir output, 'no_output'
select @dir = @dir + N'\LOG'
end
select @dir
July 16, 2008 at 9:53 am
here you go try this, reports database name, logical filename and log file locations for all databases on an instance
select a.name, b.name as 'Logical filename', b.filename from sys.sysdatabases a
inner join sys.sysaltfiles b
on a.dbid = b.dbid where fileid = 2
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 16, 2008 at 10:03 am
This script will give you what you want with SQL Server 7.0, 2000, or 2005. It gets the file information for every database on a server, and gives various levels of analysis of file space usage.
Get Server Database File Information
July 16, 2008 at 2:24 pm
I think you are looking for this:
SELECT SERVERPROPERTY('ErrorLogFileName')
(If you mean the server logs and not the database logs)
Best Regards,
Chris Büttner
July 17, 2008 at 8:50 am
I need the query to determine the mdf file location of a database.
July 17, 2008 at 10:49 am
srbrewster (7/17/2008)
I need the query to determine the mdf file location of a database.
just change fileid to 1 instead of 2 as below
select a.name, b.name as 'Logical filename', b.filename from sys.sysdatabases a
inner join sys.sysaltfiles b
on a.dbid = b.dbid where fileid = 1
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 23, 2008 at 6:49 am
Thanks a lot.
February 23, 2009 at 2:25 pm
This will give you the log and data file paths
:
select * from (select filename, (case status & 0x40 when 0x40 then 'log only' else 'data only' end) as usage from sysfiles ) as data
February 24, 2009 at 6:13 am
try sp_helpdb 'Database Name' as well
December 21, 2009 at 2:08 pm
/***Find Current Location of Data and Log File of All the Database***/
SELECT name, physical_name AS current_file_location
FROM sys.master_files
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply