• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 602
  • Last Modified:

SQL server 2005: Big LDF file

Hi there,
I have a SQL server 2005 and I have a LDF file over 50Go.
How does it clean it ? Is it risks ?
Thanks,
Regards,
Simlip
0
simlip
Asked:
simlip
4 Solutions
 
jogosCommented:
See in options of your db what recovery model is.

Most likely
If it is FULL you must take a transactionlog backup (that will take that full size) .  
Or simpler because you don't take transactionlog backups (to have a point in time restore) then you don't need a FULL-model so if you change it in recovery model SIMPLE then the transactions in the LDF will be released and the file stops from growing.
0
 
Ramesh Babu VavillaCommented:
If your databases  Recovery mode is in Full Recovery mode then you have to make practice of  taking Transaction log backup.
if your database is in SIMPLE recovery mode then check for open transaction
to check open transaction run
DBCC OPENTRAN In the selected databases.
0
 
QlemoBatchelor and DeveloperCommented:
The difference between Full/Bulk-logged recovery and Simple is that Simple holds transactions in the LDF only as long as needed (written completely into the MDF).
If the MDF gets lost, and you need recovery, you will have only the DB backup itself to do that. If you back your DB up once a day, you will have a DB backup which is at most 24 hours old.

Full/Bulk-logged however does not remove transactions from the LDF files, unless you perform a transaction log backup, as mentioned above. This mode makes sense if you need a more accurate backup, and can't or don't want to perform a frequent DB backup. Common schedule is e.g. DB backup once a day, and transaction log backups every few hours.
If the MDF file gets corrupt or lost, recovery is still possible up to the last transaction log backup, and if the LDF files are still intact, even to the most recent point in time.

For small DBs you usually do not want to care about transacton log backups, and Simple Recovery mode is your best choice in combination with a daily DB backup.
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now