April 30, 2010 at 8:45 am
Is the Huge 1GB SQL Error Log a concern for SQL Performance.
I can reset the Log and stuff, but I am just inheriting this Database Server from another dept. and their policy is to maintain the Error Log for a Week.
Maninder
www.dbanation.com
April 30, 2010 at 8:51 am
Because log shipping fills up our logs quickly we have a job to roll the error log nightly, my usual policy is to keep 32 or 64 logs.
Large logs are not a problem (except in SQL 2000 where EM would get locked up while reading it), but if that was the case, I'd just open the file in norepad.
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
April 30, 2010 at 10:39 am
Yes Large files are not a issue. but sometimes the policies across departments, lead us into a tight position.
Thanks
Maninder
www.dbanation.com
April 30, 2010 at 10:44 am
Maninder S. (4/30/2010)
Yes Large files are not a issue. but sometimes the policies across departments, lead us into a tight position.Thanks
Then what's the question? SQL is defaulted to keep six log files, even if you cycled it daily, that would still be 6 days (which is almost seven days :))
I guess I'm failing to understand. I've never heard of a department telling me how long to keep the error log, that's internal to SQL (and everything written in the SQL Error Log can is also in the Windows App Event Log). These decisions are almost universally within IT. And if they want 7 days worth of SQL Error Logs, just change the number to seven and create a job that cycles it daily (or keep one errorlog but cycle it weekly)
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
April 30, 2010 at 11:04 am
sorry, I meant was the policy regarding the disk space threshold. it is 20% for our environment and with this particular box is jagged up so nicely, that the disk space threshold is down to 5% on this box. so i will have to make other arrangements of storing the error Logs and other stuff.. and that is what i meant by policies.
the Error Log on this Highly transactional box Grows to 1GB+ with a weeks time and we have a retention period of 60 days. So we write a new plan for this box altogether.
Maninder
www.dbanation.com
April 30, 2010 at 11:16 am
I've always had a problem with the hard 80% threshold, especially when applied to backup drives.
In your situation, I would cycle the SQL logs daily, keep a few days. As part of the NT backup, the logs are probably being backup up if you needed older data (though there may be a directory exclusion for SQL). Also, since evrything is duplicated in the App Event Logs, you would have access to the data there, with better filtering capabilities.
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
April 30, 2010 at 11:27 am
The issue I've had with large logs is reading them when there are issues. It's a PIA to open a 5MB file. I'd cycle often enough to keep them opening quickly, but make sure you keep a week or so of data. That might mean keeping more than 6 files.
May 6, 2010 at 3:24 pm
Why not increase the number of logs... that way you can still keep them the desired amount of time, but cycle frequently.
As far as I have seen the only deteriment is it takes longer to open the log- I imagine there is probably some tiny performance hit writing them as well... but you can have cake and eat it to... increase number of logs to 15 or something and cycle daily.
May 6, 2010 at 6:27 pm
Does the errorlog file have a large number of messages for backup operations ? These messages can be suppressed with trace flag 3226 See http://msdn.microsoft.com/en-us/library/ms188396.aspx
SQL = Scarcely Qualifies as a Language
July 6, 2010 at 5:18 pm
How do you configure your logs to 15 and cycle daily?
Do you create a daily job that runs "EXEC master.sys.sp_cycle_errorlog" ?
July 6, 2010 at 5:42 pm
To cycle the log you need to create a SQL Job that runs daily (like just after midnight) and runs the sp_cycle_errorlog stored proc.
To set the number of files to retain: In MS, Go to Managment, right click the SQL Server Logs, Select Configure.
You will see the Limit option is not ticked, and the default value is grayed out and set to 6.
Tick the Limit option and set the default value.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
July 6, 2010 at 5:44 pm
Thanks for that Leo.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply