Avatar of contactsam
 asked on

Truncate table from SQL Server 2008


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.

Microsoft SQL ServerMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment

8/22/2022 - Mon
Raja Jegan R

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


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.

Raja Jegan R

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).
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

Thank you All for the solutions recommended.