Function
dbo.udf_SqlA_ErrorLogFile (
) Returns nvarchar(255)
/*
* Returns the location of the SQL Server Agent ErrorLogFile for
* the
* DEFAULT INSTANCE ONLY.
*
* Example:
SELECT dbo.udf_SQLA_ErrorLogFile ()
*
* © Copyright 2005 Andrew Novick http://www.NovickSoftware.com
* You may use this code in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* UDF either in print or electronically.
* Published as Coding-in-SQL Newsletter Vol 3 #2 1/11/05
http://www.NovickSoftware.com/coding-in-sql.htm
****************************************************************/
AS BEGIN
DECLARE @oem_errorlog nvarchar(255)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'ErrorLogFile',
@oem_errorlog
OUTPUT,
N'no_output'
Return @oem_errorlog
end
GO
Let's try it out:
SELECT dbo.udf_SQLA_ErrorLogFile ()
(Results)
SQL Agent ErrorLog File
--------------------------------------------------
c:\db\MSSQL\LOG\SQLAGENT.OUT
If you take a look at the directory you’ll see
that the current log has an extension of OUT and the archived logs
have extensions of 1 through 9.
Here’s part of the current log from my
development laptop. The messages have been truncated on the right
at 65 columns.
2005-01-09 07:59:00 - ? [393] Waiting for SQL Server to recover d
2005-01-09 07:59:10 - ? [100] Microsoft SQLServerAgent version 8.
2005-01-09 07:59:10 - ? [100] Microsoft SQLServerAgent version 8.
2005-01-09 07:59:10 - ? [101] SQL Server NSL3 version 8.00.760 (0
2005-01-09 07:59:10 - ? [102] SQL Server ODBC driver version 3.85
2005-01-09 07:59:10 - ? [103] NetLib being used by driver is DBMS
2005-01-09 07:59:10 - ? [310] 1 processor(s) and 1023 MB RAM dete
2005-01-09 07:59:10 - ? [339] Local computer is NSL3 running Wind
2005-01-09 07:59:10 - ! [364] The Messenger service has not been
2005-01-09 07:59:10 - ? [129] SQLSERVERAGENT starting under Windo
2005-01-09 07:59:10 - + [260] Unable to start mail session (reaso
2005-01-09 07:59:10 - + [396] An idle CPU condition has not been
The date and time at the start of each line is
obvious. What’s the meaning of the punctuation mark after the
time? It turns out that it’s a message type that’s used by the
almost hidden screen of SQL Enterprise Manager that displays the SQL
Server Agent ErrorLog. The “SQL Server Agent Error Log” form is
reachable from the Context menu of the SQL Server Agent node in
Enterprise Manager. Right click on SQL Server Agent and use the
“Display Error Log…” menu command. You’ll see this screen:
The Type dropdown lets you select from Error,
Warning, and Information messages and there’s a text box where you
can search the message contents. It would be nice to have this kind
of search capability for the SQL Server ErrorLog.
Well it turns out that there is such a
capability in xp_readerrorlog and sp_readerrorlog. If you supply -1 as the
first parameter and the path to the log file as the second
parameter, the third and fourth parameters are search strings.
That’s how SQL Enterprise Manager filters the message by both type
and “Containing text”. This could be turned into a search
capability for ErrorLog files from both SQL Server and SQL Server
Agent. In fact, this search capability could replace
dba_ErrorLog_Search from the previous Coding-in-SQL issue.
As you’ll notice udf_SQLA_ErrorLogFile reads
the location of the error log for the default instance and only for
the default instance. When you have multiple instances it’s not
helpful. Of course, since the file is always in the same place it’s
pretty easy to locate once you find the directory where the text files
are loaded. When I considered enhancing it to work correctly in any
instance I ran into a few facts:
-
It’s possible to get the name of the
instance from the SERVERPROPERTY function, which makes the UDF
pretty easy to write.
-
Registry locations are likely to change in
SQL Server 2005 (Yukon).
It’s the latter reason, the approach of SQL
Server 2005, that leads me to skip the effort in enhancing this UDF
to make it more general. And it’s a good idea to avoid reading the
registry when you don’t have to.
Next issue is about how applications can write
messages to a table to improve the quality of information about your
system. It will include some of the stored procedures that I
use to log and trace the actions of my important procedures.
As always, if you have comments about the
newsletter and in particular, the new direction that I’m planning on
taking, please let me know at
coding-in-sql@novicksoftware.com.
Read the Previous Issue #1
Read
the Next Issue #3
Transact-SQL User-Defined Functions has been published! Take a look at it
now!
Do you have an interesting stored procedure, user-defined-function,
or other sample code? If you'd like to share them please send
them in an email to:
coding-in-sql@NovickSoftware.com
and they might be published in this newsletter. I try and
respond to every request that I get.
Thanks,
Andy
Andrew Novick
