SQL Server Logs

Question

SQL Server Logs

Sonia Ferreira-370935

Right there with Babe

Points: 776
More actions
October 24, 2006 at 6:07 am

#62454

Hi There,
How do i shrink/recycle my SQL server log files in enterprise manager ? They're growing at an alarming rate.
Can i delete old log files ?
Whats the longest i should be keeping these files for before i axe them ?
Thanks,
Sonia

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

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

Clive Strong SSChampion Points: 11222 More actions · Answer 1

Do you run regular transaction log backups? This is the easiest way. This will keep the logs a managable size.

My recommendation is to backup your transaction log. Once you have done that, use DBCC SHRINKFILE to shrink your log file down to a size you are happy with.

Sonia Ferreira-370935 Right there with Babe Points: 776 More actions · Answer 2

Hi Clive,

Yes i do run regular transaction log backups - daily actually. Now the logs files i'm refering to are the SQl Server logs files found under the managment tree option in enterprise manager (2000). Are we refering to teh same thing here ?

If i sound like a noob ? Well um thats cuz i am one

So now how would i use the DBCC Shrinkfile to shrink the log file ? What syntax would i use in TSQL ?

Ed Mlynar Mr or Mrs. 500 Points: 593 More actions · Answer 3

If you run:

DBCC ERRORLOG

OR

exec master..sp_cycle_errorlog {this calls DBCC ERRORLOG}

this will create a new log file. You may want to consider scheduling a job that does this regularly.

The text version of the log files are found in \Program Files\Microsoft SQL Server\MSSQL\LOG\. If you are going to schedule the truncating of the logs, then you may want to back up the old log files. By default, only the previous six log files are kept.

EdM

Clive Strong SSChampion Points: 11222 More actions · Answer 4

I see! Sorry, my mistake. I was refering to transaction log files!

Ed has described how to recycle your error log. It is normally restarted when the SQL Server service is started.

However, if as you say, the current log is growing at an alarming rate, I would be interested to know what is in there. Are you getting lots of errors etc? We have a pretty big database and the error log does not grow that big at all, unless of course we get a whole bunch of errors, then it can explode.

Balmukund SSCertifiable Points: 6648 More actions · Answer 5

what the the contetnt of the log file. please paste the sample of it (the most reperative part)

Are you are doing audit login/logout event capture?

Yes you can delete old file.

Sonia Ferreira-370935 Right there with Babe Points: 776 More actions · Answer 6

No Problem

It doesn't look like its getting loads of errors, mostly audit trails of logons and stuff like that and also your normal SQL server actions. I can only open 2 of the archive error logs cuz the others are too bloody large to open and they cause the management console to hang. We have MOM and SMS setup on the same sql server which i think might have something to do with it ? Also my co. has never really had a sql admin. so these basic admin functions have never really been completed.

Thanks,

Sonia

Sonia Ferreira-370935 Right there with Babe Points: 776 More actions · Answer 7

Sonia Ferreira-370935

Right there with Babe

Points: 776

October 25, 2006 at 12:00 am

#667767

Thanks for the info Ed - will have give this a try

Clive Strong SSChampion Points: 11222 More actions · Answer 8

You can always copy them off and read them manually as they are just text files.

Look in the LOG folder of your SQL Server installation folder. It will be ERRORLOG (current log) or ERRORLOG.x (other logs)

Michael Roynan Old Hand Points: 357 More actions · Answer 9

You may also want to check the audit level for the server. If you don't really need to capture all logins (maybe just failed logins), that would reduce the size of the error logs. In EM, right click on the server, and go to Properties, then the Security tab.