An interesting feature added to SQL Server 7.0 and 2000 is the ability to purge
an error log through a stored procedure or DBCC command. In this article,
we are going to go over some of the undocumented stored procedures to detect, read and purge an SQL Server error log in T-SQL.
Before we move any further, we need to explain one quick point. Many of the stored procedures covered in this article need to be executed by
someone in the sysadmin role. As in most extended and system stored procedures, make sure the procedures mentioned in this article are well secured.
The error log provides some interesting details to a hacker that can be used against you. These procedures are used by Enterprise Manager mostly in its
internal workings and should not be tampered with.
The first extended stored procedure will allow you to see which
error logs are out there for you to see as well as see when
they were created.
The extended stored procedure xp_enumerrorlogs allows you to do just that. As in most xp_enum extended stored procedures, you don't need to pass it any parameters.
The results look like the following:
Archive # Date
----------- -----------------
0 04/05/2000 20:00
1 04/05/2000 19:55
2 04/03/2000 01:00
3 04/02/2000 17:47
4 03/26/2000 11:53
5 03/24/2000 20:31
6 03/24/2000 19:47
(7 row(s) affected)
You can purge an error log in SQL Server 7.0+ by running sp_cycle_errorlog without any parameters.
The stored procedure uses DBCC ERRORLOG to cycle the log. This has the same effect to the error log as stopping and starting SQL Server, except you don't see all the transaction rollbacks.
The error log is only 9 lines long after the purge.
Finally, the last extended stored procedure is used to read the error log. Using
xp_readerrorlog without any parameters, will return the entire current error log.
Below is an example of a few lines:
2000-04-05 19:55:12.78 spid51 Errorlog has been reinitialized. See
previous log for older entries.
2000-04-05 20:00:55.60 spid51 Using 'xpstar.dll' version '1999.09.18'
to execute extended stored procedure
'xp_readerrorlog'.
Take notice to two points in the error log. The first line states that the error log has be purged.
If you see this line and a system administrator didn't stop or purge the log, then you may have a security problem. It could possibly mean
that someone has done something and doesn't want to leave a trace behind. The final line is also a good idea to watch for security. It shows that someone has executed
an extended stored procedure.
Theses are some cool procedures that can be run against a SQL Server 7.0 and 2000 database to read and purge the error logs. There are also some
handy stored procedures that you can use to write to and read from the NT event log as well. For a great book about the inners of SQL Server, check out Inside SQL Server
