2010-08-24

Errorlog Directory

I need the path of the Errorlog directory when I define a SQL Server Agent jobstep. Using the Errorlog directory for the history (log files) of all jobsteps makes maintenance more easy and robust.
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
  1. @p1:Generation by integer value of the Errorlog to get. 0 (zero) is current errorlog, 1 is the last archived and so on.
  2. @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.
  3. @p3: Primary search string, max. 255 characters.
  4. @p4: Secondary search string, max. 255 characters.
The last part („\ERRORLOG“) of the string is filtered out as it is the name of the file.
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: