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

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

Some changes are not replicated to the subscribers on Transactional Replication SQL Server 2005

You can insert the deleted row in the subscriber database( if you have a backup of table)

Then start the replication agent , It will fix the problem.
http://support.microsoft.com/kb/954054

Replication in SQL Server

Replication is one of typical functionality in SQL Server. Basically Replication lets you to copying and distributing data and database objects like table, stored procedure, view and UDF to one publisher database to multiple subscription database/server on different location.


There are three types of replication

1. Snapshot
2. Transactional
3. Merge.

Replication need a special database called distribution to copying data to another server.
Main components in Replication

Distributor : Main functionality is distributing data between Publisher and Subscriber.

Publisher : Publisher is main Database Server which has database to replicating data to subscriber
You can create many publications from Publisher. There is limit for publishing articles. Maximum number of articles can be published under one publication is 256.

Subscriber : Always receiving data from publisher and in merge replication subscriber also sending back data changes to publisher

Articles : Articles nothing but Tables

There are five agents are there

1. Snapshot agent
2. Log Reader agent
3. Queue Reader agent
4. Distribution agent
5. Merge agent

sp_change_users_login to fix Orphaned SQL Server users

The stored procedure sp_change_users_login used  to fix Orphaned SQL Server users.

Microsoft says this feature will be removed in a future version of Microsoft SQL Server.  so instead of using this sp start using  ALTER USER command.

FILESTREAM

The new FILESTREAM funcationality  in the SQL Server 2008 allows you to configure a VARBINARY(max) column, so that the actual data in stored in the file system/storage area rather than within the database.

To enable FILESTREAM feature you must configure SQL Server Service for that instance.

sp_configure 'filestream_access_level' , 2


go
Reconfigure

Note : Database Mirroring is not compatiable with FILESTREAM data

The mirror database has insufficient transaction log data

When you Setting Database Mirroring if you will get the error message like  "The mirror database has insufficient transaction log data".


The mirror database, "Northwind", has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database. (Microsoft SQL Server, Error: 1478)

Error : The mirror database has insufficient transaction log data
Solution :
1.. Take the Transaction Log backup from Principal server database and
2. Restore Log on Mirror database
 Restore Log on Northwind from disk = 'c:\mssql\backup\DBP_TLog.TRN' with NORECOVERY
3. Now try to setting up Database Mirroring again from Principal Server

Microsoft SQL Server 2008 Service Pack 2

You can write in Query window to find which version of SQL Server 2008 is running ?


SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

(or)
SELECT  @@Version
Microsoft SQL Server 2008 Service Pack 2 (SP2) is now available for download.

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=8fbfc1de-d25e-4790-88b5-7dda1f1d4e17

Version                                   Build
-----------------------------------------------------
Service Pack 2 for SQL Server 2008     10.0.4000.00
Service Pack 1 for SQL Server 2008     10.0.2531.00
RTM                                    10.0.1600.22

Dedicated Administrator Connection (DAC)

The DAC allows an dba to access a SQL Server to troubleshoot problems. You can connect using either SQLCMD or SSMS.

DAC is working with port 1434 to allow client connection from Remote computers

To enable DAC for a remote computer,

sp_configure 'remote admin connections', 1;
go

In the Connect to Database Engine dialog box, in the Server name box, type ADMIN: followed by the name of the server instance. For example, to connect to a server instance named UKDEVSQL\HRMS,
type ADMIN:UKDEVSQL\HRMS

Data and Log Files configuration

1. There are two types of data files in SQL Server  Primary and Secondary

2. You can have one Data Primary file and 32766 Data Secondary file in SQL 2005
3. Transaction log records database modification you can create multiple log files per database to facilitate faster recovery
4. Filegroup is a logical structure that lets DBAs group data files and mange them as a logical unit
5. Filegroup contains data only, you can not create log file
6. Configuring database files with RAID(Redundant array of inexpensive disks) for better peformance
7. RAID 1 known as disk mirroring, provides a redundant copy of the selected disk, RAID1 improves read performance but can degrade the performance of write operations
8. RAID5 stripes the data across the disk of the RAID but it also adds parity information to provide fault tolerance.
9. RAID10 offer better availability and performance than RAID5
10. RAID10 is recommended RAID system for Transaction log, Data files and Index files
11. Always place Transaction log in RAID10 and Data and Index files on RAID5
12. Do not put Data files on the same drive that contains OS files
13. Put the Transaction log file(LDF) on separate drive from data files
14. Put the Tempdb database on a separate drive possible preferably on RAID 10 or RAID 5

Microsoft SQL Server code-named “Denali” – the next version of SQL Server

This is an exciting time for the SQL Server Team. After Ted Kummert announced the next release of SQL Server, Microsoft SQL Server code-named “Denali”, yesterday at the opening keynote of PASS Summit 2010, today Quentin Clark, General Manager of the Database Systems Group, gave PASS attendees a closer look into some of the exciting new features of code-named “Denali”.


SQL Server code-named “Denali” will help empower organizations to be more agile in today’s competitive market. Customers will be able to efficiently deliver mission-critical solutions through a highly scalable and available platform. Industry-leading tools will help developers quickly build innovative applications while data integration and management tools help deliver credible data reliably to the right users and new user experiences expand the reach of BI to enable meaningful insights.

Today, we showcased the following areas of code-named “Denali” including new capabilities that will be delivered to customers:

Enhanced mission-critical platform: An enhanced highly available and scalable platform through the new SQL Server AlwaysOn for greater flexibility in achieving increased availability and data protection and new Column-Based Query Accelerator for huge performance gains in data warehousing.

Developer and IT Productivity: A new unified development experience for data developers called SQL Server Developer Tools code-named “Juneau”, new beyond relational enhancements including FileTable for file storage within the SQL Server database, easier of use delivered via SQL Server AlwaysOn, data integration tools and features such as security & supportability.

· Pervasive Insight: Expand the reach of BI to business users via Project code-name “Crescent”, a highly interactive, web-based data exploration and visualization tool built on the breakthrough analytical performance of the VertiPaq technology. Meanwhile, holistic data integration and management tools through enhanced Master Data Services and new Data Quality Services will help ensure organizations can deliver the right data to the right users at the right time.

We are also making available the first Community Technology Preview (CTP) for SQL Server code-named “Denali”. You can download SQL Server code-named “Denali” CTP today!

The first public CTP provides an opportunity for early adopters to submit feedback on an initial set of capabilities including:

· Select capabilities of the new integrated high availability solution, SQL Server code-named “Denali” AlwaysOn, including availability groups, single active secondary for offloading read-only workloads and multi-site clustering

· Flexible server roles to allow administrators to create custom roles for ease separation of duties
· Simplified development and standardized deployment, configuration and management for SQL Server Integration Services

Here are a few things you can do to get to know SQL Server code-named “Denali” a little better:
1. Visit the code-named “Denali” web page
2. Download the first CTP
3. Reference Books Online

Download the CTP, play with it, and let us know what you think. We’re excited about SQL Server code-named “Denali” and we hope you are too!

http://blogs.technet.com/b/dataplatforminsider/rss.aspx

SQL Server Top 10 Secrets of Tempdb database

1. Tempdb is re-created every time when SQL Server restarts
2. Tempdb is always SIMPLE recovery mode
3. Certain database options can not be set for tempdb like OFFLINE, READONLY & CHECKSUM
4. You can not drop or backedup tempdb database
5. You can not run DBCC CHECKDB against Tempdb
6. You can not drop the guest user from Tempdb
7. Snapshot database can not be created against Tempdb
8. Shrinking Tempdb is not recommended
9. Move Tempdb database to new location after installation of sql server
10. Create a multiple Tempdb files give you better performance one file per CPU( Microsoft Best practice guide line says, It may be good for SAN), You need carefully plan this if you server really want it)

Managing Data Files and Transaction Log files

a. If you have more than one Data Files( MDF) make sure to keep same size of all data files e.g( AdvenDb_Data1.MDF, AdvenDb_Data.NDF)

b. Place the Transaction log file(LDF) on separate Drive

c. Disable auto shrink option

d. Alter database to increase size to make more space for data files

e. Auto growth should be configured correctly

f. Enable Auto Update Statistics &  Auto Create Statistics

g. Monitor disk space on Data file and Transaction Log file Drive

h. Monitor database size and Transaction Log file size set threshold level to get notification

i. Page Verify option set to CHECKSUM

Snapshot Database

You can not create snapshot database using SSMS instead you should use Query window to write T-SQL
If any of your user database has a snapshot database you can not dropped , restored, detached the user database.
Snapshot database can not be created against system databases like master , model and tempdb
Snapshot database can not be backup/restored.

