How do I delete the log file for a dB?

  • The log file for a database has grown to over 5 GB leaving less than 4 MB of hard disk space on my server. All this happened as a result of copying a large dB. How can I delete the log file. When I choose to delete it using Windows Explorer I get a sharing violation yet nothing I know is using it.

    All the documentation I've read only talks about backup log. I tried backup log <dB name> with truncate only and no luck. What can I do?

  • Take a look at DBCC shrinkfile. If you search our site you'll find several discussion topics and and a script or two that deal with this issue. One shortcut you can take is to detach the db, then delete the log file, then reattach (minus the log file) using sp_attach_single_file_db. Please make sure you understand the process completely before trying on your real db - test on Pubs or Northwind.

    You wont be able to delete the log file as long as it's attached - SQL maintains a lock on it. It's an incredibly important part of the db, having it disappear in the middle of a transaction would be bad!

    Andy

  • Here's a discussion that's going on in the Backup section. Steve Jones has a script referenced that will shrink the log file. Some other methods are also discussed, including Andy's straight forward detach/reattach method that works wonderfully and quickly.

    http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=2017&FORUM_ID=24&CAT_ID=1&Topic_Title=SQL+7+Trans+log+too+big%2E+How+shrinK&Forum_Title=Backups

    K. Brian Kelley

    bk@warpdrivedesign.org

    http://www.sqlservercentral.com/columnists/bkelley/

    K. Brian Kelley
    @kbriankelley

  • Keep in mind, you never want to "delete" the log file. It is essential for operation of the db. It can go out of control. Check out Andy's and Brian's suggestions. I've used the script and it works pretty fast to shrink log files.

    Steve Jones

    steve@dkranch.net

  • Easiest way is to

    Back up the database

    Detach the database (sp_detach_db)

    delete the .ldf

    attach the log file (sp_attach_single_file_db)

    You have to delete (or rename) the log file or it may be picked up again).

    Male sure you do the backup in case it fails.


    Cursors never.
    DTS - only when needed and never to control.

  • I agree, the attach and detach technique is fantastic for manipulating the files. -JG


    -JG

  • Hope this helps....

    http://www.swynk.com/friends/krishnan/tranlogshrink.asp

Viewing 7 posts - 1 through 7 (of 7 total)

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