RAISERROR WITH LOG and performance

Question

Post reply

RAISERROR WITH LOG and performance

Jesse Reich

Hall of Fame

Points: 3020
More actions
June 21, 2010 at 4:11 pm

#223999

I am curious if anyone knows if RAISERROR WITH LOG will affect performance negatively?
We have an OLTP application which will knowingly fed duplicate data to our database. The data will be caught by a unique constraint on one of our tables. We are trying to decide if we should maintain an error log on the application side, in the windows error log, or both.
I expect there to be around 1000-2000 duplicate errors per day.
I'm not sure how we could keep an error log in the database because the application, running C#, will be creating a transaction for each call to the stored procedure, and rolling it back if there is an error. Presumably a rollback will also rollback any inserts to an error logging table.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

As RAISERROR WITH LOG will write into EventLog it will negatively affect performance of the existing code for sure.

BUT!

The real question is: Will the negative effect of its use be significant enough in YOUR case! Try and you will see.

1. Rolling back of transaction will not roll back logged message, as WITH LOG tells to SQL to log the message into Windows EventLog.

2. It is not everyone can specify this option. Check the required security level for using it (try msdn it does help sometimes):

http://msdn.microsoft.com/en-us/library/ms178592.aspx

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Jesse Reich Hall of Fame Points: 3020 More actions · Answer 2

Thanks for the input. I didn't think about the security permissions either...

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 3

You can get around the security issue by using sp_altermessage, but that means *every* occurence of the message will be logged.

You might be able to get around that by altering the msg prior to the loads, running the loads, then altering the msg back .

Scott Pletcher, SQL Server MVP 2008-2010