RDBMS Information - SQL Server - Generating trace file with SQL Server Profiler

This page details out the method to generate trace file with SQL Server Profiler.

The trace file generated by SQL server Profiler allows identifying all queries executed during a session ID on a giver database and the executing time of each query.
For example, to identify a slow query or query making anaman or adgadmin hangs, we can use this tool.
Below different steps allowing generating a trace file in Sql server 2005:

1. Launch SQL server 2005 Enterprise management console.
2. In Object browser go to Management > Activity Monitor to get the session ID connected on the data base to be monitored.
   
3. Click on Activity Monitor. The below window is displayed.
   You can see the name of the database to be monitored in Database column and the session is in Process ID column
   
4. Go to Tools > SQL Server Profiler. Following window is displayed.
   
5. In SQL Server Profiler, go to File > New Trace. A connection dialog box is displayed.
   
   Connect to server hosting CAST database. A trace Properties window is then displayed.
   
6. In General tab, Enter the name of the trace file in Trace name field. The trace file will have a .trc extension.
7. Enter in Save to file field the location of folder to save the generated trace file
8. Go to Event selection tab. This tab shows different column to be displayed in trace file. You can modify the order of these columns using Organize columns button
   
9. If you need to get all information inside the stored procedure, in the trace properties you have to choose in the "Use the template" field:  “TSQL_SPs”
10. 

11. In the "Events Selection" tab, check the following options:  RPC:starting, SP:Completed, SP:Starting, SP:StmtStarting

    

12. Click on Column filters button. An Edit filter is displayed as shown below.
    
    1. Click on CPU
    2. Expand Equals
    3. Enter the session Id
13. Click on Run to start the trace file generation. We can see different queries being executed.
    
14. When the process is finish, stop the trace by going to File > Stop Trace.
15. Open the trace file by going to File > Open > Trace File.
16. You determine the execution time of each query using data in Start time Column

17. When selecting any line, you can see the whole query script in the bottom .