Need to delete large amount Data from some of tables.

Posted on 2006-05-18
Last Modified: 2012-06-21
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
Question by:Junior_Developer
    LVL 9

    Assisted Solution

    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
    LVL 142

    Expert Comment

    by: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

    LVL 4

    Expert Comment

    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.
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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.

    Author Comment

    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?

    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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)

    Author Comment

    So what should be the ideal size?
    LVL 142

    Accepted Solution

    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.

    Author Comment

    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

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now