Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Reverse Engineer the Current Settings for sp_trace_create


How to Reverse Engineer the Current Settings for sp_trace_create

Author
Message
shew
shew
SSC-Addicted
SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)

Group: General Forum Members
Points: 418 Visits: 929
SQLBOT (2/10/2010)
How often are tape backups running?


Actually, this is the "root cause" of the problem. [GRUMBLE]It depends on who you ask.[/GRUMBLE] This is a locked down shop in which the DBAs do not have access to the backup configuration. We are told that backups occur every day. However, when I need to chase down some autodeleted files, the ones I need never made it to the backup tape. "Oh, there was an issue..." Crazy I would like to make enough rollover files to survive a few days of backup "issues." Satisfied And, yes, this sadly impacts the SQL Server backups that we write to disk.



shew
shew
SSC-Addicted
SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)

Group: General Forum Members
Points: 418 Visits: 929
Lowell (2/10/2010)


Thanks for sharing your query. This is a keeper.



SQLBOT
SQLBOT
SSChasing Mays
SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)

Group: General Forum Members
Points: 660 Visits: 836
that's brutal.

If it's a big concern, I'd not hesitate to robocopy the files to a fileserver and let them back it up from there.

Cheers!

Craig Outcalt



Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258
shew
shew
SSC-Addicted
SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)

Group: General Forum Members
Points: 418 Visits: 929
SQLBOT (2/10/2010)
that's brutal.

If it's a big concern, I'd not hesitate to robocopy the files to a fileserver and let them back it up from there.


Agreed. We copied needed files elsewhere at other shops, but this shop does not allow any file shares accessible from the server.



SQLBOT
SQLBOT
SSChasing Mays
SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)

Group: General Forum Members
Points: 660 Visits: 836
Nice work, Lowell.

post it back here if you ever finish it up to write the trace start script.
It's more useful the way it is, though. You can acually see the event name.

Craig Outcalt



Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14973 Visits: 38992
OK here's my prototype for scripting out the traces;

I created a procedure where you have to pass the trace id, and it seems to script everything out .
note this assumes that you are scripting something out to create it again as NEW; so for example, if you script out the default trace, and run the script, it creates a NEW trace (ie traceid 2,3, etc) that happens to have the same settings...but at least you could then tweak the code before executing it.

it worked just fine for my two traces i have, and the part i thougth was critical was to name what the commands are doing as a comment;


you might see stuff like this :

exec sp_trace_setevent @traceidout,55,6,@on --Hash Warning,NTUserName
exec sp_trace_setevent @traceidout,55,7,@on --Hash Warning,NTDomainName

and for a filter:
exec sp_trace_setfilter traceidout,10,0,6, N'SQLT%' -- AND ApplicationName LIKE SQLT%
exec sp_trace_setfilter traceidout,10,0,6, N'MS%' -- AND ApplicationName LIKE MS%



could you please play with it a bit and give me some feedback; it seems pretty close, i fixed a few things like default values if a trace haad some HULLS, but i only had three traces to test against.


--select * from sys.traces
--drop procedure sp_ScriptAnyTrace
--sp_ScriptAnyTrace 3
CREATE PROCEDURE [dbo].[sp_ScriptAnyTrace](@traceid int)
AS
BEGIN
SET NOCOUNT ON
declare @Results TABLE(ResultsId int identity(1,1),ResultsText varchar(max))
--i thought about using a results table, decided i wanted a single varchar max string instead,
--then had to revernt back to a results table because of concat truncation issues with implicit conversions to varchar(8000)

