Clustered SQL Server freezes & restarts itself in Enterprise Manager

Hi,
In our large organisation we have SQL Server 2000 running on a 2 node cluster using Windows 2000 Advanced Server Clustering. Each node has 2GB RAM. The server has about 10 databases only one of which is significant size at about 1GB. Our website accesses this database via ASP. The website receives between 10000-40000 hits per hour.
The problem is that periodically (not necessarily during high load periods at all) this single database 'freezes' (not the whole server - the other databases seem fine) and refuses all login attempts from the website. This happened again on the weekend when the database was down for about 6 hours. When we go into Enterprise Manager and click on the instance, there is initially no activity for an unusual amount of time but eventually the instance is selected and the tree expands. When this happens everything is ok again and the website can access the database again.
We have only just enabled failure auditing today, so we don't have any info from there and are at a loss as to whats going on. Has anyone out there had similar experiences or have any ideas?

(We will look at upgrading to 2005 and upgrading the machines too but being a big company that will take a lot of time with red tape etc.)

Many thanks.

Hello,

Hopefully not an offensive question, but is there anything in either the win eventlogs or sql errorlog?

Is the problematic databases' filegroup on a seperate drive (resource) to the other db's?

Cheers

Rob

|||Hi Rob,Thanks for the reply. No there was nothing suspicious in the SQL errorlog and nothing strange in the Windows event log either. All of the databases use the same shared storage.Cheers, Nathan.|||

Hi Nathan,

I would have leant towards either blocking or an inordinate amount of locks, however the latter would affect all databases on the instance. Is there a chance the outage is being caused by the db (auto)growing (either log or data)? I think this would only be presented if it were a "large" db...

In order to narrow it down a bit, I'd be create and run a perfmon log, with all the usual suspects: memory,page reads/faults, cpu, tps, log growth, cache hit ratio, user connections etc. Run the log until you encounter the db freeze, and hopefully the perfmon log will at least give you some clue.

Are you sure it's just the one db that freezes, or have you seen if affect others?

Cheers,

Rob