Thursday, July 18, 2013

How to open SQL profiler in SQL Express LocalDB



SQL Profiler is an extremely wonderful tool to debug the SQL server problems. I personally love this tool very much. Recently while working with one of the project I had to deal with SQL Express LocalDB instance. According to my coding style I was very much sure that, I will need to debug at SQL level using SQL profiler to fix the issues.

As expected one of my stored procedure in SQL Express LocalDB was failing and I tried to debug it using SQL Profiler. You can open the SQL profiler in SQL Server Management Studio from “Tools” menu.

The login window appears where in you will need to put the SQL Express LocalDB server instance name. As a usual practice you may put the server name like for example, (localDB)\MyDB with connection option as Windows Authentication or with credentials for SQL Server authentication. However with this approach the SQL profiler may not open.

To overcome this problem we need to use SQLLocalDB utility. SQLLocalDB utility is command line tool which helps user to manage and perform administrative operations on SQL Express LocalDB instance. Here to connect SQL profiler to LocalDB instance we need to retrieve the correct instance name using SQLLocalDB utility. Open the command prompt and type following line in command line window.
Sqllocaldb info “InstanceName”
InstanceName in above command will be replaced by the instance name of your LocalDB server instance. The “info” command argument return you name, version, and state, last start time and most important “Local Pipe Name” of the specified LocalDB instance. The information is as shown below –
 
The local pipe name selected and reported in above command output need to be used for making the connection in SQL Server Profiler for SQL Express LocalDB as shown below –
 
Now by default none of the event is selected to show in SQL profiler. Therefore trace properties need to be manually selected on Event Selection window. Generally selecting “Error and Warnings, TSQL, Stored Procedures” options provides all necessary information. Following example shows the selection of TSQL event to trace. Similarly you can select all of the events mentioned above.
 
Then click on Run to start profiler.
 
Hope it helps.
Cheers…
Happy Profiling!!

1 comment:

  1. Thanks for your post.
    There is one more great free tool that will be very helpful - dbForge Event Profiler for SQL Server
    You can download it at https://www.devart.com/dbforge/sql/event-profiler/

    ReplyDelete