shutdown or reboot a database

July 6, 2009

There is an undocumented dbcc command in ASE starting with v12.5.4 ESD#1 and v15.0.2, to manipulate one or many databases on a single dataserver. This is helpful, if for example a database cannot be loaded due to zombie processes holding a lock on the database, resulting in "Database in use"-errors when no-one is actually using the database.
Restarting the database will result in dbt_keep being 0, indicating that the database is not currently being used.

 
1> dbcc help(dbreboot)
2> go
dbreboot (report | reboot | reboot_norecovery | shutdown | shutdown_load | restart | restart_norecovery, dbname1 [, dbname2 ...])
Reboot the specified DATABASES.
1> dbcc dbreboot("shutdown","db_one")
2> go
---------- Shutting Down Database 'db_one' ----------
---------- Operation on Database 'db_one' Completed Successfully ----------
1> USE db_one
2> go
DATABASE db_one WITH dbid 5 IS already shut down.
1> sp_helpdb db_one
2> go
 name   db_size       owner dbid created      STATUS
 ------ ------------- ----- ---- ------------ ----------------------------
 db_one      300.0 MB sa       5 Jun 05, 2009 SELECT INTO/bulkcopy/pllsort
(1 row affected)
 device_fragments               size          usage                created                   free kbytes
 ------------------------------ ------------- -------------------- ------------------------- ----------------
 data00                              200.0 MB DATA only            Jun  5 2009 11:38AM                 199984
 log00                               100.0 MB log only             Jun  5 2009 11:38AM       NOT applicable
DATABASE db_one WITH dbid 5 IS already shut down.

Arguments for dbcc dbreboot are:

  • report - lists databases which are possibly in need of a reboot
  • reboot - shuts down and restarts a database
  • reboot_norecovery - same as reboot, but without recovery
  • shutdown - shuts down the database
  • shutdown_load - shuts down the database and sets it into state "for load", avoiding recovery at dataserver restart. You must load the database after issuing this command or the dataserver will become corrupt.
  • restart - starts a database which was previously shut down
  • restart_norecovery - same as restart, but without recovery
  • dbname1 - the name of the affected database. More than one database can be specified separated by comma
  • Be aware, as this is undocumented, use this command at your own risk and never ever on a production system! Do not contact Sybase Technical Support.

    tags: , , , ,
    posted in ase, dbcc by Carsten

     
    Powered by Wordpress and MySQL. Theme by Shlomi Noach, openark.org