SQL Server Q&A

Symptoms
Binding 48 or more addresses to the network of a SQL Server computer causes an access violation exception to occur and the SQL Server server fails to start.
The exception that is logged to the SQL Server error log is:

42CF1ABA Module(SSNETLIB+00001ABA) (ConnectionListen+000008CA)004E2CFA Module(sqlservr+000E2CFA) (FRunCM(class StartUp *)+000000D3)004E3FA3 Module(sqlservr+000E3FA3) (initCM(void *)+00000064)7800BEA1 Module(MSVCRT+0000BEA1) (_beginthread+000000CE)From build 8.00.194
Resolution
The Ssnetlib.dll file attempts to listen on the first 64 socket addresses. A buffer overrun occurs, which causes the exception.

Symptoms
Resize is an internal class method that is used to resize an array. PvReplace may incorrectly use the Resize method leading to an exception.
The following, or similar, top of the stack is logged in the SQL Server error log when the problem occurs:

0×0051ea81 Module(sqlservr+11ea81) (CQDynArray::Resize+69)
0×0043acc7 Module(sqlservr+3acc7) (CQDynArray::PvReplace+2e)
.
Resolution
Microsoft has confirmed this to be a problem in SQL Server7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
274799?(http://support.microsoft.com/kb/274799/) INF: How to Obtain Service Pack 3 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0For more information, contact your primary support provider.

Symptoms
If you back up a database with SQL Enterprise Manager (SEM) and you check Verify backup upon completion under the Option tab, SQL Server issues the following commands: BACKUP DATABASE and RESTORE VERIFYONLY. RESTORE VERIFYONLY checks whether the backup set is complete and whether all volumes are readable.
For example, if you back up the Pubs database to a backup device called Test, SQL server runs the following two SQL commands:

BACKUP DATABASE [pubs] TO [test] WITHINIT ,NOUNLOAD ,NAME = N’pubs backup’,NOSKIP ,STATS = 10,NOFORMAT goRESTORE VERIFYONLY FROM [test] WITHFILE = 1 ,NOUNLOAD However, if you schedule a job through the SQL Enterprise Manager to do the same database back up, SQL Server only schedules BACKUP DATABASE without the RESTORE VERIFYONLY option.
Resolution
SQL Server 2000To resolve this problem, obtain the latest service pack for Microsoft 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
SQL Server 7.0To resolve this problem, obtain the latest service pack for SQL Server 7.0. For additional information, click the following article number to view the article in theMicrosoft Knowledge Base:
301511?(http://support.microsoft.com/kb/301511/EN-US/) INF: How to Obtain the Latest SQL Server 7.0 Service Pack

Symptoms
This article describes the following about this hotfix release:The issues that are fixed by the hotfix packageThe prerequisites for installing the hotfix packageWhether you must restart the computer after you install the hotfix packageWhether the hotfix package is replaced by any other hotfix packageWhether you must make any registry changesThe files that are contained in the hotfix package
Resolution
In rare situations, MicrosoftSQL Server 2000 stops responding when you cancel a query or when a query time-out occurs. Additionally, error messages that resemble the following are logged in the SQL Server error log file:

2006-07-06 11:04:11.82 spid59clean_tables_xact: active sdes for tabid 457768688
2006-07-06 11:04:12.23 spid59Using ‘dbghelp.dll’ version ‘4.0.5′ *Stack Dump being sent to e:\fastnet\MSSQL\log\SQLDump0009.txt
2006-07-06 11:04:12.51 spid59Error: 0, Severity: 19, State: 0
2006-07-06 11:04:12.51 spid59SqlDumpExceptionHandler: Process 59 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
* BEGIN STACK DUMP: *07/06/06 11:04:12 spid 59
*Exception Address = 00402836
*Exception Code= c0000005 EXCEPTION_ACCESS_VIOLATION
*Access Violation occurred writing address 00000004
* Input Buffer 514 bytes -
<Some query statements>
*******************************************************************************
* Short Stack Dump
* 00402836 Module(sqlservr+00002836)
* 00407EF3 Module(sqlservr+00007EF3)
* 0041CB9F Module(sqlservr+0001CB9F)
* 00414C76 Module(sqlservr+00014C76)
* 00404F9D Module(sqlservr+00004F9D)
* 006E732B Module(sqlservr+002E732B) (SQLExit+0017F834)
* 00642592 Module(sqlservr+00242592) (SQLExit+000DAA9B)
* 0049C46A Module(sqlservr+0009C46A)
* 41075309 Module(ums+00005309) (ProcessWorkRequests+000002D9 Line 456+00000000)
* 41074978 Module(ums+00004978) (ThreadStartRoutine+00000098 Line 263+00000007)
* 7C34940F Module(MSVCR71+0000940F) (endthread+000000AA)
* 7C57438B Module(KERNEL32+0000438B) (TlsSetValue+000000F0)
2006-07-06 11:04:31.18 spid59Stack Signature for the dump is 0×79DB129D
2006-07-06 11:04:31.34 spid59clean_tables_xact: active sdes for tabid 457768688
2006-07-06 11:06:01.39 serverStack Signature for the dump is 0×00000000
2006-07-06 11:06:01.39 serverError: 17883, Severity: 1, State: 0
2006-07-06 11:06:01.39 serverProcess 53:0 (b68) UMS Context 0×27F80DE8 appears to be non-yielding on Scheduler 1.This problem occurs if the following conditions are true: The query performs operations on binary large object (BLOB) data types. Examples of binary large object data types include the Text data type, the NText data type, and the Image data type. The query contains the UNION operator or the OUTER JOIN operator. The query spends significant time processing the binary large object data types.

Symptoms
If all of the following conditions are true, a restore to a point-in-time leaves the database in a loading state (unrecovered) and does not stop at the requested time: You perform the restore through SQL Enterprise Manager (SEM).You restore one backup file (device) at a time, and you specify a “Point in Time” to stop in the last transaction log you restore.
Resolution
A restore to a point-in-time from Enterprise Manager does not stop at the requested time because Enterprise Manager automatically changes the point-in-time value to the backup time of the last transaction log.
A mechanism in the restore to a point-in-time leaves the database in a loading state if there are no log entries after the time you specify, to allow you to apply more transaction logs if you want.

Symptoms
A restore of the msdb system database leaves the database in a loading state (unrecovered) and changes in the differential and log backups are not applied if the following conditions are true:The restore is performed from Enterprise Manager (SEM).The restore operation is a combination of:
A full database backup and a differential backup.A full database backup and transaction log backups.A full database backup and a combination of differential and transaction log backups.NOTE: To perform transaction log backups of the msdb database in SQL Server 2000, you must change the recovery model to “bulk logged” or “full.” In SQL Server 7.0, you must have the “trunc. log on chkpt.” setting turned off for the msdb database.
Resolution
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 7.0. For additional information, click the following article number to view the article in theMicrosoft Knowledge Base:
301511?(http://support.microsoft.com/kb/301511/EN-US/) INF: How to Obtain the Latest SQL Server 7.0 Service Pack