SQL Server Q&A

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
When you try to run a Transact-SQL query from an application in Microsoft SQL Server 2000, an access violation may occur. This problem occurs when the following conditions are true: The Transact-SQL query contains a UNION operator. The Transact-SQL query uses a parallel execution plan. When this problem occurs, the following error message is logged in the SQL Server error log:

<Date> <Time> server Microsoft SQL Server 2000 – 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1) … 2005-08-31 12:21:15.46 spid54 DBCC TRACEON 2861, server process ID (SPID) 54.
<Date> <Time> spid57 Open of fault log C:\Program Files\Microsoft SQL Server\MSSQL\log\exception.log failed.
<Date> <Time> spid57 Open of fault log C:\Program Files\Microsoft SQL Server\MSSQL\log\exception.log failed.
<Date> <Time> spid57 Open of fault log C:\Program Files\Microsoft SQL Server\MSSQL\log\exception.log failed.
<Date> <Time> spid57 Using ‘dbghelp.dll’ version ‘4.0.5′
*Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL\log\SQLDump0920.txt
<Date> <Time> spid57 SqlDumpExceptionHandler: Process 5984 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
* ******************************************************************************* * * BEGIN STACK DUMP:
<Date> <Time> spid 57
* Exception Address = 0040560F
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 000000B8
* d b o . P I H D 14 00 64 00 62 00 6f 00 2e 00 50 00 49 00 48 00 44 00
* e l e t e _ L e v 65 00 6c 00 65 00 74 00 65 00 5f 00 4c 00 65 00 76 00
* e l 2 §2 2 65 00 6c 00 32 00 00 00 00 00 a7 32 00 09 04 00 01 32
* F49 § 2 a 03 00 46 34 39 00 00 a7 08 00 09 04 00 01 32 07 00 61
* cdgood 63 64 67 6f 6f 64
* ——————————————————————————-
* Short Stack Dump * 0040560F Module(sqlservr+0000560F) * 00603FF5 Module(sqlservr+00203FF5) (SQLExit+0009C4FE)
* 0053577C Module(sqlservr+0013577C)
* 00420A14 Module(sqlservr+00020A14)
* 00874277 Module(sqlservr+00474277) (GetIMallocForMsxml+0007F6F7)
* 00875200 Module(sqlservr+00475200) (GetIMallocForMsxml+00080680)
* 00875611 Module(sqlservr+00475611) (GetIMallocForMsxml+00080A91)
* 005BB2F6 Module(sqlservr+001BB2F6) (SQLExit+000537FF)
* 005BAAB9 Module(sqlservr+001BAAB9) (SQLExit+00052FC2)
* 00866D25 Module(sqlservr+00466D25) (GetIMallocForMsxml+000721A5)
* 00868002 Module(sqlservr+00468002) (GetIMallocForMsxml+00073482)
* 00868B1E Module(sqlservr+00468B1E) (GetIMallocForMsxml+00073F9E)
* 0087E1CD Module(sqlservr+0047E1CD) (GetIMallocForMsxml+0008964D)
* 0087E422 Module(sqlservr+0047E422) (GetIMallocForMsxml+000898A2)
* 0055C692 Module(sqlservr+0015C692)
* 41075309 Module(ums+00005309) (ProcessWorkRequests+000002D9 Line 456+00000000)
* 41074978 Module(ums+00004978) (ThreadStartRoutine+00000098 Line 263+00000007)
* 7C34940F Module(MSVCR71+0000940F) (endthread+000000AA)
* 77E66063 Module(kernel32+00026063) (GetModuleFileNameA+000000EB)
*Dump thread – spid = 57, PSS = 0×530731f8, EC = 0×53073528 Additionally, the following events are logged in the Application log:
Event 1
Error 17055 <Date> <Time> MSSQLSERVER DUALP Server <ComputerName>\<Login> 17310 : SqlDumpExceptionHandler: Process 1780 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.Event 2
Information 17052 <Date> <Time> MSSQLSERVER DUALP Server N/A Error: 17883, Severity: 1, State: 0 Process 56:51 (c84) UMS Context 0×0029DF20 appears to be non-yielding on Scheduler 1

