October 3, 2007 at 8:58 am
We are having an ever increasing problem with our SQL Server 2005 locking up and not allowing any connections to it. It was happening at random times, but it now appears to be happening daily. What happens is if I go into the task manager and look at CPU usage, SQL is locked on 25 percent. I cannot connect to sql thru management studio and therefore must stop and restart the services just to get it running again. The system does not allow any connections to SQL at that time.
We are running SQL 2005 sp1 on Windows Server 2003 R2 sp2
Thank you in advance.
Todd
October 3, 2007 at 9:19 am
Any strange messages in the sql error log? any strange messages in the windows event log?
What's the specs of the server? (memory, cpus)
Have you tried connecting via the DAC when it hangs?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 3, 2007 at 11:58 am
The server is Windows Server 2003 R2 SP2.
The CPU is a Xeon 2.40 GHz with 1.00 GB Ram.
As for the DAC, I have not tried that yet.
October 3, 2007 at 12:35 pm
First places to look iare: SQL Error Log and then on Windows Event Log.
You should definitely consider upgrading to SP2 and hotfix update rollup 3.
* Noel
October 3, 2007 at 1:48 pm
you are on Xeon processor - so i am assuming 64 bit SQL Server installation as well
- if you have not done so far, implement "Lock pages in memory" for SQL Server startup account.
918483How to reduce paging of buffer pool memory in the 64-bit version of SQL Server 2005
http://support.microsoft.com/default.aspx?scid=kb;EN-US;918483
October 3, 2007 at 8:55 pm
Also, can you connect with the dedicated Admin connection from the local console?
October 4, 2007 at 7:53 am
I got an error Direct Admin console not supported when I tried to attach with DAC.
October 4, 2007 at 8:02 am
From management studio? I believe that error comes from the object explorer.
Try SQLCMD (the command line tool) or using management studio, don't connect the object explorer, create a new query and connect from that usign the DAC.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 4, 2007 at 8:35 am
Be sure you have enabled the DAC from the SQL Server configuration and do it from the local console.
October 5, 2007 at 8:25 am
1) lock pages in memory has no effect unless using sql enterprise ed. Also it should absolutely be avoided on a box with only 1GB of ram.
2) Speaking of 1GB, that is an EXCEEDINGLY SMALL amount of ram for a production database server - I don't care if you only have 1 10MB database on it.
3) How much data is on the server and how many connections are there?
4) You need to monitor the box from the moment it is restarted until it hangs to determine what is happening. My guess is blocking is causing threads to queue up and you run out of worker threads.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 5, 2007 at 8:37 am
Not sure I agree 1GB is too little RAM. I've run small servers on it fine. However more is definitely better.
I do think you want to do some comprehensive analysis of what's happening here. Especially if you have any idea of when this might happen. I'd also call PSS once you get some good data. They can really help diagnose what's going on.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply