We reboot the SQL Server and as soon as SQL receives a command of any sort, the SCSI RAID 5 hard drives light up and start trashing. Therefore every query and even Windows response time is painfully slow or fails to respond. When we look at the Activity Monitor and sp_who2 there is a process running a Select Into from Microsoft SQL Server Management Studio using the sa account from the last database name in our system. So we can't kill the process as it says "Cannot use KILL to kill your own process. Microsoft SQL Server, Error: 6104)".
The last Transact-SQL command from this process is:
create table #tmpDBCCinputbuffer ((Event Type) nvarchar(512),
[Parameters] int, [Event Info] nvarchar(512))
insert into #tmpDBCCinputbuffer exec ('DBCC INPUTBUFFER(56)')
select [Event Info] from #tmpDBCCinputbuffer
The only other issue I should mention is that we had to uninstall and reinstall SQL Server yesterday as SP2 only half installed. The SP2 install log indicated that SQL SP1 wasn't in a good state as we previously (3 months ago) performed an upgrade from Windows 2000 Server to Windows 2003 R2 Server.
Our other 3 Windows 2003 R2 Servers which were a clean install of both Windows and SQL are running fine.
I've been insisting that we do a new clean Windows and SQL install, but the other team members insist that it's been fine until we installed SQL Server SP2.
So can anyone provide and help or evidence to say who is right either way?
Thanks.
It could be a hardware or a controller issue. Please check the system event log or get the hardware guys to confirm that all is fine wiht hardware.
By the way processes that are running are they clocking a lot of IO under SP_WHO2?
Jag
|||It is the same hardware and we've checked the system event log.The process is Push Merge Replication and there was basically no hard drive I/O. Especially shown in PerfMon.exe
We've fixed the problem now by moving to a Dynamic Pull Merge Subscription Replication model. We've thrown more Drives at the RAID array and reindex the system table MSMerge_Contents and a few other high use tables often and it seems to production ready now.
