Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Undocumented T-SQL Tricks With Logs

By Brian Knight,

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

Total article views: 14398 | Views in the last 30 days: 11
 
Related Articles
FORUM

Extended stored procedure

Extended stored procedure

FORUM

extended stored procedure

extended stored procedure

ARTICLE

Undocumented Extended and Stored Procedures

Some useful undocumented extended and stored procedures in SQL Server 2005

FORUM

Extended Procedure

Extended Procedure

ARTICLE

DoEvents with SQL Server 2000 and Extended Procedures

In this article, we will leverage Extended Stored Procedures to extend the functionality of SQL Serv...

Tags
administration    
advanced querying    
sql server 7    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones