[SYBASE] 砍掉無法使用的Database

狀況:

Database所屬的Database Device損毀或不存在. 導致Database無法使用.
因為Database無法使用, 所以無法以DROP DATABASE命令將資料庫刪除.
當開啟Sybase Central時可能出現下列訊息:
Database ‘demo’ cannot be opened.  An earlier attempt at recovery marked it ‘suspect’. Check the SQL Server errorlog for information as the case.

Device <<>>… has not been correctly activated at startup time. Please contact a user with System Administrator (SA) role.

因為Database Device被Database佔用, 所以 Database Device也無法刪除.
當要刪除可能出現下列訊息:
Device is being used by a database. You can’t drop it.

處理:

藉由刪除系統檔中的相關設定而達到刪除資料庫的目的.

sp_configure ‘allow update’, 1
go
begin tran
go
delete from sysusages where dbid = <dbid of database to be dropped>
go
— check to make sure that only the correct rows have been deleted!!
— if OK:
commit
go
begin tran
go
delete from sysdatabases where dbid= <the dbid…>
go
— check to make sure that only the one you want to remove is gone
— if ok
commit
go
sp_configure ‘allow update’, 0
go

若要查dbid的話可以執行 select name, dbid from master..sysdatabases 查詢

參考資料:

http://www.dbforums.com/sybase/1216512-how-force-delete-database-sybase.html

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

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

Twitter picture

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

Facebook照片

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

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s


%d 位部落客按了讚: