SQL Server Error Log Size

Question

SQL Server Error Log Size

Carl Rimmer

SSCarpal Tunnel

Points: 4068
More actions
January 31, 2005 at 6:06 am

#84687

Does anybody know at what size the SQL Server Error Log can start to cause problems?
The reason I ask is that I changed the Audit Level from None to ALL on our SQL Server and as a consequence one of the Jobs that loops inside an Active X Script seems to be flooding the Application Log and the SQL Server Error Log with successful login attempts.
The Error Log only normally grows to about 2 MB and is then recycled once a week. The Error Log is currently over 25 MB and is likely to possibly grow to 75 MB in size before I can change the audit level and stop and restart the SQL Server Service this evening.
I am aware of the sp_cycle_errorlog command and have tested it successfully on a test server. I am tempted to try and recycle the Error log in case I encounter any problems later this evening when stopping and restarting the SQL Server Service.
Thanks in advance
Carl

Viewing 5 posts - 1 through 4 (of 4 total)

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

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

Large error logs are only a real problem when you want to read them! Mine hit around 80Mb a day ( using deadlock trace flags which bloat the log !)

I cycle all my error logs every day - seems a sensible move to me.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Carl Rimmer SSCarpal Tunnel Points: 4068 More actions · Answer 2

Thanks for the advice/information.

I was toying with the idea of recycling the Error Log as the Job has to run another 2 times today. I am going to change the audit level to FAILURE and stop and start the Services after hours and was concerned that if the Log file is over 70 MB it may 'hang' but if yours grow to 80 MB I should be fine - fingers crossed.

Thanks again

Carl

tjp8 SSCommitted Points: 1989 More actions · Answer 3

Carl,

I think it mostly has to do with what your system is doing. I ran into a similar issue with a few SQL instances a while back. The apps were constantly making connections and it was causing my Logs to grow quite rapidly. I do not recall exactly how large the logs got, but in one system I never saw an issue and in another I did. The difference was that in the system where I saw problems it had a job that was doing something to access the log, sorry I do not recall the specifics, but performance of this job began to crawl. Once we did an error log cycle the job sped up again. We eventually took successful logins out of our audit. Sorry I don't recall more specifics, but in my opinion the size of the error log only becomes an issue when it needs to be opened. Therefore, it only becomes an issue within SQL Server when SQL Server has to Open it.

Hope this helps some.

TJP8

Carl Rimmer SSCarpal Tunnel Points: 4068 More actions · Answer 4

Thanks for the advice.

I am aiming to turn the audit of successful logins off.

Just so you know. It seems to be a DTS package that loops via an Active X script that is causing the problem. The Job has just ran again and increased the SQL Error Log by 5 MB. It seems to be logging the success of each login of the loop seperately; the developer who wrote the code can't understand why it is acting this way ?

I'm going to change to audit only Failed logins after business and I'm going to stop and start the SQL Server Service.

Regards

Carl