Replication Issue – Duplication of Primary Key

Posted on June 14, 2013 by Nirav Gajjar

Replication Issue – Duplication of Primary Key.

Replication synchronizes data and article on subscriber same as it is on publisher.

Changing and inserting data directly at subscriber cause to break replication.

Please look the following error.

Error Message

Command attempted:

if @@trancount > 0 rollback tran
(Transaction sequence number: 0x00000031000001B1000500000000, Command ID: 1)
Error messages:
Violation of PRIMARY KEY constraint ‘PK__Replicat__3214EC27323EC80A’. Cannot insert duplicate key in object ‘dbo.ReplicationTest’. The duplicate key value is (2). (Source: MSSQLServer, Error number: 2627)
Get help: http://help/2627
Violation of PRIMARY KEY constraint ‘PK__Replicat__3214EC27323EC80A’. Cannot insert duplicate key in object ‘dbo.ReplicationTest’. The duplicate key value is (2). (Source: MSSQLServer, Error number: 2627)
Get help: http://help/2627

The problem occurs by inserting duplicate key in dbo.ReplicationTest.

Step1 : Track the problem using T-SQL Query.

Copy transaction sequence number and assign to xact_seqno.

The result will show you publisher, publisher db, source article, destination article.

USE [Distribution]
Go

SELECT
  MSA.*
FROM
  dbo.MSarticles AS MSA
JOIN   MSrepl_commands AS MSRC
  ON MSA.Article_id = MSRC.Article_id
WHERE
  MSRC.xact_seqno = 0x00000031000001B1000500000000

Step2 : To get the exact row which try to insert data in subscriber.

You have to use SP_BrowseReplCmds procedure of distribution database and need to pass transaction sequence number to @xact_seqno_start parameter.

EXEC Distribution.DBO.Sp_browsereplcmds
@xact_seqno_start = ‘0x00000031000001B1000500000000’

See the column “Command” it will shows you full query.

Error on procedure SP_MSins_DBOReplicationTest with parameter “2” and “Test” is failing, it means ID “2” is already exist on subscriber table.

You have a two options to solve, you can modify the record at subscriber or delete the record from subscriber table.

Regards,

Nirav Gajjar

Add article in Replication without initializing

Posted on June 13, 2013 by Nirav Gajjar

Add article in Replication without initializing

By this way you are only creating a snapshot only for newly added article and no need to reinitialize all subscription.

Step1 : Create New Table on Publisher.

USE Adventureworks2012

CREATE TABLE ReplicationTest
  (
     ID   INT PRIMARY KEY,
     Test VARCHAR(20)
  )

Step2 : Add this table in Article.

First check Article exist or not in a replication.


--Check Exisitng Articles.
SELECT
  sub.srvname    AS SubscriptionServer,
  pub.name       AS Publication,
  art.name       AS ArticleName,
  art.dest_table AS DestinationArticle,
  art.dest_owner AS OwnerName
FROM
  sysextendedarticlesview art
INNER JOIN syspublications pub
        ON ( art.pubid = pub.pubid )
INNER JOIN syssubscriptions sub
        ON ( sub.artid = art.artid )
WHERE
  art.name = 'ReplicationTest'

GO

Run this to add “ReplicationTest” table to your existing article.

--Script to add table in replication.
EXEC Sp_addarticle
  @publication = N'AdventureWorks2012_Pub',
  @article = N'ReplicationTest',
  @source_owner = N'DBO',
  @source_object = N'ReplicationTest',
  @type = N'logbased',
  @description = N'',
  @creation_script = N'',
  @pre_creation_cmd = N'drop',
  @schema_option = 0x00000000080350DF,
  @identityrangemanagementoption = N'manual',
  @destination_table = N'ReplicationTest',
  @destination_owner = N'DBO',
  @status = 24,
  @vertical_partition = N'false',
  @ins_cmd = N'CALL [sp_MSins_DBOReplicationTest]',
  @del_cmd = N'CALL [sp_MSdel_DBOReplicationTest]',
  @upd_cmd = N'SCALL [sp_MSupd_DBOReplicationTest]',
  @force_invalidate_snapshot = 1

GO

Now check article added successfully.

--Now Check again for newly added article.
SELECT
  sub.srvname    AS SubscriptionServer,
  pub.name       AS Publication,
  art.name       AS ArticleName,
  art.dest_table AS DestinationArticle,
  art.dest_owner AS OwnerName
FROM
  sysextendedarticlesview art
INNER JOIN syspublications pub
        ON ( art.pubid = pub.pubid )
INNER JOIN syssubscriptions sub
        ON ( sub.artid = art.artid )
WHERE
  art.name = 'ReplicationTest'

GO

Step3 : Refresh Subscription.

USE Adventureworks2012
go

Sp_refreshsubscriptions 'Adventureworks2012_pub' -- Supply Publication Name

Step4 : Go to replication monitor screen –> Select Agent Tab
–>Select Snapshot Agent in Agent Type –>Right click on Agent and Start Again.

Step5 : Now Go to replication monitor screen –> Select Agent Subscription watch list
–> Double click on your subscription and snapshot applying only for newly added article.

Regards,

Nirav Gajjar

Script to Monitor Mirroring

Posted on June 11, 2013 by Nirav Gajjar

Script to Monitor Mirroring

This script will provide all database which is mirrored with mirroring mode, mirroring status and partner information.


SELECT Sd.Name              AS Databasename,
       Mirroring_state_desc AS Status,
       CASE
         WHEN Mirroring_safety_level = 1 THEN 'High Performance'
         WHEN Mirroring_safety_level = 2 THEN 'High Safety'
         ELSE NULL
       END                  AS Mirroringmod,
       Mirroring_role_desc,
       Mirroring_partner_instance
FROM   MSDB.SYS.Database_mirroring Sdm
       JOIN MASTER.SYS.Sysdatabases Sd
         ON Sdm.Database_id = Sd.Dbid
WHERE  Mirroring_guid IS NOT NULL

Regards,
Nirav Gajjar

Script to get Replication Latency

Posted on June 10, 2013 by Nirav Gajjar

Script to get Replication Latency.

I have created monitoring script to get latency information for replication and status of replication health.

I have used “SP_REPLMONITORHELPSUBSCRIPTION ” under Distribution database.

It gives you same information which you can get from Replication Monitor –> Subscription Wath List.

In SQL Server 2012 you just need to pass value only for @Publication_type.

If you are using older version of SQL Server then you need to pass @Publication_type and @Publisher parameter, so I have created script for both different versions.

DECLARE @Srvname VARCHAR(100)
DECLARE @Pub_db VARCHAR(100)
DECLARE @Pubname VARCHAR(100)

CREATE TABLE #REPLMONITOR
 (
 Status INT NULL,
 Warning INT NULL,
 Subscriber SYSNAME NULL,
 Subscriber_db SYSNAME NULL,
 Publisher_db SYSNAME NULL,
 Publication SYSNAME NULL,
 Publication_type INT NULL,
 Subtype INT NULL,
 Latency INT NULL,
 Latencythreshold INT NULL,
 Agentnotrunning INT NULL,
 Agentnotrunningthreshold INT NULL,
 Timetoexpiration INT NULL,
 Expirationthreshold INT NULL,
 Last_distsync DATETIME,
 Distribution_agentname SYSNAME NULL,
 Mergeagentname SYSNAME NULL,
 Mergesubscriptionfriendlyname SYSNAME NULL,
 Mergeagentlocation SYSNAME NULL,
 Mergeconnectiontype INT NULL,
 Mergeperformance INT NULL,
 Mergerunspeed FLOAT,
 Mergerunduration INT NULL,
 Monitorranking INT NULL,
 Distributionagentjobid BINARY(16),
 Mergeagentjobid BINARY(16),
 Distributionagentid INT NULL,
 Distributionagentprofileid INT NULL,
 Mergeagentid INT NULL,
 Mergeagentprofileid INT NULL,
 Logreaderagentname VARCHAR(100),
 Publisher VARCHAR(20)
 )

INSERT INTO #REPLMONITOR
SELECT A.*
FROM OPENROWSET('MSDASQL',
 'DRIVER={SQL Server}; SERVER=InstanceName; UID=sa; PWD=Password',
 'SET FMTONLY OFF EXEC DISTRIBUTION.DBO.SP_REPLMONITORHELPSUBSCRIPTION @Publication_type = 0') AS A;

