Yum, pickles… where was I?
Oh yea, SQL… I run a daily morning routine where I check error logs, mirror status and then replication status… What I find so odd is that this script runs fine for 6 weeks then this week it takes over 20 minutes to run the replication part. WTF? really SQL really? How come you run nice and smooth for 6 weeks and now it decieds to be an annoying little “B”.
Well I did a little digging and found that this “exec distribution.sys.sp_replmonitorhelppublication” little procedure runs a bunch of other scripts but if I go to the replication monitor it shows up almost instantly… so where the hell is it grabbing that data from?
I think we need to find out SQL’s secrets here and find out why this takes 20 minutes or longer to run and if there is any way to reduce this time… missing index? in inefficient cursor? or demon possessed server?
Any one have thoughts? Ideas? bread crumbs to lead us to the path?
Lets update this with ideas!
Once of neatest feature of MS-SQL is the Central Management Server. I have been using this for about 3 years; mostly 2 years as I only played with it for the first year. I find it the most scary and powerful tool that allows me to run queries on all of my servers or specific environments to give me feed back on say, replication, mirroring or error log details.
This is also useful way to get the version information for SQL.
There are 2 draw backs that I have found, first it needs a central management server which for some (stupid) unknown reason cannot be added to the list of managed servers with out hoodoo black magic (using the ip address or the port instead of the instance) and secondly it only uses active directory which makes sense if you have only one account or you know one site; if you have multiple domains or multiple sites where SQL users are the only way to get to it then you are SQL… I mean SOL. 😀
Damn SQL you are a fickle “B”.
So try it out if you have one domain, or access to multiple domains via one domain account and the need to manage multiple servers then this is the tool for you… it can also run policies which is a topic for another day.