Drop and recreate Sybase ASE Database

At times it might be necessary to drop and recreate a Sybase ASE database. For example when you want to do a system copy from scratch and you need the current database to be cleaned up or when your DB is in inconsistent state and you want to created new DB and restore it.

Following command can be used to drop a database:

use master

go

drop database <dbname>

But if the DB is in inconsistent state you might get below error:

aseerror2

In such case use the below steps to drop the DB and create it again:

Note: DBCC DROPDB will only work if the database is marked as suspect:

First check if the DB is marked as suspect:

susp

If the DB is not marked as suspect, this should be marked explicitly:

  1. Allow updatesallupNote: At this stage you need to restart your database services for the changes to take effect.
  2. Set the status of DB to suspect (320)setstat
  3. Drop database using DBCC:dropdb

4.   Dont forget to disable the updates by setting “Allow Updates” to Zero and restart       the  database.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s