Symptoms
When you run a RESTORE DATABASE WITH RECOVERY statement to bring a standby server online, you might receive one of the following error messages:

Error: 9003
The LSN %S_LSN passed to log scan in database ‘%.*ls’ is invalid.

Error: 9004
An error occurred while processing the log for database ‘%.*ls’.
Resolution
This problem occurs because physical characteristics (virtual log parity) for inactive virtual log files in the transaction log are not preserved when a full database backup is restored.

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.

Symptoms
The default autogrow and autoshrink settings will work for you with no tuning on many SQL Server systems. However, there are environments where you do not have to turn the settings on or where you may have to adjust the autogrow and autoshrink parameters. This article gives you some background information to guide you when you select the settings for your environment.
Resolution
Here are some things to consider if you decide to tune your autogrow and autoshrink parameters.How do I configure the settings?You can configure the autogrow and autoshrink settings by using one of the following:An ALTER DATABASE statement (not available in SQL Server 7.0)SQL Server Management Studio or SQL Enterprise ManagerThe sp_dboption stored procedure (deprecated in SQL Server 2005)Note If you are using SQL Server 2005, use SQL Server Management Studio instead ofSQL Enterprise Manager. For more information about how to set these settings in SQL Server 2005, visit the following Microsoft Developer Network (MSDN) Web sites:
How to: Add Data or Log Files to a Database (SQL Server Management Studio)
http://msdn2.microsoft.com/en-us/library/ms189253.aspx(http://msdn2.microsoft.com/en-us/library/ms189253.aspx)
Database Properties (Files Page)
http://msdn2.microsoft.com/en-us/library/ms180254.aspx(http://msdn2.microsoft.com/en-us/library/ms180254.aspx)You can also configure the autogrow option when you create a database.
You can view the current settings through the database properties in SQL Enterprise Manager (SEM). Or, you can run the following Transact-SQL command:

