1

We have a table with 800M rows and 93 columns that needs historical data removed. The data to be removed makes up > 95%. So far, attempts to delete in batches have failed, estimating at least one week to complete. The existing indexes are the main cause of the slowness. Removing the indexes and rebuilding after deletion has also proved too slow in testing.

It was decided that we should create a new table containing only the data we need, before renaming the old table as backup and renaming new to original.

The problem is that there are at least a hundred keys, constraints and indexes.

We are considering generating a script from Object Browser, renaming all the object names using find/replace, creating the identically structured table, populating with data, renaming original as backup, then renaming the new table and all the objects to those contained by the original table.

This is a critical table on a live database with only a few overnight hours to complete the process. So many manual renamings make us nervous as there cannot be any problems when the client begins trading.

What is the most reliable method? Is there an automated process to undertake this procedure? Any recommendations appreciated.

cloudsafe
  • 2,444
  • 1
  • 8
  • 24
  • 1
    Select the data you want to keep into a wip table, truncate the main table, put the kept data back into the main table? – squillman May 30 '18 at 15:40
  • 1
    Hundreds of keys in one table? Sounds behemothic. I think I made that word up. Anyway, how about partition switching as an option? – Jacob H May 30 '18 at 15:46
  • @JacobH The keys, constraints, indexes and triggers make up about 100 objects. before I look too deeply into partition switching, we have Standard edition 2008, not Enterprise, so not possible? – cloudsafe May 30 '18 at 15:55
  • 1
    Looks like only enterprise, unfortunately. – Jacob H May 30 '18 at 15:59
  • @squillman That reduces risk of human error but leaves us without a copy of the older table which we will need if we find the process takes too long and decide to abort it. It will also take longer. We have full recovery backups but they take 5 hours to restore. We may suffer those negatives if we feel confident and tests show we have enough time. Thanks. – cloudsafe May 30 '18 at 16:12
  • I suggest that you ask this question in dba.stackexchange as this is dba territory. – MJH May 30 '18 at 22:18

2 Answers2

0

I would do the following during your downtime (i.e. the database is not used by anyone):

  1. Script out the current table, let's call it [X], and rename it to [Y].
  2. Run the script in step 1 and create a new table, still as [X]
  3. Insert into this [X] the records you need from [Y], it should be <5% of your 800M rows. (it is still ~40M rows because 800M X 5% = 40M). Of course, to quicken this step, you may consider switching recovery model to SIMPLE and/or disable triggers/NC indexes etc. Once the work is done, restore back to original settings.
  4. Once this is done, set up a regular job that will maintain the new [X] table by moving un-needed records to [Y] every day.

But the final solution would be to upgrade your sql server 2008 to SQL Server 2016+, in SQL Server 2016 SP1, even standard edition has the partition capability and that partition method will make your life much easier (another topic though)

jyao
  • 1,550
  • 3
  • 19
  • 26
  • If I rename the current table then the constraints, indexes, triggers etc will all still exist for the newly named table. The script would have to be amended so that each of the 100+ objects is renamed, which is a concern. – cloudsafe May 31 '18 at 09:00
  • @cloudsafe, even if you need to rename these constraints/indexes/triggers of the old table, it is one time thing.But to me, why not rename the constraints/indexes/triggers when you create the new table? On the other hand, once the table becomes an archive table, do you still need constraints/indexes/triggers as the live table? – jyao May 31 '18 at 15:48
  • We have a standard naming convention. Eventually, every original table would have have to have all its objects renamed so that the new one can take the original object names. The more scripting we do, the greater chance of an error and the window is not big enough to squeeze in fixes. Ideally, there would be a tried and tested script, somewhere, that generates this task for me. I would think it is a common task. – cloudsafe Jun 01 '18 at 10:08
0

Being nervous of the huge number of renames required and the critical nature of the table, I have been able to test the below script to rename all constraints, indexes, triggers, defaults etc. so will create a copy script, rename original, then run copy script:

select o.name as [Name],  o1.type as [Type], 'EXEC sp_rename N''' + o1.name + ''', '''  + o1.name + N'_OLD'''
from sys.objects o
inner join sys.objects o1 on o1.parent_object_id = o.object_id
where o.name = 'Product_history'
union
select o.name as [Name], 'I' as [Type], 'EXEC sp_rename N''PRODUCT_HISTORY.' + i.name + ''', '''  + i.name + N'_OLD'''
from sys.objects o
inner join sys.indexes i on i.object_id = o.object_id
where o.name = 'Product_history' and i.name is not null  AND i.is_primary_key = 0
order by [Type], [Name]


select o.name as [Name],  o1.type as [Type], 'EXEC sp_rename N''' + o1.name + ''', '''  + LEFT(o1.name,LEN(o1.name)-4) + N''''
from sys.objects o
inner join sys.objects o1 on o1.parent_object_id = o.object_id
where o.name = 'Product_history'
union
select o.name as [Name], 'I' as [Type], 'EXEC sp_rename N''PRODUCT_HISTORY.' + i.name + ''', '''  + LEFT(i.name,LEN(i.name)-4) + N''''
from sys.objects o
inner join sys.indexes i on i.object_id = o.object_id
where o.name = 'Product_history' and i.name is not null  AND i.is_primary_key = 0
order by [Type], [Name]
cloudsafe
  • 2,444
  • 1
  • 8
  • 24