Our particular problem related to this error 1203 is so difficult to pinpoint that it brings our server to a grinding halt. We have aprox. 50 SPs and about 4 dozen tables... we've added WITH (NOLOCK) hints to many of our read-only SPs in order to reduce the number of locks.. still nothing... over time, our error 1203s get more and more frequent, then SQL Server service stops! (note that our application never gets the message from SQL Server that something is wrong... it just merrily chugs along ... then boom!)
We've called MS in the beginning, and they were of no help... they wanted our DBs but because of HIPAA we can't give them out...
We're really stuck, and if there is anyone who is willing to bounce some ideas around, please do not hesitate to respond to this thread. We've been dealing with this problem for 3 months now, and we're completely out of ideas. We're using SQL Server 2000 SP2 with the latest pathes. Also, we are only using 1 processor(instead of the both).
About two or three years ago some 1203 errors were isolated to a certain (long running) stored procedure in the following manner:
1 Daily profiler traces were recorded on the server having the issue
2 Trace files were analyzed (and replayed) on days when a 1203 occured
After a while, it became apparent that a specific proc was almost always (and most probably always) associated with the 1203 error. However, the 1203 errors weren't reproducible, i.e.(applying the day's recorded work file to a DB restore on a dev server generally would not reproduce the error on the dev server); so nothing major was changed in the proc for a while (some of a handful of obvious initial changes had noticible performance differences, but 1203 errors still occured). Because of this and other unrelated issues the proc was subsequently targeted for a rewrite (along with some app related schema change needs, etc. that also involved the server). The proc's functionality was replaced by several procs that performed the old proc's functions more efficiently (with fewer resources), at different times (more on an as needed basis), and in association with different, more logically related functions. (The original was kind of a hodgepodge that ran some daily processing and also performed some app related maintenence activities that really weren't directly related to its purpose.)
Since this seems like a consumption of resources over time rather than an immediate hit - you need to establish some baselines and monitor resources daily to see if a pattern emerges. Both perfmon and profiler will help you with this. Have you discovered any patterns ? What is the timeline for the 1203 errors and the timeline for the server to stop ? What are the resources that are increasing over this timeline ?
Thank you guys,
But I do not think it is related to consumption of resources: CPU usage is 15%,
memory usage is 45%
This machine has 2 CPUs, but we use only one for SQL( through max degree of parallelism)
We have checked and changed a lot of SQL stuff but even by now we have that mistake in unpredictable way.
And the worst thing is we can not reproduce that mistake on Dev environment
We have some applications working from SQL
We have some big basic proc's on SQL, and our next step will be to check them
This error showed up in our development environment after we did some cosmetic renaming of fields. This is on W2k server w/ SQL 7 sp4.
If run the main query from the sp in question separately, it will die with error 1203 as well. This program has given us trouble in the past and crashed our server when we changed some outer joins to inner ones. Some of the views it uses contain TOP logic.
I had an alternative method to do the TOP logic without the TOP command, so I will probably go back to that.