How to empty MSSQL database log file?

Hi!

I've been trying to perform DTS many times w/o success thus my log file is full now. Can anyone show me how I can empty the log file to perform other transaction?

Thank you!

Nguyen
 
There is a command to empty the log, but I am not sure of it, however you can have support set it to Recovery Mode Simple and Auto Shrink quite easily.
 
I'm not very farmiliar with logging, can you please explain for me exactly how I can do that? Currently my log file is full and I cannot perform any transaction to my Jodo db (i.e. create new stored proc).

Regards,

Nguyen
 
Really the best option will be for you to send a ticket and give the database name and the server it is on, as you will more than likely not have the proper permissions to backup the log (a required step before it will allow the removal of the log)
 
You can run this command in SQL Analizer

backup log <database name> with truncate_only

This will truncate your transation log. This should happen during a backup
 
I had a similar problem DTS'ing a database with some rather large tables. To get around it, I set my database quota rather high, changed the data/log ratio to favor the log file (like 70% log), and then did the DTS. After the transfer was completed, I ran a DBCC SHRINKFILE on the database log file:
Code:
DBCC SHRINKFILE(mydbname_log, 1)

and then turned my quotas back down and changed the ratio back to something reasonable.
 
I am having a problem with the transaction log on one of my databases too, Ticket #29631. The control panel says that my transaction log is at 1M out of 15M but I am getting errors that say its full.

Steve
 
Back
Top