Management Studio 2005 running away with cpu??

[jsteph]

Hi all,
In SSMS 2005, after I open some query--it seems like any query, and run it, once it's finished then the cpu goes to 99% and stays there.

This is after the query--a small one--has finished exectuing. It can be creating a stored procedure, a simple select to check records, whatever. The query's are done--the message of 'query completed successfully' or whatever appears, and I've got nothing else running. This is a stand-alone test server, running sql-2005 developer edition. Nothing else connects to it.

It took me over a minute to get into the Activity monitor to see if anything was running--and there was nothing. Just the normal couple of connections, which were sleeping, and then what appears to be the connection for activity monitor. Hitting refresh takes a while, and the only connection showing cpu incrementing is the one for activity monitor (I'm assuming that's because I just hit the refresh). Meanwhile Windows task-manager shows 99% usage on the sql server process.

What could be happening?
Thanks,
--Jim

 

[mrdenny]

That's very strange behavior. What service pack do you have installed?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jsteph]

It's 9.00.1399.06, developer version.
At one point a yellow ballon came up from the sql icon in the systray and said something like "Can't respond now, server is processing an internal operation"
...something like that, I couldn't get the full wording because those yellow ballons sit there and fade away and clicking on it made it disappear when I thought it would bring up more info.

So I'm wondering if it's doing some rebuild of the some index or something, or maybe it's set to autoshrink daily. I never would have set that, but maybe it's a default. I left work 2 hours ago, got home and logged in just now and it's still pegged at 99%, and all process are sleeping but this one (the one I assumed was the Activity Monitor):

create table #tmpDBCCinputbuffer ([Event Type] nvarchar(512), [Parameters] int, [Event Info] nvarchar(512)) 
insert into #tmpDBCCinputbuffer exec ('DBCC INPUTBUFFER(58)') 
select [Event Info] from #tmpDBCCinputbuffer

I'm not sure what that is, the old activity monitor(in sql-2000 EM)I thought was just a wrapper around sp_who2, but I've never seen the above code before.
--Jim

 

[jsteph]

Also, I added the i/o read and write byte columns to windows task manager, and they're both incrementing fairly quickly, a couple MB per second for both read and write.
--Jim

 

[mrdenny]

That code would be the new version of the Activity Montior looking at it's own process.

You should start by patching your system. That's the RTM. There have been two service packs and several hot fixes since SQL 2005 was released.

The increasing read and write number could simply be SQL doing it's thing in the backround.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jsteph]

I ran some updates, the last one (KB 932557) failed after 3 attempts, reboots, etc. Don't know what that's about, but here's the current verison:

Microsoft SQL Server 2005 - 9.00.3054.00 <<>> Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

Although the problem seems to be fixed, I don't think it was the service back level. I checked the Options and Autoshrink was on, as was auto-update statistics. I had previously removed a field from the large table. So I believe that what was happening is that it was shrinking the database in the background, because I noticed the .mdf file dropped from 62 gig to 40 gig this morning, after the cpu had been pegged at 99 for many hours.

Anyway, I think I'm good, thanks very much for helping,

--Jim

 

[mrdenny]

That could very well have been the problem. I always recommend having AutoShrink off. Auto-update stats should be enabled for most databases.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com