THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Trace Metadata

All my good intentions to write a couple of posts a week are already down in flames.

So I’ll post a couple of entries today.

First, a followup to my previous post about the new catalog views. I admitted last time that I loved them, but I’ll admit now that not only do I not know what every column of every view means, I haven’t even discovered some of the views. But that keeps the mystery alive, and mystery is a good thing with something or someone you love, right?

This also relates to an earlier post about the relaxed syntax for the system defined table valued functions, such as fn_trace_getinfo, for which you no longer need to use the double colon syntax. It didn’t even occur to me that I don’t need to use the function at all. I use fn_trace_getinfo all the time and have no problems typing in the name quickly, so I never thought to search if there was anything new and better in SQL Server 2005.

Just last week I found out there is something new and better. There is a view called sys.traces, which not only has the five properties that fn_trace_getinfo returns, it has a lot more besides. And, the results come back in one row for each trace, so it is a lot easier to get information about multiple traces that may be running.

Here is the column list:



















Those of you who have worked with traces will probably know what most of the columns mean, but here’s a few more details. ‘is_rowset’ refers to a trace that is returning rows one at a time, and that applies to traces that are returning events to the Profiler. ‘file_position’ refers only to a trace that is writing to a file, and indicates the position in the file where the last writes were made. That can help give you an idea how big your trace file is getting to be.

I had more fun with catalog views while trying to find the metadata that would just give me the list of column names that I have above. Yes, sp_help will show me, but that doesn’t give queryable, tabular results, and I wanted just a list of column names for one particular view. I tried looking in sys.columns, but it doesn’t include columns for system objects. Then I remembered sys.system_objects, and thought there might be a comparable view called sys.system_columns. Sure enough, the following query gave me what I wanted:

select name from sys.system_columns

where object_id = object_id('sys.traces')

order by column_id


Then I thought I’d see how sp_help returned the column names, and this query showed me the definition:


select object_definition(object_id('sys.sp_help'))


It uses a view called sys.all_columns which includes columns from system objects and user objects. So I could get the columns from sys.traces from either sys.system_columns or sys.all_columns.


Now I’ll go see what other fun stuff I can discover in the metadata.

-- Kalen

Published Sunday, October 8, 2006 6:46 AM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Didier said:

How to change the max_size ?

May 7, 2008 6:36 AM

Kalen Delaney said:

Hi Didier

One of the parameters to sp_trace_create allows you to set the max file size. Please check the Books Online for the full documentation to this procedure.


May 7, 2008 4:49 PM

Saket said:

How to change max_files and other stuff?

September 29, 2008 9:09 AM

SQLDBA1 said:

Kalen Delaney - I guess everybody`s question is clear and simle:

"How to change the max_size ?" = either is possible or is not, the question was not to set it at start time, but how to alter it afterwards.

September 20, 2009 5:40 AM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Privacy Statement