February 8, 2010 at 1:50 pm
I inherited an undocumented Production SQL Server 2005 cluster running on Windows Server 2003 from a DBA who is long gone. Apparently, the prior DBA used sp_trace_create to set up a trace that rolls over when the output file reaches its target size.
Is there any way to reverse engineer the settings (i.e., sp_trace_create, sp_trace_setevent, sp_trace_setfilter, sp_trace_setstatus, etc.) that are currently in place so that I can tweek what is being captured?
For example, I would like to increase the number of rollover files so that they hang around on disk long enough to get backed up at night. (Something is autodeleting the files, I'm not sure what. I presume that the deletion comes from a setting used when creating the trace.)
February 8, 2010 at 4:37 pm
Look for a Maintenance Plan that created a job that removes the .trc files:
Management -> Maintenance Plans
SQL Server Agent -> Jobs
If it is a stored procedure that is doing the cleanup, you should find it under the Programmability section of your databases. Granted, you'll have to search it out, hopefully it was named well.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
February 8, 2010 at 6:20 pm
select * from :: fn_trace_getinfo(0)
I'm guessing it's the default trace, but who knows.
the trace settings also include a maximum number of rollover files, which will delete the oldest file automatically.
Good luck
Craig Outcalt
February 10, 2010 at 6:41 am
SQLBOT (2/8/2010)
select * from :: fn_trace_getinfo(0)
The output from this query matches up with the files that are being generated on the server:
traceid property value
----------- ----------- ------------------------------------------
1 1 2
1 2 E:\datafiles\MSSQL.1\MSSQL\LOG\log_607.trc
1 3 20
1 4 NULL
1 5 1
(5 row(s) affected)
The SQL Server documentation for fn_trace_getinfo lists the meaning of the property column as:
1= Trace options. For more information, see @options in sp_trace_create (Transact-SQL).
2 = File name
3 = Max size
4 = Stop time
5 = Current trace status. 0 = stopped. 1 = running.
---------------
>>1= Trace options. For more information, see @options in sp_trace_create (Transact-SQL).
It appears that sp_trace_create allows you to set the number of rollover files, but fn_trace_getinfo does not appear to retrieve this information. Unfortunately, I am not familiar with this aspect of SQL Server 2005. (Most of my experience is on SQL Server 2000.)
Is there a way to test changes to the trace without risking the loss of existing functionality?
Can an existing trace be edited? Or, does it have to be deleted and recreated?
February 10, 2010 at 6:50 am
From your output, only the default trace is running and there is no way to modify the settings.
You CAN turn it off. but why?
The default trace should be running... by default.
:hehe:
EXEC master.dbo.sp_configure 'show advanced options', 0;
GO
EXEC master.dbo.sp_configure 'default trace enabled', 0;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC master.dbo.sp_configure 'show advanced options', 0;
I would only turn it off if there was some sort of extreme disk space problem.
Even then there are better ways to free up disk space.
Thanks
Craig Outcalt
February 10, 2010 at 7:00 am
for me, reverse engineering is to be able to script out any and all traces that exist on the server;
the whole script like this:
--create the trace
exec sp_trace_create @traceidout output, @maxRolloverFiles, @path, @maxfilesize, NULL
--for the Event Every SQL statement completed, capture all 64 columns of accessible data
exec sp_trace_setevent @traceidout, 12, 1, @on --SQL:BatchCompleted,TextData
exec sp_trace_setevent @traceidout, 12, 2, @on --SQL:BatchCompleted,BinaryData
exec sp_trace_setevent @traceidout, 12, 3, @on --SQL:BatchCompleted,DatabaseID
--snip--
--turn on the trace
exec sp_trace_setstatus @traceidout, 1 ---start trace
i looked in detail, and i cannot find the actual events that are set with sp_trace_setevent ; there are lookup tables for the traceeventid to it's description, but not which events were set when the trace was created;
if you sp_helptext sp_trace_setevent , it's an internal statement, so you can't see which table it is sticking the data into, either.
Lowell
February 10, 2010 at 7:19 am
SQLBOT (2/10/2010)
You CAN turn it off. but why?
I must have misspoken somewhere. I do *not* want to turn off the default trace. I just need more rollover files so that they will stay on the server long enough to get backed up, and I am concerned that, if I delete the existing trace to recreate it with more rollover files, the new trace may not capture what the old trace captured. In short, I am inexperienced in this aspect of SQL 2005, and I don't want to mess up existing functionality while trying to tweek it to our needs.
February 10, 2010 at 7:22 am
Lowell (2/10/2010)
for me, reverse engineering is to be able to script out any and all traces that exist on the server;
Yes, that is the kind of output that I would like to generate for the existing trace(s), but I don't see a way to do it either. I hesitate to drop a trace to attempt a change it if I don't have the syntax for the original settings.
February 10, 2010 at 7:50 am
all the default trace's settings can be gleaned from these:
select * from :: fn_trace_getinfo(1)
select * from ::fn_trace_geteventinfo(1)
even still, the only way you can change it is to turn it off and script your own that starts with a startup proc.
I wouldn't over-engineer anything, though.
How often are tape backups running?
Craig Outcalt
February 10, 2010 at 8:12 am
Craig thank you! that was the piece could not find;
i was locked into looking for a sys view that had the joining information...with what you posted I could get the details and build the full reverse engineering thing, that helps a lot:
eventid EventName columnid ColumnName
18 Audit Server Starts And Stops 6 NTUserName
18 Audit Server Starts And Stops 7 NTDomainName
18 Audit Server Starts And Stops 8 HostName
18 Audit Server Starts And Stops 9 ClientProcessID
...
this returns most of the information you might need, give me some time to build the whole thing; this would probably be a nice article for those who needed to reverse engineer a trace.
select
X.eventid,
E.Name as EventName,
X.columnid,
V.name as ColumnName
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
select
traceid,
property,
value,
CASE
WHEN Property = 1 THEN 'Configured trace options'
WHEN Property = 2 THEN 'Trace file name'
WHEN Property = 3 THEN 'Max file size for the *.trc file'
WHEN Property = 4 THEN 'Stop time for the trace session'
WHEN Property = 5 THEN 'Current trace status (1 = On and 0 = Off)'
END
from :: fn_trace_getinfo(1)
Lowell
February 10, 2010 at 8:30 am
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.
February 10, 2010 at 8:32 am
Lowell (2/10/2010)
Thanks for sharing your query. This is a keeper.
February 10, 2010 at 8:32 am
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
February 10, 2010 at 8:39 am
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.
February 10, 2010 at 8:48 am
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
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply