• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

Need to delete large amount Data from some of tables.

I need to lot of data from some of the tables.
I know that the tranaction log file will increase in size. i would like to know once i delete all the data how do i truncate the log file.
are there any consequences if i truncate this file?
I am a newbie so please explain the steps

Thanks in advance
2 Solutions
just run
backup log dbname_log with no_log
and then run
DBCC shirnkfile(dbname_log, 2)

this will just remove the committed logs so there should not be any effect unless you want to roll back
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if you only want to keep a few or 0 rows (compared to the rows you deleted); AND if you don't have any foreign keys pointing to this table:

copy the rows you want to keep to another table
truncate your table
copy the rows back from the temp table

if you have foreign keys, truncate is not allowed:
* list all the foreign keys
* drop them
* do the copy/truncate/copy described above
* recreate the foreign keys

If you want to get rid of all data in a table, TRUNCTATE MyTable will not cause the deletions to be logged.
Alternatively, you could set recovery mode to SIMPLE, then do the deletions, then re-set it to FULL; again, deletions won't be logged.

The obvious problem with these methods is that if you inadvertently screw up your deletions there's no way of getting the data back.

Otherwise, do a backup of the db and of the log file, do your deletions, then back up the log file again.  You can then issue your truncate (or do it within EM) safely - small log file and you can still go back later if you need to.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Guy Hengel [angelIII / a3]Billing EngineerCommented:
some fine-explanations to johnclarke's:

>TRUNCTATE MyTable will not cause the deletions to be logged.
the truncate table statement itself WILL be logged, but not all the deleted table data as in the DELETE statment.

>Alternatively, you could set recovery mode to SIMPLE, then do the deletions, then re-set it to FULL; again, deletions won't be logged.
actually, the deletion IS logged, but the transaction log space used by the DELETE can be reused once the delete has been committed.
Junior_DeveloperAuthor Commented:
so if i just delete all the unwanted data then i will see my transaction log file for that database increased correct?
and once i delete i will run this command: DBCC shirnkfile(dbname_log, 2) correct?
what is 2 in the above statement?

Guy Hengel [angelIII / a3]Billing EngineerCommented:
2 is the desired size, but you should NOT try to reduce the transaction log file to a too small size, as otherwise it will have to grow again (which takes time and holds up the transactions during that time)
Junior_DeveloperAuthor Commented:
So what should be the ideal size?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the ideal size is just above the sum of the sizes of all the transactions in the busiest interval of time between 2 transaction log backups.
Junior_DeveloperAuthor Commented:
Thanks everyone!!!
I wanted to accept UdayShankar answer as thats what i was looking for a command to shrink the log file.
I did split on the points but dont know how angeIII shows accepted answer.
It should have been Udayshankar --- Accepted answer and angelIII  as assisted answer

Thanks again that was quick

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now