Find how large replicated transactions are ?.

USE distribution
GO

SELECT Getdate() AS CaptureTime
 ,Object_name(t.object_id) AS TableName
 ,st.row_count
 ,s.NAME
FROM sys.dm_db_partition_stats st WITH (NOLOCK)
INNER JOIN sys.tables t WITH (NOLOCK) ON st.object_id = t.object_id
INNER JOIN sys.schemas s WITH (NOLOCK) ON t.schema_id = s.schema_id
WHERE index_id < 2
 AND Object_name(t.object_id) IN (
  'MSsubscriptions'
  ,'MSdistribution_history'
  ,'MSrepl_commands'
  ,'MSrepl_transactions'
  )
ORDER BY st.row_count DESC

Script to fine row count of each table available in a database

The below script will useful to find row count of tables along with creation date.

SELECT SCHEMA_NAME(st.schema_id) AS [SchemaName]
 ,[Tables].name AS [TableName]
 ,st.create_date
 ,SUM([Partitions].[rows]) AS [TotalRowCount]
FROM sys.tables AS [Tables]
INNER JOIN sys.partitions AS [Partitions] ON [Tables].[object_id] = [Partitions].[object_id]
 AND [Partitions].index_id IN (
  0
  ,1
  )
INNER JOIN sys.tables st ON st.name = tables.name
-- WHERE [Tables].name = N'name of the table'
GROUP BY SCHEMA_NAME(st.schema_id)
 ,[Tables].name
 ,st.create_date
ORDER BY 4 DESC

Script to find whats going in Distribution or Replication is getting moving or not ?

USE distribution
GO

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT ma2.publisher_db
 ,mh1.delivery_latency / (1000 * 60) AS delivery_latency_Minutes
 ,mh1.agent_id
 ,mh1.TIME
 ,CAST(mh1.comments AS XML) AS comments
 ,CASE mh1.runstatus WHEN 1 THEN 'Start' 

WHEN 2 THEN 'Succeed.' 

WHEN 3 THEN 'In progress.' 

WHEN 4 THEN 'Idle.' 

WHEN 5 THEN 'Retry.' 

WHEN 6 THEN 'Fail' END AS STATUS
 ,mh1.duration
 ,mh1.xact_seqno
 ,mh1.delivered_transactions
 ,mh1.delivered_commands
 ,mh1.average_commands
 ,mh1.delivery_time
 ,mh1.delivery_rate
 ,ma2.name AS jobname
FROM mslogreader_history mh1
INNER JOIN (
 SELECT mh1.agent_id
  ,MAX(mh1.TIME) AS maxtime
 FROM mslogreader_history mh1
 INNER JOIN MSlogreader_agents ma ON ma.id = mh1.agent_id
 GROUP BY mh1.agent_id
 ) AS mh2 ON mh1.agent_id = mh2.agent_id
 AND mh1.TIME = mh2.maxtime
INNER JOIN MSlogreader_agents ma2 ON ma2.id = mh2.agent_id
ORDER BY mh1.delivery_latency DESC

Find Publisher and Subscriber details along with distribution agent job details in Replication

USE Distribution
GO

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- Get the publication name based on article 
SELECT DISTINCT srv.srvname publication_server
 ,a.publisher_db
 ,p.publication publication_name
 ,a.article
 ,a.destination_object
 ,ss.srvname subscription_server
 ,s.subscriber_db
 ,da.name AS distribution_agent_job_name
FROM MSArticles a
INNER JOIN MSpublications p ON a.publication_id = p.publication_id
INNER JOIN MSsubscriptions s ON p.publication_id = s.publication_id
INNER JOIN master..sysservers ss ON s.subscriber_id = ss.srvid
INNER JOIN master..sysservers srv ON srv.srvid = p.publisher_id
INNER JOIN MSdistribution_agents da ON da.publisher_id = p.publisher_id
 AND da.subscriber_id = s.subscriber_id
ORDER BY 1
 ,2
 ,3

Script to find Primary Key and Foreign Key table names

I used to do the below script to find Foreign Key tables associated with Primary Key table.

DECLARE @TableName VARCHAR(32) = 'Your Primary Key Table Name' -- Pass your table name here

