SQL Server Replication status

Few days back I had to query to my production sql replication server to find what all publications are running and what is the status for each of those. A search on the web found me this (unfortunately I don’t know the author of this to give credit ) :
SELECT
(CASE
WHEN mdh.runstatus = ‘1’ THEN ‘Start – ‘+cast(mdh.runstatus as varchar)
WHEN mdh.runstatus = ‘2’ THEN ‘Succeed – ‘+cast(mdh.runstatus as varchar)
WHEN mdh.runstatus = ‘3’ THEN ‘InProgress – ‘+cast(mdh.runstatus as varchar)
WHEN mdh.runstatus = ‘4’ THEN ‘Idle – ‘+cast(mdh.runstatus as varchar)
WHEN mdh.runstatus = ‘5’ THEN ‘Retry – ‘+cast(mdh.runstatus as varchar)
WHEN mdh.runstatus = ‘6’ THEN ‘Fail – ‘+cast(mdh.runstatus as varchar)
ELSE CAST(mdh.runstatus AS VARCHAR)
END) [Run Status],
mda.subscriber_db [Subscriber DB],
mda.publication [PUB Name],
right(left(mda.name,LEN(mda.name)-(len(mda.id)+1)), LEN(left(mda.name,LEN(mda.name)-(len(mda.id)+1)))-(10+len(mda.publisher_db)+(case when mda.publisher_db=’ALL’ then 1 else LEN(mda.publication)+2 end))) [SUBSCRIBER],
CONVERT(VARCHAR(25),mdh.[time]) [LastSynchronized],
und.UndelivCmdsInDistDB [UndistCom],
mdh.comments [Comments],
‘select * from distribution.dbo.msrepl_errors (nolock) where id = ‘ + CAST(mdh.error_id AS VARCHAR(8)) [Query More Info],
mdh.xact_seqno [SEQ_NO],
(CASE
WHEN mda.subscription_type = ‘0’ THEN ‘Push’
WHEN mda.subscription_type = ‘1’ THEN ‘Pull’
WHEN mda.subscription_type = ‘2’ THEN ‘Anonymous’
ELSE CAST(mda.subscription_type AS VARCHAR)
END) [SUB Type],

mda.publisher_db+’ – ‘+CAST(mda.publisher_database_id as varchar) [Publisher DB],
mda.name [Pub – DB – Publication – SUB – AgentID]
FROM distribution.dbo.MSdistribution_agents mda
LEFT JOIN distribution.dbo.MSdistribution_history mdh ON mdh.agent_id = mda.id
JOIN
(SELECT s.agent_id, MaxAgentValue.[time], SUM(CASE WHEN xact_seqno > MaxAgentValue.maxseq THEN 1 ELSE 0 END) AS UndelivCmdsInDistDB
FROM distribution.dbo.MSrepl_commands t (NOLOCK)
JOIN distribution.dbo.MSsubscriptions AS s (NOLOCK) ON (t.article_id = s.article_id AND t.publisher_database_id=s.publisher_database_id )
JOIN
(SELECT hist.agent_id, MAX(hist.[time]) AS [time], h.maxseq
FROM distribution.dbo.MSdistribution_history hist (NOLOCK)
JOIN (SELECT agent_id,ISNULL(MAX(xact_seqno),0x0) AS maxseq
FROM distribution.dbo.MSdistribution_history (NOLOCK)
GROUP BY agent_id) AS h
ON (hist.agent_id=h.agent_id AND h.maxseq=hist.xact_seqno)
GROUP BY hist.agent_id, h.maxseq
) AS MaxAgentValue
ON MaxAgentValue.agent_id = s.agent_id
GROUP BY s.agent_id, MaxAgentValue.[time]
) und
ON mda.id = und.agent_id AND und.[time] = mdh.[time]
where mda.subscriber_db<>’virtual’ — created when your publication has the immediate_sync property set to true. This property dictates whether snapshot is available all the time for new subscriptions to be initialized. This affects the cleanup behavior of transactional replication. If this property is set to true, the transactions will be retained for max retention period instead of it getting cleaned up as soon as all the subscriptions got the change.
–and mdh.runstatus=’6′ –Fail
–and mdh.runstatus<>’2′ –Succeed
order by mdh.[time]

The script can be downloaded  here

Posted in database, how to, sql server | Tagged , , , , | Leave a comment

Find SSRS reports that are run

WITH RankedReports
AS
(SELECT ReportID,
TimeStart,
UserName,
RANK() OVER (PARTITION BY ReportID ORDER BY TimeStart DESC) AS iRank
FROM dbo.ExecutionLog t1
JOIN
dbo.Catalog t2
ON t1.ReportID = t2.ItemID
)
SELECT t2.Name AS ReportName,
t1.TimeStart,
t1.UserName,
t2.Path,
t1.ReportID
FROM RankedReports t1
JOIN
dbo.Catalog t2
ON t1.ReportID = t2.ItemID
WHERE t1.iRank = 1
ORDER BY t1.TimeStart;

Posted in database, how to, sql server | Tagged , | Leave a comment

Drill your data on hadoop cluster using Apache Drill

I have been working on MapR and Cloudera big data distribution for the past year.  I have been fascinated by its capability.  One of my favorite is Apache Drill, a schema on read SQL interface to HDFS file system.  Its pretty slick, you can query the data via its native shell interface or its web interface or using Drill Explorer client that you can install on your machine.  Learn more about Apache Drill  and  best practices for Apache Drill

Happy Drilling !

Posted in BigData, hadoop, how to | Leave a comment

Big Data distribution downloads

I have been working on evaluating different big data solutions for the past few months.  If you want to give them a show, here are few of major big data solutions, these links are quick start VM images :

Cloudera
IBM
Hortonworks
MapR

You can download vanilla hadoop straight from the apache website But then you have to install the rest of the components by yourself.

 

Posted in BigData, database, internet | Tagged , , , , , , | Leave a comment