What Happened?

Question

What Happened?

Michael Gerholdt

SSCommitted

Points: 1907
More actions
August 28, 2008 at 9:33 pm

#71078

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?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Michael Gerholdt SSCommitted Points: 1907 More actions · Answer 2

SQL 2000 Standard, single box, not clustered, default (and only) instance

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 4

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