sql server log

Question

sql server log

meghardy

Default port

Points: 1453
More actions
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

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

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

mccork SSC-Insane Points: 22288 More actions · Answer 1

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

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 2

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

Steve Jones - SSC Editor SSC Guru Points: 736078 More actions · Answer 3

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://www.sqlservercentral.com/columnists/sjones

http://www.dkranch.net

meghardy Default port Points: 1453 More actions · Answer 4

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

mccork SSC-Insane Points: 22288 More actions · Answer 5

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

paul_richardson SSCarpal Tunnel Points: 4594 More actions · Answer 6

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

EdVassie SSC Guru Points: 60469 More actions · Answer 7

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

peterus SSC Veteran Points: 287 More actions · Answer 8

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

firthr SSCommitted Points: 1606 More actions · Answer 9

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.

peterus SSC Veteran Points: 287 More actions · Answer 10

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.