December 27, 2001 at 7:58 pm
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?
December 28, 2001 at 6:19 am
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
December 28, 2001 at 9:10 am
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.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
December 28, 2001 at 9:53 am
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
December 30, 2001 at 2:50 pm
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.
January 2, 2002 at 10:26 am
I agree, the attach and detach technique is fantastic for manipulating the files. -JG
-JG
January 3, 2002 at 10:18 am
Hope this helps....
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy