Wednesday, December 29, 2010

Error message 14421 that occur when you use log shipping in SQL Server

Error: 14421, Severity: 16, State: 1


The log shipping secondary database NY-SQLSRV001. AdventureWorks has restore threshold of 45 minutes and is out of sync. No restore was performed for 150 minutes. Restored latency is 1154 minutes. Check agent log and logshipping monitor information.
 
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.

Solution:
1. Disable LogShipping Settings on user Database AdventureWorks from Primary Server.
2. Take the Full backup of user database AdventureWorks from Primary Server
3. Copy the backup file to Secondary server Shared Network folder
4. On Secondary server open the Query Report to run the following statement
-- Recover a Database from a Backup Without Restoring Data
-- Restore database using WITH RECOVERY.


RESTORE DATABASE AdventureWorks WITH RECOVERY
5. Now the User database AdventureWorks allow you to restore with NORECOVERY mode
6. Restored the AdventureWorks on Secondary Server from Backup file
7.Setting up Logshipping on Primary Server to Secondary server

If you use the linked server feature with SQL Server 2005 and 2008

If you use the linked server feature with SQL Server 2005 and 2008, please read through this carefully.
Microsoft Customer Support team discovered several problems that can result in memory leak(s).

http://blogs.msdn.com/b/psssql/archive/2009/09/22/if-you-use-linked-server-queries-you-need-to-read-this.aspx

Sunday, December 19, 2010

Fill Factor option for when Create Index or Rebuild Index

When creating a index or rebuilt, The FILL-FACTOR value determines the percentage of space on each leaf-level page to be filled with data. It is important to set the fill-factor value for each index. In practice a fill-factor value is set to 80 means that 20 percent of each level-level page will be left empty.

USE AdventureWorks2008;

GO
CREATE NONCLUSTERED INDEX IDX_WorkOrder_ProductID
ON Production.WorkOrder(ProductID)
WITH (FILLFACTOR = 80,
PAD_INDEX = ON);
GO