Avatar of motioneye
motioneye
Flag 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 ?
Sybase Database

Avatar of undefined
Last Comment
motioneye

8/22/2022 - Mon
SOLUTION
alpmoon

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
alpmoon

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
motioneye

ASKER
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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
motioneye

ASKER
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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
alpmoon

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.
motioneye

ASKER
Thanks for giving some hint to initiate troubleshooting. I'm forwarding this case to sybase as per EE suggested.