Truncate table from SQL Server 2008

contactsam
contactsam used Ask the Experts™
on
Hi,

In current production environment, we have requirement to truncate approx 860 million records from table in MS SQL server 2008. Please advise the best option to truncate table without impacting end users within production environment.


Regards
Tom
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Simple, if the table doesn't have any Foreign keys, then TRUNCATE TABLE should truncate your table without any issues.
TRUNCATE TABLE ur_Table_name

Open in new window

Author

Commented:
Thank you for the update.

If there is requirement to delete in batches for eg year wise, are there any script available  and also take care of reducing transaction logging while deleting in batches.


Regards
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
If you want to DELETE in batches, then it will be logged in Transaction logs and the amount of logging can't be reduced whatever your Recovery model is in..
If you want to reduce the transaction log file to grow to a huge size while deleting in batches, then you can do the below things:
1. Take Log backups more frequently.
2. Change your Recovery model to simple(Not recommended as it would break Log chains).
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

ste5anSenior Developer
Commented:
First of all: Clarify the requirements.

Truncation is a database term, which means that the data in the entire tables is removed. It does not log the single rows deleted, thus it is faster and has less log impact. But it requires a table lock during the operation. It also requires that no foreign key relationship exists pointing to this table.

Deletion means deleting row by row and logging each row. Depending on the table it uses more locks then TRUNCATE, normally on row level. But it can escalate to page or table level.

Then: Is that table used in views, functions or procedures? When not, renaming the table, creating a new one and dropping the old should be the fastest operation (assuming that you don't need to initialize the same amount of disk space).

And last but not least: If I interpret your second post correctly, then you should look into table partitioning. Cause deleting a partition is a fast schema operation.
Technical Architect, Capgemini India
Commented:
Years ago, I wrote a piece on this  on SQLServerCentral.com at: http://www.sqlservercentral.com/articles/T-SQL/72606/

Essentially, the approach involves staging Ids of records to be deleted into a lookup table and then using that to delete the data from the main table. Keep in mind that the delete should be in batches (1000-10,000 records, based on the traffic your application generates) so that you do not lock the table for too long.

Author

Commented:
Thank you All for the solutions recommended.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial