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.
Open SQL Server Profiler - this can be run locally on the SQL server, or from an arbitrary remote machine
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
After authentication, the Trace Properties Window will display
On the General tab:
Provide a name for the Trace (Deadlock_
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)
On the Events Selection tab:
Check the Show all events box to ensure all possible event types are listed
Expand Locks and ensure the following are checked:
Lock:Timeout (timeout >0)
Expand TSQL and ensure the following are checked::
Exec Prepared SQL
Expand Transactions and choose the following:
On Events Extraction Settings tab:
Check the Save Deadlock XML events separately box and choose a filename for the XDL file.
Once the SQL trace settings are configured, start the trace by clicking the Run button
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
When enough information has been gathered, click the Stop button and close SQL Server Profiler
The trace file(s) will have a .trc extension - gather all of these files for analysis