System and application log data is essential when troubleshooting a new or complex SQL Server issue. Unfortunately, in SQL Server 2000 this was a bit cumbersome because critical data had to be reviewed from multiple logs and from different interfaces. Determining the sequence of events among the multiple logs became tedious when comparing the data across multiple sources and in different formats. The complexity grows when the logs have thousands or tens of thousands of entries with limited filtering capabilities to only review the needed data for the issue at hand.
With SQL Server 2005 the rules have changed significantly. Management Studio ships with the Log File Viewer which gives DBAs and Developers insight into the SQL Server 2005 and Windows Event Logs (Application, Security and System) all in one interface. In this interface each of the needed logs can be selected with the ability to filter the data and export as needed. In this tip we will answer common questions for those of you that are new to the tool which correspond to the tool's primary functionality.
How do I access the Log File Viewer?
- Launch SQL Server Management Studio.
- Navigate to your SQL Server 2005 instance.
- Expand the instance, then expand the Management folder followed by the SQL Server Logs folder.
- Double click on any of the log files.
- Below is the default Log File Viewer interface that will be loaded.
What are the available data sources for the Log File Viewer?
- Database Mail
- SQL Server Agent Logs
- SQL Server Error Logs
- Windows Event Logs (Application, Security and System)
How can I filter the data?
Just click on the 'Filter' button which will load the interface below. In this interface it is possible to fine tune the data that is being reviewed. At the highest level the filtering could be based on the start and end date\time stamp. At the lowest level you could look for the exact error message.
How do I search for an error message?
Just click on the 'Search' button which will load the interface below. In this interface you can search the results for the specific error message.
How can I export data?
Click the 'Export' button which will save the current contents of the Log File Viewer to a *.log, *.csv or *.txt file. This file can be reviewed later by clicking the 'Load Log' icon and reviewing the file contents in the Log File Viewer.
Does the Log File Viewer work with a SQL Server 2000 instance that I have registered with Management Studio (SQL Server 2005)?
Yes, the compatible SQL Server 2000 logs, such as SQL Server Agent Logs, SQL Server Error Logs and Windows Event Logs, can be viewed. You can consider the Log File Viewer one of those valuable features that Management Studio (SQL Server 2005) offers over Enterprise Manager (SQL Server 2000), so start using it today!
- The next time one of your SQL Server 2000 or 2005 servers acts up, be sure to make the Log File Viewer your first stop to research the issue.
- From a best practices perspective the SQL Server and Windows logs should be reviewed regularly to ensure unexpected issues are not occurring. This review could be on a daily or weekly basis if you do not already have an automated tool that reviews the logs in real time and notifies your team of current issues.
- Ensure a sufficient number of logs and amount of storage is configured to support your SQL Server and Windows logs for a specified amount of time such as 30, 60 or 90 days.
Last Update: 7/14/2006
About the author
View all my tips