| Author |
Topic |
|
denis_the_thief
Aged Yak Warrior
596 Posts |
 Posted - 2010-08-04 : 13:41:55
|
| Is there a way so that only errors with a Severity greater than 1 (or whatever) will be logged?Anyone have any links in terms of Error Logging in particular what gets logged and what doesn't? Or every Error gets logged regardless of severity? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-08-04 : 13:48:40
|
| Yes - the SQL Server Error Log. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-08-04 : 14:01:18
|
| Thankyou. I guess I did not explain this well. We are using Raiserror to pass messages back to the Application. SQL Server is adding each of these messages to the SQL server Error Log. The issue is we are logging A LOT of messages with severity 1 (Severity 1 represents just a warning). These Severity 1 messages are important to pass back to the applications, but the problem is that it is over-cluttering the SQL Server Error Log, making it hard to pick out errors which are more significant. We wondered if there was a way to not log Raise Errors of Severity 1? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-08-04 : 14:40:07
|
| No, we aren't using 'WITH LOG'. I understand that is for logging the error in the Windows Log. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-08-04 : 15:21:48
|
| Thankyou, no worries. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-04 : 15:46:38
|
| Long shot : Have they been set up as ALERTS under SQL Agent in SSMS? |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-08-04 : 16:32:10
|
quote:
Originally posted by Kristen
Long shot : Have they been set up as ALERTS under SQL Agent in SSMS?
No. These are Raiserror statements inside Stored Procedures. Problem is many of these are more like 'warnings' with a Severity of 1 and they get called frequently, then they clutter up the 'Error' log.Another idea is if the SQL Server Error Log could be filtered by Severity. But so far I've no luck finding that one. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-04 : 16:38:53
|
| "These are Raiserror statements inside Stored Procedures"But can't they, then, be configured as Alerts in SQL Agent? (Not something I know about, only that I seem to remember reading about, so probably talking out of my wrong-end) |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-08-04 : 17:17:00
|
| Not sure - to call an Alert instead of a raise error? That would be a huge number of coding changes. I have only used Alerts with respect to Jobs.Just wish there was a Server Parameter - only log if Severity is > x. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-05 : 03:42:36
|
| We're on different pages here.Maybe someone has set an ALERT on the RAISERROR that is (already) being used in the Sprocs, and that is turning it into a logged event.Its a long shot, but its easy to check - normally ALERTS (under SQL AGENT) would be empty ... have a quick look in SSMS and if you don't find anything I'll sit down again. |
|
|
|
YellowBug
Aged Yak Warrior
616 Posts |
Posted - 2010-08-05 : 04:34:50
|
| Is it possible that the messages have be set-up with LOG?Run this query to check:SELECT * FROM sys.messagesWHERE severity < 10AND is_event_logged = 1Can you post one of the RAISERROR statements causing this behaviour? It may help.Are you using any additional trace flags on the server? |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-08-05 : 09:25:17
|
quote:
Originally posted by Kristen
We're on different pages here.Maybe someone has set an ALERT on the RAISERROR that is (already) being used in the Sprocs, and that is turning it into a logged event.Its a long shot, but its easy to check - normally ALERTS (under SQL AGENT) would be empty ... have a quick look in SSMS and if you don't find anything I'll sit down again.
No alerts here Kristen. The Raiserror itself causes the message to be logged in the SQL Server Log. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-05 : 09:29:48
|
OK, I'm sitting down again  Maybe YelloBug's idea is the answer then? |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-08-05 : 09:34:20
|
quote:
Originally posted by YellowBug
Is it possible that the messages have be set-up with LOG?Run this query to check:SELECT * FROM sys.messagesWHERE severity < 10AND is_event_logged = 1Can you post one of the RAISERROR statements causing this behaviour? It may help.Are you using any additional trace flags on the server?
RAISERROR (100018, 1, 1, 'Denis Smith'). I understand the is_event_logged is for the Windows Log rather than the SQL Server Log. Although maybe that is the solution, we can set the is_event_logged to 0 where severity < 10, then we will have an uncluttered view of things in the Windows Log and we won't look at the SQL Server Log (which is cluttered full of those warnings). Maybe that will work.I think the only additional Trace Flags is for Deadlocks. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-05 : 09:42:51
|
| Are you seeing some rows for theSELECT * FROM sys.messagesWHERE severity < 10AND is_event_logged = 1query then? (I have zero rows). If so presumably someone, at some time, has set the messages to LOG? |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-08-05 : 09:51:11
|
| Yes, there are a few rows there. But I thought the is_event_logged when severity < 10 only has bearing whether or not it will appear in the Windows Log - and has no bearing on whether it will appear in the SQL Server Log. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-05 : 10:07:43
|
| Yup, I got that nuance from your earlier message, and that's what I thought too.Just checked BoL (SQL2008) and there is a small note under sp_addmessage"If a message is written to the Windows application log, it is also written to the Database Engine error log file."everywhere else I've seen it reference is only made to Windows Event log. |
|
|
|
Next Page
|
Is there an Error Logging Threshold