mibuso.com

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?

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

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

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

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.

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.

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".

Truncate table worked beautifully!