Drop Table?

Alex_Chow

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

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

bbrown

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

matttrax

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

kriki

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

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

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

Truncate table worked beautifully!