Guide to Logging for Monitor Solution SQL Server

Guide to Logging for Monitor Solution SQL Server Error Logs and Profiler Trace 8 of 8 Morgan Neill Technical Support Analyst for Monitor Solution SQL Server Logs and Trace 8 of 8 1

SQL Server Logs and Trace Explained • SQL Server Error Logs and Trace - Introduction: • There are two primary methods for diagnosing SQL Server issues: SQL Server Error Logs and SQL Server Profiler Trace. • The SQL Server error logs contains user defined events and certain system events that can be used to troubleshoot issues on the SQL Server. • The Profiler trace is a built in program to Microsoft SQL Server Management Studio. The Profiler is used for monitoring an instance of the Database Engine or Analysis Services. The Profiler trace can provide information on the following issues: Stepping through problem queries to find the cause of the problem, finding and diagnosing slow-running queries, capturing the series of SQL transactions that can then be used on a test server to recreate the issue and diagnose it, monitor the performance of SQL Server to adjust workloads, correlating performance counters to diagnose problems. SQL Server Logs and Trace 8 of 8 2

SQL Server Error Logs and Trace - When to collect: • SQL Server error logs and profiler trace are a good first step for any potential database problem. • For instance if monitoring reports aren’t working as expected it may be a good idea to check the tables that the report is populated from and you can step through the SQL queries with the profiler or you can scan the error logs for potential problems that may be related. • A trace can be handy for pinpointing the exact line of SQL that is causing an error or malfunction. SQL Server Logs and Trace 8 of 8 3

SQL Server Error Logs - How to collect: • Log on to the SQL Server with administrator credentials. • Click Start > All Programs > Microsoft SQL Server Management Studio. • Once Management Studio has started, click View > Object Explorer and this will bring up the Object Explorer view on the left hand side. • In the Object Explorer, expand a server, expand Management, and then expand SQL Server Logs. • Right Click a log and click View SQL Server Log. This will bring up the error logs in the Log File Viewer. • To export and save the file click Export within the Log File Viewer, select a location and click Save. It will be saved as a. log file. SQL Server Logs and Trace 8 of 8 4

SQL Server Profiler Trace - How to collect: • Log on to the SQL Server with administrator credentials. • Click Start > All Programs > Microsoft SQL Server Management Studio. • Once Management Studio has started, click Tools > SQL Server Profiler. • Choose the server type by selecting either Database Engine or Analysis Services and click Connect. • This will bring up the Trace Properties Window, give the trace a name and click Run. • After the trace has started, it can be saved by clicking the red stop button and clicking File > Save As > Trace File. Give the file a name and a location and it will be saved as a. trc file. SQL Server Logs and Trace 8 of 8 5

Thank you! Morgan Neill Morgan_Neill@Symantec. com 541 -335 -3508 Copyright © 2013 Symantec Corporation. All rights reserved. Symantec and the Symantec Logo are trademarks or registered trademarks of Symantec Corporation or its affiliates in the U. S. and other countries. Other names may be trademarks of their respective owners. This document is provided for informational purposes only and is not intended as advertising. All warranties relating to the information in this document, either express or implied, are disclaimed to the maximum extent allowed by law. The information in this document is subject to change without notice. SQL Server Logs and Trace 8 of 8 6