31 January 2013

Server is not configured for DATA ACCESS.

Step 1 :- Check server is listed in servers or not

Select * from sys.servers

Step 2 :- Add Server and Set it to true

EXEC sp_serveroption 'YourServer', 'DATA ACCESS', TRUE
 
 
 Step 3:- Verification

use Select * from sys.servers  verify field  “is_Data_Access_enabled” is set to 1.
 

29 January 2013

Replication Agent Errors

--To get Agents that caused Error

SELECT  distinct da.name
FROM   MSpublications pub inner join MSsubscriptions subs
                                                ON pub.publication_id = subs.publication_id
                                inner join MSdistribution_agents da
                                                ON da.id = subs.agent_id
WHERE pub.publication = 'PublicationName'


-- To check the errors
select * from msdb..sysreplicationalerts 

-- Agent Errors
SELECT * FROM sys.messages WHERE message_id in ( 14151)

=================================================================

Message ID
Predefined alert
Condition causing the alert to fire
Enters additional information in msdb..sysreplicationalerts
14150
Replication: agent success
Agent shuts down successfully.
Yes
14151
Replication: agent failure
Agent shuts down with an error.
Yes
14152
Replication: agent retry
Agent shuts down after unsuccessfully retrying an operation (agent encounters an error such as server not available, deadlock, connection failure, or time-out failure).
Yes
14157
Replication: expired subscription dropped
Expired subscription was dropped.
No
20572
Replication: Subscription reinitialized after validation failure
Response job 'Reinitialize subscriptions on data validation failure' reinitializes a subscription successfully.
No
20574
Replication: Subscriber has failed data validation
Distribution or Merge Agent fails data validation.
Yes
20575
Replication: Subscriber has passed data validation
Distribution or Merge Agent passes data validation.
Yes
20578
Replication: agent custom shutdown


22815
Peer-to-peer conflict detection alert
Distribution Agent detected a conflict when it tries to apply a change at a peer-to-peer node.
Yes
I

25 January 2013

History of a Job - T-SQL

Just change the Job name and Date, Run the query to get history of a job



SELECT      [JobName]   = JOB.name,

            [Step]      = HIST.step_id,

            [StepName]  = HIST.step_name,

            [Message]   = HIST.message,

            [Status]    = CASE WHEN HIST.run_status = 0 THEN 'Failed'

            WHEN HIST.run_status = 1 THEN 'Succeeded'

            WHEN HIST.run_status = 2 THEN 'Retry'

            WHEN HIST.run_status = 3 THEN 'Canceled'

            END,

            [RunDate]   = HIST.run_date,

            [RunTime]   = HIST.run_time,

            [Duration]  = HIST.run_duration

FROM        msdb..sysjobs JOB

INNER JOIN  msdb..sysjobhistory HIST ON HIST.job_id = JOB.job_id

WHERE    JOB.name = 'JOBNAMEEEE' and hist.run_date>'20130123'

ORDER BY    HIST.run_date, HIST.run_time 

23 January 2013

Query to find insert/update on the table in any DB



Step1 . Replace your object name in the place of TABLENAME
 
Step2 . 
Run this


exec sp_MSforeachdb

';with cte as (
select db_id() as dbid,db_name() as dbname,
so.name,
sc.text
from
sysobjects so inner join syscomments sc on so.id = sc.id
where
sc.text like ''%INSERT%INTO%TABLENAME%''
or sc.text like ''%UPDATE%TABLENAME%''
and so.xtype = ''P''
union all
select  db_id() as dbid,db_name() as dbname,
so.name,
sc.text
from
sysobjects so inner join syscomments sc on so.id = sc.id
where
sc.text like ''%insert%into%TABLENAME%''
or sc.text like ''%UPDATE%TABLENAME%''
and so.xtype = ''P''
)
select * from cte where dbid not in (''1'',''2'',''3'',''4'',''5'',''6'')

Distribution Agent - Start /Stop in T-SQL

-- To STOP the Distribution Agent:
sp_MSstopdistribution_agent @publisher, @publisher_db, @publication,
 @subscriber, @subscriber_db
 
--To START the Distribution Agent:
sp_MSstartdistribution_agent @publisher, @publisher_db, @publication,
 @subscriber, @subscriber_db
 
 
 

Cannot insert duplicate key row - Replication

  • 2601 Cannot insert duplicate key row in object '%.*ls' with unique index '%.*ls'
  • 20598 The row was not found at the Subscriber when applying the replicated command.
  • 2627 Violation of PRIMARY KEY constraint 'PK__A'. Cannot insert duplicate key in object 'dbo.A'.



