sql query to get sql log file location

Question

sql query to get sql log file location

harriet.louis

Ten Centuries

Points: 1225
More actions
July 16, 2008 at 7:27 am

#210005

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.;)

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

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

harriet.louis Ten Centuries Points: 1225 More actions · Answer 2

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:

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 3

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

Perry Whittle SSC Guru Points: 234040 More actions · Answer 4

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"

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

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

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 6

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

srbrewster SSC Enthusiast Points: 112 More actions · Answer 7

I need the query to determine the mdf file location of a database.

Perry Whittle SSC Guru Points: 234040 More actions · Answer 8

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"

srbrewster SSC Enthusiast Points: 112 More actions · Answer 9

srbrewster

SSC Enthusiast

Points: 112

July 23, 2008 at 6:49 am

#846987

Thanks a lot.

Warren Junker Newbie Points: 9 More actions · Answer 10

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

BudaCli Hall of Fame Points: 3404 More actions · Answer 11

try sp_helpdb 'Database Name' as well

What you don't know won't hurt you but what you know will make you plan to know better

sureshdeenu Right there with Babe Points: 796 More actions · Answer 12

/***Find Current Location of Data and Log File of All the Database***/

SELECT name, physical_name AS current_file_location

FROM sys.master_files

sql query to get sql log file location

Cookies on SQLServerCentral