Hi folks,

Sorry for late reply, was busy in some work.

Please find the attached initial draft of Health check script Version 1.0 , Also posting its output as below:

I am still trying to improve it If you have any more inputs and thoughts , please share with us.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#########################################################
--------------------------------------------------------
DataServer Checks
--------------------------------------------------------
DATASERVER VERSION
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Adaptive Server Enterprise/15.5/EBF 17218 SMP/P/NT (IX86)/Windows 2003/ase155/2391/32-bit/OPT/Mon Nov 09 14:18:14 2009
Instance Name Page Size of Instance
------------------------------ ----------------------
PCNAME 2048
Current Date Instance Startup date
-------------------------- --------------------------
Feb 19 2010 9:35AM Feb 19 2010 8:24AM
--------------------------------------------------------
Active Trace Flags
--------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
Active traceflags: 3604

DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
--------------------------------------------------------
Process Status
--------------------------------------------------------
SPID COMMAND CPU Usage PHYSICAL IO IP ADDRESS LOGGED IN DATE Host PROCESS ID
------ ------------------------------ ----------- ----------- ---------------------------------------------------------------- -------------------------- ------------------------------
5 CHECKPOINT SLEEP 0 170 NULL Feb 19 2010 8:24AM NULL
6 HK WASH 0 688 NULL Feb 19 2010 8:24AM NULL
14 LICENSE HEARTBEAT 0 1 NULL Feb 19 2010 8:24AM NULL
--------------------------------------------------------
Long Running Process Status
--------------------------------------------------------
<<<<<<<< NO LONG RUNNING TRANS AT THIS MOMENT >>>>>>>>>
--------------------------------------------------------
Blocking Process Status
--------------------------------------------------------
<<<<<<<< NO BLOCKING PROCCESS AT THIS MOMENT >>>>>>>>>
--------------------------------------------------------
Checks for Zombie SPIDs
--------------------------------------------------------
<<<<<<<< NO ZOMBIE SPIDS AT THIS MOMENT >>>>>>>>>
--------------------------------------------------------
Check for Engines
--------------------------------------------------------
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------ -------------------- ----------- -------------------- -------------------- -------------------- --------------------
number of engines at startup 1 0 1 1 number static
(return status = 0)
Engine No OC Proc ID STATUS START TIME
--------- ----------- ------------ --------------------------
0 5112 online Feb 19 2010 8:24AM
--------------------------------------------------------
Checks for Database Status
--------------------------------------------------------
DB NAME DBID STATUS CRATION DATE LAST DUMP TRAN DATE
------------------------------ ------ ------ -------------------------- --------------------------
master 1 0 Feb 17 2010 1:29AM Feb 17 2010 1:37AM
model 3 0 Feb 17 2010 1:29AM Feb 17 2010 1:34AM
tempdb 2 12 Feb 19 2010 8:24AM Feb 19 2010 9:34AM
sybsystemdb 31513 8 Feb 17 2010 1:29AM Feb 17 2010 1:41AM
sybsystemprocs 31514 8 Feb 17 2010 1:32AM Feb 17 2010 1:40AM
--------------------------------------------------------
Checks for Suspect/OFFLINE Database Status
--------------------------------------------------------
<<<<<<<< ALL DBS STATUS IS NORMAL >>>>>>>>>
--------------------------------------------------------
Checks for Servers
--------------------------------------------------------
SERVER NAME SERVER N/W NAME
------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYB_BACKUP PCNAME_BS
SYB_EJB EJBServer
PCNAME PCNAME
PCNAME_XP PCNAME_XP
--------------------------------------------------------
Checks for Backup Server
--------------------------------------------------------
spid status loginame hostname blk cmd
----------- ---------- ------------------------------ ------------ --- ----------------
0 runnable NULL NULL 0 NULL
2 runnable NULL NULL 0 CONNECT HANDLER
3 runnable NULL NULL 0 CONNECT HANDLER
4 runnable NULL NULL 0 CONNECT HANDLER
5 runnable NULL NULL 0 CONNECT HANDLER
6 sleeping NULL NULL 0 DEFERRED HANDLER
18 runnable NULL PCNAME 0 SITE HANDLER
20 running sa PCNAME 0 NULL
--------------------------------------------------------
Checks for Monitor config
--------------------------------------------------------
Usage information at date and time: Feb 19 2010 9:35AM.

Name Num_free Num_active Pct_act Max_Used Reuse_cnt Instance_Name
------------------------- ----------- ----------- ------- ----------- ----------- ------------------------------
max memory 24933 31387 55.73 31387 0 NULL
(return status = 0)
Usage information at date and time: Feb 19 2010 9:35AM.

Name Num_free Num_active Pct_act Max_Used Reuse_cnt Instance_Name
------------------------- ----------- ----------- ------- ----------- ----------- ------------------------------
number of locks 9912 88 0.88 114 0 NULL
(return status = 0)
Usage information at date and time: Feb 19 2010 9:35AM.

Name Num_free Num_active Pct_act Max_Used Reuse_cnt Instance_Name
------------------------- ----------- ----------- ------- ----------- ----------- ------------------------------
number of open indexes 495 5 1.00 5 0 NULL
(return status = 0)
Usage information at date and time: Feb 19 2010 9:35AM.

Name Num_free Num_active Pct_act Max_Used Reuse_cnt Instance_Name
------------------------- ----------- ----------- ------- ----------- ----------- ------------------------------
number of open objects 491 9 1.80 9 0 NULL
(return status = 0)
Usage information at date and time: Feb 19 2010 9:35AM.

Name Num_free Num_active Pct_act Max_Used Reuse_cnt Instance_Name
------------------------- ----------- ----------- ------- ----------- ----------- ------------------------------
number of user connection 24 1 4.00 1 0 NULL
(return status = 0)
Usage information at date and time: Feb 19 2010 9:35AM.

Name Num_free Num_active Pct_act Max_Used Reuse_cnt Instance_Name
------------------------- ----------- ----------- ------- ----------- ----------- ------------------------------
procedure cache size 6167 833 11.90 993 0 NULL
(return status = 0)

Excellent! Thank You !

Few Suggestion.This can be done in next version ! No hurry !

1. Can these checks be made configurable - (E.g I need only few checks of these to be done on the server...can i disbale some?)
2. Can this be changed into a shell script ( has its own benefits)
3. Can email alerts facility on detecting some problem (this also should be configurable - what is probematic and what is not) also be included ?

Cheers,
TechSupport,SybaseTeam.Com

Can this script checks all dataserver and backup server logs and tell the backup status.

Any error log entry for the day

This may need remote login.

I may have to provide input server and path of the log files to achive this.

Hi Sujit,

Yes We can include the checks for the backup server and dataserver error log for any type of error.
I will do that in next version of script . Thanks for your suggestion.

For your second point, if you want to check the all server logs from one central server, in that case we will require the remote login for that server and for DB level checks we will need interface entry in the central server.


If you have any more suggestions, please let us know. We will try to include in our next release.

Thanks.

Hi,

Thats good news that you are working on it .

Please let us know once its done.

Thanks for the help.

====

(02-25-2010 12:11 AM)sybanva Wrote:  Hi Sujit,

Yes We can include the checks for the backup server and dataserver error log for any type of error.
I will do that in next version of script . Thanks for your suggestion.

For your second point, if you want to check the all server logs from one central server, in that case we will require the remote login for that server and for DB level checks we will need interface entry in the central server.


If you have any more suggestions, please let us know. We will try to include in our next release.

Thanks.

Added checks for zombie lock spids and updated the script for the same.


Thnx.