How to increase amount of log.trc files in SQL Server 2005

Question

How to increase amount of log.trc files in SQL Server 2005

Semko Redzic

SSCommitted

Points: 1531
More actions
February 8, 2007 at 1:21 am

#116093

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

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

DCPeterson SSCoach Points: 15266 More actions · Answer 1

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

*****************/

Semko Redzic SSCommitted Points: 1531 More actions · Answer 2

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

Anders Pedersen SSChampion Points: 11410 More actions · Answer 3

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'),

@data

.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(30)'),

@data

.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(30)'),

@data

.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),

@data

.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)') ) ;

Semko Redzic SSCommitted Points: 1531 More actions · Answer 4

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

DCPeterson SSCoach Points: 15266 More actions · Answer 5

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

*****************/

Semko Redzic SSCommitted Points: 1531 More actions · Answer 6

Thanks a lot for your help I will be back with info if I find something.

Best Regards.

/Semko

cookie_vanilla26 Valued Member Points: 67 More actions · Answer 7

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.

Cath Trimble SSCarpal Tunnel Points: 4201 More actions · Answer 8

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Semko Redzic SSCommitted Points: 1531 More actions · Answer 10

Thanks a lot for yours help. I did not find any solution. I vill crate user trace.

Best Regards.

/Semko

ozgur2143 Newbie Points: 9 More actions · Answer 11

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

Semko Redzic SSCommitted Points: 1531 More actions · Answer 12

Hallo ozgur, I have created a trace on same way.

Best regards.

/Semko