Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I there a way to query SQL Server 2005+ for the file system location of its various log files. By that I mean the text log files, not database transaction log files?

For example my SQL Agent, Error Logs and maintenance plans all write to a folder called:

D:\MSSQLData\MSSQL.1\MSSQL\LOG

enter image description here

It's this folder I need to find the name of.

Updated:

As well as hunting SQL Books I had a poke around the registry under

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer

and

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER` 

but nothing jumps out at me.

I'd also like an "official" way to achieve this and would rather not rely on undocumented features.

share|improve this question

3 Answers 3

up vote 7 down vote accepted
SELECT REPLACE(CAST(SERVERPROPERTY('ErrorLogFileName') AS nvarchar(512)), 'ERRORLOG', '')

EDIT: Cleaned it up a bit with a little more googling to verify; Also to note that this particular argument "ErrorLogFileName" to the SERVERPROPERTY function is undocumented and therefore unsupported by Microsoft. Use at your own risk. etc. etc.

share|improve this answer
    
+1 but I'd love to know an "official" way to do this. –  Kev Feb 12 '12 at 14:35
    
My guess is this is as close as you're going to get to official. If MS ever does officially expose it, this would be how they would do it. And I'd say it's unlikely to be taken away. As unofficial/hack/unsupported things go, this is pretty tame and pretty low risk I'd say :D –  TetonSig Feb 12 '12 at 15:05
    
Nice method. I used to manually check the SQL server config manager, but this query is faster. –  StanleyJohns Feb 12 '12 at 16:34

If you have Powershell this will get it for you. If you have SSMS 2005 installed you can run it as is after replacing "YourServerNameHere". If you're running SSMS 2008 delete the first line and un-comment the second.

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop
#Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop

$srvObj = new-object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList "YourServerNameHere"
$srvObj.ErrorLogPath
share|improve this answer

Having had to analyse one of our SQL Server 2008 background trace logs I noticed that these invariably exist in the same folder as the log files.

So I can use this query to get me close enough:

DECLARE @path varchar(256)
SELECT @path = path
FROM sys.traces
WHERE id = 1

PRINT @path
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.