How to generate SQL server trace logs to troubleshoot and verify SQL server functionality and performance.

SQL Server tracing can be used to generate logs containing information on all of the SQL transactions initiated on a SQL server. These logs can be useful in troubleshooting many Symantec Endpoint Protection Manager (SEPM)/SQL issues including:

• Establishing a baseline for SQL performance
• Troubleshooting locks/deadlocks seen in SEPM logging
• Verifying SQL activity from external sources (3rd party applications accessing the SEPM SQL database)
• Troubleshooting general SQL performance issues

The following steps can be used to create a SQL server trace log. This type of reporting is only available from a Microsoft SQL server - not an embedded Sybase database.

Note: The following procedure requires a SQL user with SA level permissions.

1. Open SQL Server Profiler - this can be run locally on the SQL server, or from an arbitrary remote machine
2. Click the File menu and choose the New Trace option
   • At the SQL server connection prompt, provide the Server name (include the SQL instance if necessary), and Authentication information necessary to connect to the SQL
3. After authentication, the Trace Properties Window will display
   1. On the General tab:
      • Provide a name for the Trace (Deadlock__
        _ for example)
      • Ensure the trace is configured to use the Standard (default) trace template
      • Check the Save to file box and provide an easy to find path for the saved file(s)
   2. On the Events Selection tab:
      1. Check the Show all events box to ensure all possible event types are listed
      2. Expand Locks and ensure the following are checked:
         • Deadlock Graph
         • Lock:Deadlock
         • Lock:Deadlock Chain
         • Lock:Escalation
         • Lock:Timeout
         • Lock:Timeout (timeout >0)
      3. Expand TSQL and ensure the following are checked::
         • Exec Prepared SQL
         • Prepare SQL
         • SQL:Batch Completed
         • SQL:Batch Starting
      4. Expand Transactions and choose the following:
         • SQL Transaction
   3. On Events Extraction Settings tab:
      1. Check the Save Deadlock XML events separately box and choose a filename for the XDL file. 
   4. Once the SQL trace settings are configured, start the trace by clicking the Run button
4. Allow the trace to run until a sufficient sample of the SQL server's behavior is captured - A good starting point would be around a half hour
5. When enough information has been gathered, click the Stop button and close SQL Server Profiler
6. The trace file(s) will have a .trc extension - gather all of these files for analysis

References
http://msdn.microsoft.com/en-us/library/ms188246(SQL.90).aspx

http://msdn.microsoft.com/en-us/library/ms178104(SQL.90).aspx