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.
| Author |
Topic |
|
gonk
Starting Member
8 Posts |
 Posted - 2004-10-08 : 11:38:49
|
| hi,this task sounds simple: i have a sqlserver and 3 webservers running that access the sqlserver. there are many thousand statements executed each day. some by users of the web frontend and some by the database itself. some of the statements fail because of timeouts, bad code, etc. i want to trace them. sqlprofiler works fine for checking if there is an error, but it doesn't tell me what statement, storedprocedure etc caused the error. so its nearly worthless. i can't trace all statements because of the amount of disc usage just to find those few that failed.thanks for any ideaschris |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-10-08 : 12:48:02
|
| Restrict all access to the databases to only GRANT EXRC on sprocs, and do all your error handling in them?Maybe add some logging?Brett8-) |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-08 : 12:55:01
|
| Ummmm, SQL Profiler most certainly does too show you which statement caused the error. You must not be setting up the trace correctly if you are getting this information.Tara |
|
|
|
gonk
Starting Member
8 Posts |
Posted - 2004-10-11 : 08:24:42
|
| turning all sql statements into stored procs is impossible or owuld at least take weeks. sqlprofiler seemed the best solution for me but it doesn't seem able to tell me what caused the exception.i get something like this: Exception Error: 208, Severity: 16, State: 0 MS SQLEM SA 3104 51 2004-10-08 17:44:20.290 but not the statement that caused the error. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-12 : 04:17:18
|
| FWIW we converted all our dynamic SQL to SProcs, a while ago. It took a while but our productivity [particularly taking into account maintenance stuff] has increased by an enormous amount since.All our SProcs log the name of the SProc and their parameters on entry [to the SProc] and update the log row with the elapsed time, return value and any other useful info, on exit. (On entry they store "-999" as the Error Value, so that Sprocs that die (Deadlock, timeout, syntax error) show up as error outcome)We execute hundreds of thousands of SProcs a day on our servers. Its a lot of logging data, but we report, daily, on any Sproc that returned a non-zero value and then can see the full info. to see what other SProcs were called before/after in that session, and the logging data that we decided was worth storing (which may be less easy to gather from SQL profiler!).The reporting is via a web page, so we can drill-down on any error to see exactly what the user was doing at the time.Might be a big project for you to embark on, but I often ask myself what people who don't take this approach do to find errors. We often find errors that are not apparent to the user, but which are probably only "submarined" waiting for some other minor change before then blowing up big time! Also, as our applications are for web sites, we have to assume that only a very small proportion of errors will actually be reported by the users - as most users will just "go somewhere else".Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-12 : 11:12:13
|
quote:
Originally posted by gonk
turning all sql statements into stored procs is impossible or owuld at least take weeks. sqlprofiler seemed the best solution for me but it doesn't seem able to tell me what caused the exception.i get something like this: Exception Error: 208, Severity: 16, State: 0 MS SQLEM SA 3104 51 2004-10-08 17:44:20.290 but not the statement that caused the error.
The statement that is directly after this error in the trace is the one that caused the exception. Looking at your error that you posted though, that's just an error someone generated from inside Enterprise Manager while logged in with the sa account.Tara |
|
|
|
|
|
|
|
|
track all sql errors