Sql-server – Profiler TextData output is null

profilersql server

I am doing a trace on a database and dumping the trace realtime into a table.

The reason I'm doing a trace is I want to see all the queries that are being executed against the database.

+-----------+------------+----------+------------------------------+------------+-----------+-----+-------+--------+-----------+-----------------+------+-------------------------+-------------------------+------------+
| RowNumber | EventClass | TextData |       ApplicationName        | NTUserName | LoginName | CPU | Reads | Writes | Duration  | ClientProcessID | SPID |        StartTime        |         EndTime         | BinaryData |
+-----------+------------+----------+------------------------------+------------+-----------+-----+-------+--------+-----------+-----------------+------+-------------------------+-------------------------+------------+
|     13073 |         15 | NULL     | .Net SqlClient Data Provider | NULL       | reviewer  |   0 |  2172 |      0 | 439776000 |            5096 |  104 | 2012-12-14 12:54:22.893 | 2012-12-14 13:01:42.670 | NULL       |
|     13057 |         15 | NULL     | .Net SqlClient Data Provider | NULL       | reviewer  |   0 |  3198 |      4 | 436033000 |            4424 |  158 | 2012-12-14 12:52:50.457 | 2012-12-14 13:00:06.490 | NULL       |
|     10372 |         15 | NULL     | .Net SqlClient Data Provider | NULL       | reviewer  |   0 |  2124 |      0 | 400456000 |            2664 |  200 | 2012-12-14 12:48:12.347 | 2012-12-14 12:54:52.803 | NULL       |
|      4498 |         15 | NULL     | .Net SqlClient Data Provider | NULL       | reviewer  |   0 |  2099 |      0 | 399923000 |            3432 |  128 | 2012-12-14 12:06:10.140 | 2012-12-14 12:12:50.063 | NULL       |
|      8288 |         15 | NULL     | .Net SqlClient Data Provider | NULL       | reviewer  |   0 |  2525 |      2 | 399730000 |            2664 |  194 | 2012-12-14 12:38:46.863 | 2012-12-14 12:45:26.593 | NULL       |
+-----------+------------+----------+------------------------------+------------+-----------+-----+-------+--------+-----------+-----------------+------+-------------------------+-------------------------+------------+

I'm grabbing the rows where the duration is highest:

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [RowNumber]
      ,[EventClass]
      ,[TextData]
      ,[ApplicationName]
      ,[NTUserName]
      ,[LoginName]
      ,[CPU]
      ,[Reads]
      ,[Writes]
      ,[Duration]
      ,[ClientProcessID]
      ,[SPID]
      ,[StartTime]
      ,[EndTime]
      ,[BinaryData]
  FROM [salesdwh_testing].[dbo].[mytrace]
  order by duration desc

Why is the TextData null?

Please note that not all TextData is null, but it looks like these top 1000 are!

Best Answer

EventClass 15 is "Audit Logout" there's no TextData to associate with that. It's simply the connection closing. TextData would only be associated with events like "SQL:StmtStarting", etc...

See here for more info: http://weblogs.sqlteam.com/mladenp/archive/2007/11/09/Map-SQL-Server-Profiler-EventClass-ID-to-its-name-in.aspx

From comments: The duration it shows in the log is the total connection duration.