December 20, 2002 at 12:06 pm
I have a SQL 2000 server running on a computer with a small HD. The MDF file in only 175MB but the log file is about 6GB. I now have very little space left, and I don't think I would be able to perform a backup without running out of space. I don't know how to backup to a different drive.
This database is used to store summary data from another source, and to be honest backing up the transactions are not very important. I would be happy with just backing up the database and not keeping a log of all actions taken.
Any advice would be great.
Barry
December 20, 2002 at 12:13 pm
Take a look at Backup Log <log name> with truncate_only in Books Online. It may do what you want to truncate the log file. You should try to backup the database first/after.
FROM BOL:
LOG
Specifies a backup of the transaction log only. The log is backed up from the last successfully executed LOG backup to the current end of the log. Once the log is backed up, the space may be truncated when no longer required by replication or active transactions.
Note If backing up the log does not appear to truncate most of the log, an old open transaction may exist in the log. Log space can be monitored with DBCC SQLPERF (LOGSPACE). For more information, see Transaction Log Backups.
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and truncates the log. This option frees space. Specifying a backup device is unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes recorded in the log are not recoverable. For recovery purposes, immediately execute BACKUP DATABASE.
Gary Johnson
DBA
Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
January 4, 2003 at 8:58 am
check out your recovery model.
You should have either full or bulk-logged AND you are probably not making any log backup.
If so then you only have to put your database recovery model to simple and at once truncate your log (backup log YourDBName with truncate_only).
After you can mace a dbcc shrinkfile on your log file and that's all
Bye
Gabor
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply