Microsoft SQL Server 2005 经常CPU100%吃紧解决（GG出来的）

原文连接：Oringial hyperlink Texture page:
http://geekswithblogs.net/kobush/archive/2008/09/16/125204.aspx

 

转载：
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 :-)