How-To - ArcFM - Execute a Microsoft SQL Server Profiler Database Trace (SQL Server 2008 R2, 2012, 2014)

Version 27

    Solution

     

    Note: These steps are applicable to tracing in Microsoft SQL Server versions 2008 R2, 2012, and 2014. A Knowledge Base article for tracing with Microsoft SQL Server 2016 will be published in the near future.

     

    This document describes the steps to execute a Microsoft SQL Server Profiler trace when a trace is requested by Schneider Electric Technical Support. Every attempt has been made to ensure that these steps are generic and applicable to all system configurations. If you experience any issues while following these steps, please contact the Technical Support Engineer assigned to your case.

    Follow these steps to execute the trace: (Note: If Technical Support has provided a trace template to run the trace, skip to the Template Supplied by Technical Support section of this document).

    1. Start SQL Server Management Studio and connect to the instance where the trace will be performed.
    2. Select Tools > SQL Server Profiler.
    3. Connect to the instance where the trace will be performed using a login with the privileges required to perform a trace (the sa login has privileges as well as any login with the ALTER TRACE server privilege).
    4. In the Trace Properties dialog, type a trace name in the Trace name field.
    5. In the Use the template drop-down list, select one of the following:
      • Standard: For troubleshooting issues where an actual error is thrown or the application crashes
      • Tuning: For troubleshooting performance issues
    6. Check Save to file and browse to a destination to save the file. Type a file name and click Save.
    7. If the template chosen was Standard, complete the following steps:
      1. Select the Events Selection tab.
      2. Check Show all events and Show all columns located at the lower right of the dialog.
      3. Expand the Errors and Warnings Event category.
        1. Check the following events: ErrorLog, EventLog, Exception and User Error Message.
      4. Expand the Stored Procedures Event category.
        1. Check the following events: RPC:Starting, SP:Completed, SP:Starting, SP:StmtCompleted, and SP:StmtStarting.
      5. Expand the TSQL Event category.
        1. Check the following events: SQL:StmtStarting and SQL:StmtCompleted.
    8. If the template chosen was Tuning, complete the following steps:
      1. Select the Events Selection tab.
      2. Check Show all events and Show all columns located at the lower right of the dialog.
      3. Expand the Stored Procedures Event category:
        1. Check the following events: RPC Output Parameter and SP:Completed.
      4. Expand the TSQL Event category:
        1. Check the following event: SQL:StmtCompleted.
    9. Click Column Filters:
      1. Select DatabaseName, expand Like, and specify a database name (this filters the trace for that database name). Press Enter.
      2. Select DBUserName, expand Like, and specify a database user name (this filters the trace for that database user name). Press Enter.
      3. The Technical Support Engineer may specify other filters to include in the trace.
    10. Click Run to start the trace.
    11. To stop the trace, select File > Stop Trace.
    12. Compress the trace file, located in the path specified in Step 6 above, and send it to the Technical Support Engineer.

     

    Template Supplied by Technical Support

     

    The Technical Support Engineer may ask you to rerun the trace using a specific trace template, if the required information was not captured using the steps above. When this happens, the Technical Support Engineer provides you with a trace template. Follow these steps to run a trace using this template:

     

    1. Start SQL Server Management Studio and connect to the instance where the trace will be performed.
    2. Select Tools > SQL Server Profiler.
    3. Connect to the instance where the trace will be performed.
    4. Click Cancel in the Trace Properties dialog.
    5. Select File > Templates > Import Template.
    6. Browse to where the provided template was saved, select it, and click Open. If a dialog appears declaring that it already exists, click Yes to overwrite it.
    7. Click OK in the File <path> was imported successfully dialog.
    8. Select File > New Trace.
    9. Connect to the instance where the trace will be performed using a login with the privileges required to perform a trace (the sa login has privileges as well as any login with the ALTER TRACE server privilege).
    10. In the Trace Properties dialog, type a trace name in the Trace name field.
    11. Select the template that was just imported from the Use the template drop-down list.
    12. Check Save to file and browse to a destination to save the file. Type a file name and click Save.
    13. Select the Events Selection tab.
    14. Click Column Filters:
      1. Select DatabaseName, expand Like, and specify a database name (this filters the trace for that database name). Press Enter.
      2. Select DBUserName, expand Like, and specify a database user name (this filters the trace for that database user name). Press Enter.
    15. Click Run to start the trace.
    16. To stop the trace, select File > Stop Trace.
    17. Compress the trace file, located in the path specified in Step 12 above, and send it to the Technical Support Engineer.