I have a problem with SQL Mail. I am running SQL 2000 sp2 on Win 2000 server using Exchange 5.5.
The accounts used for SQL Agent and exchange client profile are same and domain admin accounts...
When the server is booted mail works fine, the session start with the usual ignorable error and mail traffic proceeds ok. However, after a period of time an error occurs and all mail functionality ceases to work, and any sql which calls xp_sendmail will hang indefinitely.
In the log the following is observed:
2002-07-30 10:35:42.21 spid68 Using 'dbghelp.dll' version '4.0.5'
2002-07-30 10:35:42.26 spid68 Error: 0, Severity: 19, State: 0
2002-07-30 10:35:42.26 spid68 SqlDumpExceptionHandler: Process 68 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
This process is in fact a mail job... but I can find no reason for this.. Also, in order to restart a working mail session I need to stop and restart all SQL services.
We are currently experiencing the exact same issues. There is a post SP2 hotfix: Q320407 that is suppose to resolve these AV's. For us it's been applied, but we have still had the AV's under certain circumstances. We have found that the xp_sendmail SP is still very unreliable even under SP2 which MS recommend and inform that issues such as memory leaks are resolved. Infact most of our issues have arisen since sp2 was installed.
The memory leak issues appear to be related to this problem. Do you have any scheduled maintenance jobs or SP's that perform xp_stopmail and xp_startmail SP? We have recently removed all instances of xp_stopmail and xp_startmail from all scheduled jobs. After doing so we have found that our server is now much more stable although we have still have issues which appear to be related to not using xp_startmail.
We have an outstanding call with MS - I will let you know the outcome.