3

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!

3 Answers 3

7

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.

6
  • Good and complete answer.
    – mfinni
    Feb 16, 2010 at 17:47
  • @Evan: I changed the model to Simple, but not seeing any change in the disk space. When does the change take effect? Feb 16, 2010 at 17:59
  • 2
    @pencilslate - did you also run DBCC SHRINKFILE?
    – mfinni
    Feb 16, 2010 at 18:01
  • @mfinni: Nope. Should i? Feb 16, 2010 at 18:11
  • 1
    @pencilslate - Yes, you will need to use the DBCC SHRINKFILE command before any disk space is returned. This is by design, so that the size of the log file can remain relatively static (particularly when using a full recovery model), and reduce the I/O caused by the log file.
    – Evan M.
    Feb 16, 2010 at 19:14
2

You can change the database to the Simple recovery model. The default of Full recovery model will only reduce the transaction logs after a full DB backup has been taken. If you have SQL Server 2000, you can also use the BACKUP LOG log_name WITH NO_LOG command which will mostly empty the log ready for shrinking.

-1

Maybe you can just try to Shrink them. MS Sql allows to Shirnk Files and Databases also. In SQLMGM studio right click on database and inside tasks you should have option Shirnk. Choice Database and see will it fit your needs for Free space.

alt text http://lh4.ggpht.com/_l7ldTfcnI34/S3rRLBIlhtI/AAAAAAAAE50/CTC2lqualsM/s800/shirnk.png

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .