Logo
programming4us
programming4us
programming4us
programming4us
Home
programming4us
XP
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Windows Phone
 
Windows Server

SQL Server 2008 : Monitoring Your Server - Using Server-Side Tracing

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
6/13/2011 5:49:56 PM
Server-side tracing is a method used to trace events occurring within your system without the GUI interface. You specify events to trace, and filters to narrow your results, using the following stored procedures:
  • sp_trace_create: Creates the trace definition

  • sp_trace_event: Adds events and event columns to the trace definition

  • sp_trace_setFilter: Defines event filters to a trace

Use SQL Server Books Online to identify which parameters to supply to the sp_trace_event stored procedure in order to add specific events and columns to a trace. Once you create the trace, you can manage the trace (turn it on and off) by running the sp_trace_setstatus stored procedure. You can also view a list all the traces, including SQL Server Profiler traces, as well as the filters, events, and columns for those traces, by invoking the following set of trace functions:

  • fn_trace_getinfo(trace id): Retrieves the trace definition for a particular trace ID or all traces if you supply NULL as the parameter.

  • fn_trace_geteventinfo(trace id): Identifies the events and columns specified for a trace.

  • fn_trace_getfilterinfo(trace id): Displays the filters in place for the trace.

Now that you understand the stored procedures available to create a trace and the functions that allow you to review your trace definition, let's create a trace. Using SQL Server Profiler is the easiest way to do this. Within SQL Server Profiler, you can define the trace using the GUI, checking all of the appropriate boxes, and then script out the T-SQL to create the trace.

NOTE

We use the GUI of SQL Server Profiler to script out our trace files because it is easier than creating these scripts without assistance.

To generate the T-SQL script from SQL Server Profiler, select the events, filters, and columns that you want in your trace, then start your trace and stop it immediately. After the trace stops, navigate to the File option, select Export => Script Trace Definition => For SQL Server 2005-2008. Specify a file name and then click on OK. You will get a file with a trace definition, such as the one in Listing 1. Once you have the trace scripted, you can execute that script to create the trace. You can also add new events, columns, and filters as you see fit.

Example 1. T-SQL Script for Creating a Server-Side Trace
/****************************************************/
/* Created by: SQL Server 2008 Profiler */
/* Date: 03/25/2009 11:43:51 PM */
/****************************************************/


-- Create a queue
DECLARE @rc int
DECLARE @TraceID int
DECLARE @maxfilesize bigint
SET @maxfilesize = 5

-- Please replace the text InsertFileNameHere, with an appropriate
-- file name prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the file name automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share.

EXEC @rc = sp_trace_create @TraceID output, 0,
N'InsertFileNameHere', @maxfilesize, NULL
if (@rc != 0) goto error
-- Client-side file and table cannot be scripted

-- Set the events
DECLARE @on bit
SET @on = 1
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

-- Set the filters
DECLARE @intfilter int
DECLARE @bigintfilter bigint

EXEC sp_trace_setfilter @TraceID, 10, 0, 7,
N'SQL Server Profiler - c97955a1-dbf3-4cc8-acc0-4606f7800ab7'
-- Set the trace status to start
EXEC sp_trace_setstatus @TraceID, 1

-- display trace ID for future references
SELECT TraceID=@TraceID
GOTO finish

error:
SELECT ErrorCode=@rc

finish:
GO

Other -----------------
- BizTalk 2009 : Host Integration Server 2009 - Security
- Microsoft Dynamics CRM 2011 : Attaching Files to Accounts and Contacts
- Upgrading to SharePoint 2010 : Performing Post-Upgrade Configurations
- Upgrading to SharePoint 2010 : Performing an In-Place Upgrade (part 2) - Configuring the In-Place Upgrade
- Upgrading to SharePoint 2010 : Performing an In-Place Upgrade (part 1) - Installing the In-Place Upgrade
- Upgrading to SharePoint 2010 : Performing a Database Attach Upgrade
- SQL Server 2008 : Monitoring Your Server - Leveraging the SQL Server Profiler
- SQL Server 2008 : Monitoring Your Server - Monitoring Disk IO
- Troubleshooting Exchange Server 2003 Server Migration and Interoperability (part 2) - Using the Netdiag and Dcdiag Command-Line Utilities
- Troubleshooting Exchange Server 2003 Server Migration and Interoperability (part 1)
 
 
Top 10
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
- Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
- Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
programming4us programming4us
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 windows Phone 7 windows Phone 8
programming4us programming4us
 
programming4us
Natural Miscarriage
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Game Trailer