The "sql server database transaction log file" files *.ldf are consuming GBs of space on my hard drive. Is there are way to clear them off when needed? I don't run live database on my PC and i use my db only while development and testing. So, these transaction logs don't make sense to be persistent. Or, can i relocate this log path to some other drive as i don't have any space on my C drive to hold this.
many thanks!
Best Answer
Do you need the ability to restore a database to any point in time, or is restoring only to the time the backup occurred good enough? If the later, then set the database(s) to use the Simple Recover Model. Once the model has been set to simple, you can then use the DBCC SHRINKFILE command to shrink the ldf files back to only a couple MB.
However, if you need point-in-time restore capability, then you'll need to start taking regular Full AND transaction log backups. The full backups are as you expect. The transaction log backups will copy the data from the transaction log to the backup, then truncate the transaction log. Once your logs are being backed up, you can go back with the SHRINKFILE command and shrink the transaction logs back to a reasonable size. You can read this article for a little more info on how to use maintenance plans to schedule your transaction log backups.