Deploying SQL Server 2005 with SAN #1

Microsoft  recommendation is to use RAID 10 both for logs and data/index files. When cost is an issue you could allocate data and index files on RAID 5 or equivalent; however, keep logs on RAID 10.


RAID 10 offers better availability than RAID 5 and better performance especially for write-intensive applications. Some of our customers have reported up to 50% adverse impact on write intensive work load when moving to RAID 5. The impact on performance of RAID 5 is dependent on the hardware vendor’s implementation of RAID 5 and the characteristics of your particular work load. You should not assume that you will get a 50% adverse impact – it could be higher or lower.

http://blogs.msdn.com/b/sqlcat/archive/2005/10/11/479887.aspx

SQL Server Error Logs

View SQL Server Error Logs in Query
EXEC xp_readerrorlog   -- (current error log file)
EXEC xp_readerrorlog 3 -- (error log file number #3)

You can re-cycle the error log by executing the DBCC ERRORLOG command (or) the sp_cycle_errorlog system procedure.

How to Enable the Lock Pages in Memory Option on Windows Server

The Windows policy Lock Pages in Memory option is disabled by default. This privilege must be enabled to configure Address Windowing Extensions (AWE). This policy determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk. On 32-bit operating systems, setting this privilege when not using AWE can significantly impair system performance. Locking pages in memory is not normally required on 64-bit operating systems. You will need to enable this right on 64-bit operating systems only when using Large Page Memory support or to configure SQL Server such that the Buffer Pool memory does not get paged out. Use the Windows Group Policy tool (gpedit.msc) to enable this policy for the account used by SQL Server 2005 Database Engine. You must be a system administrator to change this policy.


To enable the lock pages in memory option

--------------------------------------------------------------------------------
1.On the Start menu, click Run. In the Open box, type gpedit.msc.
The Group Policy dialog box opens.
2.On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.
3.Expand Security Settings, and then expand Local Policies.
4.Select the User Rights Assignment folder.
The policies will be displayed in the details pane.
5.In the pane, double-click Lock pages in memory.
6.In the Local Security Policy Setting dialog box, click Add.
7.In the Select Users or Groups dialog box, add an account with privileges to run sqlservr.exe.

Login failed for user 'DomainName\SQLAdmin'. (.Net SqlClient Data Provider)

Login failed for user 'DomainName\SQLAdmin'. (.Net SqlClient Data Provider)


Go to Start, Programs, SQL Management Studio -- Right Click on that and say Run as Administrator. Now pass your credentials.
This will allow to you open SSMS for your login(SQLAdmin).

But if you need to fix permanently The user SQLAdmin must be part of Local administrator group of the server and should have full rights to access SQL server installation path like C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER

Find the Database is set for snapshot_isolation

SELECT name,snapshot_isolation_state,
snapshot_isolation_state_desc AS Descriptions FROM sys.databases

Check the Merge Replication Generation by date wise

select convert(varchar(12),coldate)as Dates , count(*) as generation
from MSmerge_genhistory (nolock)
where year(convert(varchar(12),coldate))= '2010'
group by convert(varchar(12),coldate)
order by convert(varchar(12),coldate)

Monitor Replication in SQL 2005

The following list of sps really useful to get more information from distribution database to monitor replication.

use distribution

go
select * from MSPublisher_databases
Sp_browsereplcmds
sp_MSenum_snapshot
sp_MSenum_logreader
sp_MSenum_distribution
sp_MSenum_qreader

Check Merge Replication Confilicts 2005

CREATE PROCEDURE [dbo].[DBA_MonitorMergeConflicts]
AS
/****************************************************
Purpose : Check Merge Replication Confilicts and send
an email notification to DBA
*****************************************************/
SET NOCOUNT ON
Declare @iCnt int
Select @iCnt = count(*) from NorthWind.dbo.MSmerge_conflicts_info
--Print @iCnt


If @iCnt > 0
Begin
 EXEC msdb.dbo.sp_notify_operator
 @profile_name = N'DBAMail',
 @name = N'dba',
 @subject = N'Merge Conflicts Notification',
 @body = N'There are Merge Replication Conflicts Please check the  View Conflicts to Resolve' ;
End

Find SQL Server Information

sp_server_info

Find Linked Server Details

sp_helpserver

Transfer Backup File to another server do not use xp_cmdshell

By default in SQL Server 2005 and 2008 xp_cmdshell is disabled for security reason.

Instead of using xp_cmdshell you can create batch file to transfer bak file copy to another server

The TransferBackupFile contains the following line of command




Copy E:\mssql\backup\mydb.bak \\ServerName\E$\MSSQL\Backup  /y

The merge process was unable to deliver the snapshot to the Subscriber

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\ReplData

Solution : Change default snapshot folder settings to full qualified network name
\\SRVSQL001\C$\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\ReplData

Remove Distribution Database

-- In SQL 2005
exec sp_dropdistributor @no_checks =1, @ignore_distributor = 1;

go

Find Replicated Database

--SQL 2005/2008
sp_helpreplicationdboption

--SQL 2000
select name, databasepropertyex (name,'IsMergePublished') from
master..sysdatabases
select name, databasepropertyex (name,'IsPublished') from
master..sysdatabases

SQL Server 2008 Internals

I have been reading Kalen Delaney articles and books so many years. She is one of the world best SQL Server Specialist.

SQL Server running on Windows Server 2008 R2 Enterprise (x64)

It is amazing to work one of the high-end SQL Server 2005(64-bit) running on Windows Server 2008 R2 (x64-bit) Enterprise edition with 130GB of RAM. The Intel X7560 processor has 24Logical processors for the entire server.

Move the Tempdb database to new location

Move the tempdb database to new location to speed up the system

-- Find out the current default location of the tempdb database when you installed in SQL 2005
sp_helpdb tempdb

tempdev 1 E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf
templog 2 E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\templog.ldf


-- Move to new location for fastest disk
USE master
go

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'E:\MSSQL\Data\tempdb.mdf')
go

ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'E:\MSSQL\Data\templog.ldf')
go

