In this post I share a stored procedure I have recently added to my Transactional Replication monitoring toolbox. The procedure which I have called “MonitorReplicationDistributionAgentsSynschronizationStatus” is based on top of a few procedures used by the Replication Monitoring built in UI tool by Microsoft. Credits go to a colleague DBA Saby Levy who coded the query while I have only twisted it a little for my needs. I think it is real cool as it gives you a true indication to the last time a synchronization took place by the distribution agent. So if anything along the delivery...

Transactional Replication – Know your Distribution Queue

Here is a useful query that lets you see the undelivered pending commands at the distribution database per each distribution agent at the table level.   SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;SELECT @@SERVERNAME AS --,a.article_id ,a.article ,p.publication ,S.UndelivCmdsInDistDB ,s.DelivCmdsInDistDB ,agents.nameFROM distribution.dbo.MSdistribution_status AS sINNER JOIN distribution.dbo.MSarticles AS a ON a.article_id = s.article_id INNER JOIN distribution.dbo.MSpublications AS p ON p.publication_id = a.publication_idINNER JOIN distribution.dbo.MSdistribution_agents agents on = s.agent_idWHERE 1=1 AND subscriber_db NOT LIKE...
no comments

AlwaysOnSyncronizer version 1.6.0 is now available for download


This updated release includes the following major changes: 1. Add support for Operators 2. Add support for SQL Server 2016 3. Improve logging for the execution of script files on secondary replicas 4. Fix minor issues related to the initial deployment For additional details about AlwaysOnSyncronizer see this post. To download AlwaysOnSyncronizer click here.   Following the installation before running the program for the first time it is recommended to run the sql script file named drop_procedures.sql located under the AlwaysOnSyncronizer_1.6_Setup\Uninstall folder. Doing so you assure that you are using the latest version of the stored procedures....

Shorten Replication failover time with AlwaysOn Availability Groups


Recently, on a production system where we had implemented AlwaysOn Availability Groups not too long a go we experienced a situation where following a planned failover we noticed a latency in the delivery of commands to our Transactional Replication subscribers. Looking into this issue I found that the reason for the latency experienced is that the log reader was not harvesting transactions from the published database transaction log. When querying MSlogreader_history I noticed that the log reader was just scanning the VLFs looking for the LSN where it should start delivering transactions.  So executing sp_replcounters simply returned 0 for...

Query msdb backupset to get backup information


Check out this useful query over msdb..backupset to get all backup information needed and also useful when need to restore transaction logs Note that the query returns the most recent backup per each database but if you comment out the line that comes just after this remark /* Get the most recent backup of type FULL */  you get all rows --SELECT DB_NAME(database_id) AS , name, physical_name, size /128 AS size_mb, state_desc FROM sys.master_files WHERE database_id > 4-- SELECT LEFT( physical_name, 3) drive ,SUM(size)/128 size FROM sys.master_files GROUP BY LEFT( physical_name, 3)USE msdb; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;...
no comments

Implement SQL Server Agent Jobs with AlwaysOn Availability Groups


In this post I talk about SQL Server Agent jobs when implementing AlwaysOn Availability Groups. AlwaysOn Availability Groups synchronizes the databases that are part of an Availability Group but any other object that is not part of these databases do not get synchronized (i.e. logins, linked servers, operators etc.) and this includes SQL Server Agent jobs. For a tool that synchronizes all the objects that are beyond the scope of AlwaysOn Availability Groups see this post.   Our requirement is to have all jobs that exist on the Primary replica to exist also on the Secondary replica(s) and to...

An update to Partition Management is available


Partition Management is a generic piece of code designed to automate the maintenance of a partition sliding window. For more details about Partition Management see this post. For a direct download from the sqlserverutilities web site   This update includes the following content: 1. Add SET LOCK_TIMEOUT to prevent a case where the procedure waits to get a schema stability lock (LCK_M_SCH_S) required for the MERGE and SPLIT commands and while it is waiting in line to acquire that lock on the required resource in order to proceed it blocks all other processes waiting also to acquire a lock...
no comments

Using a start up stored procedure in SQL Server


SQL Server offers the option to define a stored procedure that will get executed at service start up. I have found this very useful and been making usage of this option over the years. An alternative can be using SQL Server Agent job which is not as good in my opinion. The procedure you set gets executed in the master database when the database completes it’s recovery phase at service startup and has the limitation that it cannot accept input parameters.   You will need to use sp_configure and sp_procoption first to allow startup execution of procdures USE...
no comments

An new update to sp_helpindex2 is now released – 20161101


This update adds support for columnstore and xml indexes and support for schemas. For direct download of sp_helpindex2 click here For more info on sp_helpindex2 go to the original post here   Usage examples USE AdventureWorks2014;EXEC sp_helpindex2 N'ProductDescription'; USE AdventureWorks2014;EXEC sp_helpindex2 @Table = N'ProductDescription' ,@Schema = N'Production' ,@IndexExtendedInfo = 0 ...
no comments

Create a Performance Baseline Repository


The scripts can be downloaded here. Long a go I blogged about this topic here. The post talks about and walks through the steps needed to create a baseline repository holding key metrics for each execution (Stored Procedure or SQL Batch) of an instance of SQL Server. The process is based on a few stored procedures that create a server side trace, load the trace files to a table and manipulate the data resulting in a summarized (aggregated) data per each execution. The idea behind is that it is always useful to have a baseline as a...
no comments