Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

The SQL Server Black Box Expand / Collapse
Author
Message
Posted Saturday, April 5, 2003 12:00 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Wednesday, June 4, 2014 12:29 PM
Points: 1,931, Visits: 234
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
Post #11125
Posted Thursday, April 10, 2003 7:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 19, 2012 7:19 AM
Points: 23, Visits: 36
What about performance impact for this?
Opening a constant trace of my production server doesn't sound right..




Post #58356
Posted Thursday, April 10, 2003 7:29 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Wednesday, June 4, 2014 12:29 PM
Points: 1,931, Visits: 234
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
bknight@sqlservercentral.com
http://www.sqlservercentral.com/columnists/bknight


Brian Knight
Free SQL Server Training Webinars
Post #58357
Posted Thursday, April 10, 2003 7:30 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Friday, September 26, 2014 11:48 AM
Points: 8,370, Visits: 742
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.




Post #58358
Posted Thursday, April 10, 2003 7:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 18, 2014 4:57 PM
Points: 2, Visits: 18
Great until you realise it only works for SQL Server 2000. Would like to see SQL Server 7.0 equivalent.




Post #58359
Posted Friday, April 11, 2003 3:22 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Friday, September 26, 2014 11:48 AM
Points: 8,370, Visits: 742
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.





Post #58360
Posted Friday, April 11, 2003 10:13 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 7:17 AM
Points: 31,080, Visits: 15,526
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
sjones@sqlservercentral.com
http://www.sqlservercentral.com/columnists/sjones
www.dkranch.net







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #58361
Posted Monday, April 21, 2003 9:43 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 10:54 AM
Points: 413, Visits: 565
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




Post #58362
Posted Monday, April 21, 2003 10:36 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Friday, September 26, 2014 11:48 AM
Points: 8,370, Visits: 742
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.




Post #58363
Posted Monday, April 28, 2003 5:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 28, 2004 7:10 AM
Points: 287, Visits: 1
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.




Post #58364
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse