| Author |
Topic |
|
argand
Starting Member
5 Posts |
 Posted - 2008-07-17 : 08:15:03
|
| The Server: SQL Server 2005 Enterprise, 4 dual-core processors, 24GB RAM, 15 HDD’s in various RAID configurations segmented for OS, DB, TempDB, Log and Index.This server normally runs great, but every now and then the CPU usage spikes to 100%. When this happens, I can remote desktop into the server and see the CPU usage, but cannot run Profiler or Management Studio to see what is currently running on the server.Is there any way to actually see what is running when you can’t connect? Is there a way to see what was running after you’ve had to kill the sqlserver process?I really appreciate any advice. I was trying to avoid having to have Profiler running all day every day and just waiting for the server to crash again. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-07-17 : 12:22:20
|
| Any error in Event log? |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-07-17 : 14:41:23
|
| Are you familar with sp_who or sp_who2? You can run "sp_who2 'active'" to see what is executing in SQL. |
|
|
|
argand
Starting Member
5 Posts |
Posted - 2008-07-17 : 14:53:54
|
| I appreciate the feedback, here are the answers to your questions.Unfortunately I cannot run sp_who because I cannot make any database connections to the server, I can only use Remote Desktop. Even from the server, I can't use Management Studio.There are no errors in the event log, but there is a notification:A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 49236, committed (KB): 24453072, memory utilization: 0%%. I have been seeing this for months now but haven't had time to research it. This is a problem, but I think it's unrelated to the CPU spike. We use SQL Diagnostic Manager and when the spike happens, all performance metrics just freeze. Disk usage, CPU, etc., all return nothing. I have not tried running MMC in a long time though and will try to keep that in mind the next time this happens. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-07-17 : 15:19:26
|
quote:
Originally posted by argand
I appreciate the feedback, here are the answers to your questions.Unfortunately I cannot run sp_who because I cannot make any database connections to the server, I can only use Remote Desktop. Even from the server, I can't use Management Studio.There are no errors in the event log, but there is a notification:A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 49236, committed (KB): 24453072, memory utilization: 0%%. I have been seeing this for months now but haven't had time to research it. This is a problem, but I think it's unrelated to the CPU spike. We use SQL Diagnostic Manager and when the spike happens, all performance metrics just freeze. Disk usage, CPU, etc., all return nothing. I have not tried running MMC in a long time though and will try to keep that in mind the next time this happens.
Ok . This is issue with 64-bit server. You can enable lock page in memory but before doing that see if any new hot fixes can solve the issue. That is the last option. |
|
|
|
tbrothers
Yak Posting Veteran
83 Posts |
Posted - 2008-07-17 : 16:51:48
|
| Hi argand:We upgraded from SQL2K Ent to SQL2K5 Ent over the July 4th weekend. On the following Monday (July 7) at 10:30 AM our server did the exact same thing ... We could RDP and see the processor at 90% utilization but could not make any other connections (SSMS, Profiler, etc.). Found an article at MS about a known issue with SQL2K5 on W2K3 whereas if the server sees to many connections to a single port it views it as a syn attack. There's a registry entry to fix. The link to MS is below.We applied this fix and our server ran fine until the following Tuesday (8 days later) and it did it again.We run W2K3 Ent 32bit on an HP DL585 Quad dual-core, 16GB RAM and a HP 6000 SAN. We were only using 4GB of RAM so I made the necessary changes and are now using 12GB. It's only been 2-1/2 days so I can't say it's fixed.Maybe the reg hack will work for you. If our server has another tantrum then I will call MS for support. I'll let you know if I find out anything else.BTW - Our server had run perfect for 2 previous years ... Never had a single issue until we upgraded to SQL2K5.http://msdn.microsoft.com/en-us/library/ms189083.aspxThanks,Terry |
|
|
|
argand
Starting Member
5 Posts |
Posted - 2008-07-18 : 06:44:58
|
| sodeep: I did forget to mention that we were using 64-bit, I'm sorry. tbrothers: I'll look into that article. I didn't even consider calling MS, I'll have to try that as well.Thank you. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-07-19 : 00:40:12
|
| >> Is there any way to actually see what is running when you can’t connect? Did you try with sqlcmd? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-07-19 : 00:43:03
|
| If you enabled DAC on the server, use it. Since it's designed for this situation. |
|
|
|
argand
Starting Member
5 Posts |
Posted - 2008-07-21 : 09:14:59
|
quote:
Originally posted by rmiao
If you enabled DAC on the server, use it. Since it's designed for this situation.
This is EXACTLY what I was looking for! We only recently migrated to 2005 from 2000 and I hadn't read about this feature yet.Thank you! |
|
|
|
argand
Starting Member
5 Posts |
Posted - 2008-07-23 : 08:47:59
|
| Unfortunately, it happened again this morning. From sometime before 7 AM until when I killed the SQL Server service at a little past 8 AM, it was running 99-100% processor usage and I could not connect using the DAC (it just kept timing out).I'm stumped here. It doesn't happen at the same time each day, sometimes it's early, sometimes it's between 10 AM and 12 PM, rarely it happens in the afternoon. I guess I'll have to start running a trace from morning to night. |
|
|
|
|
CPU 100%, Can't See What's Running