Cycling Those Error Logs
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/cyclingthoseerrorlogs.asp
-
Nice little script.
We have something similiar, only the date & time are appended to the filename so the logs don't get overwritten. They also get an extension of .txt which gets rid of the "Open With ..." dialog in Explorer.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
There also exists a sp_cycle_errorlog.
Downside of this is that it apears not to be working when used in a sqlagent job.
(complaints abount not being sa, even if I make sa jobowner and grant the sqlagent exec-account sa-authority.)
In QA it works fine.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Your solution is fine. However, based on the way you db runs, the amount of activity etc., it may be over kill. I have a large number of servers and dbs and found that the systems are quiet enough that I don't get a lot of errors. I simply setup a job that runs exec sp_cycle_errorlog each morning. That way I only need to look at a limited set of messages. Should something look strange I can always go the the previous log.
-
I rarely get errors in the SQL logs, however, all backups are logged and with Tlogs every 15 minutes, we are talking a lot of entries over a month. Cycling the error logs on a periodic basis keeps things manageable when we don't have the need to reboot a server that often.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
-
We do pretty much the same thing here as well, with a few modifications.
First off, we just automatically cycle the error log once a week (Sunday at midnight--with our setup it works to about 250k per log file). If there is any untowards log file activity, it shows up in EM with dates/times (or sizes!) breaking a discernable pattern.
Second off, you really, really want to retain more than 6 logs at a time. To ensure this happened, I created a deployment file that created the SQL Agent job running DBCC ERRORLOG, added code at the top to configure the system for 20 logs (at 1 per week that means under normal operations we'll always have over three months of logs), and ensured that it would be run on all of our servers.
Below is the T-SQL code I came up with to set the "logs retained" value. It writes to the registry with undocumented SP_ calls and it only works on SQL 2000 default instances, but once you get over that you shouldn't have any problems.
Philip
EXECUTE master.dbo.xp_instance_regwrite
N'HKEY_LOCAL_MACHINE'
,N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer'
,N'NumErrorlogs'
,REG_DWORD
,20
GO
-
We use the sp_cycle_errorlog. We have not had issues with it. Scheduled tasks have been running for years.
-
Hi Guys, Steve,
We have a similar setup, but we also have changed the registry to allow sqlserver to record 7 log entries, so that we also have the last weeks worth of logs, although not necessary in most installations, I just find a week a reasonable threshold.
But here's a side note for all you (there must be a couple) who have huge error logs, that arnt being recycled ever and you just cant get into them - if you need only the latest information, just type it thru xp_cmdshell:
exec master..xp_cmdshell 'type C:\sqlpath\error.1'
as long as you have QA returning in text, it should stream to your screen.
The following script is what we use to determine where hte log path is, for installations that we didnt manage ourselves and just inherited the servers and setup (and there are quite a few of these)
-- begin
SET NOCOUNT ON
DECLARE @ErrorLog NVARCHAR(256)
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id('tempdb..#tblRegistryParams'))
DROP TABLE #tblRegistryParams
CREATE TABLE #tblRegistryParams(
Value nvarchar(256),
Data nvarchar(256)
)
INSERT INTO #tblRegistryParams
EXEC master..xp_regenumvalues N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters'
SELECT @ErrorLog = REPLACE(Data,'-e','')
FROM #tblRegistryParams
WHERE Data Like '-e%'
DROP TABLE #tblRegistryParams
PRINT @ErrorLog
SET NOCOUNT OFF
-- end
hope this helps someone,
Alex
-
Hi!
I have set up a log recycle every night at 23:59:30 so that the day is shown nicely i EM. The number of logs is raised to 35, so I have about a month of logging available - I don's read the logs every day...
The Audit Level is set to Full, which gives me log files with a size up to 4 MB. The large ones it does take EM some time to handle, but then there's UltraEdit 🙂
I find this solution simple and easy to handle with a nice structure.
/Niels Grove-Rasmussen
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply