SQL Server Q&A

Symptoms
When you execute a statement in which an EXISTS clause contains a FULL OUTER JOIN, an access violation may be raised, and you may receive the following error message:

SqlDumpExceptionHandler: Process 51 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.Connection BrokenThe SQL Server error log may contain an error similar to the following:

********************************************************************************* BEGIN STACK DUMP:*02/28/01 21:58:41 spid 51**Exception Address = 0055A73F (COptExpr::DeriveGroupProperties(unsigned long) + 00000016 Line 0+00000000)*Exception Code= c0000005 EXCEPTION_ACCESS_VIOLATION*Access Violation occurred reading address 00000014
Resolution
To resolve this problem, obtain the latest service pack for SQL Server 2000. For additional information, click the following article number to view the article in theMicrosoft Knowledge Base:
290211?(http://support.microsoft.com/kb/290211/EN-US/) INF: How to Obtain the Latest SQL Server 2000 Service Pack

Symptoms
A computer that is running SQL Server with Address Windowing Extensions (AWE) enabled may experience an Access Violation when attempting to generate a latch warning if the buffer is not currently mapped. For more information about AWE mapping, refer to the Win32 documentation about theMapUserPhysicalPages function.
The following stack trace is logged in the SQL Server error log:

0082CA07 Module(sqlservr+0042CA07) (LatchBase::PrintWarning+0000003E)0082C984 Module(sqlservr+0042C984) (LatchBase::Suspend+00000466)004018CB Module(sqlservr+000018CB) (LatchBase::Acquire+00000098)004290A0 Module(sqlservr+000290A0) (BUF::AcquireIOLatch+00000018)..
Resolution
The PrintWarning error message refers to a member of the un-mapped buffer, which then causes the exception to occur.

Symptoms
Use of a query that involves the AN identity may result in the SQL Server client being forcefully disconnected and the following stack trace is reported in the SQL Server error log:

0×0043cc05 Module(sqlservr+3cc05) (CONSTANT::CONSTANT+6e)0×00782df9 Module(sqlservr+382df9) (trConstant::trConstant+31)0×005faad4 Module(sqlservr+1faad4) (PtrBldGetIdentity+125)0×00531660 Module(sqlservr+131660) (CCompPlan::Result+5dc)0×0044a4ab Module(sqlservr+4a4ab) (CCompPlan::FPreprocess+19c)0×004383d0 Module(sqlservr+383d0) (CCompPlan::FCompileStep+7e6)0×0043d00f Module(sqlservr+3d00f) (CProchdr::FCompile+5d9)…
Resolution
The CONSTANT::CONSTANT code line attempts to copy an extra character from the table name, which then causes the exception error.

Symptoms
This article describes the following about this hotfix release:The issues that are fixed by this hotfix packageThe prerequisites for installing the hotfix packageInformation about whether you must restart the computer after you install the hotfix packageInformation about whether the hotfix package is replaced by any other hotfix packageInformation about whether you must make any registry changesThe files that are contained in the hotfix package
Resolution
In SQL Server 2005, an error message that resembles the following is logged in the SQL Server error log after you roll back transactions on a table:

Error: 3315, Severity: 21, State: 1
During rollback, the following process did not hold an expected lock: process 51 with mode 8 at level 2 for row Rid pageid is (1:73) and row num is 0×0 in database ‘DatabaseName’ under transaction (0:546). Restore a backup of the database, or repair the database.This problem occurs when page locks are disabled on the table before the transactions are rolled back.
NotesIn the error message, DatabaseName represents the database that contains the table.You may have run the sp_indexoption stored procedure todisable the page locks on the table.For more information about how to view the SQL Server error log, visit the following Microsoft Developer Network (MSDN) Web site:
http://msdn2.microsoft.com/en-us/library/ms187885.aspx(http://msdn2.microsoft.com/en-us/library/ms187885.aspx)

Symptoms
When you run a SELECT Transact-SQL statement to fetch data from your Microsoft SQL Server 7.0 database, an access violation exception may occurif the query contains complex JOIN operations.Additionally, you may receive the following error message:

SqlDumpExceptionHandler: Process 9 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
You may also notice a short stack dump that is similar to the following in the SQL Server error log:

*******************************************************************************——————————————————————————-Short Stack Dump0×004b74ff Module(sqlservr+b74ff) (CValSwitch::GetDataWithBuffer+17)0×0041fb1f Module(sqlservr+1fb1f) (GetDataWithBuffer+28)0×0041faf7 Module(sqlservr+1faf7) (GetDataWithBuffer+0)0×00420da8 Module(sqlservr+20da8) (CValSwitch::GetDataX+19)0×0066c8e6 Module(sqlservr+26c8e6) (CQScanSpool::GetRow+19f)0×0042bcfd Module(sqlservr+2bcfd) (CQScanNLJoin::GetRow+121)0×004142d5 Module(sqlservr+142d5) (CQueryScan::GetRow+11)0×00413a37 Module(sqlservr+13a37) (CStmtQuery::FExecuteQuery+303)0×00418724 Module(sqlservr+18724) (CStmtSelect::XretExecute+1f8)0×00412f1e Module(sqlservr+12f1e) (CMsqlExecContext::ExecuteStmts+11b)0×00412a33 Module(sqlservr+12a33) (CMsqlExecContext::Execute+1be)——————————————————————————-2004-06-10 13:02:13.95 spid9Error: 0, Severity: 19, State: 02004-06-10 13:02:13.95 spid9language_exec: Process 9 generated an access violation. SQL Server is terminating this process.
Note This problem does not occur on an instance of Microsoft SQL Server 2000.
Resolution
A supported hotfix is available from Microsoft. However, this hotfix is intended to correct only the problem that is described in this article. Apply this hotfix only to systems that are experiencing this specific problem.
If the hotfix is available for download, there is a “Hotfix download available” section at the top of this Knowledge Base article. If this section does not appear, submit a request to Microsoft Customer Service and Support to obtain the hotfix.
Note If additional issues occur or if any troubleshooting is required, you might have to create a separate service request. The usual support costs will apply to additional support questions and issues that do not qualify for this specific hotfix. For a complete list of Microsoft Customer Service and Support telephone numbers or tocreate a separate service request, visit the following Microsoft Web site:
http://support.microsoft.com/contactus/?ws=support(http://support.microsoft.com/contactus/?ws=support)Note The “Hotfix download available” form displays the languages for which the hotfix is available. If you do not see your language, it is because a hotfix is not available for that language. The English version of this fix has the file attributes (or later) that are listed in the following table. The dates and times for these files are listed in coordinated universal time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.

DateTimeVersionSizeFile name————————————————————–04-Oct-200211:297.00.108128,944Dbmssocn.dll06-Sep-200211:257.00.107753,520Distrib.exe06-Sep-200211:257.00.107798,576Logread.exe05-May-200306:0454,904Opends60.dbg05-May-200306:047.00.1093155,920Opends60.dll06-Sep-200211:267.00.1077250,128Rdistcom.dll06-Sep-200211:257.00.107782,192Replmerg.exe06-Sep-200211:267.00.107778,096Replres.dll17-Sep-200210:227,941Securityhotfix.sql06-Sep-200211:267.00.1077160,016Snapshot.exe29-May-200315:5159,214Sp4_serv_uni.sql14-Jan-200313:037.00.1085344,064Sqlagent.exe06-Sep-200211:257.00.107745,056Sqlcmdss.dll15-May-200311:487.00.10942,629,632Sqldmo.dll16-May-200300:597.00.109481,920Sqlmap70.dll22-Jun-200412:487.00.11495,062,928Sqlservr.exe15-Oct-200304:397.00.1144315,392Sqltrace.dll04-Oct-200211:297.00.108145,328Ssmsso70.dll15-May-200311:487.00.109424,848Ssnmpn70.dll26-Sep-200208:0028,408Ums.dbg26-Sep-200207:577.00.107957,616Ums.dll16-May-200301:017.00.1094151,552Xpweb70.dllNoteBecause of file dependencies, the most recent hotfix that contains these files may also contain additional files.

Symptoms
This article discusses the reasons for “out of sync” error messages when you have log shipping configured for SQL Server 2000.
One of the following error messages may be logged in the SQL Server error log:
Error message 14420
Error: 14420, Severity: 16, State: 1
The log shipping destination %s.%s is out of sync by %s minutes.Error message 14421
Error: 14421, Severity: 16, State: 1
The log shipping destination %s.%s is out of sync by %s minutes.If you are using SQL Server 2005, the description for these error messages are different:
Error message 14420

Error: 14420, Severity: 16, State: 1
The log shipping primary database %s.%s has backup threshold of %d minutes and has not performed a backup log operation for %d minutes. Check agent log and logshipping monitor information.Error message 14421

Error: 14421, Severity: 16, State: 1
The log shipping secondary database %s.%s has restore threshold of %d minutes and is out of sync. No restore was performed for %d minutes. Restored latency is %d minutes. Check agent log and logshipping monitor information.
Resolution
Log shipping uses Sqlmaint.exe to back up and to restore databases. When SQL Server creates a transaction log backup as part of a log shipping setup, Sqlmaint.exe connects to the monitor server and updates the log_shipping_primaries table with the last_backup_filename information. Similarly, when you run a Copy or a Restore job on a secondary server, Sqlmaint.exe connects to the monitor server and updates the log_shipping_secondaries table.
As part of log shipping, alert messages 14220 and 14221 are generated to track backup and restoration activity. The alert messages are generated depending on the value of Backup Alert threshold and Out of Sync Alert threshold respectively.
The alert message 14220 indicates that the difference between current time and the time indicated by the last_backup_filename value in the log_shipping_primaries table on the monitor server is greater than value that is set for the Backup Alert threshold.
The alert message 14221 indicates that the difference between the time indicated by the last_backup_filename in the log_shipping_primaries table and the last_loaded_filename in the log_shipping_secondaries table is greater than the value set for the Out of Sync Alert threshold.
Troubleshooting Error Message 14420 By definition, message 14420 does not necessarily indicate a problem with log shipping. The message indicates that the difference between the last backed up file and current time on the monitor server is greater than the time that is set for the Backup Alert threshold.
There are serveral reasons why the alert message is generated. The following list includes some of these reasons: The date or time (or both) on the monitor server is different from the date or time on the primary server. It is also possible that the system date or time was modified on the monitor or the primary server. This may also generate alert messages.When the monitor server is offline and then back online, the fields in the log_shipping_primaries table are not updated with the current values before the alert message job runs.The log shipping Copy job that is run on the primary server might not connect to the monitor server msdb database to update the fields in the log_shipping_primaries table. This may be the result of an authentication problem between the monitor server and the primary server.You may have set an incorrect value for the Backup Alert threshold. Ideally, you must set this value to at least three times the frequency of the backup job. If you change the frequency of the backup job after log shipping is configured and functional, you must update the value of theBackup Alert threshold accordingly.The backup job on the primary server is failing. In this case, check the job history for the backup job to see a reason for the failure.
Troubleshooting Error Message 14421 By definition, message 14421 does not necessarily indicate a problem with Log Shipping. This message indicates that the difference between the last backed up file and last restored file is greater than the time selected for the Out of Sync Alert threshold.
There are serveral reasons why the alert message is raised. The following list includes some of these reasons: The date or time (or both) on the primary server is modified such that the date or time on the primary server is significantly ahead between consecutive transaction log backups.The log shipping Restore job that is running on the secondary server cannot connect to the monitor server msdb database to update the log_shipping_secondaries table with the correct value. This may be the result of an authentication problem between the secondary server and the monitor server.You may have set an incorrect value for the Out of Sync Alert threshold. Ideally, you must set this value to at least three times the frequency of the slower of the Copy and Restore jobs. If the frequency of the Copy or Restore jobs is modified after log shipping is set up and functional, you must modify the value of the Out of Sync Alert threshold accordingly.Problems either with the Backup job or Copy job are most likely to result in “out of sync” alert messages. If “out of sync” alert messages are raised and if there are no problems with the Backup or the Restore job, check the Copy job for potential problems. Additionally, network connectivity may cause the Copy job to fail.It is also possible that the Restore job on the secondary server is failing. In this case, check the job history for the Restore job because it may indicate a reason for the failure.