CODEWITHSUNDEEP
sqlsql-servertruncate
Pythonista anonymous
Pythonista anonymous

Reputation: 8970

Truncating a MS SQL Server Express table with one record takes forever

I am setting up a kind of test database in Microsoft SQL Server Express 2017. I have one main table with 10 columns, which is linked to 6 others, ie its primary key is the foreign key of 6 other tables.

I have populated this main table with just one record.

I need to truncate it - ie delete all the rows but not the table. I tried both truncate table and delete from but both take forever: after 4 minutes the query was still executing! I understand there are keys to check etc, but it's only one record. All the other tables are empty. This doesn't seem right. Any ideas what could be wrong and what I can do to fix it?

Upvotes: 0

Views: 723

Answers (2)

George Joseph
George Joseph

Reputation: 5932

In response to the comment

It is probably uncommitted transactions, on your child tables.

You would first be deleting all the child records prior to deleting the parent table record. Is the child tables all empty?.

Do you have any uncommitted transactions in any of those tables? If you do then attempt to kill those sessions by engaging a dba.

Upvotes: 1

dk1611
dk1611

Reputation: 11

A table that has foreign key constraints can't be truncated. Either you drop constraint, truncate table then re-create constraints Or make use of delete cascade. here is a link for same.

TRUNCATE TABLE is a DDL command, it cannot check to see whether the records in the table are being referenced by a record in the child table. In case of DELETE, database is able to make sure that it isn't being referenced by another record.

Upvotes: 0

Related Questions