Does SQL Profiler work with LocalDB?

Question

Is it possible to use SQL Profiler to observe queries being requested of a LocalDB instance?

Buffoon

You can use SQL Profiler without any tricks by using the Instance pipe name of the LocalDB instance as the Server Name in SQL Profiler. You can find the instance pipe name using SqlLocalDb – Sipper

The OP eventually found the correct answer below. SQL Profiler just works with LocalDB. You just (LocalDB)\MSSQLLocalDB. You don't need to do anything with named pipes, scripts, etc. SQL Profiler isn't included with LocalDB, but it is included with stand alone & free SSMS (learn.microsoft.com/en-us/sql/ssms/…), so you don't need Express Profiler either. – Europeanize

Answer 1

You can use SQL Profiler just as you do with all other SQL editions as long as you know the proper server name. You can find the server name using the SqlLocalDb utility.

To find it, use sqllocaldb info YourInstanceName to find the Instance Pipe Name. It has the form np:\\.\pipe\LOCALDB#12345\tsql\query

Use this as the server name to connect to the server and start profiling

Sipper

Actually, in the end I was able to access it as a regular 'Database Engine' using a name of the form (LocalDB)\MyDatabase when using SQL Profiler 2012. – Buffoon

With SQL Server Profiler 2008 R2 I had to use your solution @PanagiotisKanavos. Great tip! – Misti

$tool = "C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SqlLocalDB.exe" .$tool info v11.0 – Lighterman

Thanks @StanB for the PowerShell cmd. I was able to discover that my (localdb)\v11.0 - at least from SqlLocalDB.exe's perspective - wasn't running.. so it didn't even have a Instance Pipe Name. Thus, I issued a "start" command and re-ran your command and voila! – Marriott

Use v11.0 as instance name. List all instances use info key. Works excellent! Thank you – Ation

Newbie mistake I made: I believe (localdb)\mssqllocaldb is isolated by user. In other words, to trace it, run the profiler as the user who is accessing the DB. – Plumbic

Answer 2

