SQL in the City: Boston

A few weeks ago, I got the chance to go to a day of free SQL training event that was organized by Red Gate Software. The event was called SQL in the City. It was conducted on October 8th, 2012 at Harvard University's The Joseph B. Martin Conference Center. It had a pretty good turnout, considering that it was a Columbus day holiday (for some people).

The training covered various topics, from SQL Server maintenance, monitoring, development, and others. The training sessions were being presented by SQL Server MVPs, such as Steve Jones (blog | twitter), Grant Fritchey (blog | twitter) and Adam Machanic (blog | twitter). They really passionate and more importantly knowledgeable about SQL Server. They also were very approachable. They made themselves available for questions even after their sessions, so we can interact with them, which was awesome.

One of the sessions that I found interesting was "Architecting Hybrid Data Systems with SQL Server and Windows Azure". It was presented by Buck Woody (blog | twitter). Obviously, "Cloud" is now the buzz word in IT. Buck pointed out, which I agree totally, that before moving to the "Cloud" we should think about the current problems that we have, and if by moving to the "Cloud", it would help to resolve those problems.

There were also a lot of Red Gate people in the events. I was able to interact with some of them and learned more about their tools/ software, some of which that I did not know about. Tools such as SQL Storage Compress, which I am looking forward to test and might be useful to save some (… well hopefully large amount of) disk space in our testing environment.

Overall, it was well organized event. It was a good day for me to network and also to learn more about SQL Server and Red Gate products. Thank you Red Gate. Hopefully, this can be an annual event in Boston.

Querying SQL Server Error Log

SQL Server error log offers good way to obtain information when troubleshooting SQL Server related problem. A while back, I wrote a blog post about reading SQL Server error log using Microsoft Log Parser. That blog post can be found here. There are many ways in which you can query the SQL Server error log. One of them is using the sys.sp_readerrorlog stored procedure. This stored procedure can be located in the master database. It accepts 4 input parameters:

1. @p1 – integer: This parameter is to specify which error log to read. SQL Server error log would rollover. By default, SQL Server error log would keep a file for the current log and maximum 6 of archived logs (this setting can be changed easily), ERRORLOG, ERRORLOG.1, ERRORLOG.2, ERRORLOG.3, ERRORLOG.4, ERRORLOG5 and ERRORLOG6. ERRORLOG is where SQL Server stores the current error log, ERRORLOG.1 is where SQL Server stores the most recent archived, etc. If we put 0 or null on this parameter, we are querying the current error log (ERRORLOG). 1 would refer to ERRORLOG.1. The same concept would apply to SQL Server Agent error log.
2. @p2 – integer: This parameter is to specify if we want to query the SQL Server Error Log or the SQL Server Agent Error Log. If we enter 1 or null, we are querying the SQL Server Error Log. However, if we enter 2, then we are querying the SQL Server Agent Error Log.
3. @p3 – varchar(255): We can specify word or phrase that we are looking within the text/message on the SQL Server error log or SQL Server Agent error log.
4. @p4 – varchar(255): We can specify word or phrase that we are looking within the text/message on the SQL Server error log or SQL Server Agent error log. If we enter a word or phrase on @p3 parameter and enter another word or phrase on @p4 parameter, the stored procedure should return error log entries that contain both words/phrases (AND operator). If we leave @p3 blank but enter a word or phrase on @p4, the stored procedure would not filter the error log. It will ignore the @p4 parameter filter.

Some Usage Examples

The following would return all entries on the current SQL Server error log (ERRORLOG):

EXEC sp_readerrorlog

or:

EXEC sp_readerrorlog 0

or:

EXEC sp_readerrorlog NULL, NULL, NULL, NULL

The following would return all entries on the current SQL Server Agent error log (SQLAGENT.OUT):

EXEC sp_readerrorlog 0, 2

The following would return entry from SQL Server error log when the SQL Server was starting the msdb database (in this case it was part of the server start up):

EXEC sp_readerrorlog 0, 1, 'starting', 'msdb'

This would returns:

Wait, There’s more…

If we look at the sp_readerrorlog stored procedure code closely, it is actually calling the xp_readererrorlog extended stored procedure. The xp_readerrorlog actually accepts more input parameter than the 4 input parameters described above. The following blog article described the parameters that xp_readerrorlog would accept. Basically it would accept 3 additional parameters:

1. Log date from range – Date time: this parameter would help to filter the log entries from a specific time period.
2. Log date to range – Date time: this parameter would help to filter the log entries to a specific time period.
3. Ascending or Descending – Varchar: this parameter can be use to specify the sorting order of the log entries based on the log date. Enter ‘asc’ for ascending order, and ‘desc’ for descending order.

So, for example, if we want to get the list of current SQL Server error log entries between 6:27 PM and 6:28 PM today (7th October 2012), and list the log entries in the descending log date order, I can use the following query:

EXEC xp_readerrorlog 0, 1, NULL, NULL, '2012-10-07 18:27', '2012-10-07 18:28', 'desc'

Related Notes:

• Querying SQL Error Log with Microsoft LogParser
• Querying SQL Server Agent Error Log with Microsoft LogParser