SQL Server Performance Forum – Threads Archive
SQL Server Access Violation.
Hi there, I hope anyone can help me on this. I have a PIV 3200 HT with 2GB DDR400 RAM running SQL Server 2000 SP4 on W2k3 Server SP1. It has been running for nearly a year and has 12 databases, 11 of them contain the same tables but with different data and the other is our control stuff. Only logged users access the data through IIS, we are using ASP 3 and some .NET components, but most of the db access is through IIS. Until a few weeks ago all was well and then it started, we would get an Access Violation at some table (appears quite random) or we would run DBCC CHECKDB and consistency errors would appear. At first we run CHECKDB with the REPAIR_ALLOW_DATA_LOSS as indicated in the error message but with time we learned that just stopping the service and then restarting it would solve all problems above with no data loss until it happens again. At first it would happen once a week, now, some 3 weeks later, it happens every day. Below are some examples: Error: 0, Severity: 19, State: 0language_exec: Process 77 generated an access violation. SQL Server is terminating this process. Error: 0, Severity: 19, State: 0
SqlDumpExceptionHandler: Process 99 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Should any of you need any more info on this please ask. Thank you
Check event viewer for any hardware errors, try to restore the good backup to another server and run DBCC CHECKDB to ensure the data pages are not corrupted. If you can find what table are having corrupted data, try to BCP out and drop the table and recreate it then import the data again. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Are you always having these errors on the same database? or on certain tables? If not I would check all diagnostics on the hardware.
Also, I would run Profiler and set some trace events for Events and Warnings.
Satya, There are no hardware errors on event viewer although I am considering exchanging the memory with another machine I have with the same configs. It doesn’t seem the data is corrupted since dbcc checkdb only finds consistency errors when the problem occurs, after I stop and restart the service (SQL Server) all is well.
DStevensTN, The error is ocurring on at least five different databases, they all have the same tables, views and structures (each belong to a different client of ours). I tried the profiler but I must confess I had never tried it before and couldn’t get much out of it so far. Thank you for your responses and if you feel you might add something to this ‘investigation’ it will be appreciated.
Those EAV errors occurs if there is any mismatch with the drivers or hardware issues, so you must apply all applicable service packs or hotfixes to avoid the issues. Try reproducing the problem on another server with same configuration, if not succeeded then assume this server is mismatched. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Agreed, Service Packs could also be an issue, that potentially could look like a hardware issue. As far as profiler, I would check out everything in Events and warning section for a little while, just to see if anything suspicious pops up. This may hurt performance so don’t run it too long.
SO always test them on develoment before putting it to production.
SP on operating system must also be applied to avoid any issues with bugs. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
]]>