sp_helpdb [ [ @dbname= ] ‘name’ ]Keep in mind that the autogrow settings are per file. Therefore, you have to set them in at least two places for each database (one for the primary data file and one for the primary log file). If you have multiple data and/or log files, you must set the options on each file. Depending on your environment, you may end with different settings for each database file.What are the performance implications?If you run a transaction that requires more log space than is available, and you have turned on the autogrow option for the transaction log of that database, then the time it takes the transaction to complete will include the time it takes the transaction log to grow by the configured amount. If the growth increment is large or there is some other factor that causes it to take a long time, the query in which you open the transaction might fail because of a timeout error. The same sort of issue can result from an autogrow of the data portion of your database. To change your autogrow configuration, see the “ALTER DATABASE” topic in SQL Server Books Online.If you run a large transaction that requires the log to grow, other transactions that require a write to the transaction log will also have to wait until the grow operation completes.If you combine the autogrow and autoshrink options, you might create unnecessary overhead. Make sure that the thresholds that trigger the grow and shrink operations will not cause frequent up and down size changes. For example, you may run a transaction that causes the transaction log to grow by 100 MB by the time it commits. Some time after that the autoshrink starts and shrinks the transaction log by 100 MB. Then, you run the same transaction and it causes the transaction log to grow by 100 MB again. In that example, you are creating unnecessary overhead and potentially creating fragmentation of the log file, either of which can negatively affect performance.Physical fragmentation from changing the size of the data or log files can have a severe affect on your performance. This is true whether you use the automatic settings or whether you manually grow and shrink the files frequently.If you grow your database by small increments, or if you grow it and then shrink it, you can end up with disk fragmentation. Disk fragmentation can cause performance issues in some circumstances. A scenario of small growth increments can also reduce the performance on your system.Best PracticesFor a managed production system, you must consider autogrow to be merely a contingency for unexpected growth. Do not manage your data and log growth on a day-to-day basis with autogrow.You can use alerts or monitoring programs to monitor file sizes and grow files proactively. This helps you avoid fragmentation and permits you to shift these maintenance activities to non-peak hours.AutoShrink and autogrow must be carefully evaluated by a trained Database Administrator (DBA); they must not be left unmanaged.Your autogrow increment must be large enough to avoid the performance penalties listed in the previous section. The exact value to use in your configuration setting and the choice between a percentage growth and a specific MB size growth depends on many factors in your environment. A general rule of thumb to you can use for testing is to set your autogrow setting to about one-eight the size of the file.Turn on the <MAXSIZE> setting for each file to prevent any one file from growing to a point where it uses up all available disk space.Keep the size of your transactions as small as possible to prevent unplanned file growth.Why do I have to worry about disk space if size settings are automatically controlled?The autogrow setting cannot grow the database size beyond the limits of the available disk space on the drives for which files are defined. Therefore, if you rely on the autogrow functionality to size your databases, you must still independently check your available hard disk space. The autogrow setting is also limited by the MAXSIZE parameter you select for each file. To reduce the possibility of running out of space, you can monitor the Performance Monitor counter SQL Server: Databases Object ata File(s) Size (KB) and set up an alert for when the database reaches a certain size.Unplanned growth of data or log files can take space that other applications expect to be available and might cause those other applications to experience problems.The growth increment of your transaction log must be large enough to stay ahead of the needs of your transaction units. Even with autogrow turned on, you can receive a message that the transaction log is full, if it cannot grow fast enough to satisfy the needs of your query.SQL Server does not constantly test for databases that have hit the configured threshold for autoshrink. Instead, it looks at the available databases and finds the first one that is configured to autoshrink. It checks that database and shrinks that database if needed. Then, it waits several minutes before checking the next database that is configured for autoshrink. In other words, SQL Server does not check all databases at once and shrink them all at once. It will work through the databases in a round robin fashion to stagger the load out over a period of time. Therefore, depending on how many databases on a particular SQL Server instance you have configured to autoshrink, it might take several hours from the time the database hits the threshold until it actually shrinks.

Symptoms
According to the “Creating and Applying Transaction Log Backups” topic in SQL Server 7.0 Books Online, after a database file is added or removed from the database, you should perform a full database backup instead of a transaction log backup.
Because the information about the change in the database file structure is not included in the transaction log, this process breaks an existing sequence of transaction log backups. You need to perform a new full database backup to start a new sequence of transaction log backups.
However, even though this is recommended in SQL Server Books Online, SQL Server does not enforce this behavior but permits you to perform an apparently valid transaction log backup at that time without giving a warning that the backup sequence is broken.
If such an invalid backup is made and then an attempt to restore it is made later, the attempt to restore this transaction log fails with the following message:

Server: Msg 3155, Level 16, State 1, Line 1
The RESTORE operation cannot proceed because one or more files have been added or dropped from the database since the backup set was created.
Server: Msg 3013, Level 16, State 1, Line 1
Backup or restore operation terminating abnormally.
Resolution
To work around this problem, perform a full database backup after modifying the file structure of a database.

Symptoms
If the log data file (LDF) for a database is not available during SQL Server startup (for example, if the file has been renamed or deleted), SQL Server 7.0 sometimes attempts to rebuild the LDF file automatically to ensure database availability.
Because the information from the original LDF file is lost, this process interrupts an existing sequence of transaction log backups. A new full database backup needs to be performed to start a new sequence on transaction log backups.
However, SQL Server permits you to perform an apparently valid transaction log backup at this time without warning you that the backup sequence is broken.
If you do make such an invalid backup and then later attempt to restore this transaction log, it fails with the following message:

Server: Msg 3155, Level 16, State 1, Line 1
The RESTORE operation cannot proceed because one or more files have been added or dropped from the database since the backup set was created.
Server: Msg 3013, Level 16, State 1, Line 1
Backup or restore operation terminating abnormally.
Resolution
To work around this problem, do either of the following:Make sure that the LDF files are not deleted or renamed.
-or-Perform a full database backup to start the transaction log backup sequence over.