9

We have a table with a 150+ million records. We need to clear/delete all rows. Delete operation would take forever due to it writing to the t-logs and we cannot change our recovery model for the whole DB. We have tested the truncate table option.

What we realized that truncate deallocates pages from the table, and if I am not wrong makes them available for reuse but doesn't shrink the db automatically. So, if we want to reduce the DB size, we really would need to do run the shrink db command after truncating the table.

Is this normal procedure? Anything we need to be careful or aware about, or are there any better alternatives?

Sam
  • 7,252
  • 16
  • 46
  • 65
Paulin Shah
  • 91
  • 1
  • 2

6 Answers6

1

"Delete all rows"... wouldn't DROP TABLE (and re-recreate an empty one with same schema / indices) be preferable ? (I personally like "fresh starts" ;-) )

This said TRUNCATE TABLE is quite OK too, and yes, DBCC SHRINKFILE may be required afterwards if you wish to recover the space.

mjv
  • 73,152
  • 14
  • 113
  • 156
1

truncate is what you're looking for. If you need to slim down the db afterwards, run a shrink.

This MSDN refernce (if you're talking T-SQL) compares the behind the scenes of deleting rows versus truncating.

ProKiner
  • 697
  • 7
  • 12
  • As other comments have notied, you're going to have to deal with your foreign key constraints (if any), no matter which approach you choose. My preference would be to disable the constraints, `truncate` the table, re-enable your constraints, and then `dbcc shirinkfile` (give yourself some time). – ProKiner Nov 20 '09 at 21:02
0

One thing to remember with Truncate Table (as well as drop table) is going forward this will not work if you ever have foreign keys referencing the table.

Irwin M. Fletcher
  • 2,606
  • 3
  • 25
  • 29
  • On SQL Server, `drop table` cannot be used if there are foreign key constraints. http://msdn.microsoft.com/en-us/library/ms173790.aspx – ProKiner Nov 20 '09 at 20:30
  • @prokiner you got to deal with foreign key references no matter what, i.e. whether the row(s) are deleted, shrunk or dropped, any record in other tables referencing this row(s) must either be deleted first or have the constraint removed. In some cases this may be "automated" with ON DELETE triggers, but that is hardly applicable to 150+ Million type databases. – mjv Nov 20 '09 at 20:37
  • @prokiner, I should have been more clear. Those were meant to be two seperate thoughts, I was just pointing out the issue has the OP had stated that they had tested the truncate method. I have cleaned up my answer. – Irwin M. Fletcher Nov 20 '09 at 20:43
  • @Irwin - Sorry I wasn't more clear as well. You're right, both approaches require handling foreign key contsraints. My comment was just dealing with the `drop` option. In either case, the OPer has got more work on his hands than a simple `truncate table; dbcc shrinkfile`. – ProKiner Nov 20 '09 at 20:59
  • @mjv - you're right. One additional thing to remember that `truncate table` won't activate a trigger, but `delete` will. – ProKiner Nov 20 '09 at 21:00
0

Depending on the size of the full database, the shrink may take a while; I've found it to go faster if it is shrunk in smaller chunks, rather than trying to get it back all at once.

SqlACID
  • 4,024
  • 20
  • 28
0

You have a normal solution (truncate + shrink db) to remove all the records from a table.

As Irwin pointed out. The TRUNCATE command won't work while being referenced by a Foreign key constraint. So first drop the constraints, truncate the table and recreate the constraints.

If your concerned about performance and this is a regular routine for your system. You might want to look into moving this table to it's own data file, then run shrink only against the target datafile!

Sam
  • 7,252
  • 16
  • 46
  • 65
Chad
  • 892
  • 2
  • 11
  • 25
0

As pointed out, if you can't use truncate or drop

SELECT 1
WHILE @@ROWCOUNT <> 0
    DELETE TOP (100000) MyTable
gbn
  • 422,506
  • 82
  • 585
  • 676
  • He can use both truncate or drop, however their are possible considerations with any operation (delete,drop,truncate) that will have to be taken care of. – Chad Nov 20 '09 at 21:18