Check Default profile in use

Displays the profile of a specified agent. This stored procedure is executed at the Distributor on any database. sp_help_agent_profile sp_help_agent_profile [ [ @agent_type = ] agent_type ] [ , [ @profile_id = ] profile_id ] Value Description 1 Snapshot Agent 2 Log Reader Agent 3 Distribution Agent 4 Merge Agent 9 Queue Reader Agent use […]

Article wise Pending Transaction (Transactional Replication)

While troubleshooting transactional replication issues , DBA’s always look for script which can give which article’s transaction are waiting to be replicated and how much The script below will answer the question Script select count(*) as [# of commands] , f.publication, c.publisher_database_id, d.publisher_db, c.article_id, e.article, entry_time from MSrepl_commands  c with (NOLOCK) inner join MSrepl_transactions  t  with (NOLOCK) […]

Transactional Replication Information (Publisher–Publication-Articles-Subscriber–Articles)

The script is to pull replicated objects and when the last data was replicated from each of the objects use [Distribution Database Name] go select sss.srvname PublisherServer,mss.publication_id,mss.publisher_db, msa.article Publisher_Articals, sss1.srvname SubscriberServer,mss.subscriber_id,mss.subscriber_db,mss.article_id, msa.destination_object Subscriber_Articals, mss.subscription_time from MSsubscriptions mss join sys.sysservers sss on mss.publisher_id=sss.srvid join sys.sysservers sss1 on mss.subscriber_id=sss1.srvid join MSarticles msa on mss.publisher_id=msa.publisher_id and mss.article_id=msa.article_id   […]

Log Reader Agent Status Notification

While setting up critical Transactional Replication I came across this scenario of alerting the L3 DBA support team with Log Reader Agent Status and writing it to SQL Server Error Log. When Log Reader Agent is Retrying to read the Database Log and sending it to Distribution Database and when it fails. To achieve this […]

Transactions waiting to be replicated to Subscription Database (Transactional Replication)

While troubleshooting Replication issues it’s always a question till what point transaction has been replicated to subscription and from which point transaction are in distribution database waiting to be replicated to subscription database. Replication Architecture Let’s say distributor agent failed to replicate transaction from distributor to subscriber due to some reason and now after restarting […]

Transactional Replication Information (Publisher–Publication-Articals–LogReader)

I have created a script  to get all replication information about Publisher,Publication,Articals and LogReader in a treeview Structure .Like How many publisher withsnapshot location,how  many publication are generated from each of the publisher,How many articals are associated with each publication ,Log Reader Agents associated with each publication with LodReader Agent Job information. I will write […]

How to: Initialize a Transactional Subscription from Backup (Transactional Replication)

How to: Initialize a Transactional Subscription from a Backup (Replication Transact-SQL Programming) It’s really a gigantic task to setup a transactional replication on database of size 1.2 TB. As a new bee we will just go and setup transactional replication on any database and generate the snapshot without putting a thought that , how much […]

Generate Snapshot for New Articles added to Publication

There is always this question how to generate snapshot for the articles which are newly added to the Publication. Run the below command on the Publisher Server EXEC sp_changepublication @publication = ‘PublicationName’, @property = N’allow_anonymous’, @value = ‘false’ GO EXEC sp_changepublication @publication = ‘PublicationName’, @property = N’immediate_sync’, @value = ‘false’ GO Now add new article […]

Replicate Index on Partition Schemes at Subscriber DB (Transactional Replication)

Here we will go over how we can move the indexes of the Partitioned Publisher database (Data being partitioned on the basis of Clustered or non-clustered index) on the basis of Partition Function and Schemes or on different file groups to the subscriber database. This is applicable to the environment which has their database partitioned using partitioned […]

How to get Publisher Details from Subscriber

Working with different versions of sql servers i came across with an issue,How to get Publisher information from Subscriber. To get to know I ran sql profiler for couple of time and captured an undocumented stored procedure which helped in getting the information I needed about the publisher. exec sp_MSenumsubscriptions ‘both’ The stored procedure can […]