Continually running SQL Trace

Question

Post reply

Continually running SQL Trace

rama.mathanmohan

SSC Veteran

Points: 298
More actions
December 9, 2012 at 1:18 pm

#267895

Hi,
I am planning to run a server side trace on server. I am bit worried about the trace file growing and consuming all the C drive. I like to set a maximum size for this trace file and like old events to drop off in a FIFO manner. Can this be done? Please advise how to achieve this ?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 1

Do you have any reason why you want to run these traces all the time? What do you want to achieve ? What's your task?

***The first step is always the hardest *******

rama.mathanmohan SSC Veteran Points: 298 More actions · Answer 2

Basically, for performance troubleshooting reasons we need to capture certain TSQL command from the Dynamics NAV2013 application, which sends the end user information as comments to the SQL server.

So I am planning to run a server side trace to capture this, applying the necessary filter. I am also planning to read this trace into a SQL table regularly. I considered using extended events but the events I am after is only fully supported in SQL2013. So this server side trace solution is only for SQL2008.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

rama.mathanmohan (12/10/2012)
Basically, for performance troubleshooting reasons we need to capture certain TSQL command from the Dynamics NAV2013 application, which sends the end user information as comments to the SQL server.

always try to put some filters to minimize the logging plus space usage. in your care you can set a textdata filter for that TSQL command , it will give you all required information related with that

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

rama.mathanmohan SSC Veteran Points: 298 More actions · Answer 4

Hi Bhuvnesh,

Thanks for your response. I am after a way in which you configure the server side trace so that it drops the old entries in a FIFO (first in first out) basis. Do you know how to do it ? Any script will be very handy.

e4d4 SSCertifiable Points: 5777 More actions · Answer 5

When you choose "save to file" in profiler you have options "Enable file rollover" and "Set maximum file size", set them for your needs, set any additional parameters, choose only these events in which you interested in, set proper filters. Run and immediately stop trace choose "File->Export->Script Trace Definition" and you have script to set server trace with rollover file.

rama.mathanmohan SSC Veteran Points: 298 More actions · Answer 6

Is the rollover option will limit the size of the files used in total with out stopping the trace from collecting data ? Please note, Since this is an "unmanned process" I am trying to ensure it will not consume the C drive eventually. Can you please confirm this.

e4d4 SSCertifiable Points: 5777 More actions · Answer 7

I forgot, you must then add @filecount options to sp_trace_create in script generated by Profiler,

explanation and examples here: http://www.sqlservercentral.com/articles/SQL+Trace/71841/

http://msdn.microsoft.com/en-us/library/ms190362.aspx

Lowell SSC Guru Points: 323518 More actions · Answer 8

rama.mathanmohan (12/10/2012)
Is the rollover option will limit the size of the files used in total with out stopping the trace from collecting data ? Please note, Since this is an "unmanned process" I am trying to ensure it will not consume the C drive eventually. Can you please confirm this.

yes, when the option TRACE_FILE_ROLLOVER is true, it limits the size of data captured,and will not stop the trace.

A specific example:

if you set the rollover option & number of files, then the oldest data will be pushed off in favor of the new data;

this exampel would keep 500 meg of data.

declare @path nvarchar(256)

declare @traceidout int

declare @maxfilesize bigint

declare @maxRolloverFiles int

declare @on bit

set @on = 1

set @maxRolloverFiles = 10 --10 files in total

set @maxfilesize = 50 --meg so we are keeping the "latest" 500 meg of changes(10 x 50M)

--we want the current trace folder

--ie c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG

SELECT @path = REVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),256)) FROM sys.traces WHERE is_default = 1

SET @path = @path + N'MyDMLtrace2' --system appends .trc automatically for the filename

exec sp_trace_create @traceid = @traceidout output,

@options = 2, -- TRACE_FILE_ROLLOVER = TRUE, SHUTDOWN_ON_ERROR = FALSE

@tracefile = @path,

@maxfilesize = @maxfilesize,

@stoptime = NULL,

@filecount = @maxRolloverFiles

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!