Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
codrgi
Starting Member
11 Posts |
 Posted - 2009-03-30 : 08:29:14
|
| This problem never started until a couple of days ago, and i'm running a small database, however it constintly goes up to 100% comp usage and stays there for at least 10 seconds before going back down to normal, and i've tried reinstalling MS SQL but it still occurs, so does anyone here had this before or knows what the problem is and how i can solve it? |
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-03-30 : 09:16:11
|
| Download a free copy of SQL Server 2008 express - install in anywhere e.g. on your local PC. Connect it to your current 2005 database, use its Activity Monitor to identify CPU usage. |
 |
|
|
codrgi
Starting Member
11 Posts |
Posted - 2009-03-30 : 09:50:36
|
| Its doing 70-90%, using spid 054, how do i find out why its doing this and what procedure of the database that its doing? |
|
|
|
codrgi
Starting Member
11 Posts |
Posted - 2009-03-30 : 13:59:25
|
| Still stuck on this one |
|
|
|
codrgi
Starting Member
11 Posts |
Posted - 2009-03-30 : 17:51:31
|
| is there a command or something in the sql i could do to find out which procedure is using the most memory or causing the 100% usage? |
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-03-31 : 04:26:54
|
| See answer already given. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-03-31 : 05:02:43
|
You can use SQL Profiler to monitor exactly which command is given the high CPU.I'd guees there is a heavy fragmentation or you have AUTOSHRINK set to ON, and database is increasing the size of files.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-03-31 : 06:55:06
|
| exec sp_who2 This will give the loginname / machine name of the SPID and the type of commandThe activity monitor in 2008 will give the actual query for high CPU usage. Or use DMV views in 2005.E.g.select top 20 * from sys.dm_exec_requestsorder by cpu_timeselect top 20 * from sys.dm_exec_sessionsorder by cpu_time |
|
|
|
|
|
|
SQL Server problem