Manage size of error log

How do I manage the size of Sql Server's error log? I am not referring to th
e
transaction logs for each database. It is the log that stores successful and
unsuccessful logins (among other things). In Enterprise Manager, it is under
"Management", "logs". This file keeps growing by around 10 Mb each day. When
I click on it from Enterprise Manager, it takes 20 minutes or logner to load
and is getting longer each day. I then export this list to find out who has
successfully logged in in the last week. I can't find any place to limit its
growth. Or, is there a better way of getting a table of successful logins?
Thanks in advance!Brad,
The file is recreated each time the SQL Server service is restarted. You
can control the size of the log by periodically running the
sp_cycle_errorlog (see BOL) system stored procedure.
HTH
J
"Brad Burke Fusion Data" <BradBurkeFusionData@.discussions.microsoft.com>
wrote in message news:9AAC3DC2-7059-4E6F-BCAA-EC25C1203D3F@.microsoft.com...
> How do I manage the size of Sql Server's error log? I am not referring to
> the
> transaction logs for each database. It is the log that stores successful
> and
> unsuccessful logins (among other things). In Enterprise Manager, it is
> under
> "Management", "logs". This file keeps growing by around 10 Mb each day.
> When
> I click on it from Enterprise Manager, it takes 20 minutes or logner to
> load
> and is getting longer each day. I then export this list to find out who
> has
> successfully logged in in the last week. I can't find any place to limit
> its
> growth. Or, is there a better way of getting a table of successful logins?
> Thanks in advance!|||"Brad Burke Fusion Data" <BradBurkeFusionData@.discussions.microsoft.com>
wrote in message news:9AAC3DC2-7059-4E6F-BCAA-EC25C1203D3F@.microsoft.com...
> How do I manage the size of Sql Server's error log? I am not referring to
> the
> transaction logs for each database. It is the log that stores successful
> and
> unsuccessful logins (among other things). In Enterprise Manager, it is
> under
> "Management", "logs". This file keeps growing by around 10 Mb each day.
> When
> I click on it from Enterprise Manager, it takes 20 minutes or logner to
> load
> and is getting longer each day. I then export this list to find out who
> has
> successfully logged in in the last week. I can't find any place to limit
> its
> growth. Or, is there a better way of getting a table of successful logins?
Do not keep it from growing, just look at it as a text file, at
\\server\drive$\mssql\log\errorlog. If log grows big, EM is not the tool to
look at it.
Or take a look at "Configure SQL Server Error Logs" in BOL.
Regards
Wojtek|||Hi,
Please do not open the error log from Enterprise manager -- management if
the file is huge. It takes lots of resources and it is not a good practice
if your
file is bigger.
Best method is connect to your server and use the WINDOWS explorer and go to
SQL Server program groups / Logs folder. Errorlog with out a number
will be the latest file.
Incase if you need to open the file using enterprise manager then schedule a
job with system procedure sp_cycle_errorlog
2 to 3 times a day to recyle the error log using sql agent jobs. This will
ensure that error log file size is limted. In this case you can open the
file using
enterprise manager.
Thanks
Hari
SQL Server MVP
"Brad Burke Fusion Data" <BradBurkeFusionData@.discussions.microsoft.com>
wrote in message news:9AAC3DC2-7059-4E6F-BCAA-EC25C1203D3F@.microsoft.com...
> How do I manage the size of Sql Server's error log? I am not referring to
> the
> transaction logs for each database. It is the log that stores successful
> and
> unsuccessful logins (among other things). In Enterprise Manager, it is
> under
> "Management", "logs". This file keeps growing by around 10 Mb each day.
> When
> I click on it from Enterprise Manager, it takes 20 minutes or logner to
> load
> and is getting longer each day. I then export this list to find out who
> has
> successfully logged in in the last week. I can't find any place to limit
> its
> growth. Or, is there a better way of getting a table of successful logins?
> Thanks in advance!|||All the other respondents are correct; however, you can modify the NUMBER of
Error Log files that are kept before they are recycled. In SQL EM,
right-click on the SQL Logs folder and choose properties. You will see that
the default is grayed-out but is for 6 files. You can check the box and
then choose the number you'd like to keep the history for.
We usually only audit Failures as Successes can chew up quite a bit of space
on a busy server. Also, not only are the successes logged in the SQL Server
Error log but also the server's Application Event Log.
Finally, I usually use the xp_readerrorlog extended stored procedure. No
parameter gives you the current log. An integer value gives you the file
with the same extention: 1 for Errorlog.1, 2 for Errorlog.2, etc.
Sincerely,
Anthony Thomas
"Brad Burke Fusion Data" <BradBurkeFusionData@.discussions.microsoft.com>
wrote in message news:9AAC3DC2-7059-4E6F-BCAA-EC25C1203D3F@.microsoft.com...
How do I manage the size of Sql Server's error log? I am not referring to
the
transaction logs for each database. It is the log that stores successful and
unsuccessful logins (among other things). In Enterprise Manager, it is under
"Management", "logs". This file keeps growing by around 10 Mb each day. When
I click on it from Enterprise Manager, it takes 20 minutes or logner to load
and is getting longer each day. I then export this list to find out who has
successfully logged in in the last week. I can't find any place to limit its
growth. Or, is there a better way of getting a table of successful logins?
Thanks in advance!