Skip to content

Instantly share code, notes, and snippets.

Avatar

Phil Grayson databoffin

View GitHub Profile
@databoffin
databoffin / ReplThroughput.sql
Created Oct 17, 2017
Query to track the throughput of SQL Server distribution databases
View ReplThroughput.sql
USE distribution;
GO
--Track the throughput per minute
SELECT CONVERT(SMALLDATETIME, t.entry_time) AS EntryTime,
COUNT(1) AS Commands
FROM MSrepl_commands cmds
INNER JOIN MSarticles a ON a.article_id = cmds.article_id
INNER JOIN MSrepl_transactions t ON cmds.xact_seqno = t.xact_seqno
GROUP BY CONVERT(SMALLDATETIME, t.entry_time)
ORDER BY CONVERT(SMALLDATETIME, t.entry_time) DESC;
View Indexes for merge replication.sql
CREATE NONCLUSTERED INDEX [IX_pubid_uploadoptions]
ON [dbo].[sysmergearticles]
(
[pubid],
[upload_options]
);
GO
CREATE NONCLUSTERED INDEX [IX_uploadoptions_Included]
ON [dbo].[sysmergearticles] ([upload_options])
INCLUDE
View Indexes for transactional replication.sql
USE distribution;
GO
CREATE INDEX [IX_MSdistribution_history_runstatus]
ON [dbo].[MSdistribution_history] ([runstatus])
INCLUDE
(
[agent_id],
[timestamp],
[delivery_latency],
[time],
@databoffin
databoffin / CreatePullJobs.sql
Created Apr 28, 2017
Create SQL Server replication pull jobs
View CreatePullJobs.sql
USE [msdb];
GO
DECLARE @iPublisher AS sysname;
DECLARE @iDistributor AS sysname;
DECLARE @iSubscriber AS sysname;
DECLARE @iPublication AS sysname;
DECLARE @iPublicationDB AS sysname;
DECLARE @iSubscriptionDB AS sysname;
DECLARE @job_name NVARCHAR(400);
DECLARE @cmd NVARCHAR(4000);
You can’t perform that action at this time.