database

mySQL: size of the database

Wednesday, January 23rd, 2008

SELECT table_schema “Database”,
 sum( data_length + index_length ) / 1024 / 1024 “Size (MB)”,
 sum( data_free )/ 1024 / 1024 “Free (MB)”
FROM information_schema.TABLES
GROUP BY table_schema ;

This will work in mySQL 5.0.2 and newer. Use SHOW TABLE STATUS command for other versions.

MS SQL & Sybase: recreate master database

Thursday, January 10th, 2008

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:

Start server in single-user mode (sqlserver.exe -m)
setup.exe /qn INSTANCENAME=instance_name REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=new_SA_Password
Restart server in normal mode

Creation and using of Sybase DBCCDB database

Tuesday, March 28th, 2006

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 [...]

Keep on coding