SQL Server Performance Forum – Threads Archive
Error log issue
In the error log is there a way that I can control what gets put into it. For example it records every time a user connection was issued. This log gets cycled every day but it still consumes a lot of data for the connections. ThanksThe sql error log? The term error log is a bit of a misnomar, its really a log, including errors. Some of the non-error stuff can be controlled, including logins. You want to set the audit level. This can be set to log only none, success, failures or all. It sounds like you are logging all. Its in the server properties in EM. Seach BOL for "Audit Level" for more details on how to set it and what each option means for you and decide what you want to log, or not.
Chris
Thanks a lot before I used sp_cycleerrorlog the log was 14 gigs. It grows about a 1 gig every day with all the excess loging Thanks
Auditing all is something the odd place uses to monitor use, monitor employee arrival times or if they suspect misuse etc. In general, its good security practice to log failures. Successes, for the average place are less of a concern. Glad to help. Chris
As referred keep the security audit level to failure instead of ALL, this could be the reason ERROR LOG file is nearing to 1GB in a day. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
last night I set the property to Failure and it still records Logins. Does this propery requiere to restart. Thanks
yes it does require a restart of the SQL Server service Cheers
Twan
I believe SQL services restart is not required and settings will take affect immediately, you must review error log what kind of information is written. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
I think not neccesary.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
.. now recyle the log and monitor the events. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Ok that must be the problem, I set up a Job to cycle the log each morning, so I’ll just restart tonight. Thanks
You DO need to restart SQL Server for changes in error log audit policy of login failures/successes to become effective. Tom Pullen
DBA, Oxfam GB
Twan/Tom I’d tested this scenario and its not required to restart the SQL Services if you change the Audit level from Server properties. Can you please confirm from your end. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
I just checked the log it is about 370614 Bytes. I cycled it this morning and it still record all login activity. before I started cylcling it was 14gigs. I couldnt even open the log. Thanks
WHen are you going to change Audit level on Server? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Can you post 3-5 lines from the log. Perhaps something else is also going on. Also, can you run
xp_loginconfig ‘audit level’ and let us know what it says. Chris
just ran xp_loginconfig
Here is the output …. audit levelfailure I had set this option from all to Failure this morning like you instructed. It reads nothing but "login succeeded for user …."
Thanks
Well, I saw the conversation about restart after changing the option go back and forth, and havent tried it myself, but I found this at a MS site this morning that says you must restart the server. So I’d try that as soon as you can. http://www.microsoft.com/sql/techinfo/administration/2000/security/securingsqlserver.asp
Audit connections to SQL Server.
SQL Server can log event information for review by the system administrator. At a minimum, you should log failed connection attempts to SQL Server and review the log regularly. When possible, save these logs to a different hard drive than the one on which data files are stored. To enable auditing of failed connections with Enterprise Manager in SQL Server: Expand a server group.
Right-click a server, and then click Properties.
On the Security tab, under Audit Level, click Failure.
You must stop and restart the server for this setting to take effect.
I guess that solves the problem. Thanks you guys. Thanks
Hi Satya, I’ve had trouble with getting the audit changes to take in SQL Server, and so now always restart the services if I change the audit options Cheers
Twan
Thanks Twan But its not the case at my end, so I was bit affirm about the issue. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
]]>