November 16, 2008 at 6:27 pm
Gotta serious ghost in the machine here. I upgraded our dev sql server box from a 1 dual core, 2 gb ram, 1 Raid 5 disk volume to 2 Quad core, 8 gb RAM, 2 RAID 10 Disk volume. Needless to say, it screamed on the new box. About 20 developers were very happy. I am using SQL server 2005 SP2 with Cumulative Service pack 9. Still sounds good? Worked fine on other machines.
So we run with it for the day. About noon, it completely freezes up.
Restart, no biggie.
Check the sql server logs, nothing but one statement saying it is taking a while to retrieve data from MDF file. It then says that windows has requested a shutdown, which we had requested. Nothing at all in the windows event log... Still no biggie.
Run for 2 hours, screaming fast. Developers are in love with SQL Server all over again. I am walking tall.
FREEZE...
Check the sql server logs, nothing but one statement saying it is taking a while to retrieve data from A DIFFERENT MDF file. It then says that windows has requested a shutdown, which we had requested. Nothing at all in the windows event log... Still no biggie.
This freeze starts to happen spurodically throughout the day.
We pull memory, nothing.
We did find that if we shut down Sql Server Agent (3 instances), it won't fail; however, the jobs are pretty big with a few SISS packages scheduled throughout the day. NO BIGGIE.
My sysadmin tells me its SQL server. I am convinced that it isn't; however, you never know and I have seem some weird stuff happen over the years that puzzle me.
I decide I am going to completely un-install SQL-SERVER and install it back. I also am going to drop down to Cumulative Pack 7 (figure 2 back is better than 1).
Fingers crossed...
FREEZE...
I am now convinced that our jobs are the ignition, but not the bomb. Sysadmin feels differently.
Anyone experience something like this or at least can point us in the right direction? We moved this instance from an old server and renamed this to the old server name so that App connections need not change; however, I did notice that our sysadmin forgot to change the name (sp_dropserver,sp_addserver) of the old server. Would that cause this Ghost?
Truly,
Bobby Johnson
(ignore the tone and grammatical errors, its been a very long weekend)
November 16, 2008 at 8:48 pm
This is almost always hardware. Freezing like this could be bad hardware, strange drivers, anything like that.
November 17, 2008 at 2:58 am
Robert,
In deed looks very strange.
Does your new SQL Server installation goes well without any problem? Have you created test DB and test?
If new installation is working fine:
As you mention it's having problem in finding MDF file; hence I suggest you to detach and attach the user as well as system DB. Hope should work fine.
Also from other computer start SQL Profiler to fetch any error which might give you more visibility.
Good Luck!!!
FM
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
November 17, 2008 at 6:14 am
I think it is hardware. We are sending it to IBM today.
November 17, 2008 at 6:15 am
I did all that you mentioned above. Profiled, etc... Nothing that says it DB.
November 17, 2008 at 9:12 am
Does the new box freeze without SQL Server installed or running?
My bet is hardware too, but that's what every DBA likes to say 🙂
November 18, 2008 at 2:34 am
just out of interest - you upgraded from 2gb of ram to 8gb - did you set /3GB and /PAE in your boot.ini. if so did you do sp_configure 'awe enabled',1 - finally what did you set as the max mem.
maybe i'm barking up the wrong tree though...
MVDBA
November 18, 2008 at 3:06 am
michael vessey (11/18/2008)
finally what did you set as the max mem.maybe i'm barking up the wrong tree though...
I've seen SQL servers do this before and it has (as others suggested) been hardware. A memory fault which is only "discovered" as SQL starts to eat its way up through the new RAM (to its max setting). When first started it may only need about 2Gb, then as users hammer away it decides another 2Gb would be good and so on. Then it hits the fault and pukes.
Of course if you're running multiple instances and haven't limited the max RAM they can have... 😉
November 18, 2008 at 4:17 am
I fully agreed with FNS.
As I have experience the same thing in my environment. As soon as wintel teams reboot the server works fine and after while again sink. 🙁
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
November 18, 2008 at 6:54 am
It will freeze when the SQL Agent is running (which has a massive amount of SQL server jobs)
November 18, 2008 at 6:57 am
Robert, i had a similar problem once, Take a drastic measure, disable all the jobs and enable them one by one, see which job causes your SQL to freeze, then concentrate on that job to see what actually happens behind the scenes, else use a profiler to get a trace data and figure it out. 🙂
November 18, 2008 at 6:59 am
are all the jobs running concurrently ? if so there is a hard limit on the number of threads that can be allocated to these jobs
details can be found at
http://support.microsoft.com/kb/306457
but in summary the initially configured limits are as follows
activescripting 10
cmdexec 10
distribution 100
log reader 25
merge 100
snapshot 100
tsql 20
MVDBA
November 18, 2008 at 7:04 am
UPDATE... We took the server down and shipped it off to IBM. I will update once the test results come back.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply