Drop Table?

Alex_Chow · 2011-10-26

We have a database with a huge amount of data in the Change Log table (60GB). Is there a way to use SQL command to drop the table without having it write to the transaction log?

bbrown · 2011-10-26

You could use "TRUNCATE TABLE" instead. It logs much less and also leaves the table intact.

bbrown · 2011-10-26

Another option is a temporary switch to Bulk Logged Recovery Method.

matttrax · 2011-10-26

I always do TRUNCATE TABLE for this one. So much faster

kriki · 2011-10-27

Or you can run a procedure that runs every N minutes and deletes the N oldest records. You will have to run that for a long time. I always do something like that instead of deleting all in 1 big go.

matttrax · 2011-10-27

Yeah, usually the a job is run to archive the data into a different database / table. If you don't need to preserve the data, though, I recommend the TRUNCATE.

bbrown · 2011-10-27

BTW - You cannot use "Truncate Table" on a table that is used by an Indexed View. So if you have defined any SumIndexes on the table you won't be able to use "Truncate Table".

Alex_Chow · 2011-10-27

Truncate table worked beautifully!

Drop Table?

Answers

Categories