Is there somewhere that I can tell when the last time either the publication or better yet a subscrition replicated data (in a system table or view maybe)?
I want to set up monitoring to make sure I am aware if something for some reason does not replicate
OH and it is on SQL 2005 enterprise... an
If synchronization is processed by SQL Server Agent Job, the best choice is to view job state.
Here is example how to do it, if there is Merge publication with Merge Push Subscriptions:
1. Call sp_MSEnum_Merge_subscriptions in Distribution database to determine job ID's of the Push subscriptions:
Get PushSubscriptions Dataset
exec distribution..sp_MSEnum_Merge_subscriptions 'publisher_server','pub_database','publication_name'
2. The returning dataset contains a column named [job_id], that contains Agent Job ID in Binary format. You must convert it to hexadecimal string format (for example, 0x8392E005C4941A4C9A279A68EFC9F3D8)
3. Execute sp_help_job in MSDB database to retrieve current job state:
Get Job State
exec msdb..sp_help_job @.job_id = 0x8392E005C4941A4C9A279A68EFC9F3D8 , @.job_aspect ='job'
The SP returns dataset, containing number of information on the job: date_created, last_run_date, last_run_time, next_run_date, next_run_time, last_run_outcome etc...
I think, this will be helpful!
Regards,
|||Also check out MSMerge_history in the distribution database. Fill history of the job in there.
Martin
