August 28, 2008 at 9:33 pm
Here's what happened: A firmware installation prompted an unexpected (but graceful) shutdown of SQL Server 2000. Ten minutes later it started back up ... but SQL Agent was left unstarted.
That may or may not have anything to do with this, but it happened around 7:20-7:30 AM on the day the rest of this happened.
Late morning, complaints that the app was performing poorly. I looked at the database. It seemed lively and responsive. CPU was a bit high but not pegged or locked - moving up and down.
Some heavy user actions of archiving items would have created more than normal transactions. But by early afternoon the CPU is sitting at 75% and hits 98% but stays mostly around 75 ... then drops back down to 0 and to 25 (which was one of the four CPUs pegged at 100%).
Throughout the day the database seems to be doing its normal business with no problem. Backups are fine, etc.
A little after 4 PM, I note that SQL Agent is off. I manually start the SQL Agent service. Within minutes, the database gracefully shuts off and restarts. The log says it is responding to a request from the Service Manager.
After it comes back up, performance via the app is better than ever and all is well.
Through all of this - no errors noted in SQL log or Windows logs; and no sign of any intrusion etc. SQL logs in particular are normal as could be ...
What happened?
August 29, 2008 at 1:49 am
Clustered SQL installation?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 29, 2008 at 6:45 am
SQL 2000 Standard, single box, not clustered, default (and only) instance
August 29, 2008 at 8:07 am
Check the windows event log. There should be a record in the application log of SQL shutting down. There sshould also be the login name that requested the shutdown included in the event.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 29, 2008 at 12:16 pm
Is there any dump file created under log folder in sql server installation directory? Can you schedule a job to just track the sp_who2 output so that you can see which user was consuming the most cpu or memory due to which restart is happening in case you are not getting any information in logs(as suggested by Gail).
BEGIN
SET NOCOUNT ON
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'WHORESULTS') AND xtype IN (N'U'))
CREATE TABLE WHORESULTS (
[WHORESULTSID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
[SPID] CHAR (5) NULL,
[INSERTDT] DATETIME NULL DEFAULT(GETDATE()),
[STATUS] VARCHAR(30) NULL,
[LOGIN] VARCHAR(30) NULL,
[HOSTNAME] VARCHAR(30) NULL,
[BLKBY] VARCHAR(30) NULL,
[DBNAME] VARCHAR(30) NULL,
[COMMAND] VARCHAR(30) NULL,
[CPUTIME] INT NULL,
[DISKIO] INT NULL,
[LASTBATCH] VARCHAR(30) NULL,
[PROGRAMNAME] VARCHAR(200) NULL,
[SPIDINT] INT NULL)
--table exists, insert some data
INSERT INTO WHORESULTS(SPID,Status,Login,HostName,BlkBy,DBName,Command,CPUTime,DiskIO,LastBatch,ProgramName,SPIDINT)
EXEC sp_who2
--don't care about spids less than 50 anyway:
DELETE FROM WHORESULTS WHERE SPIDINT < 50
END
You can schedule the above script to capture the user activity just before the restart happens so as to narrow down our approach towards the restart victim diagnosis.........
MJ
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply