Post reply

logging during delete

  • November 8, 2006 at 10:54 am

    #117367

    Hi,

    is there any way to avoid logging for conditional delete in SQL2k5 ,if not what could be the best way to avoid large impact for logging.

    Thanks

  • November 8, 2006 at 10:40 pm

    #670764

    One way to cut down is to delete in batches. It doesn't eliminate logging, but it will down the log entries. Also, the deletes will run faster if you order the batch by the clustered index.


    Enjoy!

    A Brown

    Manage all of your backups in one simple job using this script.

  • November 9, 2006 at 7:21 am

    #670831

    DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back.

  • January 14, 2007 at 5:02 am

    #683112

    Thanks ,but i am deleting rows from trace database to claim some space on the drive but as soon as i delete the batch ,log file grows for this database and space issue still persist ,please advise if i need to do some parallel operation for log file to claim some space while deleting records from my database,thanks in advance.

  • January 15, 2007 at 5:23 am

    #683185

    you could put your trace database into simple recovery, that may help. Buy more disks.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply