CODEWITHSUNDEEP
sql-server
chat
chat

Reputation:

What is the LDF file in SQL Server?

What is the LDF file in SQL Server? what is its purpose?

can I safely delete it? or reduce its size because sometimes it's 10x larger than the database file mdf.

Upvotes: 73

Views: 207934

Answers (5)

Ross
Ross

Reputation: 674

LDF holds the transaction log. If you set your backups correctly - it will be small. It it grows - you have a very common problem of setting database recovery mode to FULL and then forgetting to backup the transaction log (LDF file). Let me explain how to fix it.

  1. If your business can afford to lose a little data between backups, just set the database recovery mode to SIMPLE, then forget about LDF - it will be small. This is the recommended solution for most of the cases.
  2. If you have to be able to restore to the exact point in time - use FULL recovery mode. In this case you have to take regular Transaction Log backups. The simplest way to do it is to use a tool like SqlBackupAndFTP (disclosure - I am a developer). The log file will be truncated at this time and would not grow beyond certain limits.

Some would suggest to use SHRINKFILE to trim you log. Note that this is OK only as an exception. If you do it regularly, it defeats the purpose of FULL recovery model: first you go into trouble of saving every single change in the log, then you just dump it. Set recovery mode to SIMPLE instead.

Upvotes: 53

Jhonny D. Cano -Leftware-
Jhonny D. Cano -Leftware-

Reputation: 18013

ldf saves the log of the db, certainly doesn't saves any real data, but is very important for the proper function of the database.

You can however change the log model to the database to simple so this log does not grow too fast.

Check this for example.

Check here for reference.

Upvotes: 3

Michael Gorsuch
Michael Gorsuch

Reputation: 847

The LDF is the transaction log. It keeps a record of everything done to the database for rollback purposes.

You do not want to delete, but you can shrink it with the dbcc shrinkfile command. You can also right-click on the database in SQL Server Management Studio and go to Tasks > Shrink.

Upvotes: 12

Waseem Khan
Waseem Khan

Reputation: 37

The LDF stand for 'Log database file' and it is the transaction log. It keeps a record of everything done to the database for rollback purposes, you can restore a database even you lost .msf file because it contain all control information plus transaction information .

Upvotes: 2

Eric Pohl
Eric Pohl

Reputation: 2344

The LDF file holds the database transaction log. See, for example, http://www.databasedesign-resource.com/sql-server-transaction-log.html for a full explanation. There are ways to shrink the transaction file; for example, see http://support.microsoft.com/kb/873235.

Upvotes: 34

Related Questions