Hi Experts,

User was running a job from Application end which runs under SQL Agnet service account. The job was keeps stallingwithout any error. I could the related processes are 'Sleeping' by using sysprocesses cmd. User requesting to run profiler to check what's going on? So I would like to know what are the event needs to be selected to capture the relevant info, Thanks for help  Version: SQL 2008 R2 (Enterprise), Windows 2008 R2

Use the code below to get the spid and then you can filter it in the SQL Profiler.

http://www.sqlservercentral.com/scripts/Connections/86792/

For selecting events:

https://msdn.microsoft.com/en-us/library/ff650699.aspx

To identify a long-running query interactively

1. Start SQL Profiler.
2. On the File menu, click New, and then click Trace.
3. Specify the relevant connection details to connect to the computer running SQL Server.

   The Trace Properties dialog box appears.

4. In the Template name list, click SQLProfilerTSQL_Duration.
5. Click the Events tab.

   Notice that two key events are selected:

   • RPC:Completed from the Stored Procedures event class
   • SQL:BatchCompleted from the T-SQL event class
6. Click Run to run the trace.
7. Start SQL Query Analyzer.
8. Run the queries you want to analyze. For example, using the Pubs database, you might run the following query.

   select au_id, au_lname + ' ' + au_fname, phone from authors where au_lname like 'G%' order by au_lname
   select * from authors
   Select au_id from authors
   

9. View the trace output in SQL Profiler. Note that durations are given in milliseconds.

   Figure 1 shows sample output.

   

   Figure 1: Sample SQL Profiler output using the SQLProfilerTSQL_Duration template

10. Stop the trace.

    Note   Your duration times may vary from those shown, and may even appear as zero if the database server has a small load.