Trapping Error raising SQL statements

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Trapping Error raising SQL statements

Author

Topic

REDDY
Starting Member

43 Posts

Posted - 2003-10-23 : 09:01:37

Can any one help me capturing the "errors and warnings" event with their corrresponding sql statements
in profiler???

Ours is very busy database with an average > 10 sqlstatements/Storedprocedures submitted persecond
during the busy hours.

When I ran the profiler with events "errors and warnings" I am getting ample quantites of
Errors,to find the sql/sp where these errors occuring ,I used "dbcc inputbuffer(spid)"

some times it is becomming very difficult for me to get this spid from profiler and
execute dbcc inputbuffer(spid), before I excute dbcc inputbuffer(spid), that spid goes for next sql execution.
so it is becomming tough for me to capture the exact sql statements where the error is occuring.
I even tried profiler with events "error and warnings" and "T-sql" both events,this way I am getting tonnes of
other sql also which I dont want.

My main aim is to capture all the sqlstatements/SPS and their error messages during the busy hours(8am to 6pm)
when the database is used by web and other Applications

note: I don't want to get the other sql/sps in profiler which are error free

any help will be greately apprciated.

Thanks
Reddy

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-23 : 11:44:57

How are you interfacing with the database?
It's usually easier to trap these things from the client. If you have built an application then you should have a database access layer in which you can turn on tracing to log all statements and errors.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

REDDY
Starting Member

43 Posts

Posted - 2003-10-23 : 12:16:56

We do not have any buit in application,So is it not possible to capture the sql that
are rising the error from any of the Microsoft tools like Profiler??

thanks
Reddy

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-23 : 12:30:13

You don't need to run DBCC INPUTBUFFER to get the code that the spid was running. DBCC INPUTBUFFER only shows partial code anyway if the code is greater than a certain number. If you trace errors and warning, plus also trace TSQL SQL:BatchCompleted and Stored Procedures SP:StmtCompleted, you'll be able to see what was running.

Tara

REDDY
Starting Member

43 Posts

Posted - 2003-10-23 : 12:52:31

Tara
If I take TSQL SQL:BatchCompleted and Stored Procedures SP:StmtCompleted with "errors and warnings
I am getting all the sql ,not the specific sql that rise the errors,Because of this reason I didn't selected the other events.
If get the all sql , I cannot run the profiler for a long time.
At least I want to run the profiler all the buisiness hours(8AM to 6PM) for few days.

Just to remind you again from my first post

"note: I don't want to get the other sql/sps in profiler which are error free" I just want get those sql that rise the errors during my monitoring(8AM to 6PM)

Thanks
Reddy

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-23 : 13:05:03

You can't do that in SQL Profiler. If you only turn on errors and warnings and then use DBCC INPUTBUFFER, you're not going to get what you want. DBCC INPUTBUFFER could be showing the next statement already by the time that you run the command. Those other events would give you the statement that is having problems. Can't you just filter your trace on specific logins or something so that you don't collect unnecessary data? You should be filtering out the system tasks definitely.

If you run SQL Profiler on a client machine, SQL Profiler won't have as much of an impact. We've got a system that is very write intensive and we can run SQL Profiler for hours without any performance problems as long as we aren't tracing everything. Filters is what you need to use.

Tara

REDDY
Starting Member

43 Posts

Posted - 2003-10-23 : 13:26:40

Thak you very much Tara for your valuable comments and suggestions,it is really

upsetting thing to know that such a basic thing is not available in Profiler.

We use one login for all the places,so this login filter not going to help me

As you said I can filter system tasks, and remaning process I will do as per your suggestions..

Still if there is any other to capture only error rising SQL statements let me know.

once again thank you very much for your help

Regards
Reddy

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-23 : 13:32:25

The reason why it doesn't do it in SQL Profiler is because they are separate events. The error is one event and the statement is another event. So you have to trace both events, both events in entirety.

How much performance degradation do you see when you run SQL Profiler? It really should be a very small amount if done on a client machine and if using the appropriate filters and not tracing too many events.

Tara

REDDY
Starting Member

43 Posts

Posted - 2003-10-23 : 13:49:37

That reason make sense...I never tried performence check when I ran the profiler,but it should be ok if I run on client machine as you said.

I appriciate for your help

Regards
Reddy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources