SQL Server Performance


Discussion in 'Contribute Your SQL Server Scripts' started by barozai, Feb 8, 2003.

  1. barozai New Member

    Hi All...
    Let me know if somebody knows about the "Blackbox" of SQL Server.
    How can I use it to trace out DB craches in SQl server.
    Let me know in EM context as well as Query Analyzer.
    BOL doesn't provide enough info about that.


  2. Argyle New Member

    Here is a script for SQL 2000:<br /><pre><br />------------------------<br />DECLARE @returnCodeINT<br />DECLARE @traceIDINT<br /><br />--Specify a blackbox trace. Option value 8 = TRACE_PRODUCE_BLACKBOX. See BOL.<br />EXEC @returnCode = sp_trace_create @traceID OUTPUT, 8<br /><br />PRINT 'Return code: ' + CONVERT(VARCHAR(10),@returnCode)<br />PRINT 'Trace ID: ' + CONVERT(VARCHAR(10),@traceID)<br /><br />--Start the blackbox trace<br />EXEC @returnCode = sp_trace_setstatus @traceID, 1<br /><br />PRINT 'Return code: ' + CONVERT(VARCHAR(10),@returnCode)<br />------------------------<br /></pre><br />If any of the return values are not zero, check BOL for info. Also note that the blackbox.trc file (that you will find in ...MSSQLdatalackbox.trc) will have a size of zero until the SQL Server is stopped or the trace file size exceeds 5MB and starts using blackbox_01.trc. Once blackbox_01.trc also exceeds 5MB it will revert back to blackbox.trc.<br /><br />You can put the above code in a stored procedure and make that procedure start when SQL Server starts. See "sp_procoption" for details. With SQL 7 it is a bit different. You use the command "xp_trace_setqueryhistory". Never used it on SQL 7 though but this article might help:<a target="_blank" href=http://www.sqlmag.com/Articles/Index.cfm?ArticleID=8368>http://www.sqlmag.com/Articles/Index.cfm?ArticleID=8368</a><br /><br /><br /><br />Edit:<br />Duh.. just saw that there is a knowledgebase article about his. Maybe time to replace my own script [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />INF: Stored Procedure to Create a SQL Server 2000 Blackbox Trace<br /<a target="_blank" href=http://support.microsoft.com/default.aspx?scid=KB;en-us;q281671>http://support.microsoft.com/default.aspx?scid=KB;en-us;q281671</a><br /><br />/Argyle
  3. barozai New Member

    Hi Argyle
    Thanks for reply.
    It works well and I got what I want.


Share This Page