http://www.mssqltips.com/sqlservertip/2469/handling-data-consistency-errors-in-sql-server-transactional-replication/

22 January 2013

Distribution Agents that are actively distributing transactions

The following query will pull all information about distribution agents that are actively distributing transactions (in progress) and will provide useful information such as the delivery rate (txs/sec).

SELECT      da.name, da.publisher_db, da.subscription_type,
            dh.runstatus, dh.delivery_rate, dh.start_time, dh.duration
FROM        dbo.MSdistribution_history dh WITH (NOLOCK)
INNER JOIN  dbo.msdistribution_agents da WITH (NOLOCK)
ON          dh.agent_id = da.id
WHERE       dh.runstatus = 3
AND         dh.start_time BETWEEN DATEADD(dd,-30,GETDATE()) AND GETDATE()
ORDER BY    dh.start_time DESC


More On :- http://www.mssqltips.com/sqlservertip/2853/troubleshooting-sql-server-replication/


16 January 2013

Transactional Replication “Cannot insert explicit value for identity column…”

Make the triggers on the subscriber not for replication and ensure that the identity column on the subscriber is marked as not for replication. This should take care of the problem.

 alter table dbo.TableName
alter column [pkcolumn] add NOT FOR REPLICATION


More Details on  :- 

http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/f142b042-c9c9-49e0-a3d6-a19494fc6046

15 January 2013

Replication Error

Step 1 :- 


select * from dbo.MSarticles where article_id in ( select article_id from MSrepl_commands where

xact_seqno = 0x00002077000058EB006D00000000)

Step 2 :- 


exec sp_browsereplcmds @xact_seqno_start = '0x00002077000058EB006D00000000', @xact_seqno_end =

'0x00002077000058EB006D00000000'

Step 3 :-

{CALL [dbo].[sp_MSins_dboDie] (1004136,N'2234-41733',2234,41733,NULL)}

Step 4 :- 

Connect to Subscriber, run the command


set identity_insert die on
exec [sp_MSins_dboDie]

@c1='1004136',
@c2='2234-41733',
@c3=2234,
@c4=41733,
@c5=NULL

11 January 2013

Replication Without Generating Snap Shot- step by step

--STEP1 - @PUBLISHER
select immediate_sync , allow_anonymous from syspublications

--STEP2 - @PUBLISHER
EXEC sp_changepublication @publication = 'DBNAME', @property =

N'allow_anonymous', @value='False'

Go

EXEC sp_changepublication @publication = 'DBNAME', @property =

N'immediate_sync', @value='false'

Go

--STEP3- @PUBLISHER DB
select immediate_sync , allow_anonymous from syspublications


--STEP4 ADD ARTICLES --@PUBLISHER DB

EXEC sp_addarticle @publication = 'DBNAME', @article ='ctxxxxxxxxxx',

@source_object='ctxxxxxxxx', @force_invalidate_snapshot=1

--STEP5 Verify if you are using CONCURRENT or NATIVE method for synchronization by running the following command.

select sync_method from syspublications --Here its 3

--STEP6 If the value is 3 or 4 then it is CONCURRENT and if it is 0 then it is NATIVE.If you are using the NATIVE method for synchronization then the parameter


--@reserved=’Internal’ is optional but there is no harm in using it anyways. But if it is CONCURRENT then you have to use that parameter. Else the next time you run the snapshot agent it is going to generate a snapshot for all the articles.

EXEC sp_addsubscription @publication = 'DBNAME', @article = 'ctxxxxxxxxx',

@subscriber ='PRA*******\SQL**', @destination_db = 'DBNAMEKKK',

@reserved='Internal'



--STEP 7
--start the SNAPSHOT AGENT job from the job activity monitor.

--STEP 8

--Verify that the snapshot was generated for only one article.

--REPEAT ADD artical and generate snapshot


-- Generate the snapshot
--http://thinknook.com/sql-replication-the-initial-snapshot-for-publication-is-not-yet-available-2012-04-19/

/*


    The Snapshot Agent did not run after a new subscription has been created: Basically if a new subscription is created, the Snapshot Agent needs to run once to generate a recent snapshot, in order to initialize a subscription.

    The Snapshot Agent did not run after a subscription has been initialized: Similar scenario to the above, if a subscription has been initialized you need to run the Snapshot Agent and generate a recent snapshot.

    A database that contains a subscription has been restored: If a database has been restored, you will need to initialize the subscription by generating a new Snapshot.


    */