SQL server Side trace ->Hidden SQL in-Trace...

Too sound outside because of Cracker's!! because today India is celebrating Deepwali/Diwali
(Happy Diwali!!!!!to all).

 

How to Run Server Side Trace in SQL server 2005..

Why the SQL server side trace

+ 

Uses less resources to do the activity.

1. SQL server 2005 –Server Side Trace.

Connect to your SSMS ->go to Tools->SQL server Profiler->you will see below window

Go to File ->New Trace or click on the Gui button called new Trace, you can see below screenshot

Fill which ever template you required or you can create your own template and select the template in the  Use the template .

Iam Using the template ->T-SQL ->check the box of save to file ->Save it which ever Drive needed

b) you can set  maximum file size(MB) as per your requirement (I have selected 100MB).

c)you want to stop the trace at Particular time you can go for Enable trace Stop time.

So after filling the screen as shown below

Next Go to  Event Selection Tab ->By default some of the  Events selection enabled as shown below

If you need any other events you can Play with Show all events and you want to see all th columns of other Events you can play with Show all columns (depends on your requirement)

Here I have selected By default-

Click on Run ->the trace will start ->you can validate by running any query so that you can validate the trace is capturing the data ->so I have ran the quey select * from sys.sysaltfiles and Stoped the trace

Next is generating the Script (here you can generate either SQL 2005 and 2000 also)

Go to File ->Export->Script Trace definition->for SQL server  2005(I have used 2005)-

Click on for SQL server 2005 ->save where ever you needed(extension of the file is .sql)

You will get message after saving-

So now you the Script is ready for the SQL server side trace in my ex-the script is as mentioned below

/****************************************************/

/* Created by: SQL Server Profiler 2005             */

/* Date: 09/25/2010  01:49:27 PM         */

/****************************************************/

-- Create a Queue

declare @rc int

declare @TraceID int

declare @maxfilesize bigint

declare @DateTime datetime

set @DateTime = '2010-09-25 15:27:20.000'

set @maxfilesize = 100

-- Please replace the text InsertFileNameHere, with an appropriate

-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension

-- will be appended to the filename 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, @Datetime

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, 14, 1, @on

exec sp_trace_setevent @TraceID, 14, 14, @on

exec sp_trace_setevent @TraceID, 14, 12, @on

exec sp_trace_setevent @TraceID, 15, 14, @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, 14, @on

exec sp_trace_setevent @TraceID, 11, 2, @on

exec sp_trace_setevent @TraceID, 11, 14, @on

exec sp_trace_setevent @TraceID, 11, 12, @on

exec sp_trace_setevent @TraceID, 13, 12, @on

exec sp_trace_setevent @TraceID, 13, 1, @on

exec sp_trace_setevent @TraceID, 13, 14, @on

-- Set the Filters

declare @intfilter int

declare @bigintfilter bigint

-- 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

>Now you can change the Path where ever the trace needed to store in my script

I have changed the  InsertFileNameHere to C:\Test  Trace\test

Or you can save in the other server also By specifying the UNC Path

Ex =>\\myserver\d$\Testtrace\test

Note =>Make sure that you have all the required accesses to the shared UNC path and also

I have Executed the script

The out come of the script is as shown below-

Validation

Go to the Path where you have given the path to save the trace file in My example

 

Or run the query

select * from sys.traces

2)

Extra

If you want to track for particular database or any thing you  needed you can do in the trace properties

2.You can also stop and start the trace by executing  the sp_trace_setstatus with the your trace ID

Use Book Online—

 1.To stop the profiler trace before its end time has occurred you exec two commands.

One stops the trace and the other closes the trace file.
2.Here are the commands:
a.Execute "select * FROM ::fn_trace_getinfo(default)"
b.Identify the traceid you using the folder name specified when starting the trace.
c.Execute these two commands replacing # with the trace id.
EXEC sp_trace_setstatus @traceid = #, @status = 0; -- Stop/pause Trace
EXEC sp_trace_setstatus @traceid = #, @status = 2; -- Close trace and delete it from the server
3.Verify it has stopped by executing "select * FROM ::fn_trace_getinfo(default)"
4.To view the captured trace files, open them using SQL Server Profiler.
5.Old profiler trace files are not automatically deleted. You will need to delete them your-self when you are finished.

Note =>Even though you run the  Server side trace some of the temp file will create under your

Temporary buffer area usually the path is

                C:\Documents and Settings\Udaya\Local Settings\Temp