We use SQL PE 2000 in our lab to store sampled data (lots of data collected at high sampling rate) in real-time. i know this may be an uncommon use of SQL server. For this particular application, performance is much more important than data recoverability. In other words, once-in-a-while data loss is acceptable. Actually we have never used the transaction log for recovery since we started using the SQL PE in this way a few years ago. Certain transaction logging such as bulk deletion is particularly wasteful of the resource. As my understanding from reading the online book and some threads on this forum, the best thing we can do is using bulk-logged recovery model. I am not sure if that helps us at all because it seems that bulk deletion (i.e. DELETE FROM MyTable WHERE ..., may affect hundreds of thousands of records) is not on the list of minimally logged transactions.
I am wondering if anyone could share some good advice.
Thanks in advance!
Correct Deletes (or any normal DML command) are fully logged transactions. There is no way to disable this behavor in Microsoft SQL Server.
In order to reduce logging when deleting large amounts of data do the deletes in smaller batches. Something like this should work.
Code Snippet
set rowcount 1000
select count(*) from sysdatabases --This is just to get the loop started
while @.@.ROWCOUNT <> 0
BEGIN
delete from table
where something = somethingelse
END
This will do small deletes of 1000 records at a time which while still logged will be done in small batches so with the log in simple recovery mode shouldn't get very large.
Doing deletes in this method will take longer than a single large delete, but you don't have to worry about a large transaction log.
|||Denny, thanks for clarifying this.
I am wondering if reducing the transactio log file size helps the performance. My understanding is that the same amount of logging still takes place. We are not concerned about storage space that we have plenty. We truncate the log file periodically.
|||Reducing the log file size shouldn't effect performance (unless you shrink the file to small and SQL has to grow the file often). It is recommended to pre-allocate the file size so that SQL Server doesn't ever have to grow the file.