question

srivivek avatar image
srivivek asked

Sql Root Directory and error log location

Hi, Can anyone help me in finding the SQL server Root directory and error log location using a sql script for multi-instances, cluster and non cluster servers.
sqlerrorlogdirectoryroot
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image
Pavel Pawlowski answered
SQL script will not be the best candidate for this. I suggest you take a look for a PowerShell script. As the installation directory is stored in the registry and the log location is part of the startup parameters of the SQL Server service. Anyway you can take a look here for the inspiration of the t-sql solution to retrieve installation directory: [How to find the SQL Server install directory from tsql][1] [1]: http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/04cc91e9-7dc3-462f-a2b7-23f00eaa863f/
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Shawn_Melton avatar image
Shawn_Melton answered
I was playing around with some stuff on a cluster and using PowerShell you can actually use this to get your information (if you have PowerShell running on the servers of course). You will also need SMO loaded, which this is already there if SSMS is installed on the host machine. [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Management.SMO') |
out-null
$s = New-Object 'Microsoft.SqlServer.Management.Smo.Server' instance_name
$s.Information.RootDirectory | Write-Host -foreground Red
$s.Information.ErrorLogPath | Write-Host -foreground Red
$s.Information.MasterDBPath | Write-Host -foreground Red
$s.Information.MasterDBLogPath | Write-Host -foreground Red
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.