Hello,
I have had a couple of instances where my production server has
stopped serving. I have not been able to determine any specific
cause. On two occasions I have noticed another admin has Altered, or
installed new Stored Procs about 2 hours prior to the Denial of
Service. The db that the procs were installed in is a very high use
db especially the tables the proc manipultes.
I have tried killing processes that were locking other process to no
avail, attempted to stop and start the SQL Server Service also not
working. Ended up rebooting the server.
Is there anyway I can trouble shoot this now days after the incident?
(I happen to be offsite whenever these problems arise)
Any info would be greatyl appreciated!

Thanks!
Bill

Hi Bill,

Sounds like it's too late to do anything about it now, unfortunately.

You might want to investigate setting up a server-side trace, and
having it run permanently, with some choice events - take a look here:
http://mssqltips.com/tip.asp?tip=1035

Events to monitor could include deadlocks, lock acquisitions that take
a considerable amount of time, long running queries etc. etc.

You can set up a server-side trace script using SQL Server Profiler
2005 (am not sure whether you could do this in 2000), by using File/
Export/Script Trace Definition...

Regards,

Will