Truncate table from SQL Server 2008

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
contactsamAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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

contactsamAuthor 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 GuideCommented:
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).
5 Ways Acronis Skyrockets Your Data Protection

Risks to data security are risks to business continuity. Businesses need to know what these risks look like – and where they can turn for help.
Check our newest E-Book and learn how you can differentiate your data protection business with advanced cloud solutions Acronis delivers

ste5anSenior DeveloperCommented:
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.
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
contactsamAuthor Commented:
Thank you All for the solutions recommended.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.