A. Other messages:
I am getting a message 'dbprocess dead' or 'language exec' from SQL Server.
I am seeing an 'Exception Access Violation' message in the SQL errorlog.
I am getting *.DMP files in the <sql>\log directory.
I am getting "symptom dump" messages.
Basically SQL is probably internally gpf'ing/AV'ing (same thing) - you should see messages to this effect in the SQL errorlog.
There are only three reasons for this to happen (in order of ascending probability) :-
1. A database corruption - you should rule this out by running dbcc checkdb, newalloc and checkcatalog commands.
2. A hardware problem - usually duff memory. Run vendor supplied diagnostic routines and/or sqlhdtst/sql7iostress utilities to check out the hardware.
3. A bug in the SQL Server code (this is the most likely cause - database corruptions rarely cause gpf's, and hardware errors normally show up in other ways). This is the Microsoft C code that makes up SQLSERVR.EXE and dll's, NOT your TSQL code. If you have SQL code that causes an AV it is Microsoft's bug, not yours. There is nothing anyone outside of Microsoft support can do to help you.
Assuming it's not a database corruption/hardware fault, then follow the following diagnostic process :-
1. Check the Microsoft Kb on Technet (if you don't have Technet then order it now!). Also check the on-line website at http://support.microsoft.com/support which is more up to date than Technet. Search on "kbbug AND AV AND SQL" to find all documented AV bugs - note AV's are a generic symptom of lots of bugs. Many articles contain workarounds but it is usually difficult to match up the stack traces to see if it is relevant to your situation.
2. Are you on the latest version of SQL Server and the latest service pack? Microsoft fix a lot of AV errors in every service pack, so it is definitely worth getting current. If you're not on the latest service pack then that is the first thing Microsoft are going to ask you to do if you contact them anyway. If you can't apply it to the production system immediately then apply the latest SP on a test system and see if it fixes the problem.
3. Check the SQL errorlog and save away all the messages - especially anything telling you what SQL was being executed at the time.
4. Check the \<sql>\LOG directory for SQLxxxx.DMP files that may have been created. These contain information on what SQL Server was doing at the time, module stack traces etc. Save these away for Microsoft support as necessary. (Though there is a PRINTDMP.EXE utility supplied the output of this is still of no use to anyone unless they have the SQLServer C source code)
5. Can you re-create the problem at will? If the SQL being run is not shown in the errorlog, then find out what the user/developer was doing at the time. Use SQL Trace/Profiler to capture the actual SQL code being run if you can. If you can't recreate it, it's still worth reporting as long as you have the errorlog(s) and dump file(s).
6. If you can re-create the problem, then see if you can create a reproduction script to show the problem. This needs to be capable of running on a brand-new install of SQL Server on a new database. Therefore it needs to contain all tables, user defined data types, triggers, views etc. needed to show the problem. If it needs data then try and keep this to a minimum. (If the script/data is reasonably short then post to one of the
newsgroups and one of MVP's can report it to Microsoft for you). Alternatively re-write your query (if possible) to run against the pubs or northwind (v7 only) database as these are always installed with SQL Server and come complete with a variety of tables, foreign keys, indices and data.
7. Can you work around the problem by re-writing the SQL? Even with a reproduction script Microsoft are unlikely to turn a fix around quickly - unless you are a multi-million dollar customer. And even then you wouldn't just be applying one small fix, it would be a latest build with lots of other fixes too - it won't have been regression tested, so it could cause more problems than it fixed anyway.
8. If SQL Server terminates from the Access Violation and there is no dump file produced then a possible cause of the problem is the use of SQL Trace. There is a bug in this (fixed in 6.5 SP5 and above) that can terminate the SQL Server being monitored. Another cause of this is heavy deadlocking - also fixed in SP5a.
9. Report the problem to Microsoft PSS. PLEASE do this even if you can workaround it. Unless Microsoft get these bug reports then they can't fix them. (With a repro script an MVP will do it for you). Your call fee WILL be re-imbursed as all calls about bugs are free. (However, on the "normal" support-line the person answering the phone can't know it's a bug, so they'll need your credit card details anyway). For PSS contacts see http://support.microsoft.com/support/supportnet/default.asp
Microsoft will need you to supply :-
SQL Errorlog(s)
NT event log(s) - if any NT errors were occuring at the time
TSQL code running at the time
Details of hardware, version of NT, servicepacks etc. WINMSDP output is good for this.
With SQL 7 there is a new utility that will garner most of this information for you automatically. It is called sqldiag -
sqldiag -U<login> -P<password> -O<output filename>
