CODEWITHSUNDEEP
sqlazuretruncate
BIDeveloper
BIDeveloper

Reputation: 847

Truncate Statement Taking Too much time

I have a table Which has more than 1 million records, I have created a stored Procedure to insert data in that table, before Inserting the data I need to truncate the table but truncate is taking too long.

I have read on some links that if a table is used by another person or some locks are applied then truncate takes too long time but here I am the only user and I have applied no locks on that.

Also no other transactions are open when I tried to truncate the table.

As my database is on SQL Azure I am not supposed to drop the indexes as it does not allow me to insert the data without an index.

Upvotes: 3

Views: 13746

Answers (2)

user4725980
user4725980

Reputation:

Drop all the indexes from the table and then truncate, if you want to insert the data then insert data and after inserting the data recreate the indexes

Upvotes: 4

Archlight
Archlight

Reputation: 2079

When deleting from Azure you can get into all sorts of trouble, but truncate is almost always an issue of locking. If you can't fix that you can always do this trick when deleting from Azure.

declare @iDeleteCounter int =1
while @iDeleteCounter > 0
begin
    begin transaction deletes;
    with deleteTable as
    (
        select top 100000 * from  mytable where mywhere
    )
    delete from deleteTable
    commit transaction deletes
    select @iDeleteCounter = count(1) from   mytable where mywhere

    print 'deleted 100000 from table' 

end

Upvotes: -1

Related Questions