Keeping an Eye on Server Side Traces in SQL Server
Just a quick snippet, thought I'd share a method of keeping an eye on server-side traces.
When you've set up your SST and it's running successfully, you will often use something like:
SELECT * FROM sys.traces WHERE id = [your trace id];
This renders a whole load of useful information - id, path, stop time, etc. But there's a couple of noticeable features which could be handy. Sometimes I'd like to know a) how long the trace has left to run and b) how large the file size will get.
We can work out these additional two parameters with some simple mathematics.
So, first - how long left. Well, we need to work out the difference between the current time and the planned end time, and display the difference. Nice and easy.
[SELECT...] DATEDIFF ( minute, GETDATE(), stop_time ) [minutes_remaining]
The second requirement is a little trickier. We want to know how large the file size will get. We can work this out dynamically by measuring a) how much time has passed so far since the trace was started, b) how large the file size is now. We then divide b) by a) to get an estimation of file growth per minute. We then multiply by the number of minutes the trace will run for.
Note this method isn't foolproof since for a start the file growth happens in intervals, not continuously, and that file growth won't be linear under a skewed load. However, it's reasonably accurate.
First, work out how much time has passed in minutes, CAST to FLOAT for greater accuracy.
CAST(( DATEDIFF (minute, start_time, stop_time ) - DATEDIFF ( minute, GETDATE(), stop_time ) AS FLOAT )
Now include the division:
file_position / CAST(( DATEDIFF (minute, start_time, stop_time ) - DATEDIFF ( minute, GETDATE(), stop_time ) AS FLOAT )
This is the estimated growth per minute.
Now multiply by the number of minutes in the trace interval (difference between start and end times):
file_position / CAST(( DATEDIFF (minute, start_time, stop_time ) - DATEDIFF ( minute, GETDATE(), stop_time ) AS FLOAT ) * DATEDIFF (minute, start_time, stop_time )
Now round off to 2 d.p for cosmetic neatness:
ROUND((file_position / CAST(( DATEDIFF (minute, start_time, stop_time ) - DATEDIFF ( minute, GETDATE(), stop_time ) AS FLOAT ) * DATEDIFF (minute, start_time, stop_time )),2)
Now alias it for a column name, combine with our earlier SELECT query for the minutes remaining, and some other useful info from the sys.traces view:
SELECT id, path, stop_time, file_position, last_event_time, event_count,
DATEDIFF ( minute, GETDATE(), stop_time ) [minutes_remaining],
ROUND((file_position /
CAST(( DATEDIFF minute, start_time, stop_time )
- DATEDIFF ( minute, GETDATE(), stop_time )) AS FLOAT )
* DATEDIFF (minute, start_time, stop_time )),2)
[forecast_final_size]
FROM sys.traces
WHERE id = -- insert your id # here
Which yields a result like the following:
As you can see, this gives us extra, useful information that's easy to refresh with F5 (or put into a scheduled job and output elsewhere, or similar) that can allow us to avoid problems like runaway trace file sizes - and stop us forgetting about traces.