SELECT CASE Status
 WHEN 1 THEN 'Started'
 WHEN 2 THEN 'Succeeded'
 WHEN 3 THEN 'In Profress'
 WHEN 4 THEN 'Idle'
 WHEN 5 THEN 'Retrying'
 WHEN 6 THEN 'Failed'
 END AS Status,
 Publication,
 Publisher_db Subscriber_db,
 Subscriber_db,
 CONVERT(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR, Latency
 ), 6),
 5, 0, ':'), 3, 0,
 ':'), 108) AS Latency,
 CASE Monitorranking
 WHEN 60 THEN 'Error'
 WHEN 56 THEN 'Warning: performance critical'
 WHEN 52 THEN 'Warning: expiring soon or expired'
 WHEN 50 THEN 'Warning: subscription uninitialized'
 WHEN 40 THEN 'Retrying failed command'
 WHEN 30 THEN 'Not running (success)'
 WHEN 20 THEN 'Running (starting, running, or idle)'
 END AS Healthcheck
FROM #REPLMONITOR

DROP TABLE #REPLMONITOR

Query For SQL Server 2005 and 2008.

DECLARE @Srvname VARCHAR(100)
DECLARE @Pub_db VARCHAR(100)
DECLARE @Pubname VARCHAR(100)

CREATE TABLE #REPLMONITOR
  (
     Status                        INT NULL,
     Warning                       INT NULL,
     Subscriber                    SYSNAME NULL,
     Subscriber_db                 SYSNAME NULL,
     Publisher_db                  SYSNAME NULL,
     Publication                   SYSNAME NULL,
     Publication_type              INT NULL,
     Subtype                       INT NULL,
     Latency                       INT NULL,
     Latencythreshold              INT NULL,
     Agentnotrunning               INT NULL,
     Agentnotrunningthreshold      INT NULL,
     Timetoexpiration              INT NULL,
     Expirationthreshold           INT NULL,
     Last_distsync                 DATETIME,
     Distribution_agentname        SYSNAME NULL,
     Mergeagentname                SYSNAME NULL,
     Mergesubscriptionfriendlyname SYSNAME NULL,
     Mergeagentlocation            SYSNAME NULL,
     Mergeconnectiontype           INT NULL,
     Mergeperformance              INT NULL,
     Mergerunspeed                 FLOAT,
     Mergerunduration              INT NULL,
     Monitorranking                INT NULL,
     Distributionagentjobid        BINARY(16),
     Mergeagentjobid               BINARY(16),
     Distributionagentid           INT NULL,
     Distributionagentprofileid    INT NULL,
     Mergeagentid                  INT NULL,
     Mergeagentprofileid           INT NULL,
     Logreaderagentname            VARCHAR(100)
  )

INSERT INTO #REPLMONITOR
SELECT A.*
FROM   OPENROWSET('MSDASQL',
       'DRIVER={SQL Server}; SERVER=InstanceName; UID=sa; PWD=Password',
' set fmtonly off EXEC distribution.dbo.sp_replmonitorhelpsubscription @publication_type = 0, @Publisher = ''ServerName'''
) AS A;

SELECT CASE Status
         WHEN 1 THEN 'Started'
         WHEN 2 THEN 'Succeeded'
         WHEN 3 THEN 'In Profress'
         WHEN 4 THEN 'Idle'
         WHEN 5 THEN 'Retrying'
         WHEN 6 THEN 'Failed'
       END                                                AS Status,
       Publication,
       Publisher_db                                       Subscriber_db,
       Subscriber_db,
       CONVERT(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR, Latency
                                             ), 6),
                                 5, 0, ':'), 3, 0,
                                               ':'), 108) AS Latency,
       CASE Monitorranking
         WHEN 60 THEN 'Error'
         WHEN 56 THEN 'Warning: performance critical'
         WHEN 52 THEN 'Warning: expiring soon or expired'
         WHEN 50 THEN 'Warning: subscription uninitialized'
         WHEN 40 THEN 'Retrying failed command'
         WHEN 30 THEN 'Not running (success)'
         WHEN 20 THEN 'Running (starting, running, or idle)'
       END                                                AS Healthcheck
FROM   #REPLMONITOR

DROP TABLE #REPLMONITOR

Regards,
Nirav Gajjar

Script to get replication Undistributed Commands

Posted on June 9, 2013 by Nirav Gajjar

Script to get replication Undistributed Commands

Script to Monitor Replication by checking last refresh time and pending commands

SELECT ( CASE
           WHEN Mdh.Runstatus = '1' THEN 'Start'
           WHEN Mdh.Runstatus = '2' THEN 'Succeed'
           WHEN Mdh.Runstatus = '3' THEN 'InProgress'
           WHEN Mdh.Runstatus = '4' THEN 'Idle'
           WHEN Mdh.Runstatus = '5' THEN 'Retry'
           WHEN Mdh.Runstatus = '6' THEN 'Fail'
         END )                                 [Run status],
       Mda.Subscriber_db                       [Subscriber db],
       Mda.Publication                         [Pub name],
       Mdh.[Time]                              [Lastsynchronized],
       Und.Undelivcmdsindistdb                 [Undistcom],
       Mdh.Comments                            [Comments],
       DATEDIFF(SECOND, Mdh.[Time], GETDATE()) Lastrefresh
