Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Read the end of a large SQL Server Error Log


By:   |   Last Updated: 2009-10-26   |   Comments (8)   |   Related Tips: 1 | 2 | 3 | More > Error Logs

Problem

I manage a team of Oracle and SQL Server DBAs and what often strikes me are the differing tools, methodologies and processes that are used in performing DBA tasks between and Oracle and SQL DBAs. In some cases I find SQL Server more efficient and in some cases Oracle is the clear winner. The Oracle DBAs in my team are touch typists and perform most of their tasks through multiple command line PuTTY windows whereas the SQL Server DBAs perform most of their tasks through Management Studio or other GUIs.

A really useful UNIX command which I often see the Oracle DBAs use is "tail -f". Tail is a command-line tool that displays a user-specified number of the last lines of a text file in a console window. This makes Tail great for watching log files.

Solution

You can find a Windows version of the UNIX Tail command in the Windows Server 2003 Resource Kit which you can download for free from Microsoft. Once the Windows Server 2003 Resource Kit is installed the tail command will be in your path and you'll be able to utilize it from the command line to read from the end of large error log files.

Tail for Windows syntax

tail [/?] {[/n] [/f] [[Path]FileName]

Parameters

/? - Displays command-line usage.

/n - Allows the user to specify the number of lines from the end of the file to display, where n is a decimal number. If /n is not specified, Tail shows the last 10 lines of the file.

/f - Shows the last 10 lines of the specified file name, then keeps checking for new lines. If a new line exists, it is displayed at the end of the output that is already on the screen. To exit, press CTRL+C.

Examples using Tail

Let say you have a really massive SQL Server Error Log file and its too slow to open using SSMS Log File Viewer or Notepad and you urgently need to check what the last entries were to give you and indication why it crashed. Tail can do this for you. Tail can handle very large files with ease. The following command will return the last 20 lines of the ERRORLOG.1 file:

C:\Documents and Settings\alanc>tail -20 "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG.1"

transaction log

SQL Server was re-started and you're waiting for a database to recover fully. You can tail the current SQL Error Log to get an idea of the progress. Usually you would leave this running in a window on your workstation so you could keep an eye on it. The following command will return the last 10 lines of the ERRORLOG file and display any new entries as and when they get written to the error log:

C:\Documents and Settings\alanc>tail -f "\\SERVER1\C$\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG"

database Parist
Next Steps


Last Updated: 2009-10-26


get scripts

next tip button



About the author
MSSQLTips author Alan Cranfield Alan Cranfield is a versatile SQL Server DBA with over 10 years experience managing critical systems in large distributed environments.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, August 18, 2010 - 4:22:07 PM - Jeremiah Wilton Back To Top
So, I noticed the output is all full of the trailing ASCII zeros on each character.  I think this has something to do with gnuwin tail not handling the UTF8 codepage correctly.  Or maybe something else.

Anyway I stripped out all the ASCII zeros by piping through another gnuwin utility, sed.

c:\>tail -f \sqlserverlogs\errorlog | sed s/\x00//g

Hope this helps,

Jeremiah Wilton

Blue Gecko, Inc.

http://www.bluegecko.net


Tuesday, October 27, 2009 - 10:27:29 PM - --cranfield Back To Top

looks like you've discovered the missing 5th and 6th parameters!


Tuesday, October 27, 2009 - 12:49:09 PM - SankarReddy Back To Top

I knew about only 4 parameters for xp_readerrorlog but the comments section in the above tip helped me learn about 3 more parameters to the xp_readerrorlog. This is incredible.

This one almost matches to the capapbilities of the TAIL command.

 EXEC xp_readerrorlog 01, NULL, NULL, '10/26/2009''10/27/2009''asc'


Tuesday, October 27, 2009 - 12:40:27 PM - SankarReddy Back To Top

Testing my HTML skills for the above link.

Reading the SQL Server log files using T-SQL | http://www.mssqltips.com/tip.asp?tip=1476


Tuesday, October 27, 2009 - 10:08:51 AM - admin Back To Top

Actually it is both sp_readerrorlog and xp_readerrorlog.

Refer to this tip:

http://www.mssqltips.com/tip.asp?tip=1476


Tuesday, October 27, 2009 - 5:38:22 AM - --cranfield Back To Top

For the readers: I think you are referring to sp_readerrorlog (not xp_) .  This is very handy for reading the error log from T-SQL.

The beauty with TAIL is that it can read from the end of the file.  Also, when the file is many GBs it will still read it no problem.

Thanks for the post


Monday, October 26, 2009 - 5:30:24 PM - admin Back To Top

The URL for the download has been corrected.


Monday, October 26, 2009 - 5:13:33 PM - SankarReddy Back To Top
Alan, I am NOT a fulltime DBA but this looks like an interesting approach. So far I have been using EXEC xp_readderrorlog 0, 1 or EXEC xp_readderrorlog 0, 1, 'specific text' to look at the error log and its been working out well for me. Good to know about this new technique. By the way, link to the download seems broken and here is the correct link. http://www.microsoft.com/downloads/details.aspx?FamilyID=9d467a69-57ff-4ae7-96ee-b18c4790cffd&DisplayLang=en

Learn more about SQL Server tools