Truncate Large Table in SQL Server AG – How to Truncate a Table with 17 Billion Rows in Availability Groups

availability-groupssql servertransaction-logtruncate

I need to truncate a table with 17 billion rows, the table is in a database that is part of an AG.

What will be the effect of this operation on the AG latency and the size of log backups?

Is there a recommended way of doing this?

Best Answer

Truncate table command truncates rows instantly and does not write deleted rows to transaction log file

Usually Truncate table executes instantly and there is no noticeable network traffic between replicas in AG, there will be no noticeable log backups as a consequence, etc., compared to when you do Delete from. However, there can be noticeable traffic and noticeable log backup, because 17 billion rows is a lot.

P.S. Consider backing up the database and save the backup to archive before doing truncate, so you can restore the 17 billion table later, if needed

Related Solutions

Oracle – Resolving Slow Truncate on Large Table

I assume you are using dictionary managed tablespaces. You have to wait until all extents are deallocated. The following statement should report the number of extents that are to deallocate:

select extents 
  from dba_segments 
  where owner='TABLEOWNER' 
  and segment_name='TABLENAME'

Index segments and materialized view logs have to be deallocated too, if they exist.

If you drop and recreate the table you will have to wait, too. Dropping the table will take as long as truncation of the table because the number of extents to release is almost the same.

Maybe the following will help: instead of drop and recreate you

1) rename the table TABLENAME to OLDTABLE
2) create a new table with the original name TABLENAME. Now you have an empty table TABLENAME
3) you drop the table OLDTABLE

The advantage of this approach is you can continue your work with table TABLENAME after step 2. But the space occupied by the former table TABLENAME will be release during the execution of step 3. In Linux you can start the job that does step 3 in the background with nohup so that you must not leave your terminal window open. Use nohup sqlplus user/password@tnsname @dropscript.sql &.

Even if you want to restart the database during execution of step 3 this is not a problem because Oracle can handle such situations. As far as I remember it does this in the following way: After the restart of the instance (or if the sessions that does the drop of the table dies for some other reasons) the segment of the table is converted to a temporary segments. Its extents are deallocated by a background process (I think it is smon). The table is removed from the dictionary after the death of session so the tablename OLDTABLE is not visible in the dictionary anymore.

SQL Server – Slow INSERT/SELECT After TRUNCATE TABLE

update statistics tablename. Enjoy.

Best Answer

Related Solutions

Oracle – Resolving Slow Truncate on Large Table

SQL Server – Slow INSERT/SELECT After TRUNCATE TABLE

Related Question