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. Thanx Aman
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='' />]<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
