October 31, 2012 at 7:50 am
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
October 31, 2012 at 7:57 am
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.
October 31, 2012 at 8:15 am
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
October 31, 2012 at 10:57 am
Thanks for the replies guys.
October 31, 2012 at 11:02 am
How do you setup unlimited rollovers?
October 31, 2012 at 11:33 am
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply