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

How to Reverse Engineer the Current Settings for sp_trace_create Expand / Collapse
Author
Message
Posted Wednesday, February 10, 2010 8:30 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 04, 2014 12:13 PM
Points: 389, Visits: 788
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..." I would like to make enough rollover files to survive a few days of backup "issues." And, yes, this sadly impacts the SQL Server backups that we write to disk.



Post #863302
Posted Wednesday, February 10, 2010 8:32 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 04, 2014 12:13 PM
Points: 389, Visits: 788
Lowell (2/10/2010)


Thanks for sharing your query. This is a keeper.



Post #863303
Posted Wednesday, February 10, 2010 8:32 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, January 03, 2014 10:59 AM
Points: 626, 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
Post #863304
Posted Wednesday, February 10, 2010 8:39 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 04, 2014 12:13 PM
Points: 389, Visits: 788
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.



Post #863310
Posted Wednesday, February 10, 2010 8:48 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, January 03, 2014 10:59 AM
Points: 626, 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
Post #863326
Posted Wednesday, February 10, 2010 10:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:12 PM
Points: 12,741, Visits: 31,052
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #863482
Posted Wednesday, February 10, 2010 11:13 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, January 03, 2014 10:59 AM
Points: 626, 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
Post #863492
Posted Wednesday, February 10, 2010 12:04 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 04, 2014 12:13 PM
Points: 389, Visits: 788
Lowell (2/10/2010)
OK here's my prototype for scripting out the traces;

Nice job!



Post #863531
Posted Tuesday, November 30, 2010 11:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 3:42 AM
Points: 275, Visits: 737
Thanks, works great!


Post #1028167
Posted Tuesday, November 30, 2010 12:06 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:12 PM
Points: 12,741, Visits: 31,052
Mike Good (11/30/2010)
Thanks, works great!


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


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1028171
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse