Post reply

Delete or shrink a transaction log

  • January 12, 2007 at 8:09 am

    #153383

    I have been trying to delete spurious data from an SQL database i have recenty inherited.  There isn't much space left on my hard drive. When start deleting data i end up with a massive transaction log, which has now almost completely filled up the drive. 

    Can anyone tell me can i delete the transaction log, or even shrink it?

    Can i stop transaction logging so i can delete the information i need to get rid of?

    Thanks

            James

     

  • January 12, 2007 at 8:23 am

    #682923

    James

    I think the key is to do your delete in small(er) batches so that it doesn't fill your transaction log up in one go.  Before you do that, read in Books Online about Shrinking the Transaction Log, Truncating the Transaction Log and Recovery Models.  You can't stop transaction logging.

    Please post again if there's anything you don't understand having read about the topics above.

    Good luck

    John

  • January 12, 2007 at 8:56 am

    #682945

    James,

    You can run the following...(this will truncate the data within the log file)

    backup log yourdatabasename with truncate_only

    Then run this...(find the log database file name in the database properties, and size is in MB - so 100 would shrink it down to 100mb)

    dbcc shrinkfile(yourdatabasefilename, size)

     

  • January 12, 2007 at 8:57 am

    #682947

    Oh, and John is absolutely correct in saying that you should run your deltes in smaller batches to help control your problem.

  • January 12, 2007 at 8:57 am

    #682946

    You can batch things like this:

    http://www.sqlservercentral.com/columnists/sjones/batching.asp

  • January 12, 2007 at 9:51 am

    #682966

    Thanks a million lads, that worked just fine.  The log file is nice and small now.  Will do the deletes in smaller batches......... on monday!!!!!

     

    Have a good weekend

     

    James

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

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