February 8, 2007 at 1:21 am
SQL Server 2005 has an always running trace - called "default trace". All object drop/alter/creation is audited (among other things). It keeps the history in up to 5 separate trace files with a limit of 20 MB per file. Since on every SQL server restart a new file is created the history is limited also by the last 5 SQL Server restarts.
QUESTION: How I kan change it to have more files or disk space because I want to have more history back and want to have 10 trc files.
Thanks.
/Semko
February 12, 2007 at 8:00 am
This was removed by the editor as SPAM
February 12, 2007 at 10:25 am
If there is a hack to change the default auditing file size or number, I haven't found it. However, you can certainly define your own trace. That's what I have done. You can totally roll your own or you can use sqldiag.exe, or you can even use Event Tracing for Windows (ETW)
Here's a pretty good discussion of setting up a trace: http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
Here's a discussion of SQL ETW: http://blogs.msdn.com/sqlqueryprocessing/comments/1065974.aspx
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
February 14, 2007 at 5:12 am
Thanks, but the trace I want is the one that I can see in the "Summery/Report/Schema changes history".
Last week a developer complained that one SP disapeared and I want to see who droped it and when.
In "Schema changes history" report you kan get info back which are in default trace and because you can have just 5 files you can not see longer back. More info in first message.
If I can change some where param to be able to have more files it should help me to get more info back.
I still have the same QUESTION: How I kan change it to have more files or disk space because I want to have more history back and want to have 10 trc files.
Thanks a lot.
/Semko
February 14, 2007 at 7:39 am
Have you considered using DDL triggers instead?
I use the one below, it saves any DDL statements in a table and I have a job that every morning sends me a report of what changes have happened in the last 24 hours. Please use this on a test server first so you know what the implications are for your system, among the big gotchas is security on the table it stores the values in. I have the following trigger on all databases on my production server.
CREATE
TRIGGER [trg_DDL_log]
ON
database
FOR
DDL_DATABASE_LEVEL_EVENTS
AS
set
ansi_padding on
DECLARE
@data XML
SET
@data = EVENTDATA()
INSERT
db_admin.dbo.ddl_log
(PostTime, DB_User,IsSA, DBName, ObjectName, Event, TSQL)
VALUES
(GETDATE(),
CONVERT(nvarchar(100), SYSTEM_USER),
IS_SRVROLEMEMBER ('sysadmin'),
.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(30)'),
.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(30)'),
.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)') ) ;
February 14, 2007 at 9:03 am
Thanks, but this is still a work around.
Do someone know if it is possible to change the amount of "default trace" files because I want to have more history back in report "Summery/Report/Schema changes history" and want to have 10 trc files
Thanks.
/Semko
February 14, 2007 at 9:51 am
I don't believe there is a way to change the setting for the default trace. However, you can turn it off and then define your own custom trace. This would allow you to define how many files and how big the files can be, as well as the events etc...
Or, if you really wanted to keep the report functionality that exists with the default trace, just define an additional trace.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
February 15, 2007 at 12:59 am
Thanks a lot for your help I will be back with info if I find something.
Best Regards.
/Semko
October 30, 2008 at 2:51 am
Hi Semko - Do you have any updates? I've been googling for the solution but got no good solution. The problem with defining an own trace is:
1. If SQL Server restarts, the trace should be manually started
2. The numbering of trace file is not dependent on any previous file. Ex. if I currently have the ff. logs :
log_113, log_114, log_115
then I need to manually create a new log (log_116) for me to see the whole audit trail.
This is done automatically by the default trace. Except that it deletes the '6th' audit log.
October 30, 2008 at 3:00 am
Hi Cookie,
You could create your trace and put it into a scheduled job. In the Schedule tab of the job you can drop down the box just beneath the job name and choose the option to run it whenever the SQL Agent starts up.
I don't know if you can change the retention of logs - but you could set up a DOS copy job to move the files somewhere else.
Cath
October 30, 2008 at 3:50 am
The default trace is hard coded at 5 files. The only thing you can do with it is disable it.
I would suggest you set up your own trace. Put the script to start the trace into a stored proc in master and make it a startup procedure. SQL will then run that any time the service starts. You can also limit the trace to exactly what events you want to see, rather than the defaults.
For monitoring schema changes, DDL triggers are probably best. There's less overhead than for the trace and in the trigger you have the option to roll back the change.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 31, 2008 at 12:57 am
Thanks a lot for yours help. I did not find any solution. I vill crate user trace.
Best Regards.
/Semko
October 1, 2009 at 7:55 am
I found this code. i think it will help you 🙂
@maxfilesize is file size
DECLARE @TraceId int
DECLARE @maxfilesize bigint
SET @maxfilesize = 25
EXEC sp_trace_create
@TraceId OUTPUT,
@options = 8,
@tracefile = NULL,
@maxfilesize = @maxfilesize
EXEC sp_trace_setstatus @TraceId, 1
October 22, 2009 at 12:22 am
Hallo ozgur, I have created a trace on same way.
Best regards.
/Semko
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply