Reputation
1,028
Top tag
Next privilege 2,000 Rep.
Edit questions and answers
Badges
1 7 13
Impact
~134k people reached

  • 0 posts edited
  • 0 helpful flags
  • 9 votes cast
Apr
5
comment Microsoft SQL Replication
Option 4: Production Instance → Distributor on Production Instance → Reporting Instance. TL;DR - the choice of where to put the distributor and the reporting database are orthogonal.
Mar
22
comment Why is user: system doing an hourly backup of my SQL databases?
Where/how are you seeing that it's being run by a user called "system"? Have you checked the backupset table in msdb for other relevant data (I've seen good stuff in name, description, software_vendor_id, etc).
Mar
15
awarded  Notable Question
Feb
27
answered Simple Mode - Is using checkpoints to truncate the transaction log a bad practice?
Feb
17
comment I can't create a transactional replication
It sounds like you created a pull subscription. Did you call sp_addsubscription at the publisher?
Feb
13
comment Odd service broker behavior
My understanding of NEW_BROKER is that it assigns a new broker identifier which would invalidate all of my current routes. Also, the current setup allows for messages of certain types to flow while others not. Just looking for an explanation as to why that would be. I can force it to work with a nuke and pave but want to understand what's happening.
Feb
12
comment Odd service broker behavior
I'm not restoring with ENABLE_BROKER, but issuing an ALTER DATABASE statement to enable service broker post-restore should be functionally equivalent right? And I'm aware of that article; that is one of the sources that suggests that a common cause of these symptoms is a routing problem.
Feb
12
asked Odd service broker behavior
Feb
3
awarded  Famous Question
Jan
26
comment SQL Server Service Broker - CHECKDB found consistency errors, how could this happen?
@LSZ: I was more curious about the other side of the conversation, but sure. Either way, do an 'end conversation <handle> with cleanup' and monitor for it in the future.
Jan
25
comment SQL Server Service Broker - CHECKDB found consistency errors, how could this happen?
Also, are you able to observe that there are messages in the queue that don't have a corresponding row in sys.conversation_endpoints?
Jan
10
awarded  Yearling
Dec
26
answered Can't get any fragmentation of index for testing in SQL Server database
Nov
22
comment SQL Server Backup Schedule Overlap
Why are you taking full and differential backups so frequently? The typical cadence is full backups semi-frequently (perhaps once a week), differentials slightly more frequently (perhaps once a day), and then log backups to support your RPO. Either way, taking a full backup once an hour seems very aggressive.
Nov
20
comment Service broker was backed up, now receiving, but doesn't seem to be processing
By way of coincidence, I'm implementing my own SB solution right now. I found that by taking any transactions out of the activation procedure that performance jumped dramatically. As a compromise, I implemented a "dead letter" table. I wrapped my processing logic in a gigantic "try/catch" and if there are any problems, I throw the message in that table. But processing the queue marches forward (i.e. no rollbacks).
Nov
18
comment Service broker was backed up, now receiving, but doesn't seem to be processing
Troubleshoot it like any other slow stored procedure. Find out which statement(s) is/are slow in the proc then see what they're waiting on. It could have nothing to do with service broker or everything to do with it. But until you profile and get data, it's just guessing.
Nov
16
comment Service broker was backed up, now receiving, but doesn't seem to be processing
I disagree that the loop is unnecessary for exactly the situation that you're in now. According to this BOL article (technet.microsoft.com/en-us/library/…), activation is not likely being triggered often enough. Which is why, when it is triggered, you want each procedure to keep processing messages until there aren't any left to process. Just my two cents. In order to dig yourself out of the hole you're in now, you could do something like while exists (select 1 from dbo.ENAudit_SBQueue) begin exec [dbo].[Parse_EN_Messages]; end in SSMS to fake it.
Nov
16
comment Service broker was backed up, now receiving, but doesn't seem to be processing
Just because you're doing a receive top(1) doesn't mean that it's doing them one at a time. That is, if you do something like: begin tran; while(1=1)begin; receive top(1)…; if (@@rowcount = 0) break; <<do processing>>;end; commit you'd effectively be trying to process all the messages in one transaction. Again, I think seeing the proc would be helpful.
Nov
16
comment Service broker was backed up, now receiving, but doesn't seem to be processing
So, from the description of your problem, you're using internal activation. Is there anything listed in sys.dm_broker_activated_tasks? What does your activation procedure look like? Are you able to post it here? It could be that the procedure is trying to process all the messages at once (i.e. in one transaction) which could be bad. Also, there's nothing that prevents you from running the activation by hand yourself.
Nov
10
comment Determine 3rd Friday of each month
@spaghettidba: Since DATEFIRST can be set within an execution context, do you think it would be more readable/obvious to set that within the stored procedure or query that's doing this calculation? If you're super concerned about changing the setting, you could always save of the value of @@DATEFIRST and then restore it when you're done.