Troubleshooting dialogs, the sequel

November 28th, 2007

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 »

Is Service Broker in SQL Server 2008 compatible with the one in SQL Server 2005?

November 28th, 2007

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 »

The latest SQL Server 2008 CTP

November 27th, 2007

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.

Prevent ERRORLOG growth

November 11th, 2007

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.

When it rains, it pours

November 10th, 2007

I’ve seen a number of customers reporting problems about ERRORLOG growing out of control (tens of GBs) because of error like following:

2007-10-12 11:18:32.44 spid25s The query notification dialog on conversation handle ‘{EC54573A-9978-DC11-961C-00188B111155}.’ closed due to the following error: ‘<?xml version=”1.0??><Error xmlns=”http://schemas.microsoft.com/SQL/ServiceBroker/Error”><Code>-8490</Code><Description>Cannot find the remote service ‘SqlQueryNotificationService-4869b411-fa1c-4d8a-ab37-5bf5762eb98b’ because it does not exist.</Description></Error>’.

2007-10-12 11:37:20.69 spid51s The activated proc [dbo].[ SqlQueryNotificationStoredProcedure-633a0c13-66e4-410e-8bd8-744146d2258d] running on queue tempdb.dbo. SqlQueryNotificationService-633a0c13-66e4-410e-8bd8-744146d2258d output the following: ‘Could not find stored procedure ‘dbo. SqlQueryNotificationStoredProcedure-633a0c13-66e4-410e-8bd8-744146d2258d‘

2007-10-12 10:59:39.32 spid51 Error: 28054, Severity: 11, State: 1.
2007-10-12 10:59:39.32 spid51 Service Broker needs to access the master key in the database ‘tempdb’. Error code:25. The master key has to exist and the service master key encryption is required.

All these messages are related one way or another to the ADO.Net component SqlDependency. I’ll present each one how it is caused and how to avoid id.

Read the rest of this entry »

Pro SQL Server 2008 Service Broker

November 9th, 2007

Klaus has announced that he will release an update on his ‘Pro SQL Server 2005 Service Broker’ book with changes covering new features, revisiting some of the 2005 features like Query Notifications with more detailed coverage and much more. Not only that, but his announcement at http://www.csharp.at/blog/PermaLink,guid,a863c47b-2cfd-4395-bb32-e87bf33d6efe.aspx actually invites you to post back comments with what would you like to see in the new book.

Remove pooling for data changes from a WCF front end

November 1st, 2007

The question was asked on the forums at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2320807&SiteID=1: How to enable a WCF based application to benefit from SQL Server 2005 Query Notifications? Basically the front end clients should be notified when data is changed on the back-end server tables as possible with least possible pooling.

Deploying SqlDependency in the WCF service itself to leverage the server’s Query Notification is a relatively easy process, simply follow the SqlDependency usage guidelines for any application: start SqlDependency, then attach a SqlDependency object instance to your SqlCommand objects, use this object’s OnChange event to react to changes, and subscribe again once notified.

Read the rest of this entry »