Reputation
1,013
Top tag
Next privilege 2,000 Rep.
Edit questions and answers
Badges
1 6 13
Impact
~121k people reached

  • 0 posts edited
  • 0 helpful flags
  • 9 votes cast
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.
Oct
6
answered Scenario of huge SQL Server Database backup
Sep
21
comment restoring SQL Server from backup into a database with different database name?
Does a database with the name "MyDB_new' already exist? Do you want to overwrite it? If so, add the "REPLACE" option to the RESTORE statement.
Sep
13
answered When to use full backup or full backup and then differential backup
Aug
13
awarded  Popular Question
Aug
3
comment Replication with fail over cluster in SQL Server 2014
@pradeepkumaralugurthi: No. In a failover cluster instance (FCI), there is only one instance of the SQL server which can be live on one (and only one) of the machines in the Windows cluster at a time.
Aug
1
comment There is no option of audit in security for SQL Server 2014 .How to enable audit option for same?
Audit is an Enterprise-only feature. What edition of SQL Server are you running?
Jul
1
answered How to reduce database log file size frequently?
May
11
comment Large Transaction Backup
Are you saying that there's a chunk of time during which you aren't running t-log backups? If so, my first suggestion would be to put that on a regular schedule that runs all the time.