How To Collect SQL Profiler Logs For Database Issues

News Knowledge Base
1

How to collect SQL Profiler Logs to resolve any database issues?

The procedure to collect profiler logs vary slightly depending on what version of SQL is being used.

Collect Profiler Logs for SQL 2008:

  1. Login to the SQL 2008 server as an administrator
  2. From the Start Menu, click 'Programs - Microsoft SQL Server 2008 R2 - Performance Tools - SQL Server Profiler'.
  3. Click "File - New Trace"
  4. In the 'Connect to Server' window, select relevant SQL server name from the 'Server name' drop down list and click the 'Connect' button.
  5. In the 'Trace Properties' dialog box, change "Use the Template" to be "Blank"
  6. Tick the box "Save to file" and choose an appropriate location to save the file
  7. Tick "Enable file rollover" and set the maximum file size to 5 (Mb)
  • This will mean that when the trace file reaches 5Mb, a new trace file will be created (and so on)
  • Smaller file sizes will make it easier to transfer the results to others

image.png

  1. Click on the tab "Events Selection"
  2. Locate the section "Errors and Warnings":

image.png

  1. Tick all of the following options:
  • Blocked process report
  • Error log
  • Eventlog
  • Sort warnings

  1. Locate the section "Locks"
  2. Tick the option "Deadlock graph"

image.png

  1. Locate the section "OLEDB"
  2. Tick the option "OLEDB errors"

image.png

  1. Locate the section "Performance"
  2. Tick the option "Showplan all"

image.png

  1. Locate the section "TSQL"
  2. Tick the option "SQL:StmtCompleted"

image.png

  1. Click "Run" when ready
  2. The Profiler logs can now be collected.