Hello folks, 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 re-use 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?
+1
A:
"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
2009-11-20 20:19:13
A:
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
2009-11-20 20:24:01
On SQL Server, `drop table` cannot be used if there are foreign key constraints. http://msdn.microsoft.com/en-us/library/ms173790.aspx
ProKiner
2009-11-20 20:30:02
@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
2009-11-20 20:37:57
@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
2009-11-20 20:43:30
@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
2009-11-20 20:59:03
@mjv - you're right. One additional thing to remember that `truncate table` won't activate a trigger, but `delete` will.
ProKiner
2009-11-20 21:00:46
+1
A:
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
2009-11-20 20:24:17
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
2009-11-20 21:02:25
A:
Truncating a table is normal procedure, yes. This exact situation is why the keyword was added in the first place.
Mark Byers
2009-11-20 20:25:33
A:
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
2009-11-20 20:35:53
A:
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!
Good Luck
Chad
2009-11-20 20:36:24
A:
As pointed out, if you can't use truncate or drop
SELECT 1
WHILE @@ROWCOUNT <> 0
DELETE TOP (100000) MyTable
gbn
2009-11-20 21:01:01
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
2009-11-20 21:18:07