October 23, 2006 at 9:04 am
My hard drive has just filled up. I think that the .LDF files are responsible. If I just back these up to a backup drive is it likely to cause any problems to the databases. I don't really understand what these .LDF files are for.
October 23, 2006 at 9:32 am
These are the log files. You should periodically back these up and that will prevent them from filling up. (BACKUP TRANSACTION)
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 23, 2006 at 5:14 pm
.ldf files are the log files. They keep track of all the transactions that occur on the database. If your database is set to SIMPLE recovery mode, then when the log file gets checkpointed, SQL Server can overwrite that part of the file. If your database is in FULL recovery mode, you need to do log backups (BACKUP LOG). The backup will set the checkpoint and then the log file space can be reused.
There are other things that cause the log file to grow rapidly. If you reindex your database, the log file can grow to 2.5 times the size of the database. It needs to keep track of how the database was before the reindexing began so it can roll it back if need be.
-SQLBill
October 25, 2006 at 3:33 pm
You can always backup your transaction log with truncate_only then perform a shrink on the log files to make them smaller. This should reclaim the space on your drive. After that, if you choose to keep the database with a full recovery model, then back up your transaction logs frequently if they continue to grow quickly.
November 1, 2006 at 11:41 am
If you do backup the transaction log with truncate_only and shrink the log file, this would be a good time to do a full backup of the database. Since the log file has been cleaned out you would not be able to recover to a point after your last full or transaction log backup. I would check the space available first, though.
Steve
November 1, 2006 at 2:22 pm
Use the command:
BACKUP LOG dbName WITH NO_TRUNCATE
November 1, 2006 at 2:49 pm
If you backup the log WITH NO_TRUNCATE you will not free up any space which doesn't address the initial problem Mark had of running out of space. I've seen transaction log files that are many times larger than the data file.
As Shane stated, performing a backup of your transaction log with truncate_only will free up space and it is a good idea start doing regular transaction log backups after that to keep them from growing so large.
The only comment I added to the conversation was to perform a full database backup so information is not lost because the tranaction log was truncated.
Steve
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply