Sybase Blog -Anything About Sybase ASE,REP,IQ.

Monday, March 29, 2010

Dataserver Health Check Script

Hi Folks,

This is going to be first post of month march, was busy due to relocation.  Now new topic health check...

In our production support environment we need do the health check on our data server on time to time basis, such as after the any long activity, in the production monitoring, after the server restart, and there are lot of many more cases.

I have tried to compile the Hc script v 1.0 as below , I am still trying to improve it , your suggestions and thoughts most welcome...

/***************************************************************
## Dataserver Health Check - Version 1.0
## Intial Drafted by - sybanva for SYBASETEAM.COM
## Date - 19th Feb 2010
****************************************************************/

print "#########################################################"
set nocount on
go
print "--------------------------------------------------------"
print " DataServer Checks"
print "--------------------------------------------------------"
go
select @@version "DATASERVER VERSION"
go
select @@servername "Instance Name", @@maxpagesize " Page Size of Instance"
go
select getdate() "Current Date", @@boottime "Instance Startup date"
go
print "--------------------------------------------------------"
print " Active Trace Flags"
print "--------------------------------------------------------"
dbcc traceon(3604)
go
dbcc traceflags
go
print "--------------------------------------------------------"
print " Process Status"
print "--------------------------------------------------------"
select spid SPID,cmd COMMAND,cpu "CPU Usage",physical_io "PHYSICAL IO",ipaddr "IP ADDRESS",loggedindatetime "LOGGED IN DATE",hostprocess "Host PROCESS ID" from sysprocesses where physical_io>0 or cpu >0
go
print "--------------------------------------------------------"
print " Long Running Process Status"
print "--------------------------------------------------------"
If exists (select count(1) from syslogshold where spid <> 0)
print "<<<<<<<< NO LONG RUNNING TRANS AT THIS MOMENT >>>>>>>>>"
else
select dbid "Database ID",spid SPID,starttime "START TIME",name NAME from syslogshold where spid <> 0
go
print "--------------------------------------------------------"
print " Blocking Process Status"
print "--------------------------------------------------------"
go
If not exists (select count(1) from sysprocesses where blocked > 0)
select spid SPID,cmd COMMAND,cpu "CPU Usage",physical_io "PHYSICAL IO",ipaddr "IP ADDRESS",loggedindatetime "LOGGED IN DATE",hostprocess "Host PROCESS ID" from sysprocesses where blocked > 0
else
print "<<<<<<<< NO BLOCKING PROCCESS AT THIS MOMENT >>>>>>>>>"
go
print "--------------------------------------------------------"
print " Checks for Zombie SPIDs"
print "--------------------------------------------------------"
go
if exists(select spid from master..syslogshold slh where slh.spid <> 0 and slh.spid not in(select spid from master..sysprocesses))
select spid from master..syslogshold slh where slh.spid <> 0 and slh.spid not in(select spid from master..sysprocesses)
else
print "<<<<<<<< NO ZOMBIE SPIDS AT THIS MOMENT >>>>>>>>>"
go
print "--------------------------------------------------------"
print " Check for Engines"
print "--------------------------------------------------------"
go
sp_configure 'number of engines'
go
select engine "Engine No",osprocid "OC Proc ID",status "STATUS",starttime "START TIME" from sysengines
go
print "--------------------------------------------------------"
print " Checks for Database Status"
print "--------------------------------------------------------"
go
select name "DB NAME",dbid "DBID",status "STATUS",crdate "CRATION DATE",dumptrdate "LAST DUMP TRAN DATE" from sysdatabases
go
print "--------------------------------------------------------"
print " Checks for Suspect/OFFLINE Database Status"
print "--------------------------------------------------------"
go
if exists(select name from sysdatabases where status in (-32768,64,256,32))
select name, status from sysdatabases where status in (-32768,64,256,32)
else
print "<<<<<<<< ALL DBS STATUS IS NORMAL >>>>>>>>>"
go
print "--------------------------------------------------------"
print " Checks for Servers"
print "--------------------------------------------------------"
select srvname "SERVER NAME",srvnetname "SERVER N/W NAME" from sysservers
go
print "--------------------------------------------------------"
print " Checks for Backup Server"
print "--------------------------------------------------------"
go
SYB_BACKUP...sp_who
go
print "--------------------------------------------------------"
print " Checks for Monitor config"
print "--------------------------------------------------------"
go
sp_monitorconfig "max memory"
go
sp_monitorconfig "number of locks"
go
sp_monitorconfig "number of open indexes"
go
sp_monitorconfig "number of open objects"
go
sp_monitorconfig "number of user connection"
go
sp_monitorconfig "procedure cache size"
go
Same Thread @ http://www.sybaseteam.com/dataserver-health-checkout-qa-script-t-684-3.html