Link to home
Start Free TrialLog in
Avatar of Junior_Developer
Junior_Developer

asked on

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
SOLUTION
Avatar of udayshankar
udayshankar

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Guy Hengel [angelIII / a3]
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.
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.
Avatar of Junior_Developer
Junior_Developer

ASKER

uday,
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?

thanks
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)
So what should be the ideal size?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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