2

We have pretty big table with hundreds of millions of rows. It takes about 5-15 minutes to run removal of rows for a specific foreign key value. For example removing 8 million rows takes 15 minutes.

The questions is that does the removal of the rows actually even free up space as the database has transaction logging on? Can I remove rows with by-passing transaction logging for that operation?

ux.engineer
  • 10,082
  • 13
  • 67
  • 112
  • TRUNCATE TABLE - does not affect transaction logs but you can't use WHERE clause if it is the case for you – Andrey Morozov Jan 14 '15 at 12:14
  • Some suggestions here: http://stackoverflow.com/questions/2126434/bulk-delete-on-sql-server-2008-is-there-anything-like-bulk-copy-bcp-for-delet – kayakpim Jan 14 '15 at 12:18
  • This thread is relevant - it doesn't prevent transaction logging altogether, but does reduce the burden some. https://stackoverflow.com/questions/11230225/how-to-efficiently-delete-rows-while-not-using-truncate-table-in-a-500-000-rows – SQLServerSteve Sep 18 '18 at 03:21

1 Answers1

2

In simple terms, you can't get around the transaction logging. That's just how the database ensures consistency - if the transaction fails halfway through (or the server's power fails, for example), the database engine needs to know how to get into a consistent state again. Also, appending the things to be changed into the transaction log is much faster than actually performing a change on the data files of the DB, especially in cases like yours.

There's a few special cases where it's safe to get around those things - truncate table will remove all the rows at once, and only if the table has no foreign keys, which makes it rather trivial. You can't limit it in any way, though.

The newly free space will be reclaimed as part of the database maintenance cycle. During each database backup, the database is synchronized to have all the data written in the data files, and the transaction log is backed up and emptied in the DB itself (I'm oversimplifying, since there's a lot of possible configurations - in any case, this is something your DBA should care about).

If this is posing a problem to you, the solution wouldn't be to get around the transaction logging anyway. You probably want to ask why (and how often) you need to delete millions of rows at a time.

Luaan
  • 62,244
  • 7
  • 97
  • 116