This is what I used on SQL Server Express 2012 (note: not "LocalDB* - I have never used LocalDB so maybe that is different to a "regular" SQL Server Express).

Step 1: Setup the trace

This is basically the "hard work". You first need to find out where the default log directory of SQL Server is. You need this directory name to specfiy the trace file.

Then create a trace by doing something like this:

DECLARE @TraceID int
DECLARE @tracefile nvarchar(255)
DECLARE @endDate datetime
DECLARE @size bigint

-- no file extension!
SET @tracefile = 'C:\Data\sqlserver\MSSQL11.SQLEXPRESS\MSSQL\Log\mydb_trace' 

-- tracing stops when either the max size of the file is reached 
-- or the enddate (whichever occurs first)
-- size is in MB
SET @size = 250
SET @enddate = DateAdd(DAY, 15, GetDate())

EXEC @rc = sp_trace_create @TraceID output, 2, @tracefile, @size, @enddate

Now for every event that should be traced, you need to call sp_trace_setevent multiple times to define which column for that event should be returned:

For a full list of events and columns, see: http://msdn.microsoft.com/en-US/library/ms186265%28v=sql.90%29.aspx

-- Enable Event: 45 = SP:StmtCompleted
EXEC sp_trace_setevent @TraceID, 45, 27, @on -- 27: EventClass
EXEC sp_trace_setevent @TraceID, 45, 12, @on -- 12: SPID
EXEC sp_trace_setevent @TraceID, 45, 35, @on -- 35: DatabaseName
EXEC sp_trace_setevent @TraceID, 45, 11, @on -- 11: SQLSecurityLoginName
EXEC sp_trace_setevent @TraceID, 45,  6, @on --  6: NTUserName
EXEC sp_trace_setevent @TraceID, 45,  8, @on --  8: ClientHostName
EXEC sp_trace_setevent @TraceID, 45, 10, @on -- 10: ApplicationName
EXEC sp_trace_setevent @TraceID, 45,  1, @on --  1: TextData
EXEC sp_trace_setevent @TraceID, 45, 13, @on -- 13: Duration
EXEC sp_trace_setevent @TraceID, 45, 14, @on -- 14: StartTime
EXEC sp_trace_setevent @TraceID, 45, 15, @on -- 15: EndTime
EXEC sp_trace_setevent @TraceID, 45, 18, @on -- 18: CPU
EXEC sp_trace_setevent @TraceID, 45, 29, @on -- 29: Nesting Level

All of the above calls must be done for each and every event you want to trace!

I find the events 12 = SQL:BatchCompleted, 42 = SP:Starting, 43 = SP:Completed, 45 = SP:StmtCompleted, 50 = SQL Transaction the most interesting ones.

Optionally you can setup a filter, I usually filter out system events and only show events for a specific database:

-- Exclude system events (so only user events are shown)
-- 60: IsSystem Column
--  0: logical Operator: AND (only)
--  1: comparison operator: not equal
--  1: value
EXEC sp_trace_setfilter @TraceID, 60, 0, 1, 1

-- only mydb database
EXEC sp_trace_setfilter @TraceID, 35, 0, 6, N'mydb'

Once the trace is setup, it must be activated:

EXEC sp_trace_setstatus @TraceID, 1

(Note the above must be run as a single batch, because of the variable usage).

To see how the trace was defined, you can use the following statement:

select traceid,
       case property
         when 1 then 'Trace Options'
         when 2 then 'Trace file'
         when 3 then 'Max. file size'
         when 4 then 'Stop time'
         when 5 then 'Status'
       end as property_name,
       case
         when property = 5 then
            case convert(nvarchar(max), value)
               when '1' then 'Active'
               else 'Inactive'
            end
         else convert(nvarchar(max), value)
       end as value
from ::fn_trace_getinfo(null)
where property in (2,3,5)

Now run your application or whatever issues statements to the database that you want to trace.

Step 2: Retrieve trace information

For this you need to know the full path to the actual trace file (from Step 1). Note that for fn_trace_gettable you need to specify the file including the file extension.

SELECT ApplicationName,
       LoginName,
       HostName,
       SPID,
       Duration,
       StartTime,
       EndTime,
       DatabaseName,
       reads,
       writes,
       RowCounts,
       cpu,
       EventClass,
       case EventClass
        when 10 then 'RPC:Completed'
        when 11 then 'RPC:Starting'
        when 12 then 'SQL:BatchCompleted'
        when 13 then 'SQL:BatchStarting'
        when 40 then 'SQL:StmtStarting'
        when 41 then 'SQL:StmtCompleted'
        when 42 then 'SP:Starting'
        when 43 then 'SP:Completed'
        when 44 then 'SP:StmtStarting'
        when 45 then 'SP:StmtCompleted'
        when 50 then 'SQL Transaction'
        when 67 then 'Execution Warnings'
        when 71 then 'Prepare SQL'
        when 72 then 'Exec Prepared SQL'
        when 73 then 'Unprepare SQL'
       end as Event,
       LineNumber,
       TextData
FROM ::fn_trace_gettable('C:\Data\sqlserver\MSSQL11.SQLEXPRESS\MSSQL\Log\mydb_trace.log', default)
order by StartTime;

Adjust the above to return the information you are interested in.

Once you have the information you need, you have to turn off the trace:

Step 3: disable the trace

For this you need to know the Trace-ID (e.g. by running the "info statement from Step 1). The with this ID, you need to first stop the trace, then you can delete it:

-- stop the trace
EXEC sp_trace_setstatus @TraceID, 0

-- delete the trace
EXEC sp_trace_setstatus @TraceID, 2

Lauraine

You don't need to do all that, just use the Instance Pipe Name as the server name in SQL Server Profiler – Sipper

@PanagiotisKanavos: but the SQL Profiler only comes with the entprise tools. It's not part of the SQL Server Express (or LocalDB) installation. – Lauraine

That's not what the OP asked. He just asked whether he can use SQL Profiler with LocalDB - yes he can. Using LocalDB doesn't mean you are trying to use the free tools. Perhaps you are using SSDT (as I am) and want to see what gets written to the database. – Sipper

Answer 3

From http://expressprofiler.codeplex.com/

ExpressProfiler (aka SqlExpress Profiler) is a simple but good enough replacement for SQL Server Profiler with basic GUI.

No requirements, no installation.

Can be used with both Express and non-Express editions of SQL Server 2005/2008/2008r2/2012 (including LocalDB)

Illconditioned

Answer 4

It as simple as setting the server to (LocalDB)\v11.0

http://expressprofiler.codeplex.com/discussions/456518

Keys

Answer 5

Microsoft SQL Server 2012 Express LocalDB is an execution mode of SQL Server Express targeted to program developers.

Sql Profiler is not provided with SQL Server Express.

Thus, you can't use Sql profiler for your LocalDB.

However,you can go through alternative ways.

How to use SQL Profiler with SQL Server Express Edition

Canterbury

Answer 6

I had the same issue and following solution worked for me. Open the SQL profiler and connect to the sql server. In the new trace select event selection tab. Then check the show all events check box. After that, from the list select the checkboxes under data base name. Now use column filter then select databasename. Select like and type %localDB%. then start the trace. It will capture every thing on that database.

Beaubeauchamp