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 

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/

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/

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

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