I’ve seen an issue with Service Broker that others may be seeing. Messages are sent into the service broker, and are processed as normal, and they are removed from the message queue. However you still see the conversation in the sys.conversation_endpoints DMV in a CONVERSING state instead of a CLOSED state. There are no records in the sys.transmission_queue which is the very strange part. There are also no errors when checking with SQL Server Profiler.
Apparently this is a known issue which they are working on. The strange thing is that when it happens on my system, it only happens on a single queue in my database.
Currently the only workaround is to do an END CONVERSATION WITH CLEANUP on the conversations. I’ve written this script which clears out the conversations. I’ve made it so that it only removes the messages which are for the problem conversation which don’t currently exist in the queue (this queue is not auto processed, there is a service which queries the queue every 30 seconds so there can be a backlog of valid messages in the queue which I don’t want to delete).
declare @i int
set @i = 1
while @i <> 10000
declare @conversation_handle uniqueidentifier
declare cur CURSOR for
SELECT TOP (1000) conversation_handle
WHERE NOT EXISTS (SELECT *
FROM [tcp://AWT/Sonar/Q_ObjectDelete] a
WHERE a.conversation_handle = sys.conversation_endpoints.conversation_handle)
AND sys.conversation_endpoints.far_service = 'tcp://AWT/Sonar/Svc_ObjectDelete'
AND sys.conversation_endpoints.state <> 'CD'
fetch next from cur into @conversation_handle
while @@fetch_status = 0
end conversation @conversation_handle with cleanup
fetch next from cur into @conversation_handle
set @i = @i + 1
I run this every hour to clean up the bogus records in the sys.conversation_endpoints DMV.
Without cleaning up the sys.conversation_endpoints DMV the tempdb will slowly start to fill up and throw out of space messages while sp_spaceused shows that the tempdb is empty in the same that id did in the other post I did a while back.
Part two of my two part series on hardware tuning of your SQL Server has just been released on SearchSQLServer.com. This part is titled “Tuning SQL Server performance via memory and CPU processing“.
This post is specifically about setting up DTC on Windows 2008. If you are looking for the post about setting up DTC on Windows 2003 you’ll want to go to the post “How to configure DTC on Windows 2003“.
The basic idea behind DTC setup in Windows 2008 is very similar to Windows 2003. DTC needs to be installed and then configured. To install DTC open the Control Panel, then Programs and Features. Click on the link to the right which says . Eventually the Server Manager will open and finish querying the system for the list of features and roles which are setup. Click on Add Feature button and select Application Server from the Feature list (you may need to click next to get past the welcome to the wizard screen. On the screen which tells you about the Application Server click next. On the next page you can select the services which you be installed as part of this role. Select the Incoming and/or Outgoing Remote Transactions depending on which one you need. If SOAP will be used you may need the WS-Atomic Transactions installed. If so select that as well. (In this example I’ll check them all.)
Then click next. If you have selected the WS-Atomic Transactions you’ll be presented with a page to select an SSL cert. You can either select one, of create a self signed cert, or request one later from a CA. I selected a self signed cert as I didn’t have one already installed. Click next, then install. If you didn’t install WS-Atomic Transactions the next screen will simply have the summary and install button.
It will take a while as Windows is going to install .NET 3.0 as part of this install. Now is a good time for coffee or a smoke.
After installation is complete click close.
If you click the plus sign next to Roles in the right hand menu you can navigate down to Roles > Application Server > Component Services > Distributed Transaction Coordinator > Local DTC.
Right click on Local DTC and select properties, then select the security tab. This tab looks very similar to the Windows 2003 one.
Check which ever boxes you need to in order to get the DTC setup to match the other servers in your environment. Be sure to enable “Allow Remote Clients” or client machines won’t be able to access the DTC on this machine. If you want to be able to remotely enable DTC from another machine you’ll need to check the “Allow Remote Administration”. It’s been my experience that once DTC is setup and working remove administration probably isn’t that important so I’d say leave that unchecked and simply RDP into the server if it needs to be worked on. Your screen will look something like this.
When you click OK DTC will prompt you to restart it. Click yes (unless you want to schedule the restart for another time) and DTC will restart. You can see the DTC events in the log by selecting the “Application Server” option from the menu on the left. You are going to want the most recent event to look something like this.
MSDTC started with the following settings:
Security Configuration (OFF = 0 and ON = 1):
Allow Remote Administrator = 0,
Network Clients = 1,
Trasaction Manager Communication:
Allow Inbound Transactions = 1,
Allow Outbound Transactions = 1,
Transaction Internet Protocol (TIP) = 0,
Enable XA Transactions = 1,
MSDTC Communications Security = No Authentication Required,
Account = NT AUTHORITY\NetworkService,
Firewall Exclusion Detected = 0
Transaction Bridge Installed = 1
Filtering Duplicate Events = 1
(Yes, I’m aware of the spelling issues in the error message. That’s a straight copy and paste from Windows 2008’s event log.)When configuring DTC on a cluster you only need to configure one node. This is because DTC is a cluster aware service so when you install DTC after setup clustering (or you setup clustering after installing DTC) the DTC service will already be setup as a clustered resource within the first cluster resource group created. When you configure DTC for network access on a cluster the settings are written to the Quorum drive as well as the system registry which allows both nodes to share the settings. If you have a cluster and you have to go through a firewall with DTC and have followed KB Article 250367 (I’m sure there is a Windows 2008 version of this KB article somewhere, I just haven’t run across it yet) you will need to have more than 20 ports available to DTC. This is because when you configure the DCOM protocols to use specific ports you are configuring all of RPC to use those specific ports. This means that the cluster administrator needs to use these ports as does the Component Services window which monitors for distributed transactions. When dealing with a cluster it is recommended that you have at least 100 ports open between the machines within the transaction. If you need to setup MS DTC to talk to another DTC coordinator then you will probably need to use the No Authentication Required setting unless they support the other options. Check with the vendor of the other coordinator to find out.
(These are the settings which you need no matter which version or edition of SQL Server you have installed.)
I’ve been asked to come back to the San Diego SQL Server Users Group on September 18, 2008. This time around I’ll be speaking about Federated Databases, and some various techniques which you can use to federate your systems.
I’m still finishing up the slide deck and demos. I’ll try and get them posted in advance. If I can’t I’ll post them shortly after.
A new tip of mine has just been published on SearchSQLServer.com. This tip, “Get SQL Server log shipping functionality without Enterprise Edition” is all about writing your own Log Shipping code without using Microsoft’s, allowing you to use Log Shipping on editions of SQL Server other than Enterprise Edition.
The error reporting in SQL Server Replication isn’t all that great. This is a well known issue that pretty much everyone knows about. Something that I don’t know if a whole lot of people know about, is that there is a way to get a lot more information from replication about what’s going on, and what’s going wrong.
This is most easily done by running the replication job manually from within a command window on the distributor. This will allow you to add switches or change values as needed and easily see the output, or redirect the output to a file for analysis, sending to Microsoft, your consultant, etc.
Replication is run by jobs, with job steps of some funky types. These step types simply mask what is happening in the background. SQL is shelling out and running a command line app and passing it all the switches as they are within the job step.
All the command line apps which replication uses are in the “C:\Program Files\Microsoft SQL Server\90\COM” folder by default (for SQL 2000 replace the 90 with 80, for SQL 2008 replace the 90 with 100). In that folder you will find a few apps which are of interest. When you run the snapshot job snapshot.exe is called. When you run a distribution job DISTRIB.exe is called (I’ve got no idea why it’s uppercase). When the log reader is running logread.exe is run. When you are running merge replication replmerg.exe is run.
All of these can be run manually from a command prompt. For starters open up the SQL Job and edit step two, the one which actually does the work. Copy all the text in the command window and paste is after the filename in the command prompt window and press enter. You will need to stop the SQL Agent job before you can actually run the command from the command prompt, as replication is designed so that you can only run the commands one at a time.
Now the whole point of this was to get more log info because the replication is failing. This is done by added the “-OutputVerboseLevel” switch to the command. This switch has between 3 and 5 logging levels depending on which command you are running. 0 (zero) is basically no logging, and as the number goes up more data will be shown. The distrib.exe, replmerg.exe and snapshot.exe takes 0-2, while the logread.exe takes 0-4.
You should only do this when replication is failing and you can’t figure out why, and all SQL is telling you is some cryptic error message.
Hopefully you’ll find this information useful.
The webcast which I did for Quest Software called Don’t Consolidate Yourself Into Performance Problems is now available for viewing after the fact.
So you’ve been tasked with setting up a quick and dirty reporting server. The goal is to restore the log files from the production server to the reporting server nightly.
The backups are simple, use the SQL Maintenance plan to backup the logs, and then copy them to the remote machine. But how do you restore the logs to the reporting server nightly.
Well I’ve got a two step SQL job which should help you out.
Step 1 kills all current sessions in the database, and step 2 does the actual restores.
The code for step 1 is:
declare @spid varchar(20)
declare cur CURSOR FOR
where dbid = db_id('Your Database Name Here') /*<---Put your database name here*/
and spid > 50
fetch next from cur into @spid
while @@FETCH_STATUS = 0
exec ('kill ' + @spid)
fetch next from cur into @spid
The code for step 2 is:
create table #Files
insert into #Files
exec xp_dirtree 'd:\', 1, 1
delete from #Files
where IsFile = 0
declare @FileName nvarchar(4000)
declare cur CURSOR FOR SELECT FileName from #Files
fetch next from cur into @FileName
WHILE @@FETCH_STATUS = 0
SET @FileName = 'D:\Path\To\Your\Log\Backups\' + @FileName
RESTORE LOG YourDatabaseNameHere FROM DISK=@FileName WITH STANDBY='D:\Path\To\Your\StandBy\File.standby'
IF @@ERROR <> 0
exec xp_delete_file @FileName
fetch next from cur into @FileName
I hope this makes your process easier. Now this code only works on SQL Server 2005 and up as the system stored procedures which I use were not included until SQL Server 2005.
The Code Camp is the weekend before PDC, and right near where PDC will be held. So if you are coming to PDC this year, come a couple of days early and check out what is hoped to be the largest SoCal Code Camp ever.
I’ve got four sessions scheduled, all of which are tagged with “SQL Server“.
- Back To Basics: Getting Back To The Basics of SQL Server (Part 1)
- Back To Basics: Getting Back To The Basics of SQL Server (Part 2)
- Scaling that database bigger than ever (Part 1)
- Scaling that database bigger than ever (Part 2)
- Storage For the DBA
If you are planning on attending the Code Camp, be sure to mark the sessions you want to attend with the interest check box. That way the Code Camp staff knows how big of a room each session will need.
If you are interested in speaking at the Code Camp, feel free to enter more sessions. The more sessions we have, the more popular the event will be.
As the date gets closer additional sessions will be entered onto the site. Be sure to check back often.
I’ll do my best to get the slide decks and sample code posted before the Code Camp starts. Odds are I’ll get them posted on Saturday morning as I did last time. If you missed any of my sessions from prior Code Camps, let me know in the comments and I’ll see what I can do about adding those sessions to the new schedule. See you at the Code Camp.
Last night was my talk with the NJ SQL User Group. I would jut like to thank them for the invite, I had a great time coming and speaking with them. It was a very interactive night, with a lot of questions. Quest Software was nice enough to not just send me out there, but one of the local sales reps also came, with a massive amount of give aways for the members. He brought T-Shirts, demo disks, and the new Quest Tote Bags which were a huge hit.
The presentation went great, and there were a ton of questions.