My favorite way of getting the path of the Errorlog diretory is by reading the Errorlog, while it contains a entry in the beginning about the path of the directory like
Logging SQL Server messages in file 'E:\MSSQL\MSSQL10_50.SANDY\MSSQL\Log\ERRORLOG'.
Using a (undocumented) stored procedure, I get the path by the script
DECLARE @_errorlog TABLE (
LogDate DATETIME
,ProcessInfo NVARCHAR(MAX)
,ErrorText NVARCHAR(MAX));
INSERT INTO @_errorlog ([LogDate], [ProcessInfo], [ErrorText])
EXECUTE [master].[dbo].[sp_readerrorlog]
@p1 = 0 -- 0 = current errorlog
,@p2 = 1 -- 1 = SQL Server Errorlog
,@p3 = N'Logging SQL Server messages in file ';
DECLARE @errorlog_directory NVARCHAR(256);
SELECT @errorlog_directory = REPLACE(REPLACE([ErrorText],N'Logging SQL Server messages in file ''',''),N'\ERRORLOG''.','')
FROM @_errorlog;
DELETE @_errorlog;
SELECT @errorlog_directory AS N'errorlog_directory';
The result is like
E:\MSSQL\MSSQL10_50.SANDY\MSSQL\Log
The (undocumented) procedure "sp_readerrorlog" takes up to four parameters
@p1
:Generation by integer value of the Errorlog to get. 0 (zero) is current errorlog, 1 is the last archived and so on.@p2
: Define by integer or NULL which log to read. 1 (one) or NULL to read SQL Server Errorlog, 2 to read SQL Server Agent Log.@p3
: Primary search string, max. 255 characters.@p4
: Secondary search string, max. 255 characters.
The path of the Errorlog directory is also available through the Windows Registry. Unfortunately it is indirect so I don't like it. But it could be by the script
DECLARE @reg_key_data NVARCHAR(255);
EXECUTE [master].[dbo].[xp_instance_regread]
N'HKEY_LOCAL_MACHINE'
,N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters'
,N'SQLArg1'
,@reg_key_data OUTPUT;
DECLARE @errorlog_directory NVARCHAR(256);
SELECT @errorlog_directory = REPLACE(REPLACE(@reg_key_data,N'-e',''),N'\ERRORLOG','');
SELECT @errorlog_directory AS N'errorlog_directory';
Reference
Greg Robidoux: „Reading the SQL Server log files using T-SQL“ (MSSQLTips)
No comments:
Post a Comment