IF NOT EXISTS(SELECT 1 FROM sys.traces where id = @traceid)
BEGIN
insert into @Results(ResultsText)
SELECT 'No trace exists with ID = ' + convert(varchar,@traceid)
SELECT * FROM @Results order by ResultsID
RETURN
END
insert into @Results(ResultsText)
SELECT '--declare variables for parameterizing the command ' UNION ALL
SELECT 'declare @traceidout int ' UNION ALL
SELECT 'declare @options int ' UNION ALL
SELECT 'declare @path nvarchar(256) ' UNION ALL
SELECT 'declare @maxfilesize bigint ' UNION ALL
SELECT 'declare @maxRolloverFiles int ' UNION ALL
SELECT 'declare @stoptime datetime ' UNION ALL
SELECT 'declare @on bit ' UNION ALL
SELECT ' ' UNION ALL
SELECT 'set @on = 1 --for scripting purposes, I think its better Always setting a script to start the trace after creation.'
--script the settings from sys.traces
insert into @Results(ResultsText)
SELECT 'set @maxfilesize = ' + CASE WHEN max_size IS NULL THEN '20' ELSE CONVERT(varchar,max_size) END + ' --size in MB ' from sys.traces WHERE id =@traceid
insert into @Results(ResultsText)
SELECT 'set @maxRolloverFiles = ' + CASE WHEN max_files IS NULL THEN ' 5 ' ELSE CONVERT(varchar,max_files) END + ' --number of files; ie if 5 files, start rewriting on rollover ' from sys.traces WHERE id =@traceid
insert into @Results(ResultsText)
SELECT 'set @stoptime = ' + CASE WHEN stop_time IS NULL THEN 'NULL' ELSE '''' + CONVERT(varchar(40),stop_time,121)+ '''' END + ' -- null if never ends, else a specific date ' from sys.traces WHERE id =@traceid
insert into @Results(ResultsText)
SELECT 'set @options = 2' --rollover, ignore all other settings.
insert into @Results(ResultsText)
SELECT 'set @path = ''' + CASE WHEN path IS NULL THEN 'mytrace' ELSE LEFT(path,LEN(path) - 4) END + '''' + ' -- the trace adds ".trc" to the pathname, so avoiad "name.trc.trc" by removing it for scripting ' from sys.traces WHERE id =@traceid
insert into @Results(ResultsText) SELECT ''

--sp_trace_create [ @traceid = ] trace_id OUTPUT
--, [ @options = ] option_value
--, [ @tracefile = ] 'trace_file'
--[ , [ @maxfilesize = ] max_file_size ]
--[ , [ @stoptime = ] 'stop_time' ]
--[ , [ @filecount = ] 'max_rollover_files' ]
insert into @Results(ResultsText) SELECT ' --create the trace '
insert into @Results(ResultsText)
SELECT '--create the trace ' UNION ALL
SELECT 'exec sp_trace_create ' UNION ALL
SELECT ' @traceid = @traceidout output, ' UNION ALL
SELECT ' @options = @options, ' UNION ALL
SELECT ' @tracefile = @path, ' UNION ALL
SELECT ' @maxfilesize = @maxfilesize, ' UNION ALL
SELECT ' @stoptime = @stoptime, ' UNION ALL
SELECT ' @filecount = @maxRolloverFiles '
--details
insert into @Results(ResultsText) SELECT ''
insert into @Results(ResultsText) SELECT ' --for the Event Every SQL statement completed, capture columns of accessible data '
--exec sp_trace_setevent @traceidout, 12, 6, @on --SQL:BatchCompleted,NTUserName
insert into @Results(ResultsText)
SELECT ' exec sp_trace_setevent @traceidout,' + CONVERT(varchar(max),X.eventid) + ',' + CONVERT(varchar(max),X.columnid) + ',@on --'
+ E.Name + ',' + V.name
from ::fn_trace_geteventinfo(1) AS X
INNER JOIN sys.trace_events E ON X.eventid = E.trace_event_id
INNER JOIN sys.trace_columns V ON X.columnid = V.trace_column_id
insert into @Results(ResultsText) SELECT '--filters'
insert into @Results(ResultsText) SELECT ''
insert into @Results(ResultsText)
select ' exec sp_trace_setfilter traceidout' + ','
+ CONVERT(varchar,X.columnid) + ','
+ CONVERT(varchar,logical_operator) + ','
+ CONVERT(varchar,comparison_operator) + ','
+ ' N''' + CONVERT(varchar(8000),value) + ''' '
+ ' -- ' + CASE WHEN logical_operator = 0 THEN ' AND ' ELSE ' OR ' END + V.name
+ CASE
WHEN comparison_operator = 0 THEN ' = '
WHEN comparison_operator = 1 THEN ' <> '
WHEN comparison_operator = 2 THEN ' > '
WHEN comparison_operator = 3 THEN ' < '
WHEN comparison_operator = 4 THEN ' >= '
WHEN comparison_operator = 5 THEN ' <= '
WHEN comparison_operator = 6 THEN ' LIKE '
WHEN comparison_operator = 7 THEN ' NOT LIKE '
END
+ CONVERT(varchar(8000),value)
from
::fn_trace_getfilterinfo(@traceid) X
INNER JOIN sys.trace_columns V
ON X.columnid = V.trace_column_id

insert into @Results(ResultsText) SELECT '---final step'
insert into @Results(ResultsText) SELECT ''
insert into @Results(ResultsText) SELECT '--turn on the trace '
insert into @Results(ResultsText) SELECT ' exec sp_trace_setstatus @traceidout, 1 ---start trace '
insert into @Results(ResultsText) SELECT ' --exec sp_trace_setstatus TRACEID, 0 ---stop trace, you must know the traceid to stop it '
insert into @Results(ResultsText) SELECT ' --exec sp_trace_setstatus TRACEID, 2 ---close trace you must know the traceid to delete it '

SELECT * FROM @Results order by ResultsID
END



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLBOT
SQLBOT
SSChasing Mays
SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)

Group: General Forum Members
Points: 660 Visits: 836
Work of art.

it appears to work better than the profiler's script trace definition.

you're one smart dog, Lowell!

Craig Outcalt



Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258
shew
shew
SSC-Addicted
SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)

Group: General Forum Members
Points: 418 Visits: 929
Lowell (2/10/2010)
OK here's my prototype for scripting out the traces;

Nice job!



Mike Good
Mike Good
SSC-Addicted
SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)

Group: General Forum Members
Points: 405 Visits: 1020
Thanks, works great!



Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14973 Visits: 38992
Mike Good (11/30/2010)
Thanks, works great!


thank you for the feedback! glad it helped you out!

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search