Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

sql query to get sql log file location Expand / Collapse
Author
Message
Posted Wednesday, July 16, 2008 7:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 12, 2010 8:36 AM
Points: 26, Visits: 98
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.;)
Post #535179
Posted Wednesday, July 16, 2008 7:44 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 26, 2012 5:26 AM
Points: 1,367, Visits: 1,585
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




Andras Belokosztolszki, MCPD, PhD
GoldenGate Software
Post #535191
Posted Wednesday, July 16, 2008 8:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 12, 2010 8:36 AM
Points: 26, Visits: 98
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
Post #535254
Posted Wednesday, July 16, 2008 8:38 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 7:22 AM
Points: 1,327, Visits: 4,506
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



Post #535265
Posted Wednesday, July 16, 2008 9:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:57 AM
Points: 6,304, Visits: 13,601
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"
Post #535359
Posted Wednesday, July 16, 2008 10:03 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 1:10 PM
Points: 3,135, Visits: 11,480
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
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058
Post #535367
Posted Wednesday, July 16, 2008 2:24 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:47 AM
Points: 2,840, Visits: 3,872
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
Post #535573
Posted Thursday, July 17, 2008 8:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 23, 2008 6:48 AM
Points: 2, Visits: 2
I need the query to determine the mdf file location of a database.
Post #536067
Posted Thursday, July 17, 2008 10:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:57 AM
Points: 6,304, Visits: 13,601
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"
Post #536158
Posted Wednesday, July 23, 2008 6:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 23, 2008 6:48 AM
Points: 2, Visits: 2
Thanks a lot.
Post #539206
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse