April 5, 2003 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bknight/blackbox.asp
Brian Knight
Free SQL Server Training Webinars
April 10, 2003 at 7:10 am
What about performance impact for this?
Opening a constant trace of my production server doesn't sound right..
April 10, 2003 at 7:29 am
Not too bad. I noticed a 1.5% CPU performance degregation by starting the trace. Keep in mind, even though it's a SQL Trace, it's tracing minimal items.
Brian Knight
http://www.sqlservercentral.com/columnists/bknight
Brian Knight
Free SQL Server Training Webinars
April 10, 2003 at 7:30 am
Normally you don't run traces on a Production server unless you absolutely need to. However, the blck box trace is designed to be saved locally and the data is not sent across the network (network bandwidth is a major concern with traces). You usually only start a black box on a problematic server. The idea is to catch all the queries right up to the point the server fails or memory stalls. Doing this serveral times should give you a clue as to what query/queries ultimately lead to the problem. Then you have a starting point as to testing. In testing you try to recreate the symptoms again intinally. If you are able to then you break the query down to determin what wihin the query may be the cause. Other things to look at are the errors in the trace to see if any will give you an idea of maybe a bad setting or corruption in the Database. But again black box is intended to capture events on a server known to be problematic already and as such adding the trace is no real issue against performance.
April 10, 2003 at 7:58 pm
Great until you realise it only works for SQL Server 2000. Would like to see SQL Server 7.0 equivalent.
April 11, 2003 at 3:22 am
Look up Blockbox in SQL BOL.
Here is one excerpt and I have used it on several occasion with a previous issue on a 7 box.
quote:
Use sp_trace_create with the TRACE_PRODUCE_BLACKBOX option to define a trace that appends trace information to a blackbox.trc file in the \Data directory. Once the trace is started, trace information is recorded in the blackbox.trc file until the size of the file reaches 5 megabytes (MB). The trace then creates another trace file, blackbox_1.trc, and trace information is written to the new file. When the size of blackbox_1.trc reaches 5 MB, the trace reverts to blackbox.trc. Thus, up to 5 MB of trace information is always available.
April 11, 2003 at 10:13 am
I wouldn't necessarily run this on every server, but those that are giving me problems I would have this ready and set it up.
Steve Jones
April 21, 2003 at 9:43 am
Brian,
I was sampling out the code you gave and I didn't create the actual proc, STARTBLACBOX. i only wanted to issue the individual sql command in query analyzer. So i executed the 'declare' and the 'exec_sp_trace_create' line and stopped there. I noticed it did build a trace file in my data directory. I then proceeded to run the EXEC SP_TRACE_SETSTATUS line and it said i needed to declare the @traceid, so i inserted a declare statement before the exec sp_trace_setstatus and then it said that 'Procedure expects parameter '@traceid' of type 'int'.. how do i know what this @traceid value is? I know these questions are real elementary but I obviously missed the boat on this one. I guess what I'm wondering is, are you supposed to run the 2 sp _trace statements together like you did in your sample? Also, how do i stop this trace after it is started. I'm testing this all out on my test server.
Confused,
Juanita
April 21, 2003 at 10:36 am
sp_trace_create has an output parameter to fill that for you. You declare your value then run sp_trace_create to get the output and then you pass to later items.
By default at each server restart it starts at 0 so yours is probably 0. Otherwise when SQL stops it clears the traces and you have to rebuild them.
April 28, 2003 at 5:28 am
We have an intermittent problem on one production server which leads to very slow response times for users. It happens 1-2 times per week so Black Box monitoring looked like a good place to start tracking down the cause.
Problem is that the 5Mb trace files filled every 2 minutes so by the time I got to hear about the slow response from the Help Desk, the data had already been over-written !
I've found that you can create larger trace files using the following SQL code
declare @TraceID int ,@fsize bigint, @fname nvarchar(128)
select @fsize=25, @fname=''
exec sp_trace_create @TraceID output, 8, @fname,@fsize
exec sp_trace_setstatus @traceID, 1
Trace files of 25 Mb gave me enough time to take copies the trace files for investigation at a later time.
May 7, 2003 at 5:36 pm
I had the same problem with not being able to stop the trace. Run this statement and you will get a list of all active traces: "select * from :: fn_trace_getinfo(default)". Then you can determine which trace ids are active and stop & close them using "sp_trace_setstatus [trace_id], [status]".
Edited by - jhkemp on 05/07/2003 5:40:04 PM
May 8, 2003 at 8:46 am
I encountered an extremely intermitant problem that called for a trace. Three times one day and nothing for several weeks. I set up a trace but fired it from PerfMon when my Tempdb exceeded 5 Gigabyte. If you are looking for a specific problem you can start the trace from Perfmon.
June 10, 2003 at 9:46 am
Brian,
Liked your article. I also like your book SQL Server 2000 For Experienced DBAs.
My Blackbox trace alternates between writing to blackbox.trc and blackbox_1.trc. How does this correlate with your advice?
"Make sure that you occasionally go through and delete the old trace file that may've rolled over."
Thanks,
Larry Ansley
Atlanta, GA
Larry Ansley
Atlanta, GA
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply