SQL Server Q&A

Symptoms
This article discusses two alternative approaches to a disaster recovery plan for transactional replication. This article contains more information about the following:Problems in the approach for the disaster recovery plan that is mentioned in SQL Server Books Online.Two alternative approaches to a disaster recovery plan.Step-by-step procedure to set up a disaster recovery plan for the two alternative approaches.Step-by-step procedure to recover the replication databases for the two alternative approaches.
Resolution
In SQL Server Books Online, the “Strategies for backing up and restoring transactional replication” topic discusses the disaster recovery plan for transactional replication. However, the approach that is mentioned in SQL Server Books Online may not satisfy the requirement of minimum latency. This article discusses two alternative approaches that can be used to set up a disaster recovery plan for databases that are involved in transactional replication. The approaches that are discussed in this article can be used to recover the replication databases from a failure situation, such as a hardware failure.

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
When you restore a system database from a backup device file in Microsoft SQL Server, you may receive an error message that is similar to the following:

The backup of the system database on device Full Path of the Backup Device File cannot be restored because it was created by a different version of the server (134218407) than this server (134218488).
RESTORE DATABASE is terminating abnormally.
The error message contains version numbers in parentheses that are not valid (7.00.623 for SQL Server 7.0 or 8.00.760 for SQL Server 2000 Service Pack 3 [SP3], for example).
Resolution
To determine the correct version of a database backup device file in SQL Server, run the RESTORE HEADERONLY Transact-SQL statement using the backup device file name.TheRESTORE HEADERONLY Transact-SQL statement returns the following columns to help you determine the version of the database backup file:SoftwareVersionMajorSoftwareVersionMinorSoftwareVersionBuildFor more information about the RESTORE HEADERONLY Transact-SQL statement, visit the following Microsoft Web site:
http://msdn2.microsoft.com/en-us/library/aa238455(SQL.80).aspx(http://msdn2.microsoft.com/en-us/library/aa238455(SQL.80).aspx)The decimal numberthat indicates the version of SQL Server in the error message is obtained by converting part of the SQL Server version number to a hexadecimal representation.You can also manually determine the actual version number of SQL Server from the decimal number that is mentioned in the error message.To do so, follow these steps: Convert the decimal number that is mentioned in the error message to a hexadecimal representation. Convert only the last four digits of the hexadecimal representation to a decimal representation.Replace the last three digits of the hexadecimal representation with the decimal number from step 2.To determine the actual version number of SQL Server, format the result by using the information in the “Standard format of SQL Server version numbers” section of this article.Note You can use the Calculator program in Microsoft Windows in Scientific view to convert the number from a decimal representation to a hexadecimal representation.
For example, to determine the actual SQL Server version number for the decimal number that is mentioned in the error message (134218407), follow these steps: Convert the decimal number 134218407 to a hexadecimal representation. The hexadecimal representation of 134218407 is 80002A7.Convert the last four digits of the hexadecimal representation to a decimal representation. The decimal representation of 02A7 is 679.Replace the last four digits of the hexadecimal representation with the decimal numbers that you obtained in step 2. The number is 800679.Format the number in step 3 by using the information in the “Standard format of SQL Server version numbers” section of this article. The version of SQL Server is 8.00.679.
Standard format of SQL Server version numbersThe version numbers of SQL Server use the following standard format:
Major version number.Minor version number.Build number
For example, the version number of an instance of SQL Server 2000 SP3 is 8.00.760, where the first number (8) indicates the major version number, the next two numbers (00) indicate the minor version number, and the last three numbers (760) indicate the build number.

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.

Symptoms
The sqlmaint utility does not generate a message when attempting to back up the transaction log from a database that has the truncate log on checkpoint option set. This is also true when using the Database Maintenance Plan Wizard.
The backup of the log takes place and a blank file is created. However, if you run the backup by using the SQL Query Analyzer, the following error message occurs:

Server: Msg 4208, Level 16, State 1, Line 1
BACKUP LOG is not allowed while the trunc. log on chkpt. option is enabled. Use BACKUP DATABASE or disable the option using sp_dboption.
Server: Msg 3013, Level 16, State 1, Line 1
Backup or restore operation terminating abnormally.
Resolution
You can get SQL Server to report an error message if you have it write to either an HTML file or to a table in the msdb database. Use the following Sqlmaint.exe parameters:HtmlRpt, which writes to an HTML file.
-or-
WriteHistory, which writes a history to msdb.dbo.sysdbmaintplan_history.For example:

C:\MSSQL7\Binn>sqlmaint -D pubs -S servername -U sa -BkUpLog “c:\temp\pubslog.bak” -BkUpMedia DISK -UseDefDir -HtmlRpt “c:\temp\sqlmaint.htm”