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:

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:

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

1. Allow updatesNote: 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)
3. Drop database using DBCC:

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

Scheduling failed on Sybase ASE

Error in DB13 while running SAP on Sybase database – “Scheduling Failed”.

These errors are intermittent and not reproducible.

Below I outline some steps to troubleshoot this issue:

Check for errors in log files (<SID>, <SID>_BS, <SID>_JSAGENT> under installation directory:

Ex: “D\:sybase\<SID>\ASE-15_0\install.

If you cannot read these files (Because it is in use), copy it to another directory .

Try to dump the transaction log using below command:

dump transaction <SID> to “<Dump Directory>”.

If you are able to successfully dump or truncate the log file, then try to delete the further schedules and try to reschedule the backup again from DB13.

If you are not able to dump or truncate the transaction log , database might be in a standstill (Root cause explained at the end).

In that case, stop SAP and restart the DB services (SQL Server and Backup Server).

In our case, we found following errors in the <SID> log file when the issue happened.

Error: 8213, Severity: 20, State: 3
server Failed to acquire address lock on object DDLOG

Note: There were no Dump DATABASE or Create INDEX were running on the database during that time.

This is caused by a bug in the ASE database as explained in the SAP Note 2337582.

Database had to be updated to one of the release mentioned in the note to resolve the issue.

Important Notes:

2178977 – Job scheduling fails on DBA Planning Calendar – SAP ASE for Business Suite

 

2477608 – DBA Planning Calendar shows duplicate entries with status scheduling failed – SAP ASE for Business Suite

 

2688308 – How to troubleshoot Job Scheduler issues in Standalone ASE

 

2249796 – Unable to create SAP schedules from DBA Cockpit – SAP ASE for Business Suite

 

2692998 – Unable to schedule database job due to ASE Error SQL4002

 

2099027 – Database Extractor Setup Failed for SAP ASE for Business Suite

 

2494044 – Unable to setup job scheduler Solution manager 7.2