-2

I have a 15GB database with some binary data that accounts for 50% of the database. I want to remove that unneeded data but my transaction log is going to explode in size. What are my options?

I can only delete one column data: UPDATE t SET binData=NULL WHERE binData IS NOT NULL

And yes, the space might be the problem: hence my question. I need to do this on 1000+ databases (most of them are cca 1-2GB). I don't have 2x space available

Serg
  • 22,285
  • 5
  • 21
  • 48
Ljudevit
  • 368
  • 3
  • 12
  • how much space do you have ? – Hiten004 Jan 06 '17 at 16:28
  • 2
    Assuming the data is in a table that can have all data purged: http://stackoverflow.com/questions/24213299/how-to-delete-large-data-of-table-in-sql-without-log – xQbert Jan 06 '17 at 16:28
  • 2
    Either delete in batches or truncate the table. Note that truncate will greatly reduce the impact on the transaction log but it only logs the page deletes as opposed to all of the data. – Sean Lange Jan 06 '17 at 16:32
  • Depends what proportion of your data you are keeping. `TRUNCATE` logs differently to `DELETE` so one approach could be move the data you want to keep to a new table, truncate your original table, insert the data you are keeping from the new table, then drop the new table. If you insert the data you are keeping using an SSIS dataflow task then you can configure the `Rows per Batch` and `Maximum Insert Commit Size` in OLEDB destination to have even further control over the transaction logging. – GarethD Jan 06 '17 at 16:33
  • My question wasn't specific enough: I can only delete one column data: UPDATE t SET binData=NULL WHERE binData IS NOT NULL – Ljudevit Jan 06 '17 at 16:38
  • And yes, the space might be the problem: hence my question. I need to do this on 1000+ databases (most of them are cca 1-2GB). I don't have 2x space available – Ljudevit Jan 06 '17 at 16:40
  • If you can truncate then you can still use this approach, just when inserting into the temp table, don't insert the binary data (or even have a column for it), then truncate, and re-insert without the binary data. – GarethD Jan 06 '17 at 16:41

4 Answers4

0

Note: please try this on test fist. DO NOT DO THIS IN THE PRODUCTION !!!

  1. Import the data want to keep into new temp table
  2. truncate original table
  3. import data from new temp table to original table
Hiten004
  • 2,425
  • 1
  • 22
  • 34
  • He will generate some transaction activity for the rows he's moving from the persisted table to the temporary table, and back, but I guess there's no way around that. – Radu Gheorghiu Jan 06 '17 at 16:56
0

Based on your last comments, it would seem as if the best approach would be something like this:

Note: PLEASE try this on a test box / installation first. DO NOT DO THIS IN PRODUCTION WITHOUT PRIOR TESTING AND PLANNING!

  • Set the DBs logging mode to Simple
  • ALTER TABLE t DROP COLUMN BinData
  • ALTER TABLE t ADD BinData [define column]
  • Set the Logging mode back to Full
  • Take a full backup
  • (Optional) Shrink your .mdf file(s)

Note: No matter what, when you have to clear out a column like this, set your logging to Simple before your process to keep your actual logging to a minimum.

Laughing Vergil
  • 3,706
  • 1
  • 14
  • 28
0
select 1 
while @@rowcount > 0
begin 
   UPDATE t 
   SET top (100000) binData = NULL 
   WHERE binData IS NOT NULL
end

have you db set to truncate log on checkpoint

paparazzo
  • 44,497
  • 23
  • 105
  • 176
0

You can simply update the records in several smaller batches. Maybe you will have to also invoke the CHECKPOINT after each batch as a precaution. If your database is in the SIMPLE RECOVERY mode, then once allocated log space should be reclaimed automatically. If your database is in the FULL RECOVERY mode then you have to do a log backup after each batch in order to allow the reuse of the allocated space in the log. You can keep the log size under control in this way.

Martin Karouš
  • 43
  • 5