SQL 2000 logfiles in EM

I just noticed that the log files in EM are not rolling on a regular 
schedule.

I don't know how to change this, but I would prefer them to cycle daily, or 
weekly.

How? 

You mean the errorlog files? If so it only cycles on SQL Server startup. Check out sp_cycle_errorlog 
(which you can schedule of you wish).

-- 
Tibor Karaszi, SQL Server MVP
 http://www.**--****.com/ 
 http://www.**--****.com/ 

Yup, that was it.

My problem was that the current log file was about 500 MB (covering about 7 
months) and pulling it from my workstation caused some issues. I suspect 
mostly from the source server, which is under powered, has a lousy network 
connection and slow and fragmented drives.

So, what is a reasonable time frame for an error log? If you're on top of 
the system, I think once a day, perhaps once a week. No less often than once 
a month though.

Opinions?


"Tibor Karaszi" < XXXX@XXXXX.COM > wrote in 

> So, what is a reasonable time frame for an error log?

The usual: It depends. :-)

First, you can configure number of history files. Through GUI in EM's 2000. I'm not sure whether 
SSMS exposes this, but I bet you can find it in the registry. If you re-cycle frequently, you 
probably want to have more old files.

Or, you can add up the smartness by each time you cycle, you take the errorlog.1 file and zip it 
into and archive (it zips really well since it is text).

And, if you are really ambitious, do some search (grep(?) or whatever utilities there might be) to 
remove stuff you know you aren't interested in, before you zip it.

Also, since you are concerned with size, why not add a condition for the cycle based on file size?

And in SQL Server 2000, you can control to some extent whether some system messages are written to 
eventlog/errorlog in the first place. See sp_altermessage and the dlevel column in sysmessages.

So, there's much you can do. One thing I do recommend, is to keep a reasonable amount of history for 
these. For instance, the first time a corruption is encountered, SQL Server writes information. Say 
it takes a few days before you discover this (you are on vacation), then you definitely want these 
entries available. And you probably want to back-track to see if there are older things that can 
possibly be the root cause.

-- 
Tibor Karaszi, SQL Server MVP
 http://www.**--****.com/ 
 http://www.**--****.com/ 

So if you schedule the log file rollover to happen once a month, you 
shouldn't have to do anything, since SQL Server will keep more than enough 
to meet your 6-month threshold.

However, there is a scenario you may have to pay attention to: manual / 
unplanned restarts.  Say you apply windows updates and/or SQL SPs during a 
maintenance window and restart the service and/or reboot the box multiple 
times, now you just lost a good portion of your archive.  So, you may want 
to have a separate windows scheduled task that archives the files once a 
week and deletes any archives > 6 months old (or whatever your desired 
range).