One of the interesting features of the OUTPUT clauses introduced in SQL Server 2005 is that one can actualy chain DML statements into one complex statement that operates updates on several tables at once. Say we have a table with customer data and a process that has to bill each customer periodically. The ‘billing’ process consist of an update on the table (say extend the subscription date), but the billing has to be processed separately. Consider an example where the processing involves a Web call to a bank portal to charge a credit card and, like all HTTP calls, it has the potential to fail. So we have two tables like this:

Read the rest of this entry »

As many you probably already know, the Professional Association of SQL Server (aka. PASS) will have an european conference at Neuss, Germany, between April 14 and April 16 this year. The event site http://www.european-pass-conference.com has details about the event, including the agenda, sessions and speakers list. I myself am one of the speakers and I will present techniques for administering, monitoring and troubleshooting Service Broker, with a focus on the new monitoring and troubleshooting tools available for Service Broker in SQL Server 2008.

So if you attend the conference stop by to discuss things Service Brokerish or just to say Hi!

I had reviewed the way SqlDependency works several times in this blog, but the implementation of this feature will probably continue to surprise me for a long time. After reading about numerous reports of ERRORLOG files getting filled with messages like:

The query notification dialog on conversation handle '{5925E62A-A3BA-DC11-9E8E-000C293EC5A4}.'closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8470</Code><Description>Remote service has been dropped.</Description></Error>'.

I decided to take a closer look. The immediate cause of this error is quite clear, is it because the target service of a Query Notification subscription was dropped. I have covered the system error 8470 and others in my earlier post Resending messages. But of course, the real question is why is this happening? Is there some coding error on the part of the application developer and some way to avoid this problem? Is there some configuration issue?

Read the rest of this entry »

Klaus Aschenbrenner is following up on his Pro SQL Server 2005 Service Broker book with an update for 2008. The book is already available for pre-order on Amazon: http://www.amazon.com/Pro-Server-2008-Service-Broker/dp/1590599993 and will contain a troubleshooting and administration chapter writen by yours truly

This article is a continuation of my two articles on using Service Broker as a pure data-push one-way communication channel: Reusing Conversations and Recycling Conversations. I originally did not plan for this third part, but I was more than once asked the same question: if a conversation is in error what happens to the messages that were sent but not yet delivered?

The short answer is that messages that are still pending are in the sender’s database sys.transmission_queue system table so when an Error message is received the sender can scan this table and resend each message that is still pending. An example of such procedure is not difficult to code:

Read the rest of this entry »

I will attend tomorrow, December 4, the Data Mining and Business Intelligence for Enterprises event in Bucharest thanks to an invitation from my friend the SQL Server MVP Cristian Lefter. Now I know that most readers of this blog will not be in Romania 🙂 but I thought to post this case you happen to be there and want to have a Service Broker chat or just want to meet me and say Hi!

Almost two years ago I have posted the original Troubleshooting dialogs post in this blog (back in the day when it was hosted at MSDN). I have often referenced this post when I was asked to help investigate some Service Broker issue, and I have seen others referencing it. Although it lacked a lot of details it was a good starting point for anybody who was asking itself ‘The messages don’t get through, wonder what’s wrong…’

It is time for an updated entry on this topic because the bar was raised to a whole new level: the Service Broker Diagnostics Tool was shipped in the November CTP of SQL Server 2008!

Read the rest of this entry »

Now that the SQL Server 2008 CTP is no longer a virtual image but a true install and the quality of this CTP is near ship quality many of you are probably going to look at testing existing applications on SQL Server 2008 and perhaps start coding some pilot projects exploring the new features in SQL Server 2008. For the distributed applications Service Broker supports one question arrises immedeatly: is Service Broker in SQL Server 2008 compatible with SQL Server 2005?

Read the rest of this entry »

I’ve been away for the last couple of weeks and I had no time to update this blog when the latest SQL Server 2008 CTP release has been made available (download at http://www.microsoft.com/sql/2008/prodinfo/download.mspx), so the news that this CTP was released is probably no longer news for most of you.

Why this release is so important for Service Broker fans is that the diagnostics tool is contained in it: ssbdiagnose.exe. The best part is that this tool works with and can diagnose SQL Server 2005 deployments as well as SQL Server 2008, so you can actually use it to troubleshoot your existing projects and sites. The usage of this tool is straightforward once you get past the complex command line syntax, and I will cover this soon with a more lengthy tutorial post dedicated to it.

A problem I’ve often seen with many SQL Server deployments is when ERRORLOG grows and grows and grows, eventually filling all the available disk space. Reasons for this growth can be various, but the surprising thing is that sometimes they are absolutely legit. Kevin Farlee mentions on this blog entry http://blogs.msdn.com/sqlserverstorageengine/archive/2007/10/30/when-is-too-much-success-a-bad-thing.aspx about a site that is doing backups every 12 seconds, and each backup is reporting a success entry in ERRORLOG. Other reasons I’ve seen in production that caused growth were related to SqlDependency like the ones I mentioned previously here: http://blogs.msdn.com/remusrusanu/archive/2007/10/12/when-it-rains-it-pours.aspx. Now one thing to remember is that irellevant to what is causing the growth, there actualy is a stored procedure that forces an errorlog cycle: sp_cycle_errorlog. This procedure closes the current ERRORLOG, renames it as ERRORLOG.1 and opens a new one, pretty much like a server restart. If ERRORLOG growth is an issue in your environment, add an Agent job to periodically cycle the current ERRORLOG, perhaps followed up by an archive step.