Troubleshooting Out of Sync Error in Log Shipping

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:

1. 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.
2. 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.
3. 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.
4. 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.
5. 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:

1. 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.
2. 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.
3. 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.
4. 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.
5. 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.

System Databases in SQL Server

SS supports 3 types of dbs

 * System Dbs
 * Sample ,,
 * User Defined Dbs.
Note:
 Don't install Sample dbs in production server.

System Databases

* These are mandatory dbs which consists of meta data of ther server.
* We cannot configure the following features on system dbs.
  * Replication
  * Log Shipping
  * Db Mirroring

* By default at the time of installation 5 system dbs     are created.
  * Master
  * Model
  * MsDb
  * TempDb

  * Resource (Hidden db)

* We cannot detach the system dbs directly.
* To detach system dbs the server should be running in    single user mode with trace flag 3608

 c:\Net start mssqlserver /m /c /T3608

* Only Model and MSDB can be detached.

Note:
 To detach MSDB what are the steps
 
 Sol:
  * Start server with single user mode        with trace flag 3608
  * Stop SS agent service.

1. Master
---------
* It works as entry point for SQL Server.
* Total server level settings are stored in master db.
* We cannot detach master db.
* To restore master db we have to run the server in     single user mode.
* Its db id is 1.
* By default guest user is enabled.
* Its recovery model is SIMPLE.
* It consists of
 * All dbs details (sysdatabases)
 * Logins (SysLogins)
 * Linked servers information (sys.servers)
 * Error Messages (sysmessages)
 * Server level configuration settings
    etc
FAQ:- How to move master db.
 
Steps
------
 1. Stop server
 2. Move the master db data file and TLog file     into different location.
 3. Provide read/write permissions on the     destination folder to SQL Server service     account.
 4. Change the path in start up parameters.
 5. Start the SQL Server service.

* We need daily backups.

2. Model
--------
* Works as template for new dbs.
* It consists of system defined objects (1788/1978)     which are copied into every new db.
* No need of regular backups.

FAQ:- Once I create a new db there should be one user       ssrs_user.
Sol:
 Create the user (ssrs_user) in model db.

3. MsDb
-------
* Consists of total scheduling information.
* Automation details are stored in this db.
* It consists of the details

 * Backup and restore
 * Db Mail
 * Log Shipping
 * SSIS Packages
 * Maintenance plans
 * Jobs
 * Alerts
 * Operators
  etc
* It consists of speacial roles related to SSIS and Db    mail.
 * DatabaseMailUserRole
 * db_ssisAdmin
 * db_ssisltdUser
 * db_ssisoperator

* Regular backups are required.

4. TempDb
---------

* Consists of Temp objects like 
 * Temp tables, cursors, resultsets of join
 * The process of ordering, grouping is done    here. 
 * DBCC Checkdb(Dbname) utilizes more tempdb
 * Rebuilding indexes consumes more space in    tempdb.
* No need of backups.
* It is created/cleared when the server is started.
* Add similar no of data files similar to no of CPUs in   the server.
* Place tempdb files in highest rpm disk.

FAQ:- TempDb is growing fastly. How u know which        transaction is causing the problem?

Sol:
 DBCC OPENTRAN -- or DBCC OPENTRAN('tempdb')

FAQ:- If TempDb full. How to resolve the issue?

 1. Short term fix
 -----------------
  * Find long running query which is     causing tempdb to grow and kill it by     user acceptance. 
  * Truncate the T.Log file of tempdb     periodically by setting the recovery      model to simple.

 2. Long Term Fix
 ----------------
  * Restart the server by raising CM with     client.

5. Resource
-----------
* Consists of all system defined objects physically.
* It maintains the service packs changes.
* By using old data file and T.Log file of this db we   can rollback any changes (sps).
* This db files are present in

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn

6. Distribution
---------------
* It is create automatically when the instance is   qualified as distributor in replication feature.
* It consists of complete replication details.

Note:
 If we install Reporting Services then 2 more  dbs are created automatically
  * ReportServer
  * ReportServerTempdb

Recovery Models in SQL Server

Database Recovery models plays an important role in the data recovery and high availability possibilities in SQL Server. Complete behaviour of Transaction Log file of a database depends on recovery models. The following features of Transaction Log depends on recovery models of database.

            1. What is recorded in Transaction Log File.
            2. Which types of backups are possible.
            3. When the Transaction Log file is truncated.
            4. Log shipping , Database mirroring are possible or not.
            5. Point in time recovery is possible or not.

• SQL Server supports 3 types of recovery models
• Full
• Bulk Logged
• Simple

We can set the recovery model of database as follows
    USE MASTER
    GO
   ALTER DATABASE <dbName> SET RECOVERY <FULL/BULK_LOGGED/SIMPLE>

We can check the recovery model of database from sysdatabases or sys.databases view of master database.