Link to home
Start Free TrialLog in
Avatar of motioneye
motioneyeFlag for Singapore

asked on

drop table not working in sybase

tried many times to drop one of the table in sybase, somehow the table did not dropped. I'm just little bit puzle why this simple command did not work.

what I have tried is:

drop table TableName - waiting for 3 hours but nothing is happens.
checking any constraints in the table by running sp_helpconstraint tableName but results says not found any declarative constraint

what else I can do to drop this table completely ?
SOLUTION
Avatar of alpmoon
alpmoon
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of motioneye

ASKER

I see nothing in sql logs sp_lock also show noting except my query with spid 962 below which executing from table statement, so far now dropping still in progress for last two hour


> sp_lock

> The class column will display the cursor name for locks associated with a cursor for the current user and the cursor id for other users.
fid    spid   loid        locktype                     table_id    page        row    dbname          class                          context

------ ------ ----------- ---------------------------- ----------- ----------- ------ --------------- ------------------------------ -----------
-----------
     0    676        1352 Sh_intent                     1817106533           0      0 master          Non Cursor Lock

     0    962        1924 Ex_table                      1886681819           0      0 MyDB       Non Cursor Lock
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi,
The table have been truncated and no data all, btw  the table now no longer exist but the sessions is still there. If I kill 962 then it might rollback the statement, the session have been there since last 8 hours.


2>
 fid spid status   loginame origname hostname blk_spid dbname   tempdbname cmd        block_xloid
 --- ---- -------- -------- -------- -------- -------- -------- ---------- ---------- -----------
   0  962 sleeping sa       sa       myDB         0 MHSSYBDB tempdb     DROP TABLE           0

(1 row affected)
(return status = 0)
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi,
The only problems is when I kill the session, the table now again exist my guess the drop statement have been roll back. Our sybase run with below version.

Adaptive Server Enterprise/15.5/EBF 18376 SMP ESD#3/P/Sun_svr4/OS 5.8/asear155/2531/64-bit/FBO

I'm now created another tempdb for sa use then now dropping the table once again.
It is really strange that this is happening on ASE 15.5. I think you should call Sybase tech support to find out an explanation. It must be a bug or something very obscure.
Thanks for giving some hint to initiate troubleshooting. I'm forwarding this case to sybase as per EE suggested.