Post reply

sql server log

  • July 2, 2003 at 3:24 am

    #58700

    Hi all,

    I have taken over as DBA at my new company. I am trying to tidy things up on the server. At the moment, when i look in SQL Server Logs on enterprise manager, its recording all successful logins. This is a bit of a pain as loads of people login in one day, causing the log file to be massive. Its recycled every night, but if I try to open one for a day, it takes forever! I also think its pretty pointless as I cant think of a time when i would ever really need that information. Would anyone disagree?? If not, does anyone know how to turn it off??

    Thanks alot,

    Meg

  • July 2, 2003 at 4:24 am

    #463889

    I agree. Turn it off. Enterprise Manager, right click on the server, Properties, Security tab, Audit Level.... change to failure.

    You need to restart SQL Server.


    Cheers,
    - Mark

  • July 2, 2003 at 7:52 am

    #463922

    I'm not so quick to agree. We logging all successful logins, as well as unsuccesful logins. One reason we do this is to audit logins to see when was the last time a login was used to access SQL Server. Now I know it is a painfully slow to bring up a large log via EM, but you can also browse the log directory and look a log files with NOTEPAD. Using NOTEPAD bring these large files up more quickly then EM, and you see all the log. Sometimes EM will truncate very long log entries.

    Gregory Larsen, DBA

    If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

    Gregory A. Larsen, MVP

  • July 2, 2003 at 9:34 am

    #463939

    Greg has a good point. Before you change something like this, be sure there is not some reason it is setup. The last thing you want is to turn off auditing when the CEO requested it.

    Steve Jones

    sjones@sqlservercentral.com

    http://qa.sqlservercentral.com/columnists/sjones

    http://www.dkranch.net

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • July 2, 2003 at 9:47 am

    #463942

    Thanks for the advise guys.

    I have found the reason for the logging...there is something set up for checking particular logins. Its needed for that. So i'll have to live with the large log files. Good to know I can check them from another source though, EM was a nightmare, especially after 12 hours!

    Thanks again,

    Meg

  • July 2, 2003 at 4:03 pm

    #463982

    I am more for turning it off and, if there's a particular need for it, using a scheduled profiler trace to gather the required successful login info. A trace can be directed to a new trc file daily, can be filtered, can return more (not much more) info, and keeps the SQL logs to a manageable level.


    Cheers,
    - Mark

  • July 3, 2003 at 2:38 am

    #464020

    Hi, i've had a similar problem in the past. Too many successful login messages in the errorlog. What I did was use the NT command FINDSTR to ignore any lines that contain the words "login succesful". and redirect the output to a file which is then a lot easier to read.

    I set up a job with a Cmdexec step as follows :-

    e:\mssql7\dba\findstr\filter_errorlog.bat

    and the filter_errorlog.bat file contains the following (on one line) :-

    findstr /I /V /G:e:\MSSQL7\DBA\FINDSTR\ignore.txt e:\MSSQL7\log\Errorlog >e:\MSSQL7\DBA\FINDSTR\filtered_errorlog.txt

    (You'll obviously have to change the paths !)

    and ignore.txt contains one line :-

    Login succeeded

    When the job is run the errorlog is cloned, but the login successful entries are ignored. You just have to view the new file in notepad

    !

    Hope this helps

    Paul

  • July 3, 2003 at 6:36 am

    #464041

    We run with SQL Logging all logons, and NT auditing set on for most things. There have been a few situations where people have claimed the DBAs 'must have' changed or broken something. So far, in all of these situations us DBAs have been able to use the logs to show the problem was really caused by people outside the DBA team.

    My recommendation is to keep such logging turned on permanently. As already pointed out, log information can be filtered if necessary, but it is hard to turn it on retrospectively.

    "All information provided is a personal opinion that may not match reality"

    All information provided is a personal opinion that may not match reality.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • July 3, 2003 at 8:58 am

    #464071

    I would recommend running dbcc errorlog every night, maybe even Midnight and Noon, depending on the amount of logins you have.

  • July 3, 2003 at 10:10 am

    #464091

    if you ran dbcc errorlog you would have to increase the number of logs you saved otherwise you could soon lose all ability to trace problems.

  • July 3, 2003 at 10:37 am

    #464110

    Absolutely! I keep one months worth myself, running dbcc errorlog everynight at midnight. Not alot of problems here so I don't track anything longer than that....

    quote:

    if you ran dbcc errorlog you would have to increase the number of logs you saved otherwise you could soon lose all ability to trace problems.

Viewing 11 posts - 1 through 10 (of 10 total)

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