SELECT o1.name AS FK_table
 ,c1.name AS FK_column
 ,fk.name AS FK_name
 ,o2.name AS PK_table
 ,c2.name AS PK_column
 ,pk.name AS PK_name
 ,fk.delete_referential_action_desc AS Delete_Action
 ,fk.update_referential_action_desc AS Update_Action
FROM sys.objects o1
INNER JOIN sys.foreign_keys fk ON o1.object_id = fk.parent_object_id
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns c1 ON fkc.parent_object_id = c1.object_id
 AND fkc.parent_column_id = c1.column_id
INNER JOIN sys.columns c2 ON fkc.referenced_object_id = c2.object_id
 AND fkc.referenced_column_id = c2.column_id
INNER JOIN sys.objects o2 ON fk.referenced_object_id = o2.object_id
INNER JOIN sys.key_constraints pk ON fk.referenced_object_id = pk.parent_object_id
 AND fk.key_index_id = pk.unique_index_id
WHERE o2.name = @TableName
ORDER BY o1.name
 ,o2.name
 ,fkc.constraint_column_id

This script will helpful in below scenarios

1.Data archival from one table to other table 

- We need to disable FK constraints to move only

Primary key data to archival tables.

2.To delete only parent table data and keeping data in child table

In order to achieve the above scenarios we need to find PK and FK relation ships with help

of this scripts.

Script to share lead blocker details to mail and store lead blocker details in physical table.

The below script will help to send both blocking and blocked sessions along with query in 

single mail .

It will take automatically default mail profiler if it configured and send you mail

for the required list of recipients.

DECLARE @waittime AS INT = 180000
DECLARE @count VARCHAR(100)
DECLARE @dbname VARCHAR(100)
DECLARE @vcsubject VARCHAR(1000)

SELECT @count = count(*)
FROM master..sysprocesses
WHERE (blocked != 0)
 AND spid != blocked
 AND cmd != 'DB MIRROR'
 AND waittime > @waittime

IF (@count) >= 1
BEGIN
 DECLARE @tableHTML NVARCHAR(MAX);
 DECLARE @Servername VARCHAR(100);

 SELECT @Servername = convert(VARCHAR(100), SERVERPROPERTY('servername'));

 DECLARE @MAILPROFILE VARCHAR(100);

 IF OBJECT_ID('tempdb..#MAILDEFAULPROFILE') IS NOT NULL
  DROP TABLE #MAILDEFAULPROFILE

 CREATE TABLE #MAILDEFAULPROFILE (
  PRINCIPALID INT
  ,PRINCIPALNAME VARCHAR(200)
  ,PROFILEID INT
  ,PROFILENAME VARCHAR(200)
  ,ISDEFAULT BIT
  );

 INSERT INTO #MAILDEFAULPROFILE
 EXEC MSDB.DBO.SYSMAIL_HELP_PRINCIPALPROFILE_SP;

 SELECT @MAILPROFILE = PROFILENAME
 FROM #MAILDEFAULPROFILE
 WHERE ISDEFAULT = 1;

 SELECT *
 FROM #MAILDEFAULPROFILE

 IF OBJECT_ID('tempdb..##temptab') IS NOT NULL
  DROP TABLE ##temptab

 IF OBJECT_ID('tempdb..##Emailoutput') IS NOT NULL
  DROP TABLE ##Emailoutput

 CREATE TABLE ##temptab (blk INT)

 INSERT INTO ##temptab (blk)
 SELECT blocked
 FROM sys.sysprocesses
 WHERE blocked NOT IN (
   SELECT spid
   FROM sys.sysprocesses
   WHERE blocked <> 0
   )
  AND blocked <> 0

 CREATE TABLE ##Emailoutput (
  [Root Blocking SPID] VARCHAR(50)
  ,[Blocking Host] VARCHAR(100)
  ,[Blocking Login] VARCHAR(100)
  ,ProgramName VARCHAR(1000)
  ,[Blocked SPID] VARCHAR(50)
  ,[Blocking SPID] VARCHAR(50)
  ,[Blocked Status] VARCHAR(50)
  ,[Blocked Login] VARCHAR(100)
  ,[Blocked Host] VARCHAR(100)
  ,[Wait Time in Mins] VARCHAR(50)
  ,[blocked text] VARCHAR(max)
  ,[Root Blocking SPID Text] VARCHAR(max)
  )

 DECLARE @blc INT

 DECLARE cur CURSOR
 FOR
 SELECT DISTINCT blk
 FROM ##temptab

 OPEN cur

 FETCH NEXT
 FROM cur
 INTO @blc

 WHILE @@FETCH_STATUS = 0
 BEGIN
  INSERT INTO ##Emailoutput
  SELECT x.SESsION_ID AS [Root Blocking SPID]
   ,x.host_name [Blocking Host]
   ,x.login_name [Blocking Login]
   ,ProgramName
   ,y.*
   ,cast(x.SESsION_ID AS VARCHAR(10)) + '  - ' + x.TEXT [Root Blocking SPID Text]
  FROM (
   SELECT es.*
    ,program_name ProgramName
    ,st.TEXT
   FROM sys.dm_exec_sessions es
   INNER JOIN sys.dm_exec_connections ec ON ec.session_id = es.session_id
   CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) st
   WHERE es.session_id = @blc
   ) x
   ,(
    SELECT spid [Blocked SPID]
     ,blocked [Blocking SPID]
     ,STATUS [Blocked Status]
     ,loginame [Blocked Login]
     ,hostname [Blocked Host]
     ,waittime / 60000 [Wait Time in Mins]
     ,cast(spid AS VARCHAR(10)) + ' - ' + TEXT [blocked text]
    FROM sys.sysprocesses r
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
    WHERE r.blocked = @blc
    ) y

  FETCH NEXT
  FROM cur
  INTO @blc
 END

 CLOSE cur

 DEALLOCATE cur

 INSERT INTO Test..LeadBlockersHistory
 SELECT *
 FROM ##Emailoutput

 SET @tableHTML = N'<H1>Blocking on  ' + N' Servername : '

 + @Servername + '</H1>' + N'<table border="1">' 

+ N'<tr><th>Root Blocking SPID</th><th>Blocking Host</th>

<th>Blocking Login</th><th>ProgramName</th>' 

+ N'<th>Blocked SPID</th><th>Blocking SPID</th>

<th>Blocked Status</th><th>Blocked Login</th><th>Blocked Host</th>'

 + N'<th>Wait Time in Mins</th><th>Blocked Text</th>

<th>Root Blocking SPID Text</th>' + CAST((
    SELECT td = CAST([Root Blocking SPID] AS VARCHAR(50))
     ,''
     ,td = CAST([Blocking Host] AS VARCHAR(100))
     ,''
     ,td = CAST([Blocking Login] AS VARCHAR(100))
     ,''
     ,td = CAST(ProgramName AS VARCHAR(1000))
     ,''
     ,td = CAST([Blocked SPID] AS VARCHAR(100))
     ,''
     ,td = CAST([Blocking SPID] AS VARCHAR(100))
     ,''
     ,td = CAST([Blocked Status] AS VARCHAR(100))
     ,''
     ,td = CAST([Blocked Login] AS VARCHAR(100))
     ,''
     ,td = CAST([Blocked Host] AS VARCHAR(100))
     ,''
     ,td = CAST([Wait Time in Mins] AS VARCHAR(100))
     ,''
     ,td = CAST([blocked text] AS VARCHAR(max))
     ,''
     ,td = CAST([Root Blocking SPID Text] AS VARCHAR(max))
     ,''
    FROM ##Emailoutput
    FOR XML PATH('tr')
     ,TYPE
    ) AS NVARCHAR(MAX)) + N'</table>';
 SET @vcsubject = 'Blocking occuring in ' 

+ convert(VARCHAR(100), SERVERPROPERTY('servername')) + ', Please start monitoring'

 EXEC msdb.dbo.sp_send_dbmail @recipients = 'XYZ@MAIL.com'
  ,@profile_name = @MAILPROFILE
  ,@subject = @vcsubject
  ,@body = @tableHTML
  ,@body_format = 'HTML';
END