Database Recovery Model and Compatibility Level

Find the Recovery model and compatibility level for all databases on instance.

SELECT [name], recovery_model_desc, [compatibility_level]
FROM sys.databases;

Find Server Information on SQL Server 2008

exec xp_msver "ProductName", "ProductVersion", "Language", "Platform",
"WindowsVersion", "PhysicalMemory", "ProcessorCount"

Microsoft certified technology specialist

MCTS in SQL Server 2008

PivotViewer Extension for Reporting Services

PivotViewer Extension for Reporting Services is a utility that enables users to effortlessly build stunning data experiences on top of their Business Intelligence data.

Please visit Microsoft web site for more information about Pivot viewer.
http://www.microsoft.com/sqlserver/tour/en/videos/pivot-viewer.aspx

You Cannot drop the guest user from master or tempdb

---------------------------
Microsoft SQL-DMO (ODBC SQLState: 42000)
---------------------------
Error 15182: Cannot drop the guest user from master or tempdb.
---------------------------
OK
---------------------------

Find Database collation

-- List SQL Server default collation
sp_helpsort

Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data

select SERVERPROPERTY('Collation')
SQL_Latin1_General_CP1_CI_AS



-- Find a collation name of users database
SELECT CONVERT (varchar, DATABASEPROPERTYEX('database_name','collation'))
eg:
-- find master database collation
SELECT CONVERT (varchar, DATABASEPROPERTYEX('master','collation'))


--List out all Collations
SELECT * from ::fn_helpcollations()

SQL ServerProperty Info

SELECT SERVERPROPERTY('ServerName')
ASIAPAC06

SELECT SERVERPROPERTY('Machinename')
ASIAPAC06


SELECT SERVERPROPERTY('edition')
Enterprise Evaluation Edition

SELECT SERVERPROPERTY('enginedition')
NULL

SELECT SERVERPROPERTY('Instancename')
NULL

SELECT SERVERPROPERTY('ProductVersion')
10.50.1600.1

SELECT SERVERPROPERTY('ProductLevel')
RTM

SELECT SERVERPROPERTY('ResourceVersion')
10.50.1600

SELECT SERVERPROPERTY('ResourceLastUpdateDateTime')
2010-04-02 17:38:24.957

Creating Database Snapshot on SQL Server 2005

When you Creating Database Snapshot on SQL Server 2005 Standard Edtion you will receive the following error message. This feature will available on SQL Server Enterprise Edition only.

--Error
Msg 1844, Level 16, State 1, Line 1
Database Snapshot is not supported on Standard Edition.


