SQL Agent Log

Question

SQL Agent Log

SA-1

SSCrazy Eights

Points: 8113
More actions
May 9, 2007 at 10:01 am

#178287

Has anyone seen the below problem:
The SQL Agent Log has 9 log files totalling 260GB in size. How do I find out why this is happening and how to free up the space.
TIA.

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

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

It's normal behavior for SQL Server Agent to keep 9 archived error log files in addition to the current log. It opens a new one when the Agent service is stopped and started. You can delete the archived files while the Agent is stopped.

In SQL 2005, you can cycle the log without stopping the Agent.

Greg

SA-1 SSCrazy Eights Points: 8113 More actions · Answer 2

I stopped the agent, deleted all the sqlagent.* files (except for .out) and restarted the agent and everything looks good at this time.

Thank you.

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 3

You can run the following stored procedure to recycle your error logs.

EXEC sp_cycle_errorlog

Greg Charles SSC-Forever Points: 45403 More actions · Answer 4

sp_cycle_errorlog cycles the SQL Server error log, not the SQL Server Agent error log.

Greg

Leo Miller SSC-Addicted Points: 427 More actions · Answer 5

I would be seriously concerned if any of my SQL installations generated 260GB of log files. Are you doing full logging? And what sort of errors are you seeing in the log?

Leo

SA-1 SSCrazy Eights Points: 8113 More actions · Answer 6

These are agent logs and not error logs. It seems to me like 260GB of logs is unusual and this is the only server that i have had this problem on. The logs are pretty small since i had deleted the large files.

Anything in particular i should be looking for?

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 7

Here is a link, which may be helpful for the SQLAgent management.

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=6645

tvu4251 Mr or Mrs. 500 Points: 560 More actions · Answer 8

is there some way to limit the size of the error logs, for example 2 or 5 MB each? Would appreciate any pointers/advices.

SQLBill SSC Guru Points: 51440 More actions · Answer 9

None that I know of. Just create a job that cycles the log. Try cycling it once a month. If that's not enough, do once a week.

-SQLBill

SA-1 SSCrazy Eights Points: 8113 More actions · Answer 10

If the agent logs cycle each time you restart it then schedule a Windows job to do that every week or however often you think it is necessary

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 11

Not by size. But for each new server I setup, I make it standard practice to put in a job to run sp_cycle_errorlog and sp_cycle_agent_errorlog at midnight every day. This keeps the logs limited to one days worth of messages, at a size that is easily handled and loaded into text editor. I've seen servers where the SQL Server does not get restarted for months, and the log file will be > 100mb, and it is difficult to open and review the log for problems.

Also, I set the SQL Server Error Log to keep 30 days of log files, so I can check back up to a month.

Note that in SQL 2005, you can right-click on SQL Agent Error Log in Mgmt Studio and recycle the log manually.

Hope this helps

Mark

J B-262165 Old Hand Points: 332 More actions · Answer 12

It's actually sp_cycle_agent_errorlog to cycle the sql agent log if you're on sql 2005.