Transaction Log SQL Server

Question

All,

We often delete many rows from a table and even though we are using set rowcount 10000 most of the times we fill up the Transaction Log. Is there something to do to avoid this problem happening?

Thanks,

M

Answer 1

before and after deleting data; you can run this query to empty transaction log:

dump tran SAMPLE_DB with truncate_only
go

Answer 2

Two things you can do:

• you can set your database's recovery model to SIMPLE - this will limit the amount of data being logged - that's only part of a fix, however

• you need to establish frequent transaction log backups - especially just before and just after batch deletes.

This is really more of a sysadmin/DBA question, and thus you'll probably get more and more useful answers on http://serverfault.com.