-- Script to Create Database Snapshot
CREATE DATABASE NorthWindb_test_Snapshot
ON ( NAME = N'NorthWindb_test_datafile',
FILENAME = N'C:\MSSQL\Data\NorthWindb_test_Snapshot.snap' ) AS SNAPSHOT OF NorthWindb_test;

How to check Transaction log space used in SQL Server Databases?

Displays Transaction Logspace information for all databases in the current instance.

dbcc sqlperf(logspace)
go

SQL Server 2008 Express Edition Storage Limit

SQL Server 2008 Express Edition supports 10 GB database storage.

Designed to work with Visual Studio and ASP.NET.

Difference between sysxlogins and syslogins in SQL 2000 and 2005

In SQL 2005
System table sysxlogins(master database) is removed from SQL 2005.
and SysObjects and SysUsers are changed from System table to View



-- In SQL 2000
use master
go
select * from sysobjects where name in ('sysxlogins' ,'syslogins') order by xtype
select * from sysobjects where name in ('sysobjects','sysusers') order by xtype


sp_help sysxlogins --system table
go
sp_help syslogins-- view

sp_help sysobjects --system table
go
sp_help sysusers --system table
----------------------------------------------------------

-- In SQL 2005
use master
go
select * from sysobjects where name in ('sysxlogins' ,'syslogins') order by xtype
select * from sysobjects where name in ('sysobjects','sysusers') order by xtype


sp_help sysxlogins --system table is REMOVED no longer exists
go
sp_help syslogins-- view


sp_help sysobjects --VIEW
go
sp_help sysusers --VIEW

Creating Indexed View in Microsoft SQL Server 2005 & 2008

Indexed View is the one of the best solutions to Business users


use Northwind
go

sp_help Orders
sp_help Customers

select * from Customers
select * from Orders



-- Select data from two tables for test
select OrderID, OrderDate , Orders.CustomerID,CompanyName
from Orders Inner Join Customers
on Orders.CustomerID = Customers.CustomerID
go

-- Step 1

-- create view from joined two tables
create view [dbo].[vw_OrderbyCustomer] with schemabinding as
(
select [OrderID], [OrderDate] , [Orders].CustomerID, [CompanyName]
from [dbo].[Orders]
Inner Join [dbo].Customers on [Orders].CustomerID = [Customers].CustomerID
)
go

select * from [vw_OrderbyCustomer]


-- Step 2
-- create a Clustered Index on View
create unique clustered index Idx_CI_View_OrderId_CustomerId on [vw_OrderbyCustomer](OrderID,CustomerID)

-- check the Index from Tables
sp_helpIndex vw_OrderbyCustomer

-- create a NonClustered Index on View
create unique index Idx_NCI_View_OrderId on [vw_OrderbyCustomer](OrderID)
create index Idx_NCI_View_CustomerId on [vw_OrderbyCustomer](CustomerID)
create index Idx_NCI_View_OrderDate on [vw_OrderbyCustomer](OrderDate)
create index Idx_NCI_View_CompanyName on [vw_OrderbyCustomer](CompanyName)

-- Step 3
sp_helpIndex vw_OrderbyCustomer

Idx_CI_View_OrderId_CustomerId clustered, unique located on PRIMARY OrderID, CustomerID
Idx_NCI_View_CompanyName nonclustered located on PRIMARY CompanyName
Idx_NCI_View_CustomerId nonclustered located on PRIMARY CustomerID
Idx_NCI_View_OrderDate nonclustered located on PRIMARY OrderDate
Idx_NCI_View_OrderId nonclustered, unique located on PRIMARY OrderID

Setting up new subscription for Merge Publication Server

Setting up new subscription for Merge Publication Server in SQL 2000

Issue : Schema Definition of base Table at Subscriber Does Not Match with Table in Publisher Database




Solutions:
1. Check the table structure of the Publisher database and Subscriber database are same.

 

Install SQL Server 2005 on Windows XP

Installing SQL Server 2005 Standard Edition on Windows XP will be failed.

Installation of MSXML 6 Service Pack 2 (KB954459) failed because a higher version already exists on the machine.

Solutions:
Using Windows Installer CleanUp utility to remove references to MSXML 6.0 Service Pack 2.
http://support.microsoft.com/kb/290301

Install SQL Server 2005 on Windows xp.