How to collect SQL Profiler Logs?
The procedure to collect profiler logs vary slightly depending on what version of SQL is being used.
The procedure to 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
8. Click on the tab "Events Selection"
9. Locate the section "Errors and Warnings":
10. Tick all of the following options:
- Blocked process report
- Error log
- Sort warnings
11. Locate the section "Locks"
12. Tick the option "Deadlock Graph":
13. Locate the section "OLEDB"
14. Tick the option "OLEDB errors"
15. Locate the section "Performance"
16. Tick the option "Showplan all":
17. Locate the section "TSQL"
18. Tick the option "SQL:StmtCompleted":
19. Click "Run" when ready.
The Profiler logs can now be collected.