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 […]
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) […]
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 […]
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 […]
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 […]
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 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 […]
Thursday i was too happy that weekend started as I took a leave on friday, as i stepped out from my technology room i got an unexpected call from the technology datacenter hearing what he said said my dream of weekend shattered. The problem was one of the SQL Server on Cluster failed .On the […]
In this blog we will talk about why to create an indexed view, how to create an index view and replicate the index view as a table to existing or new Transactional Replication. While working with one of my client running PeopleSoft Application having database on SQL Server 2005 wanted to segregate the reporting part. […]
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 […]
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 […]
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 […]