FROM   DISTRIBUTION.DBO.Msdistribution_agents Mda
       LEFT JOIN DISTRIBUTION.DBO.Msdistribution_history Mdh
              ON Mdh.Agent_id = Mda.Id
       JOIN (SELECT S.AGENT_ID,
                    Maxagentvalue.[Time],
                    SUM(CASE
                          WHEN Xact_seqno > Maxagentvalue.Maxseq THEN 1
                          ELSE 0
                        END) AS Undelivcmdsindistdb
             FROM   DISTRIBUTION.DBO.Msrepl_commands T (NOLOCK)
                    JOIN DISTRIBUTION.DBO.Mssubscriptions AS S (NOLOCK)
                      ON ( T.ARTICLE_ID = S.ARTICLE_ID
                           AND T.PUBLISHER_DATABASE_ID = S.PUBLISHER_DATABASE_ID
                         )
                    JOIN (SELECT Hist.AGENT_ID,
                                 MAX(Hist.[TIME]) AS [Time],
                                 H.Maxseq
                          FROM   DISTRIBUTION.DBO.Msdistribution_history Hist (
                                 NOLOCK)
                                 JOIN (SELECT Agent_id,
                                              ISNULL(MAX(Xact_seqno), 0x0) AS
                                              Maxseq
                                       FROM
                                 DISTRIBUTION.DBO.Msdistribution_history (
                                 NOLOCK)
                                       GROUP  BY Agent_id) AS H
                                   ON ( Hist.AGENT_ID = H.Agent_id
                                        AND H.Maxseq = Hist.XACT_SEQNO )
                          GROUP  BY Hist.AGENT_ID,
                                    H.Maxseq) AS Maxagentvalue
                      ON Maxagentvalue.Agent_id = S.AGENT_ID
             GROUP  BY S.AGENT_ID,
                       Maxagentvalue.[Time]) Und
         ON Mda.Id = Und.Agent_id
            AND Und.[Time] = Mdh.[Time]
ORDER  BY Mdh.[Time] DESC

Regards,
Nirav Gajjar

Query to get Database size and table size

Posted on June 5, 2013 by Nirav Gajjar

Query to get Database size and table size.

Query to get Database size in GB.


SELECT
 D.NAME,
 Cast((SUM(MF.SIZE) * 8.00 /1024.00/1024.00)as Numeric(18,2)) AS [SIZE (In GB)]
FROM SYS.MASTER_FILES MF
INNER JOIN SYS.DATABASES D
 ON D.DATABASE_ID = MF.DATABASE_ID
 WHERE D.DATABASE_ID > 4
 GROUP BY D.NAME
 ORDER BY [SIZE (In GB)] DESC

Query to get Table size in GB with no of rows.


SELECT SCHEMA_NAME(T.Schema_id) + '.' + T.Name
 AS
 Tablename,
 SUM(
		CASE
			WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows
			ELSE 0
		END
	   )
 AS Rowcounts,
 CAST(( SUM(A.Total_pages) * 8 ) / 1024.000 / 1024.000 AS NUMERIC(
 18, 3)) AS
 Totalspacegb
FROM SYS.Tables T
 INNER JOIN SYS.Indexes I
 ON T.Object_id = I.Object_id
 INNER JOIN SYS.Partitions P
 ON I.Object_id = P.Object_id
 AND I.Index_id = P.Index_id
 INNER JOIN SYS.Allocation_units A
 ON P.Partition_id = A.Container_id
 INNER JOIN SYS.Filegroups Fg
 ON I.Data_space_id = Fg.Data_space_id
 INNER JOIN SYS.Database_files Df
 ON Df.Data_space_id = Fg.Data_space_id
WHERE T.Is_ms_shipped = 0
 AND I.Object_id > 255
GROUP BY SCHEMA_NAME(T.Schema_id) + '.' + T.Name
ORDER BY Totalspacegb DESC

Query to get Service Broker Queue size in GB with no of rows.

Select OBJECT_NAME(IT.parent_object_id),SUM(
 CASE
 WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows
 ELSE 0
 END
 )
 AS Rowcounts,CAST(( SUM(A.Total_pages) * 8 ) / 1024.000 / 1024.000 AS NUMERIC( 18, 3)) AS Totalspacegb
