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.
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).
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?
(ignore the tone and grammatical errors, its been a very long weekend)