Online Re-Indexing Limitation on LOB Columns

Re-index maintenance job fails with error:

ALTER INDEX <Index_PK> ON [database].[dbo].[table] REBUILD  WITH(ONLINE = ON)  : An online operation cannot be performed for index 'Index_PK' because the index contains column 'Col1' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, or large CLR type. For a non-clustered index, the column could be an include column of the index. For a clustered index, the column could be any column of the table. If DROP_EXISTING is used, the column could be part of a new or old index. The operation must be performed offline.

Why it failed?

Online Index Build operations in SQL Server 2005, 2008 and 2008 R2 do not support tables that contain LOB columns but this limitation removed in SQL 2012.

Workaround:

Option1: Rebuild indexes using a custom script, such as Ola Hallengren's, available at http://ola.hallengren.com (recommended).

Option2: To have two re-indexing tasks, one for the table with LOB column/index by offline and other for all tables except the table with LOB by online.

Option3: Remove online index operations inside the Rebuild Indexes task in the Maintenance Plan.  This has a large impact on the server, so not recommended.

Find the Index fragmentation levels and Recomendations

SELECT Db_name(Db_id())             AS DBName,
       SS.name                      AS SchemaName,
       SO.name                      AS TableName,
       SI.name                      AS Indexname,
       index_type_desc              AS IndexType,
       avg_fragmentation_in_percent AS FragmentationPercentage,
       ( CASE
           WHEN avg_fragmentation_in_percent BETWEEN 10 AND 30 THEN
           'Time to Defrag'
           WHEN avg_fragmentation_in_percent > 30 THEN 'Time to Reindex'
           ELSE 'No Action Required Now'
         END )                      AS Recomendation
FROM   sys.Dm_db_index_physical_stats(Db_id(Db_name()), NULL, NULL, NULL,
       'DETAILED')
       IPS,
       sys.indexes SI,
       sys.objects SO,
       sys.schemas SS
WHERE  IPS.index_id = SI.index_id
       AND IPS.object_id = SI.object_id
       AND SI.object_id = SO.object_id
       AND SO.schema_id = SS.schema_id
       AND IPS.index_type_desc IN ( 'NONCLUSTERED INDEX', 'CLUSTERED INDEX' )
       AND IPS.indes_level = 0 -- leaf level only
ORDER  BY recomendation DESC 

Negative SPIDs in SQL Server

While investigating SQL performance issues,you might see SPIDs with negative number blocking other processes.

SPID -2  -> orphaned distributed transaction SPID

The SPID may still be holding on resources ( table, page or row locks), and blocking other SPIDs which want to access that database object. But  KILL command can’t handle SPIDs with a value of less than 1.

Kill -2
Msg 6101, Level 16, State 1, Line 1
Process ID -2 is not a valid process ID. Choose a number between 1 and 2048

To fix it:

select req_transactionUOW
from master..syslockinfo
where req_spid = -2

This will return a 32 digit UOW number eg: 'EDF10728-0156-1112-C501-AG48D72D2310'

KILL 'EDF10728-0156-1112-C501-AG48D72D2310'

Now if you check sp_who/sp_who2 all the offending SPID would have disappeared.

For negative SPIDS (-3 and -4)..

Please refer..

http://www.sqlskills.com/BLOGS/PAUL/post/Disaster-recovery-101-dealing-with-negative-SPIDS-%28-2-and-3%29.aspx

To Establish DB Mirroring between different domain servers using certificates

DB mirror cannot be established between different domain servers and might fail with below error.

 "The server network address "TCP://server.domain.com:5022" can not be reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)"

In this case, DB mirroring can be configured by using certificates.

Steps to setup DB mirroring using certificates.

On Principle Server:

-- Create the database master key
USE master;
CREATE master KEY encryption BY password = '<Password>';
go

-- Create a certificate for DB mirror
USE master;
CREATE certificate principlecert WITH subject =
'Principle certificate for DB mirror';
go

-- Create a mirror endpoint using certificate
CREATE endpoint endpoint_mirroring state = started AS tcp ( listener_port=5022,
listener_ip = ALL ) FOR database_mirroring ( authentication = certificate
principlecert, encryption = required algorithm aes, role = ALL );
go

-- Back up the certificateBACKUP CERTIFICATE PrincipleCert TO FILE = 'D:\PrincipleCert.cer';
-- Copy the certificate (PrincipleCert.cer) to mirror server


On Mirror Server:

-- Create the database master key
USE master;
CREATE master KEY encryption BY password = '<Password>';
go

--- Create a certificate for DB mirrror
USE master;
CREATE certificate mirrorcert WITH subject = 'Mirror certificate for DB mirror';
go

-- Create a mirror endpoint using certificate
CREATE endpoint endpoint_mirroring state = started AS tcp ( listener_port=5022,
listener_ip = ALL ) FOR database_mirroring ( authentication = certificate
mirrorcert, encryption = required algorithm aes, role = ALL );
go

--Back up the certificate
BACKUP certificate mirrorcert TO FILE = 'D:\MirrorCert.cer';
go
-- Copy the certificate(MirrorCert.cer)  to Principle server 

Note: For DB mirror purpose, I have created same login (eg:MirrorUser) on both Principle and Mirror server.

On Principle Server:

-- Create a login on Principle for Mirror server
USE master;
CREATE login mirroruser WITH password = 'password';
go

-- Create user on master
USE master;
CREATE USER mirroruser FOR login mirroruser;
go

-- Associate the certificate with the user
CREATE certificate mirrorcert AUTHORIZATION mirroruser FROM FILE =
'C:\MirrorCert.cer'
go

-- Grant CONNECT permission for endpoint.

GRANT connect on endpoint::endpoint_mirroring to [mirroruser];
go
On Mirror Server:

-- Create a login on Mirror for Principle server
USE master;
CREATE login mirroruser WITH password = 'password';
go

-- Create  user on master
USE master;
CREATE USER mirroruser FOR login mirroruser;
go

-- Associate the certificate with the user
CREATE certificate principlecert AUTHORIZATION mirroruser FROM FILE =
'D:\PrincipleCert.cer'
go

-- Grant CONNECT permission for endpoint GRANT connect on endpoint::endpoint_mirroring to [mirroruser];
go 

Configuring the Mirroring Partners

On Mirror Server:

ALTER DATABASE mirrortestdb
SET partner = 'TCP://PrincipleServer.domain.com:5022';
go 
On Principle server:

ALTER DATABASE mirrortestdb
SET partner = 'TCP://MirrorServer.domain.com:5022';
go

-- Change to high-performance mode
ALTER DATABASE mirrortestdb
SET partner safety OFF
go