From sys.partitions P
Inner Join sys.internal_tables IT On IT.OBJECT_ID = P.OBJECT_ID
INNER JOIN SYS.Allocation_units A
ON P.PARTITION_ID = A.Container_id
Where P.index_id In (1, 0)
Group By OBJECT_NAME(IT.parent_object_id)
Order by 3 DESC

Regards,
Nirav Gajjar

Query to get Open Transaction

Posted on June 4, 2013 by Nirav Gajjar

Query to get Open Transaction.

Query to monitor open transactions in your sql server instance.

If you are using SQL Server 2012 use open_transaction_count column instead of enlist_count.

Query to get open transactions by host. remove the comment if you want open transactions just from ssms window.


SELECT
SUM(Tst.Enlist_count) Noofopentran,
Host_name
FROM   SYS.Dm_tran_session_transactions Tst
INNER JOIN SYS.Dm_exec_sessions Es
ON Tst.Session_id = Es.Session_id
--WHERE program_name = 'Microsoft SQL Server Management Studio - Query'
GROUP  BY Host_name

List Open transactions in details.


SELECT
Tst.Session_id,
Tst.Enlist_count,
Host_name,
Program_name,
Text
FROM   SYS.Dm_tran_session_transactions Tst
INNER JOIN SYS.Dm_exec_connections Ec
ON Tst.Session_id = Ec.Session_id
INNER JOIN SYS.Dm_exec_sessions Es
ON Tst.Session_id = Es.Session_id
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(Ec.Most_recent_sql_handle)
--where program_name = 'Microsoft SQL Server Management Studio - Query'

Regards,
Nirav Gajjar

SQL Server long running and blocking queries

Posted on June 3, 2013 by Nirav Gajjar

SQL Server long running and blocking queries

This query helps you to get all running queries including user queries and SQL Server engine queries.

with duration, so you can examine which query running long.

You can see BlockingWith field which shows you query is blocking with another process.

I have ordered query by blocking first so you can get all blocking query at first and you can take necessary actions.

SELECT
DATEDIFF(MI, Start_time, GETDATE()) AS Mins,
'USE ' + DB_NAME(Database_id) + '; SELECT object_name(' + CAST(Objectid AS VARCHAR) + ')' AS Objectname,
SUBSTRING(St.Text, ( Statement_start_offset / 2 ) + 1, ( ( CASE Statement_end_offset WHEN -1 THEN DATALENGTH(St.Text) ELSE Statement_end_offset END -   Statement_start_offset ) / 2 ) + 1) AS Statement_text,
DB_NAME(Database_id) AS Dbname,
Session_id,
Blocking_session_id AS Blockingwith,
Status,
Command
FROM SYS.Dm_exec_requests R
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(Sql_handle) AS St
ORDER BY Blockingwith DESC, Mins DESC

This query show you only user queries. any queries running by SQL Server machine will be filtered.

Here are few more things to observe.

CPU field which shows the CPU usage, Reads, Writes, Host_Name which shows query running from which machine, Program_Name Which shows from which program the query executed.

SELECT
R.Cpu_time AS Cpu,
DATEDIFF(MINUTE, Last_request_start_time, GETDATE()) AS Mins,
S.Host_name AS Host,
LEFT(Client_interface_name, 15) AS Client,
S.Session_id AS Sessionid,
Blocking_session_id AS Blocking,
'USE ' + DB_NAME(Database_id) + '; SELECT object_name(' + CAST(Objectid AS VARCHAR) + ')' AS Objectname,
SUBSTRING(St.Text, ( Statement_start_offset / 2 ) + 1, ABS(( ( CASE Statement_end_offset WHEN -1 THEN DATALENGTH(St.Text) ELSE Statement_end_offset END - Statement_start_offset ) / 2 ) + 1)) AS Statement_text,
DB_NAME(Database_id) AS Dbname,
Blocking_session_id AS Blockingwith,
R.Reads,
R.Writes,
S.Session_id,
S.[Program_name],
S.Login_name,
S.Status,
S.Last_request_start_time,
R.Logical_reads
FROM SYS.Dm_exec_requests R
INNER JOIN SYS.Dm_exec_sessions S ON S.Session_id = R.Session_id AND S.Is_user_process = 1
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(Sql_handle) AS St
ORDER BY Blocking DESC, Mins DESC

Regards,
Nirav Gajjar