|
|
|
Old Hand
      
Group: General Forum Members
Last Login: 2 days ago @ 8:04 AM
Points: 375,
Visits: 714
|
|
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.
|
|
|
|
|
Old Hand
      
Group: General Forum Members
Last Login: 2 days ago @ 8:04 AM
Points: 375,
Visits: 714
|
|
Lowell (2/10/2010)
Thanks for sharing your query. This is a keeper.
|
|
|
|
|
SSChasing Mays
      
Group: General Forum Members
Last Login: Monday, February 18, 2013 3:22 PM
Points: 626,
Visits: 835
|
|
|
|
|
|
Old Hand
      
Group: General Forum Members
Last Login: 2 days ago @ 8:04 AM
Points: 375,
Visits: 714
|
|
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.
|
|
|
|
|
SSChasing Mays
      
Group: General Forum Members
Last Login: Monday, February 18, 2013 3:22 PM
Points: 626,
Visits: 835
|
|
|
|
|
|
SSChampion
        
Group: General Forum Members
Last Login: Today @ 9:24 AM
Points: 11,791,
Visits: 28,073
|
|
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
|
|
|
|
|
SSChasing Mays
      
Group: General Forum Members
Last Login: Monday, February 18, 2013 3:22 PM
Points: 626,
Visits: 835
|
|
|
|
|
|
Old Hand
      
Group: General Forum Members
Last Login: 2 days ago @ 8:04 AM
Points: 375,
Visits: 714
|
|
Lowell (2/10/2010) OK here's my prototype for scripting out the traces; Nice job!
|
|
|
|
|
SSC Veteran
      
Group: General Forum Members
Last Login: Yesterday @ 11:57 AM
Points: 267,
Visits: 658
|
|
|
|
|
|
SSChampion
        
Group: General Forum Members
Last Login: Today @ 9:24 AM
Points: 11,791,
Visits: 28,073
|
|
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
|
|
|
|