Sybase Prod commands..

Check Blocking session..

select spid," login " = convert(varchar(12),suser_name(suid)),cmd,
" database " = convert(varchar(12),db_name (dbid)),cpu,physical_io,memusage,blocked,hostname,time_blocked from
master..sysprocesses where blocked>0

select spid,uid,blocked,cmd,dbid,'dbname'=db_name(dbid) from master..sysprocesses where blocked > 0

check db is in logsuspend or not

select spid,uid,blocked,cmd,dbid,'dbname'=db_name(dbid) from master..sysprocesses where cmd = 'LOG SUSPEND'

Check opentrans on db..

select spid,loggedindatetime from master..sysprocesses where spid
in (select spid from master..syslogshold)

select * from master..syslogshold
sp_whodo "spid"
sp_whodo "n_cycle"

Check DB startup time

select "dbname" = convert(varchar(12),db_name(dbid)),crdate,"currentdate" =getdate()
from sysdatabases where dbid=2


Check Number of user connections on db

select suser_name(suid),count(*) from sysprocesses group by suid

Check what all sessions on db

select spid,blocked,cmd,memusage,physical_io from sysprocesses order by memusage desc
select spid,blocked,cmd,memusage,physical_io from sysprocesses order by physical_io desc
select spid,suser_name(suid),cpu,cmd,status from sysprocesses where status
like '%run%'
select spid,status,hostname,program_name,hostprocess,cmd, cpu,physical_io from sysprocesses where status like 'runn%'

Check ServerDetails

select * from sysservers

select * from syslisteners -- check port details

To get sqlplan

dbcc traceon(3604)
dbcc sqltext(379)
sp_showplan 379,null,null,null

Checkin User process details

select spid,cmd,db_name(dbid),ipaddr,uid from sysprocesses where suser_name(suid) ='drdapp'

select count(suser_name(suid)),suser_name(suid) login from sysprocesses group by suser_name(suid) order by count(suser_name(suid)) DESC

select spid,cmd,status,suser_name(suid),loggedindatetime from sysprocesses where suser_name(suid)='drdapp' and loggedindatetime <'Oct 29 2009'

select spid,suser_name(suid) from sysprocesses where suser_name(suid)='drdapp' and loggedindatetime <'Oct 28 2009'

how to kill user sessions

kill spid

select distinct 'kill ' + convert(char,blocked) from master..sysprocesses where blocked > 0

select 'kill ', spid from sysprocesses where suser_name(suid)='drdapp' and loggedindatetime <'Oct 28 2009'

select 'kill ', spid from sysprocesses where suser_name(suid)='unxadmin';

kill 325 with statusonly

Useful Commands

sp_monitor
sp_monitorconfig "all" -- all config parameter values
sp_monitorconfig 'locks'
sp_monitorconfig 'procedure cache size

sp_configure "user connections"
sp_configure 'number of user connections',600 - to increase no.of connectins
sp_configure "number of locks"
sp_configure "max_memory"
sp_configure 'auditing'

select @@errorlog
select @@boottime
select @@version
select @@servername
select getdate()


check Parameters on db

select paramname,paramvalue from d_dbdba002..task_params
select paramname,paramvalue from d_dbdba002..task_params where paramname='DUMP_GENERATIONS'
select * from d_dbdba002..task_params where paramname like '%DUMP%'

All jobs/Tasks listed on db
select * from d_dbdba002..tasks

--job configured or not
nc_task 'job_name'

To get backup details
select * from d_dbdba002..backup_files where backup_time like '%Nov%';

checking database status on sysbase cluster db

/opt/VRTSvcs/bin/hastatus -sum | grep 'dbName'

--Login details:

sp_displaylogin 'Username'
use db
sp_helprotect 'Username'
sp_helprotect Bond -- object name
sp_helpgroup 'groupname'
sp_helpuser 'Username'

how to password reset

sp_password "mypwd",newpwd,username


how to dump database or chk bkpserver working or not

dump database master to "/tmp/sud.d"

chk Stable queue Usage

admin disk_space

DB and segment details

select * from sysobjects where name='out'
sp_list U
sp_spaceused out -- 'out' is an object name
sp_help out

sp_helpdb
sp_helpdb "DBNAME"
sp_helpsegment "default"
"DBNAME"..sp_helpsegment "default"

- -To check wheteher table contains any indexes or not :

"DBNAME"..sp_helpindex "OBJECT_NAME"

---StackTrace:
select * from syslocks where spid not in (select spid from sysprocesses)

-- DBSPACE VIOLATION:

login to server isql -Uusername -Sserver_name -w300

use dbname

sp_dbspace

sp_helpdb "DBNAME"

"DBNAME"..sp_helpsegment logsegment

sp_dev_alloc

If client wants to alter or violation reaches -- do alter db on any data device

alter database on = 2000

- -THRESHOLD LOG DUMP ERROR:

use db
sp_helpsegment logsegment

select * from master..syslogshold

select db_id("dbnAME") -- to get the db_id of specifed database


dbcc checkverify(DB_NAME)

use DB_NAME

select object_name(11111)

dbcc indexalloc(tablename,indexnumber)

RepAgent chks

sp_help_rep_agent
admin who_is_down

DBCC CHECKSTORAGE ALERTS..

if it is dba database pls run below..if it is user db, Hv to investigage
dbcc checkstorage(d_dbdba002)
dbcc checkverify(d_dbdba002)