A few questions regarding Server Side Tracing

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » A few questions regarding Server Side Tracing...

A few questions regarding Server Side Tracing

Rate Topic

Display Mode

Topic Options

Author

Message

Andrew Miller-489894

Posted Wednesday, October 31, 2012 7:50 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, June 13, 2013 8:03 AM
Points: 59, Visits: 304

I've recently started working at a new company and they have server side tracing setup. I've read thru the stairways series on SSC and had a few remaining questions.

1. After a trace is setup and active, does the trace need to be restared if the SQL Server is restarted or does the trace start automatically with the Server once it is setup and set to active? I understand that if I have to, then I can use a SQL Job to accomplish this.

2. With regards to setting Max File Size and Max Files for Rollover, does anyone have a good method to estimate what these settings should be to capture 1.5 days of data. I understand that depending on what I'm including in the trace, my trace will be larger or smaller. I'm thinking that I'll just need to take a sample for a few hours and calulate based on that but was wondering if anyone had a better idea.

Thanks In Advance For Any Suggestions

Post #1379313

George M Parker

Posted Wednesday, October 31, 2012 7:57 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 2:44 PM
Points: 526, Visits: 1,007

1. After a trace is setup and active, does the trace need to be restared if the SQL Server is restarted or does the trace start automatically with the Server once it is setup and set to active? I understand that if I have to, then I can use a SQL Job to accomplish this.

The trace will only restart if it is designed to. If the code is written in such a way as to only use a date in the filename, then the subsequent restart will fail if the file already exists. You could add the stored procedure that creates the trace to the SQL Server startup process to make sure it starts when SQL Server starts.

2. With regards to setting Max File Size and Max Files for Rollover, does anyone have a good method to estimate what these settings should be to capture 1.5 days of data. I understand that depending on what I'm including in the trace, my trace will be larger or smaller. I'm thinking that I'll just need to take a sample for a few hours and calulate based on that but was wondering if anyone had a better idea.

This one is difficult to answer because it really depends on what is included in the trace and how much activity occurs on the server. Our server side trace uses 5 MB file sizes with unlimited rollovers. It is run through a scheduled job that kicks off at 5:00 AM and has a specific stop time 12 hours later.

Post #1379321

GSquared

Posted Wednesday, October 31, 2012 8:15 AM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:45 PM
Points: 15,442, Visits: 9,572

I generally use a proc in the master database, set to run on startup, that will restart the trace. Use a dynamic name based on the date and time, to avoid file-name collision. Makes it more difficult to query the trace files later, and old files with a different name won't be automatically recycled, but it at least keeps the trace running through a restart.

On the data volume, I've actually set up a daily job that checks the oldest data in the trace, vs when the trace was originally started (logged in a table), and if the oldest data is younger than the desired retention-period, and the start-date is older than that, then stop the trace, automatically add a file to the allowed file count, and re-start it, logging the change in a configuration table. Then, if it needs to be restarted (for a server restart or whatever), it will automatically use the increased number of files. It will do this daily till it finds the data is hitting the retention-period target. Another piece of it checks against a max-retention, and reduces the number of files if it needs to.

Same job also monitors disk space used by the files and raises an alert it that gets excessive, or if the disk they're being written to goes below a threshhold of free space.

Any changes, restarts, etc., send an alert e-mail to the DBA, to review what was done. That way you can adjust threshholds if you find that every day, it adds a file to the count, removes a file the next day, then adds a file the day after that, then removes one, then adds one, etc., or anything like that.

It's INSANELY complex code, and I suggest not going that far. Especially since Microsoft suggests not using traces at all these days, because you should be using Extended Events instead. (Per MSDN, they'll remove traces from a future version of SQL Server, but they haven't said yet how far off that is, so far as I know. Means it's at least 3 versions away, if I remember the rules for that kind of thing correctly.)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #1379333

Andrew Miller-489894

Posted Wednesday, October 31, 2012 10:57 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, June 13, 2013 8:03 AM
Points: 59, Visits: 304

Thanks for the replies guys.

Post #1379441

Andrew Miller-489894

Posted Wednesday, October 31, 2012 11:02 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, June 13, 2013 8:03 AM
Points: 59, Visits: 304

How do you setup unlimited rollovers?

Post #1379448

George M Parker

Posted Wednesday, October 31, 2012 11:33 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 2:44 PM
Points: 526, Visits: 1,007

Andrew Miller-489894 (10/31/2012)

How do you setup unlimited rollovers?

There are two parameters to the sp_trace_create stored procedure that control this @options and @filecount

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

[ @options= ] option_value
Specifies the options set for the trace. option_value is int, with no default. Users may choose a combination of these options by specifying the sum value of options picked. For example, to turn on both the options TRACE_FILE_ROLLOVER and SHUTDOWN_ON_ERROR, specify 6 for option_value.

Setting this to two "@options = 2" enables trace file rollover. That is what we do.

[ @filecount= ] 'max_rollover_files'
Specifies the maximum number or trace files to be maintained with the same base filename. max_rollover_files is int, greater than one. This parameter is valid only if the TRACE_FILE_ROLLOVER option is specified. When max_rollover_files is specified, SQL Server tries to maintain no more than max_rollover_files trace files by deleting the oldest trace file before opening a new trace file. SQL Server tracks the age of trace files by appending a number to the base file name.

We do not set this value and therefore SQL Server does not implement a maximum number of file rollovers.

Post #1379461

« Prev Topic | Next Topic »

Permissions