Viewing 15 posts - 1,036 through 1,050 (of 5,502 total)
Thank you Johan for "joining the party"!!! Much appreciated.
Your script should help to narrow it down even further.
One thing I noticed:
In the last section of the script where END Conversation...
September 2, 2011 at 12:51 am
Is there a Service Broker guru around?
I could need some help on this thread: http://www.sqlservercentral.com/Forums/FindPost1167028.aspx
I think I narrowed it down to the WMIEventProviderNotificationQueue associated with the
SQL/Notifications/ProcessWMIEventProviderNotification/v1.0 message type.
But since...
September 1, 2011 at 2:25 pm
juan08 (9/1/2011)
One last question, what do you think of doing this:http://sqlplanet.blogspot.com/2008/07/recreate-corrupt-msdb-database.html
Regards,
Juan
A clear and easy answer: NO!!!!!
As long as you don't know what process is responsible for the messages, rebuilding the...
September 1, 2011 at 2:16 pm
What would be the logic to populate those values?
Do you already have the columns defined?
Is the start value a running total?
September 1, 2011 at 12:34 pm
What doesn't work as expected?
The following code snippet does neither return 3 nor 4:
DECLARE @form TABLE (FromID INT)
INSERT INTO @form VALUES (1),(2),(3),(4),(5)
SELECT *
FROM @form
WHERE FromID <> 3 AND...
September 1, 2011 at 12:19 pm
It seems like there is no processing logi at all to Receive the messages and actually perform any actions based on it.
To get someone in to look at the system...
September 1, 2011 at 1:24 am
Change your query to
SELECT
nodeParams.value('PName[1]','VARCHAR(250)') AS PName,
nodeParams.value('PGrpId[1]','INT') AS PGrpId,
nodeParams.value('AsOf[1]','DATETIME') AS AsOf,
nodeSummStat.value('TrackError[1]','FLOAT') AS TrackError,
nodeSummStat.value('S_V_95[1]','FLOAT') AS V_95,
nodeSummStat.value('S_V_99[1]','FLOAT') AS V_99,
nodeSummStat.value('PValue[1]','FLOAT') AS Value,
D2.TracingDetails,
D2.SysDate,
D2.Name
FROM XmlDataTable D1
OUTER APPLY D1.ResultXML.nodes('SinglePVar') nodeslist(nodeEntry)
OUTER APPLY nodeEntry.nodes('Params') nodeslist2(nodeParams)
OUTER APPLY nodeEntry.nodes('SummStat') nodeslist3(nodeSummStat)
LEFT OUTER...
August 31, 2011 at 3:36 pm
We're getting closer...
As expected, it's a ServiceBroker communication causing the issue. Namely a WMI Provider. But at this point I'm getting lost since I've never used WMI. It seems like...
August 31, 2011 at 2:59 pm
What does the following statement return:
SELECT *
FROM sys.service_queues
WHERE object_id=1165247206
I still assume it has to do with ServiceBroker.
About 6000 rows in sys.conversation_endpoints sounds unusual...
Could you also...
August 31, 2011 at 2:16 pm
It seems like the function is called 14 times (once for each SELECT value). It might help to shred the data using the function and insert it into an indexed...
August 31, 2011 at 1:32 pm
I would check if the following section of the code is one of the performance killers:
CASE WHEN A.TransactionType in ('I', 'D', 'DYS', 'E') AND LEN(RTRIM(A.Reference)) = 4 THEN
(SELECT...
August 31, 2011 at 1:02 pm
You'll need to look at the performance of each query. It seems like both can be optimized.
What does the fnOrderDetailsXML() function actually do? Can you post the code of it?...
August 31, 2011 at 12:50 pm
angadsk83 (8/30/2011)
Data type is ntext.Its an requirement from the client.
If ntext is a requirement from the client, then you cannot store more than 2GB in the db.
You could store the...
August 31, 2011 at 12:35 pm
Do you run Service Broker on that system?
If so, what does the following query return on msdb?
SELECT count(*) FROM sys.conversation_endpoints with (nolock)
August 31, 2011 at 12:35 am
Do you know what table(s) causing the msdb growth?
You could run the following code against the msdb:
create table #tableSize
(name varchar(100), rows int, reserved varchar(50), data varchar(50), index_size varchar(50), unused varchar(50))
...
August 30, 2011 at 3:19 pm
Viewing 15 posts - 1,036 through 1,050 (of 5,502 total)