Server-Side Trace Pack
What does this script do?
This script loads temporary stored procedures that assist with server-side tracing. Its purpose is to make server-side tracing easier to perform, faster to implement, and generally less intimidating.
Why should I use this script?
-
It allows you to reference traces by a single name for all actions. You won't have to reference a trace_id (unless you want to) and you won't have to use a '.trc' extension for some actions and not others.
-
It simplifies the parameters you would need to use with the built-in SQL Server procedures. These ones are more intuitive and shouldn't require double-checking the online specifications.
-
The use of temp stored procs keeps you from cluttering up the server. Once your session is closed (same as closing the tab in SSMS), the proc definitions are cleared.
-
It provides pre-written queries in the lookup procs with filtering parameters and plenty of extra details. These can help you review your own traces or inspect other traces on the server you aren't familiar with.
-
It minimizes the actions required for running traces to only 'create', 'start', 'stop', and 'close'. Note that preparation is usually required to load trace events beforehand.
How do I use it?
In short, you execute the full script to load the temp procs and then go to the bottom of the script to use the pre-written EXEC statements. However, this overlooks the preparation required to load your own trace events. Please see the instructions in the comments at the head of the script.
Where can I learn about server-side traces?
This script doesn't excuse the user from being familiar with the use of server-side traces, or tracing in general. Please educate yourself on the subject to get the most out of it. The following set of articles is a great place to start.
Stairway to Server-side Tracing
Why did you make it?
My job requires that I perform Production support on many databases that are located on clients' servers. When troubleshooting issues in a Production environment, server-side trace is the way to go because it requires no extra software running and has negligible performance impact. However, I have always found the SQL Server built-in procedures for managing these traces to be difficult to work with and deploy quickly. With the client looking on, I really want to be able to quickly and confidently perform all the actions required to manage my traces.
I started out by developing scripts to act as wrappers for the built-in functions. Scripts are usually nice because they don't require me to install new objects on a client's server. But these scripts got big and there were several required so I ended up fumbling around between multiple SSMS tabs or within a long script. That led me to turn the individual scripts into temp procs so I could work primarily in a compact EXEC area. As a bonus, using temp procs allowed me to separate the reusable code and that gave me a mini-framework with which to integrate my trace-related queries as lookup procs.
/*
* Server-Side Trace Pack (SSTP)
* Released on 2012-03-07 by Greg Drake
*
* DESCRIPTION:
* This script loads temporary stored procedures that assist with server-side tracing.
* Its purpose is to make server-side tracing easier to perform, faster to implement, and generally less intimidating.
* At the bottom of the script are a number of pre-written EXEC statements to help use the stored procs.
*
* USAGE OVERVIEW (details below)
* Prepare script
* Step 1: Use SQL Server Profiler to choose events and filters
* Step 2: Copy the events and filters into this script
* Use script on desired server
* Step 3: Execute this script to load the temporary stored procedures on the server
* Step 4: Use the stored procedures to run and manage the server-side trace
*
* DETAILS/ADVICE
* Step 1: Profiler
* Although the purpose of a server-side trace is basically to avoid using Profiler in a certain situation, Profiler is still the best tool when it comes to selecting events in preparation for any trace.
* On a dev/test system, use Profiler to create a trace with the events and filters that are desired.
* Note that Profiler always adds a filter to exclude queries performed by Profiler itself.
* The filter will read something like this: ApplicationName NOT LIKE 'SQL Server Profiler - a7f81b9b-44e7-4108-8b68-d60eb2289dc3'
* In a server-side trace this filter may not be desirable, but it's also mostly harmless.
* Export the definition of the trace by using File > Export > Script Trace Definition > For SQL Server 2005...
* Step 2: Copy events and filters
* Scroll near the bottom of this script, above the large ASCII heading saying 'EXEC AREA', to find the smaller ASCII headings that say 'EVENTS' and 'FILTERS'.
* Delete any events and filters that were left in the script from previous use.
* Copy all the events from the Profiler export under the 'EVENTS' header.
* The events should be easy to spot and every row should look similar to the following...
* exec sp_trace_setevent @TraceID, 1, 1, @on
* Don't include the @on variable declaration.
* Copy all the filters from the Profiler export under the 'FILTERS' header.
* Filters often are not needed on a trace; if there are none just leave this section empty.
* Include any SET statements mixed in for variables like @bigintfilter.
* DO NOT include the comment line '-- Set the trace status to start' or anything after it.
* Step 3: Exec the script
* On the desired server, execute this entire script without selecting any portion in particular.
* Messages will be printed out naming the SP's that have been loaded.
* The EXEC statements at the bottom of the script won't be executed because there is a RETURN statement after the SP definitions.
* This will load several temporary stored procedures on the server.
* Temp SP's are just like temp tables in that they are prefixed with '#', stored in tempdb, and accessible only to the session that created them.
* In other words, only the query window you created them in will be able to use them and they are removed from the server automatically when you close the window.
* Despite this, if two sessions attempt to load these procs on the same server, the second session may receive naming conflict errors and be unable to proceed until the first is cleared off.
* Remember that the events and filters are in one of these SP definitions and this will affect traces that are created.
* If the events or filters are to be modified, this script will need to be executed again to load the new definition.
* Re-executing or even closing this script will have no effect on the traces that are currently running on the server.
* Step 4: Use the stored procedures
* Go to the bottom of the script under the large ASCII text comment that says 'EXEC AREA'.
* Note that there are two sub-sections here: 'LOOKUPS' and 'ACTIONS'.
* LOOKUPS - has procs containing pre-written queries to help inspect existing traces.
* ACTIONS - has procs that help create, modify, and remove traces.
* All of the EXEC statements are preceded by comments to describe their purpose.
* Before beginning a new server-side trace, choose its name and location on disk (find & replace on previous name in script).
* Basic action guide:
* Beginning a trace requires that it be (1) created and (2) started. (When a trace is created, it does not automatically start recording)
* Ending a trace requires that it be (3) stopped and (4) closed.
* A "stop" can be considered like a pause; once a trace is created it can be stopped and started again any number of times.
* Once the trace is closed, it isn't recommended that one attempt to recreate it with the same name/location unless the original file is removed.
*
* NOTES:
* Compatibility: 2005 SP1 (not verified on higher versions)
* On most servers there will usually be a system 'default' trace that is always running. That's a standard SQL Server thing so don't mess around with it unless you know what you're doing.
* Some reading material about the benefits of server-side traces compared to using Profiler.
* http://sqlserverpedia.com/wiki/The_Server-side_Trace:_What,_Why,_and_How
* http://sqlblog.com/blogs/linchi_shea/archive/2007/08/01/trace-profiler-test.aspx
* http://qa.sqlservercentral.com/articles/SQL+Trace/71693/
* Also check out the 'sqltrace' stored procedure by Lee Tudor (a.k.a Mr Tea).
* It dynamically runs a server-side trace on a batch of T-SQL that is provided as a parameter.
* http://www.sommarskog.se/sqlutil/sqltrace.html
*/
USE master
-- _ _ ____
-- | | ___ __ _ __| | | _ \ _ __ ___ ___ ___
-- | | / _ \ / _` |/ _` | | |_) | '__/ _ \ / __/ __|
-- | |__| (_) | (_| | (_| | | __/| | | (_) | (__\__ \
-- |_____\___/ \__,_|\__,_| |_| |_| \___/ \___|___/
--
-- SELECT * FROM tempdb.sys.objects WHERE [type] = 'P' AND [name] LIKE '#%'
IF (EXISTS (SELECT * FROM tempdb.sys.objects WHERE [type] = 'P' AND [name] LIKE '#sstp_lookup_active_trace%')) DROP PROCEDURE #sstp_lookup_active_trace
IF (EXISTS (SELECT * FROM tempdb.sys.objects WHERE [type] = 'P' AND [name] LIKE '#sstp_query_all_traces%')) DROP PROCEDURE #sstp_query_all_traces
IF (EXISTS (SELECT * FROM tempdb.sys.objects WHERE [type] = 'P' AND [name] LIKE '#sstp_query_trace_events%')) DROP PROCEDURE #sstp_query_trace_events
IF (EXISTS (SELECT * FROM tempdb.sys.objects WHERE [type] = 'P' AND [name] LIKE '#sstp_query_trace_event_columns%')) DROP PROCEDURE #sstp_query_trace_event_columns
IF (EXISTS (SELECT * FROM tempdb.sys.objects WHERE [type] = 'P' AND [name] LIKE '#sstp_query_trace_filters%')) DROP PROCEDURE #sstp_query_trace_filters
IF (EXISTS (SELECT * FROM tempdb.sys.objects WHERE [type] = 'P' AND [name] LIKE '#sstp_query_trace_results%')) DROP PROCEDURE #sstp_query_trace_results
IF (EXISTS (SELECT * FROM tempdb.sys.objects WHERE [type] = 'P' AND [name] LIKE '#sstp_trace_create%')) DROP PROCEDURE #sstp_trace_create
IF (EXISTS (SELECT * FROM tempdb.sys.objects WHERE [type] = 'P' AND [name] LIKE '#sstp_trace_setstatus%')) DROP PROCEDURE #sstp_trace_setstatus
IF (EXISTS (SELECT * FROM tempdb.sys.objects WHERE [type] = 'P' AND [name] LIKE '#sstp_trace_setevent_setfilter%')) DROP PROCEDURE #sstp_trace_setevent_setfilter
GO
-----------------------------------------------------------
PRINT 'Loading #sstp_lookup_active_trace...'
GO
CREATE PROCEDURE #sstp_lookup_active_trace
@trace_id int = NULL OUTPUT
,@trace_name nvarchar(245) = NULL OUTPUT
,@file_path nvarchar(260) = NULL OUTPUT
,@file_name nvarchar(260) = NULL OUTPUT
,@file_rollover nvarchar(10) = NULL OUTPUT
,@full_file_path nvarchar(260) = NULL OUTPUT
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRY
SELECT
@trace_name = nullif(@trace_name, '')
,@file_name = NULL
,@file_path = NULL
IF (((@trace_id IS NULL) AND (@trace_name IS NULL)) OR ((@trace_id IS NOT NULL) AND (@trace_name IS NOT NULL)))
RAISERROR ('Procedure requires that values be provided for one of these two parameters: @trace_id or @trace_name. Providing values for both parameters will also result in this error.', 11, 1)
BEGIN TRANSACTION
IF (@trace_id IS NULL)
BEGIN
SELECT TOP 1
@trace_id = id
FROM
master.sys.traces
WHERE
right([path], (charindex(N'\', reverse([path]), (len('.trc') + 1)) - 1)) = @trace_name + '.trc'
OR right([path], (charindex(N'\', reverse([path]), (len('.trc') + 1)) - 1)) LIKE @trace_name + N'[_]%' + '.trc'
IF (@trace_id IS NULL)
RAISERROR ('No trace could be found with parameter @trace_name = ''%s''', 11, 1, @trace_name)
END
ELSE IF NOT EXISTS (SELECT 1 FROM master.sys.traces WHERE id = @trace_id)
RAISERROR ('No trace could be found with parameter @trace_id = %i', 11, 1, @trace_id)
SELECT
@trace_id = id
,@trace_name = CASE
WHEN (pos_last_underscore > pos_file_path_end) AND (pos_last_underscore < pos_file_ext_begin) THEN
substring([path], (pos_file_path_end + 1), (pos_last_underscore - pos_file_path_end - 1))
ELSE
substring([path], (pos_file_path_end + 1), (pos_file_ext_begin - pos_file_path_end - 1))
END
,@file_name = substring([path], (pos_file_path_end + 1), (pos_file_ext_begin - pos_file_path_end - 1))
,@file_path = substring([path], 1, pos_file_path_end)
,@full_file_path = [path]
FROM
(
SELECT
id
,[path]
,pos_file_path_end = (len([path]) - charindex(N'\', reverse([path]), (len('.trc') + 1)) + 1)
,pos_last_underscore = (len([path]) - charindex(N'_', reverse([path]), (len('.trc') + 1)) + 1)
,pos_file_ext_begin = (len([path]) - len('.trc') + 1)
FROM
master.sys.traces
WHERE
id = @trace_id
) trc
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF (@@trancount > 0) ROLLBACK TRANSACTION
DECLARE @error_message nvarchar(4000), @error_severity int
SELECT @error_message = error_message(), @error_severity = error_severity()
RAISERROR(@error_message, @error_severity, 1)
RETURN 1
END CATCH
RETURN 0
END
GO
-----------------------------------------------------------
PRINT 'Loading #sstp_query_all_traces...'
GO
CREATE PROCEDURE #sstp_query_all_traces
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
[id] = t.id
,[trace_name] = CASE
WHEN t.is_rowset = 1 THEN '(Running from Profiler)'
WHEN (t.pos_last_underscore > t.pos_file_path_end) AND (t.pos_last_underscore < t.pos_file_ext_begin) THEN
substring(t.[path], (t.pos_file_path_end + 1), (t.pos_last_underscore - t.pos_file_path_end - 1))
ELSE
substring(t.[path], (t.pos_file_path_end + 1), (t.pos_file_ext_begin - t.pos_file_path_end - 1))
END
,[status] = convert(varchar(10), t.[status]) + ' - ' + CASE t.[status] WHEN 0 THEN 'Stopped' WHEN 1 THEN 'Running' ELSE 'Unknown' END
,[file_path] = substring(t.[path], 1, t.pos_file_path_end)
,[file_name] = substring(t.[path], (t.pos_file_path_end + 1), (len(t.[path]) - t.pos_file_path_end))
,[max_file_size] = convert(varchar(20), t.max_size) + ' MB'
,[max_files] = t.max_files
,[stop_time] = t.stop_time
--,[is_rowset] = t.is_rowset
,[is_rollover] = t.is_rollover
,[is_shutdown] = t.is_shutdown
,[is_default] = t.is_default
,[start_time] = t.start_time
,[start_time_diff] = convert(varchar(10), (datediff([second], t.start_time, getdate()) / 60 / 60 / 24)) + ' days '
+ right('0' + convert(varchar(10), (datediff([second], t.start_time, getdate()) / 60 / 60) % 24), 2) + ':'
+ right('0' + convert(varchar(10), (datediff([second], t.start_time, getdate()) / 60) % 60), 2) + ':'
+ right('0' + convert(varchar(10), (datediff([second], t.start_time, getdate()) % 60)), 2)
,[last_event_time] = t.last_event_time
,[event_time_diff] = convert(varchar(10), (datediff([second], t.last_event_time, getdate()) / 60 / 60 / 24)) + ' days '
+ right('0' + convert(varchar(10), (datediff([second], t.last_event_time, getdate()) / 60 / 60) % 24), 2) + ':'
+ right('0' + convert(varchar(10), (datediff([second], t.last_event_time, getdate()) / 60) % 60), 2) + ':'
+ right('0' + convert(varchar(10), (datediff([second], t.last_event_time, getdate()) % 60)), 2)
,[events_set] = eventinfo.event_count
,[event_columns_set] = convert(varchar(10), eventinfo.event_column_count) + ' (' + convert(varchar(20), convert(decimal(10, 1), (eventinfo.event_column_count / (eventinfo.event_count * 1.0)))) + '/event)'
,[filters_set] = filterinfo.value_count
,[captured_events] = t.event_count
,[dropped_events] = t.dropped_event_count
--,[buffer_count] = t.buffer_count
--,[buffer_size] = convert(varchar(10), t.buffer_size) + ' KB'
--,[file_position] = t.file_position
--,[reader_spid] = t.reader_spid
FROM
(
SELECT
*
,pos_file_path_end = (len([path]) - charindex(N'\', reverse([path]), (len('.trc') + 1)) + 1)
,pos_last_underscore = (len([path]) - charindex(N'_', reverse([path]), (len('.trc') + 1)) + 1)
,pos_file_ext_begin = (len([path]) - len('.trc') + 1)
FROM
master.sys.traces
) t
OUTER APPLY
(
SELECT
[event_count] = count(DISTINCT eventid)
,[column_count] = count(DISTINCT columnid)
,[event_column_count] = count(*)
FROM
master.dbo.fn_trace_geteventinfo(t.id)
) eventinfo
OUTER APPLY
(
SELECT
[value_count] = count(DISTINCT value)
FROM
master.dbo.fn_trace_getfilterinfo(t.id)
) filterinfo
RETURN 0
END
GO
-----------------------------------------------------------
PRINT 'Loading #sstp_query_trace_events...'
GO
CREATE PROCEDURE #sstp_query_trace_events
@trace_id int = NULL
,@trace_name nvarchar(245) = NULL
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
EXECUTE #sstp_lookup_active_trace
@trace_id = @trace_id OUTPUT
,@trace_name = @trace_name OUTPUT
IF (@trace_id IS NULL)
RETURN 1
SELECT
[trace_id] = t.id
,[category_name] = tcat.[name]
,[category_type] = CASE tcat.[type] WHEN 0 THEN 'Normal' WHEN 1 THEN 'Connection' WHEN 2 THEN 'Error' END
,[event_id] = tevt.trace_event_id
,[event_name] = tevt.[name]
,[columns_saved] = count(*)
FROM
(
master.sys.traces t
CROSS APPLY
master.dbo.fn_trace_geteventinfo(t.id) tevt_def
)
INNER JOIN
(
master.sys.trace_categories tcat
INNER JOIN
master.sys.trace_events tevt ON (tevt.category_id = tcat.category_id)
) ON (tevt.trace_event_id = tevt_def.eventid )
WHERE
t.id = @trace_id
GROUP BY
t.id
,tcat.[name]
,tcat.[type]
,tevt.trace_event_id
,tevt.[name]
ORDER BY
t.id
,tcat.[name]
,tevt.[name]
RETURN 0
END
GO
-----------------------------------------------------------
PRINT 'Loading #sstp_query_trace_event_columns...'
GO
CREATE PROCEDURE #sstp_query_trace_event_columns
@trace_id int = NULL
,@trace_name nvarchar(245) = NULL
,@event_name nvarchar(128) = NULL
,@column_name nvarchar(128) = NULL
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
EXECUTE #sstp_lookup_active_trace
@trace_id = @trace_id OUTPUT
,@trace_name = @trace_name OUTPUT
IF (@trace_id IS NULL)
RETURN 1
-- Look up events and columns defined in a specific trace
SELECT
[trace_id] = t.id
,[category_name] = tcat.[name]
,[category_type] = CASE tcat.[type] WHEN 0 THEN 'Normal' WHEN 1 THEN 'Connection' WHEN 2 THEN 'Error' END
,[event_id] = tevt.trace_event_id
,[event_name] = tevt.[name]
,[column_id] = tcol.trace_column_id
,[column_name] = tcol.[name]
FROM
(
master.sys.traces t
CROSS APPLY
master.dbo.fn_trace_geteventinfo(t.id) tevt_def
)
INNER JOIN
(
master.sys.trace_categories tcat
INNER JOIN
master.sys.trace_events tevt ON (tevt.category_id = tcat.category_id)
) ON (tevt.trace_event_id = tevt_def.eventid )
INNER JOIN
master.sys.trace_columns tcol ON (tcol.trace_column_id = tevt_def.columnid)
WHERE
t.id = @trace_id
AND (
@event_name IS NULL
OR @event_name = ''
OR @event_name = tevt.[name]
)
AND (
@column_name IS NULL
OR @column_name = ''
OR @column_name = tcol.[name]
)
ORDER BY
t.id
,tcat.[name]
,tevt.[name]
,tcol.[name]
RETURN 0
END
GO
-----------------------------------------------------------
PRINT 'Loading #sstp_query_trace_filters...'
GO
CREATE PROCEDURE #sstp_query_trace_filters
@trace_id int = NULL
,@trace_name nvarchar(245) = NULL
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
EXECUTE #sstp_lookup_active_trace
@trace_id = @trace_id OUTPUT
,@trace_name = @trace_name OUTPUT
IF (@trace_id IS NULL)
RETURN 1
SELECT
[trace_id] = t.id
,[logical_operator] = CASE tfil_def.logical_operator WHEN 0 THEN 'AND' WHEN 1 THEN 'OR' END
,[column_id] = tcol.trace_column_id
,[column_name] = tcol.[name]
,[comparison_operator] = CASE tfil_def.comparison_operator
WHEN 0 THEN '='
WHEN 1 THEN '<>'
WHEN 2 THEN '>'
WHEN 3 THEN '<'
WHEN 4 THEN '>='
WHEN 5 THEN '<='
WHEN 6 THEN 'LIKE'
WHEN 7 THEN 'NOT LIKE'
END
,[value] = tfil_def.[value]
FROM
(
master.sys.traces t
CROSS APPLY
master.dbo.fn_trace_getfilterinfo(t.id) tfil_def
)
INNER JOIN
master.sys.trace_columns tcol ON (tcol.trace_column_id = tfil_def.columnid)
WHERE
t.id = @trace_id
ORDER BY
t.id
,tcol.[name]
RETURN 0
END
GO
-----------------------------------------------------------
PRINT 'Loading #sstp_query_trace_results...'
GO
CREATE PROCEDURE #sstp_query_trace_results
@trace_id int = NULL
,@trace_name nvarchar(245) = NULL
,@file_path nvarchar(245) = NULL
,@file_name nvarchar(50) = NULL
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- TODO: Add extra parameters to filter on certain trace columns like ClientProcessID or SPID
DECLARE
@full_file_path nvarchar(245)
IF ((@trace_id IS NOT NULL) OR (@trace_name IS NOT NULL))
BEGIN
IF ((@trace_id IS NOT NULL) AND (@trace_name IS NOT NULL))
BEGIN
RAISERROR ('Procedure does not require values for parameters @trace_id and @trace_name, but providing values for both parameters is not allowed.', 11, 1)
RETURN 1
END
EXECUTE #sstp_lookup_active_trace
@trace_id = @trace_id OUTPUT
,@trace_name = @trace_name OUTPUT
,@file_path = @file_path OUTPUT
SELECT
@file_name = @trace_name
END
ELSE IF ((@file_path IS NULL) OR (@file_name IS NULL))
BEGIN
RAISERROR ('Invalid parameters provided to procedure. For active traces, provide either @trace_id or @trace_name, but not both. For closed traces, provide both @file_path and @file_name', 11, 1)
RETURN 1
END
SELECT
@full_file_path = left((@file_path + CASE WHEN (right(@file_name, 4) = '.trc') THEN @file_name ELSE @file_name + '.trc' END), 256)
-- TODO: (maybe) Add ability to read specific trace files when given in params
SELECT
[category] = isnull(tcat.[name], '')
,[event] = CASE
WHEN ttab.EventClass > 60000 THEN CASE
WHEN ttab.EventClass = 65527 THEN '[Trace Rollover]'
WHEN ttab.EventClass = 65533 THEN '[Trace Stop]'
WHEN ttab.EventClass = 65534 THEN '[Trace Start]'
ELSE '[Unknown internal trace EventClass ' + convert(varchar(10), ttab.EventClass) + ']'
END
ELSE isnull(tevt.[name], '')
END
,[subclass] = isnull(tscv.subclass_name, '')
-- Not pretty, but the full text is not available in the database.
-- 2005 definition: http://msdn.microsoft.com/en-us/library/ms180953%28v=SQL.90%29.aspx
,[object_type] = CASE isnull(ttab.ObjectType, -1)
WHEN -1 THEN ''
WHEN 8259 THEN 'Check Constraint'
WHEN 8260 THEN 'Default (constraint or standalone)'
WHEN 8262 THEN 'Foreign-key Constraint'
WHEN 8272 THEN 'Stored Procedure'
WHEN 8274 THEN 'Rule'
WHEN 8275 THEN 'System Table'
WHEN 8276 THEN 'Trigger on Server'
WHEN 8277 THEN '(User-defined) Table'
WHEN 8278 THEN 'View'
WHEN 8280 THEN 'Extended Stored Procedure'
WHEN 16724 THEN 'CLR Trigger'
WHEN 16964 THEN 'Database'
WHEN 16975 THEN 'Object'
WHEN 17222 THEN 'FullText Catalog'
WHEN 17232 THEN 'CLR Stored Procedure'
WHEN 17235 THEN 'Schema'
WHEN 17475 THEN 'Credential'
WHEN 17491 THEN 'DDL Event'
WHEN 17741 THEN 'Management Event'
WHEN 17747 THEN 'Security Event'
WHEN 17749 THEN 'User Event'
WHEN 17985 THEN 'CLR Aggregate Function'
WHEN 17993 THEN 'Inline Table-valued SQL Function'
WHEN 18000 THEN 'Partition Function'
WHEN 18002 THEN 'Replication Filter Procedure'
WHEN 18004 THEN 'Table-valued SQL Function'
WHEN 18259 THEN 'Server Role'
WHEN 18263 THEN 'Microsoft Windows Group'
WHEN 19265 THEN 'Asymmetric Key'
WHEN 19277 THEN 'Master Key'
WHEN 19280 THEN 'Primary Key'
WHEN 19283 THEN 'ObfusKey'
WHEN 19521 THEN 'Asymmetric Key Login'
WHEN 19523 THEN 'Certificate Login'
WHEN 19538 THEN 'Role'
WHEN 19539 THEN 'SQL Login'
WHEN 19543 THEN 'Windows Login'
WHEN 20034 THEN 'Remote Service Binding'
WHEN 20036 THEN 'Event Notification on Database'
WHEN 20037 THEN 'Event Notification'
WHEN 20038 THEN 'Scalar SQL Function'
WHEN 20047 THEN 'Event Notification on Object'
WHEN 20051 THEN 'Synonym'
WHEN 20549 THEN 'End Point'
WHEN 20801 THEN 'Adhoc Queries which may be cached'
WHEN 20816 THEN 'Prepared Queries which may be cached'
WHEN 20819 THEN 'Service Broker Service Queue'
WHEN 20821 THEN 'Unique Constraint'
WHEN 21057 THEN 'Application Role'
WHEN 21059 THEN 'Certificate'
WHEN 21075 THEN 'Server'
WHEN 21076 THEN 'Transact-SQL Trigger'
WHEN 21313 THEN 'Assembly'
WHEN 21318 THEN 'CLR Scalar Function'
WHEN 21321 THEN 'Inline scalar SQL Function'
WHEN 21328 THEN 'Partition Scheme'
WHEN 21333 THEN 'User'
WHEN 21571 THEN 'Service Broker Service Contract'
WHEN 21572 THEN 'Trigger on Database'
WHEN 21574 THEN 'CLR Table-valued Function'
WHEN 21577 THEN 'Internal Table (For example, XML Node Table, Queue Table.)'
WHEN 21581 THEN 'Service Broker Message Type'
WHEN 21586 THEN 'Service Broker Route'
WHEN 21587 THEN 'Statistics'
WHEN 21825 THEN 'User'
WHEN 21827 THEN 'User'
WHEN 21831 THEN 'User'
WHEN 21843 THEN 'User'
WHEN 21847 THEN 'User'
WHEN 22099 THEN 'Service Broker Service'
WHEN 22601 THEN 'Index'
WHEN 22604 THEN 'Certificate Login'
WHEN 22611 THEN 'XMLSchema'
WHEN 22868 THEN 'Type'
ELSE convert(varchar(10), ttab.ObjectType)
END
,ttab.TextData
,ttab.BinaryData
,ttab.DatabaseID
,ttab.DatabaseName
,ttab.TransactionID
,ttab.LineNumber
,ttab.NTUserName
,ttab.NTDomainName
,ttab.HostName
,ttab.ClientProcessID
,ttab.ApplicationName
,ttab.LoginName
,ttab.SPID
,ttab.Duration
,ttab.StartTime
,ttab.EndTime
,ttab.Reads
,ttab.Writes
,ttab.CPU
,ttab.[Permissions]
,ttab.Severity
,ttab.ObjectID
,ttab.Success
,ttab.IndexID
,ttab.IntegerData
,ttab.ServerName
--,ttab.EventClass
--,ttab.EventSubClass
--,ttab.ObjectType
,ttab.NestLevel
,ttab.State
,ttab.Error
,ttab.Mode
,ttab.Handle
,ttab.ObjectName
,ttab.FileName
,ttab.OwnerName
,ttab.RoleName
,ttab.TargetUserName
,ttab.DBUserName
,ttab.LoginSid
,ttab.TargetLoginName
,ttab.TargetLoginSid
,ttab.ColumnPermissions
,ttab.LinkedServerName
,ttab.ProviderName
,ttab.MethodName
,ttab.RowCounts
,ttab.RequestID
,ttab.XactSequence
,ttab.EventSequence
,ttab.BigintData1
,ttab.BigintData2
,ttab.GUID
,ttab.IntegerData2
,ttab.ObjectID2
,ttab.Type
,ttab.OwnerID
,ttab.ParentName
,ttab.IsSystem
,ttab.Offset
,ttab.SourceDatabaseID
,ttab.SqlHandle
,ttab.SessionLoginName
,ttab.PlanHandle
FROM
master.dbo.fn_trace_gettable(@full_file_path, DEFAULT) ttab
LEFT OUTER JOIN
(
master.sys.trace_categories tcat
INNER JOIN
master.sys.trace_events tevt ON (tevt.category_id = tcat.category_id)
) ON (tevt.trace_event_id = ttab.EventClass)
LEFT OUTER JOIN
master.sys.trace_subclass_values tscv ON (tscv.trace_event_id = ttab.EventClass AND tscv.subclass_value = ttab.EventSubClass)
WHERE
ttab.EventClass != 65528 -- Internal trace EventClass that is always the first item returned by fn_trace_gettable(), but it's not a useful entry for us.
RETURN 0
END
GO
-----------------------------------------------------------
PRINT 'Loading #sstp_trace_setstatus...'
GO
CREATE PROCEDURE #sstp_trace_setstatus
@trace_id int = NULL
,@trace_name nvarchar(245) = NULL
,@set_status int
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRY
EXECUTE #sstp_lookup_active_trace
@trace_id = @trace_id OUTPUT
,@trace_name = @trace_name OUTPUT
PRINT CASE @set_status WHEN 0 THEN 'Stopping' WHEN 1 THEN 'Starting' WHEN 2 THEN 'Closing and removing' END + ' trace ' + convert(varchar(10), @trace_id) + ' ''' + @trace_name + '''...'
EXECUTE sp_trace_setstatus
@traceid = @trace_id
,@status = @set_status
PRINT ' - Status: ' + CASE @set_status WHEN 0 THEN 'Stopped' WHEN 1 THEN 'Running' WHEN 2 THEN 'No longer defined on server' END
END TRY
BEGIN CATCH
IF (@@trancount > 0) ROLLBACK TRANSACTION
DECLARE @error_message nvarchar(4000), @error_severity int
SELECT @error_message = error_message(), @error_severity = error_severity()
RAISERROR(@error_message, @error_severity, 1)
RETURN 1
END CATCH
RETURN 0
END
GO
-----------------------------------------------------------
PRINT 'Loading #sstp_trace_create...'
GO
CREATE PROCEDURE #sstp_trace_create
@file_path nvarchar(245)
,@trace_name nvarchar(50)
,@max_file_size bigint = NULL
,@max_files int = NULL
,@stop_time datetime = NULL
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRY
DECLARE
@full_file_path nvarchar(245)
,@options int
,@TraceID int
,@on bit
,@intfilter int
,@bigintfilter bigint
,@msg_detail int
IF (len(@file_path + @trace_name) > 245)
RAISERROR ('The values provided in parameters @file_path and @trace_name can not be longer then 245 characters combined.', 11, 1)
IF (@file_path NOT LIKE '%\')
RAISERROR ('The file path must end in the character ''\''.', 11, 1)
IF (@trace_name LIKE '%[_]%')
RAISERROR ('Underscores in @trace_name are not allowed. Please use spaces or dashes. This helps avoid confusion with the sequential trace file naming scheme.', 11, 1)
SELECT
@msg_detail = count(*)
FROM
(
SELECT
id
,[path]
,pos_file_path_end = (len([path]) - charindex(N'\', reverse([path]), (len('.trc') + 1)) + 1)
,pos_last_underscore = (len([path]) - charindex(N'_', reverse([path]), (len('.trc') + 1)) + 1)
,pos_file_ext_begin = (len([path]) - len('.trc') + 1)
FROM
master.sys.traces
) trc
WHERE
@trace_name = CASE
WHEN (pos_last_underscore > pos_file_path_end) AND (pos_last_underscore < pos_file_ext_begin) THEN
substring([path], (pos_file_path_end + 1), (pos_last_underscore - pos_file_path_end - 1))
ELSE
substring([path], (pos_file_path_end + 1), (pos_file_ext_begin - pos_file_path_end - 1))
END
IF (@msg_detail > 0)
RAISERROR ('The specified trace name ''%s'' is already in use on this server. The existing trace file might be located in a different directory, but the supporting SSTP functions must be able to reference a unique name without that information.', 11, 1, @trace_name)
SELECT
@full_file_path = left(@file_path + @trace_name, 245)
,@max_file_size = isnull(@max_file_size, 5)
,@max_files = isnull(@max_files, 1)
,@on = 1
-- For the @options param of sp_trace_create, this script only allows the use of TRACE_FILE_ROLLOVER, and that is done automatically based on @max_files.
-- If the other options are required they must be manually configured because they are not good in casual traces and should be used with care.
SELECT
@options = CASE WHEN @max_files > 1 THEN 2 ELSE 0 END
,@max_files = CASE WHEN @max_files > 1 THEN @max_files ELSE NULL END
PRINT 'Creating new trace on server with name ''' + @trace_name + '''...'
EXECUTE sp_trace_create
@traceid = @TraceID OUTPUT
,@options = @options
,@tracefile = @full_file_path
,@maxfilesize = @max_file_size
,@stoptime = @stop_time
,@filecount = @max_files
PRINT ' - Trace ID: ' + convert(varchar(10), @TraceID)
/* ____ _ _ ____ _ _ ___ ____
* |___ | | |___ |\ | | [__
* |___ \/ |___ | \| | ___]
*
* Use Profiler to pick events, then export definition using File > Export > Script Trace Definition > For SQL Server 2005...
*/
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 15, 15, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 9, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
/* ____ _ _ ___ ____ ____ ____
* |___ | | | |___ |__/ [__
* | | |___ | |___ | \ ___]
*
* Use Profiler as described above for Events. Include any @bigIntFilter settings.
*/
--exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%SELECT%'
---------------------------------------
SELECT @msg_detail = count(DISTINCT eventid) FROM master.dbo.fn_trace_geteventinfo(@TraceID)
PRINT ' - Events: ' + convert(varchar(10), @msg_detail)
SELECT @msg_detail = count(*) FROM master.dbo.fn_trace_geteventinfo(@TraceID)
PRINT ' - Event columns: ' + convert(varchar(10), @msg_detail)
SELECT @msg_detail = count(*) FROM master.dbo.fn_trace_getfilterinfo(@TraceID)
PRINT ' - Filters: ' + convert(varchar(10), @msg_detail)
END TRY
BEGIN CATCH
IF (@@trancount > 0) ROLLBACK TRANSACTION
DECLARE @error_message nvarchar(4000), @error_severity int
SELECT @error_message = error_message(), @error_severity = error_severity()
RAISERROR(@error_message, @error_severity, 1)
RETURN 1
END CATCH
RETURN 0
END
GO
-----------------------------------------------------------
RETURN
-- _____ _
-- | ____|_ _____ ___ / \ _ __ ___ __ _
-- | _| \ \/ / _ \/ __| / _ \ | '__/ _ \/ _` |
-- | |___ > < __/ (__ / ___ \| | | __/ (_| |
-- |_____/_/\_\___|\___| /_/ \_\_| \___|\__,_|
--
-- Utility query to generate a unique name
SELECT [timestamp_filename] = 'Support Trace ' + convert(varchar(20), getdate(), 112) + '-' + substring(convert(varchar(20), getdate(), 114), 1, 2) + substring(convert(varchar(20), getdate(), 114), 4, 2) + substring(convert(varchar(20), getdate(), 114), 7, 2)
-- LOOKUPS
-- All traces defined on the server
EXECUTE #sstp_query_all_traces
-- All events set on a trace
EXECUTE #sstp_query_trace_events
@trace_id = NULL
,@trace_name = 'Support Trace'
-- All events set on a trace with the columns expanded
EXECUTE #sstp_query_trace_event_columns
@trace_id = NULL
,@trace_name = 'Support Trace'
,@event_name = ''
,@column_name = ''
-- All filters set on a trace
EXECUTE #sstp_query_trace_filters
@trace_id = NULL
,@trace_name = 'Support Trace'
-- Results of a trace that is active or saved to file
EXECUTE #sstp_query_trace_results
@trace_id = NULL
,@trace_name = NULL
,@file_path = 'C:\'
,@file_name = 'Support Trace'
-- ACTIONS
-- Create new trace
EXECUTE #sstp_trace_create
@file_path = 'C:\'
,@trace_name = 'Support Trace' -- '.trc' extension is added automatically. Underscores not allowed.
,@max_file_size = 20 -- MB
,@max_files = 5 -- Will rollover files if > 1
-- Start trace
EXECUTE #sstp_trace_setstatus
@trace_id = NULL
,@trace_name = 'Support Trace'
,@set_status = 1
-- Stop trace
EXECUTE #sstp_trace_setstatus
@trace_id = NULL
,@trace_name = 'Support Trace'
,@set_status = 0
-- Close and remove trace
EXECUTE #sstp_trace_setstatus
@trace_id = NULL
,@trace_name = 'Support Trace'
,@set_status = 2
