SQL Server Performance

Error log issue

Discussion in 'General DBA Questions' started by PattyLand, Dec 11, 2003.

  1. PattyLand New Member

    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.

    Thanks
  2. ChrisFretwell New Member

    The 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

  3. PattyLand New Member

    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
  4. ChrisFretwell New Member

    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
  5. satya Moderator

    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
  6. PattyLand New Member

    last night I set the property to Failure and it still records Logins. Does this propery requiere to restart.

    Thanks
  7. Twan New Member


    yes it does require a restart of the SQL Server service

    Cheers
    Twan
  8. satya Moderator

    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
  9. Luis Martin Moderator

    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
  10. satya Moderator

    .. now recyle the log and monitor the events.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  11. PattyLand New Member

    Ok that must be the problem, I set up a Job to cycle the log each morning, so I'll just restart tonight.

    Thanks
  12. thomas New Member

    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
  13. satya Moderator

    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
  14. PattyLand New Member

    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
  15. satya Moderator

    WHen are you going to change Audit level on Server?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  16. ChrisFretwell New Member

    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
  17. PattyLand New Member

    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
  18. ChrisFretwell New Member

    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.
  19. PattyLand New Member

    I guess that solves the problem. Thanks you guys.

    Thanks
  20. Twan New Member

    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
  21. satya Moderator

    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

Share This Page