Sybase:
before 12.5: buildmaster (bldmatr for NT)
buildmaster -d master_device -s size_in_2k_pages
12.5 and later: dataserver with some options (sqlsvr for NT)
dataserver -d master_device -b size -forcebuild -z page_size -Z size_of_master_db
MS SQL:
before MS 2005:
rebuildm utility (GUI)
MS SQL 2005:
Warning! It depends on the statistics and could be not accurate!
Sybase
select user_name( obj.uid), obj.name , rowcnt(doampg)
from sysindexes idx, sysobjects obj
where idx.indid < 2 and idx.id = obj.id and obj.type = 'U'
order by 1,2
go
select object_name( idx.id ), rowcnt(doampg)
from sysindexes idx
where idx.indid < 2
and object_name( idx.id ) in ('object1','object2')
go
Oracle
select OWNER, TABLE_NAME, NUM_ROWS
from dba_tables
where TABLE_NAME = upper( '&what_tbl.' );
select OWNER, TABLE_NAME, NUM_ROWS
from dba_tables
order by 1,2;
Planning:
sp_plan_dbccdb '{dbname}'
Creation:
create database dbccdb on ... log on ...
isql -U sa -i $SYBASE/$SYBASE_ASE/scripts/installdbccdb
DBCCDB Configuration:
sp_configure "number of worker processes", {value_from_sp_plan}
-- create named cache for dbccdb
sp_cacheconfig "dbccdb_cache", "{cache_size}M"
-- Size is at least 40 * 16K * (number of working processes)
sp_poolconfig "dbccdb_cache", "{pool_size}M", "16K"
Working space configuration:
sp_dbcc_createws dbccdb, "default", scan_{dbname}, scan, '{size_from_sp_plan}'
sp_dbcc_createws dbccdb, "default", text_{dbname}, text, '{size_from_sp_plan}'
sp_dbcc_updateconfig {dbname}, 'max worker processes', '{value_from_sp_plan}'
sp_dbcc_updateconfig {dbname}, 'dbcc named cache','dbccdb_cache', '{size}'
sp_dbcc_updateconfig {dbname}, 'scan workspace','scan_{dbname}'
sp_dbcc_updateconfig {dbname}, 'text workspace','text_{dbname}'
dbcc checking:
dbcc checkstorage ({dbname})
dbcc checkverify ({dbname})
dbcc report:
sp_dbcc_summaryreport
sp_dbcc_configreport
sp_dbcc_statisticsreport
sp_dbcc_faultreport ['short'|'long']
-- all above
sp_dbcc_fullreport
Fixing the errors
Warning: just some tips here. See Sybase documentation for the complete description of the repair action
100035, 100025, 100021 - dbcc checktable ( %s , "fix_spacebits")
forwarded rows errors - reorg compact %s with resume , time = '%d'
Others:
Index code == 0 - dbcc tablealloc ( %s , "full" , "fix")
Index code != 0 - dbcc indexalloc ( %s , %s , "full" , "fix")
System tables require special processing - see Sybase documentation for the details
select low/16777216 "vdevno", name, phyname
from master..sysdevices
where status&=2
order by 1
Warning! For vdevno>127 it could generate incorrect results.
Sometimes it’s necessary to convert Sybase timestamp to something more readable
Warning! Timestamp is not date type!
declare @p timestamp
declare @s varchar(16)
declare @t1, @t2 varbinary(4)
//--- from char to timestamp
@t1 = hextoint(substring(lower(@s),1,8))
@t2 = hextoint(substring(lower(@s),9,8))
@p = @t1 + @t2
//--- from timestamp to char
@s=(lower(convert(varchar(8),intohex(substring(p,1,4))))+ lower(convert(varchar(8),intohex(substring(p,5,4))))
Only the short info about Sybase database devices.
Parameters:
min_space – minimum of required space (i.e. 40 will display the devices with >=40M free space)
the_name – the template for the device name (i.e. %mast% will display only master device)
Warning! It was not tested for all possible configurations, so the result could be incorrect on some OS.
create procedure sp_freedevice
@min_space int = null,
@the_name varchar(40) = null
as
begin
declare @numpgsmb integer /* Number of 'virtual' Pages per Megabytes */
select @numpgsmb = (1048576. / @@pagesize)
select "phyname"=convert(varchar(40), d.phyname),
"name"=convert(varchar(28),d.name),
"d_size"=convert(varchar(6),(1 + d.high - d.low) / @numpgsmb),
"d_used"=convert(varchar(6), sum(u.size / @numpgsmb)) ,
"d_free"=((1 + d.high - d.low) / @numpgsmb) - sum(u.size / @numpgsmb),
vdevno=d.low/power(2,24) & 255
into #free_device_all_tbl
from master..sysusages u, master..sysdevices d
where u.vstart between d.low and d.high
and d.status & 2 = 2
group by d.name
select
vdevno=low/power(2,24) & 255,
"Physical Name"=convert(varchar(40), d.phyname),
"Device Name"=convert(varchar(28),d.name),
"Size"=convert(varchar(6),(1 + d.high - d.low) / @numpgsmb),
"Used"=convert(varchar(6),0),
"Free"=convert(varchar(6),(1 + d.high - d.low) / @numpgsmb)
from master..sysdevices d
where d.name not in (select tmp.name from #free_device_all_tbl tmp)
and d.status & 2 = 2
and ( @min_space is null or ((1 + d.high - d.low) / @numpgsmb) >= @min_space )
and ( @the_name is null or d.phyname like @the_name or d.name like @the_name)
union
select vdevno , phyname , name , d_size , d_used , convert(varchar(6), d_free)
from #free_device_all_tbl
where ( @min_space is null or d_free >= @min_space )
and ( @the_name is null or phyname like @the_name or name like @the_name)
order by 1
end
go
The procedure displays only the necessary fields from sysprocesses:
create procedure sp_who_all as
select PR.spid ,
PR.fid ,
substring(suser_name(PR.suid),1,10) Login_name,
substring(PR.program_name,1,10) Program ,
PR.status ,
cmd ,
substring(db_name(dbid),1,19) db_name,
substring(tran_name,1,22) tran_name,
blocked,
physical_io
from master..sysprocesses PR
go
#!/usr/bin/ksh
echo "Started..."
print -u2 -n "Enter password:"
stty -echo
read PASS
stty echo
print
rm -f databases.lst
isql -U sa -S $DSQUERY -w 999<
$PASS
select 'DBLABEL' "XXX", name from master..sysdatabases
go
exit
EOF
for DB in $(cat databases.lst) ; do
print -u2 "Processing $DB ..."
isql -U sa -S $DSQUERY -w 999 -D $DB <