Find SQL Server Error Log Path

,

This script helps out to query the SQL Server Errorlog path using T-SQL. This script will work on both SQL 2000/2005.

 

Note : if there exists problem copying the code directly to Query Analyzer or SSMS. Please copy it to wordpad/Word, from there copy to your query window.

--SQL 2005/2000 Version
set nocount on
go



DECLARE @SQLLogPath varchar(500)
DECLARE @SQLPath varchar(500)
DECLARE @svr_name varchar(100)
DECLARE @instance_name varchar(20)
DECLARE @reg_key varchar(500)
DECLARE @SQlVersion varchar(500)
Declare @filepath varchar(1000)
Declare @fileavailable varchar(20)
Declare @filestatus int

if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '9'
BEGIN
 --SQL 2005
 
select  @svr_name = CAST(SERVERPROPERTY('ServerName')AS sysname)
select  @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)
set @SQlVersion = (select @@version)
set @SQlVersion = left(@SQlVersion,40)

if @instance_name is NOT NULL
BEGIN
 set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\Sql'


EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', 
@key=@reg_key, @value_name=@instance_name,
@value=@SQLPath output

END

if @instance_name is NULL
BEGIN
 set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\Parameters'
END
ELSE BEGIN
 set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @SQLPath + '\MSSQLServer\Parameters'
END


EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', 
@key=@reg_key, @value_name='SQLArg0',
@value=@SQLLogPath output

if left(@SQLLogPath,2) <> '-e' 
BEGIN
 EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', 
 @key=@reg_key, @value_name='SQLArg1',
 @value=@SQLLogPath output
END

if left(@SQLLogPath,2) <> '-e' 
BEGIN
 EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', 
 @key=@reg_key, @value_name='SQLArg2',
 @value=@SQLLogPath output
END


select @filepath = ltrim(Rtrim(substring(@SQLLogPath,3,len(@SQLLogPath))))

 EXEC master..xp_fileexist @filepath , @filestatus out
 if @filestatus = 1
 set @fileavailable = 'Available'
 else
 set @fileavailable = 'NOT Available'

select @svr_name as ServerName,@filepath as ErrorLogPath,@fileavailable as ErrorLogAvailability,@SQlVersion as SQlServerVersion


END

if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '8'
BEGIN
 --SQL 2000
 
select  @svr_name = CAST(SERVERPROPERTY('ServerName')AS sysname)
select  @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)
set @SQlVersion = (select @@version)
set @SQlVersion = left(@SQlVersion,38)
if @instance_name is NULL
BEGIN
 set @reg_key = 'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters'
END
ELSE BEGIN
 set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer\Parameters'
END

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', 
@key=@reg_key, @value_name='SQLArg0',
@value=@SQLLogPath output

if left(@SQLLogPath,2) <> '-e' 
BEGIN
 EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', 
 @key=@reg_key, @value_name='SQLArg1',
 @value=@SQLLogPath output
END

if left(@SQLLogPath,2) <> '-e' 
BEGIN
 EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', 
 @key=@reg_key, @value_name='SQLArg2',
 @value=@SQLLogPath output
END


select @filepath = ltrim(Rtrim(substring(@SQLLogPath,3,len(@SQLLogPath))))
 EXEC master..xp_fileexist @filepath , @filestatus out
 if @filestatus = 1
 set @fileavailable = 'Available'
 else
 set @fileavailable = 'NOT Available'

select @svr_name as ServerName,@filepath as ErrorLogPath,@fileavailable as ErrorLogAvailability,@SQlVersion as SQLServerVersion

END

Rate

5 (2)

Share

Share

Rate

5 (2)