March 2, 2011 at 1:46 pm
Mike Hinds (3/2/2011)
(No column name)(No column name)usedtotalqueue_messages_846626059339836631990103199419sysdercv6796973484825484888
sysdesend67969734883648856
sysconvgroup67969733140131408
sysobjvalues2596186
What are they, and what can I safely do with them?
:blink: Do you have Service Broker active on this database? Check to see if you have a source queue.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 2, 2011 at 2:47 pm
Craig Farrell (3/2/2011)
Mike Hinds (3/2/2011)
(No column name)(No column name)usedtotalqueue_messages_846626059339836631990103199419sysdercv6796973484825484888
sysdesend67969734883648856
sysconvgroup67969733140131408
sysobjvalues2596186
What are they, and what can I safely do with them?
:blink: Do you have Service Broker active on this database? Check to see if you have a source queue.
Holly %?G*&H(*?J&)Y*(.
Never seen that one before!!!
March 3, 2011 at 5:55 am
Craig Farrell (3/2/2011)
Mike Hinds (3/2/2011)
(No column name)(No column name)usedtotalqueue_messages_846626059339836631990103199419
sysdercv6796973484825484888
sysdesend67969734883648856
sysconvgroup67969733140131408
sysobjvalues2596186
What are they, and what can I safely do with them?
:blink: Do you have Service Broker active on this database? Check to see if you have a source queue.
There is a Service Broker - and here I turn ignorant. I don't know how to access or examine it, i.e. to verify the source queue.
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS
March 3, 2011 at 11:45 am
Mike Hinds (3/3/2011)
There is a Service Broker - and here I turn ignorant. I don't know how to access or examine it, i.e. to verify the source queue.
Alright, there's a couple of moving parts to Service Broker. My guess is either you've got a Source Queue that's not shipping to a target, or a Target that's receiving messages and not closing the conversations. Either would simply keep building up messages.
I've only got a copy of Express handy to me at the moment (stayed home ill today), so bear with me while I do this by memory... also, my handy dandy references from Adam Machanic:
http://www.simple-talk.com/sql/learn-sql-server/service-broker-foundations-workbench/
http://www.simple-talk.com/sql/learn-sql-server/service-broker-advanced-basics-workbench/
And an overview of the system views:
http://www.sqlteam.com/article/how-to-troubleshoot-service-broker-problems
Alright, in the object nodes in the database, you should find one listed as service broker. Easy enough.
Do a SELECT count(*) FROM <queue_name> to find out if it's loaded up.
And lets find out if you're simply queue'd up, or if you have conversations:
select COUNT(*) FROM sys.conversation_endpoints
You'll want to check this for errors:
select * from sys.transmission_queue
And hopefully to find out where either it's going or coming from:
select * from sys.routes
and finally lets see what you can find out about the services themselves:
select * from sys.services
This will take a bit to help you troubleshoot, if you're completely unfamiliar with Service Broker. The cause is most likely simple but getting there will take a few iterations.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 3, 2011 at 11:46 am
Ninja's_RGR'us (3/2/2011)
Craig Farrell (3/2/2011)
Mike Hinds (3/2/2011)
(No column name)(No column name)usedtotalqueue_messages_846626059339836631990103199419sysdercv6796973484825484888
sysdesend67969734883648856
sysconvgroup67969733140131408
sysobjvalues2596186
What are they, and what can I safely do with them?
:blink: Do you have Service Broker active on this database? Check to see if you have a source queue.
Holly %?G*&H(*?J&)Y*(.
Never seen that one before!!!
I have a few times. It's a nasty way to end up with hidden bloat if people don't close the dialog/conversation properly... it just sits there forever.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 3, 2011 at 12:02 pm
Craig - many thanks!
I'll be saving and studying the info you provided.
FWIW, the 3rd party software vendor now tells me they are familiar with this behavior.
He asked me to wait until after business hours, and then run:
ALTER DATABASE DBName SET NEW_BROKER
Anyway, thanks again. I've learned a lot, and hopefully others will as well.
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS
March 3, 2011 at 12:04 pm
Mike Hinds (3/3/2011)
Craig - many thanks!I'll be saving and studying the info you provided.
FWIW, the 3rd party software vendor now tells me they are familiar with this behavior.
He asked me to wait until after business hours, and then run:
ALTER DATABASE DBName SET NEW_BROKER
Anyway, thanks again. I've learned a lot, and hopefully others will as well.
How the heck does that fix anything???
Won't it start all over again tomorrow?
March 3, 2011 at 12:04 pm
Mike Hinds (3/3/2011)
Craig - many thanks!I'll be saving and studying the info you provided.
FWIW, the 3rd party software vendor now tells me they are familiar with this behavior.
He asked me to wait until after business hours, and then run:
ALTER DATABASE DBName SET NEW_BROKER
Anyway, thanks again. I've learned a lot, and hopefully others will as well.
EEEEEeeeekk... talk about sledgehammering a screw.
Glad to help though, good luck!
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 3, 2011 at 12:07 pm
Yeah, I like that... "We know about it, but it would take too much to fix, so just whack it when it becomes a problem..." 😛
March 3, 2011 at 12:07 pm
Ninja's_RGR'us (3/3/2011)
Mike Hinds (3/3/2011)
Craig - many thanks!I'll be saving and studying the info you provided.
FWIW, the 3rd party software vendor now tells me they are familiar with this behavior.
He asked me to wait until after business hours, and then run:
ALTER DATABASE DBName SET NEW_BROKER
Anyway, thanks again. I've learned a lot, and hopefully others will as well.
How the heck does that fix anything???
Won't it start all over again tomorrow?
Yep, it also breaks any existing routes and they need to be re-created with the new identifier. It's not a holistic fix.... and mucks up any existing conversations.
It doesn't so much 'fix' things as removes the problem. At a guess, the vendor has some code somewhere that stuffs crap into the broker and doesn't use it again, and it's legacy code. It's the only reason you'd use a sledgehammer like this.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 28, 2014 at 2:00 pm
Mike:
Are you still having problems with SB tables bloating? I may have a lighter-weight fix for this than rebuilding SB from scratch...
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply