Geeks With Blogs

News
View Szymon Kobalczyk's profile on LinkedIn

Szymon Kobalczyk's Blog A Developer's Notebook

Some time ago I got very strange situation on my development SQL Server 2005. Immediately after starting the process it went up to 100% CPU and stayed there for hours. There were no external connections, and I couldn't figure out what was causing this so I asked for help our best SQL geek Paweł Potasiński. With his help we were able to figure out that this was caused by service broker running on one of the databases although we didn't find the root cause for this.

Fast forward few months, and last week I got into the same trouble after upgrading the database. Of course I didn't recall how exactly we fixed it last time. So today I had to gave up and ask for Paweł's help again. I wrote this post mostly because I don't want to bother him again on this.

So first you use this to see which databases have service broker enabled:

SELECT * FROM sys.databases WHERE is_broker_enabled = 1
Then you can use ALTER TABLE ... SET DISABLE_BROKER but this would only work

when database is not locked. Therefore the best way to do this is like this:

ALTER DATABASE ... SET SINGLE_USER WITH ROLLLBACK IMMEDIATE
GO
ALTER DATABASE ... SET DISABLE_BROKER
GO
ALTER DATABASE ... SET MULTI_USER
GO

Note that this will kick off all users connected to this database.

Thanks for help Paweł - I owe you one more time :-)

Posted on Tuesday, September 16, 2008 8:21 PM Development | Back to top


Comments on this post: What to do if your MS SQL Server 2005 eats 100% CPU at idle

# re: What to do if your MS SQL Server 2005 eats 100% CPU at idle
Requesting Gravatar...
FYI:
ALTER DATABASE ... SET DISABLE_BROKER with ROLLBACK IMMEDIATE
will do the trick
Left by YM on Mar 19, 2010 11:55 AM

Your comment:
 (will show your gravatar)


Copyright © Szymon Kobalczyk | Powered by: GeeksWithBlogs.net