# MDF and LDF contents in SQL Server

Let's say I have a database with a single .mdf and .ldf file.

The database is detached, there are no transactions and SQL Server is stopped.

I am presuming that all the data is already in the .mdf file?

The .ldf file is essentially empty? If the .ldf file is not empty what does it contain?

If I delete the .ldf file and manually create a new 0 bytes long .ldf file with same name as before, I would be able to attach the database again with no data loss?

-

## migrated from stackoverflow.comSep 18 '12 at 10:12

This question came from our site for professional and enthusiast programmers.

Not sure why it has been downvoted... Could you provide some comment? Maybe then I can redo the question –  Eduard Sep 17 '12 at 16:03
Why not try what you suggested (deleting the .ldf log file and creating a new one) and see what happens. When you've done that please come back, tell us what you found and flag or @ reply me. Thanks. –  Kev Sep 17 '12 at 22:33
This question may help: stackoverflow.com/questions/5801675/… –  E.J. Brennan Sep 17 '12 at 22:35
What are you trying to accomplish, or are you just curious about what will happen? –  Jon Seigel Sep 18 '12 at 16:06
I'm curious about what will happen and more importantly I would like to have a feeling of what it could contain. I didn't have a deep knowledge on databases but if LDF only contains transactional info, when the database server is stopped and the database detached I don't see any reason for the ldf file to have anything at all. –  Eduard Sep 18 '12 at 21:23

(Disclaimer for peeps flying in from Google: I do not recommend using the method asked about in this question to reclaim log file space! Use DBCC SHRINKFILE instead.)

I am presuming that all the data is already in the .mdf file?

Yes, detaching initiates a CHECKPOINT in the database, so all dirty data pages make it to disk.

A database cannot be detached if the transaction log is required by another process (mirroring, replication, etc.) or if exclusive access cannot be obtained (all open transactions in the database need to commit or roll back). Therefore, the log will never contain in-flight transactions like you might see if the power suddenly turned off (i.e., a crash).

The .ldf file is essentially empty? If the .ldf file is not empty what does it contain?

• SIMPLE recovery: the log file will contain the log records describing the CHECKPOINT that occurred before the database was shut down. (A CHECKPOINT also clears the log in this mode.)

• BULK_LOGGED and FULL recovery: the log file will contain all the log records that haven't yet been cleared out. (The only process that clears the log in this mode is a log backup.)

Tip: log records in the log file can be inspected using the undocumented TVF sys.fn_dblog.

If I delete the .ldf file and manually create a new 0 bytes long .ldf file with same name as before, I would be able to attach the database again with no data loss?

Doing this will destroy the file header information, thus corrupting the log file.

To get the database to attach, you would have to remove/rename the log file, and attach the database using ATTACH_REBUILD_LOG to have SQL Server create a new log file from scratch in the default location.

Assuming you did that,

• In SIMPLE recovery, no user data would be lost as all that disappeared were relatively unimportant CHECKPOINT log records.

• In the other recovery models, it would break the log backup chain, and obviously any deleted log records could no longer be backed up (and probably weren't already backed up). This has obvious implications should the database need to be restored.

So... technically, no, there wouldn't be any immediate data loss; however, this method is certainly not a good practice, particularly in a recovery model other than SIMPLE.

-
Thank you very much. I would need to read some more info regarding CHECKPOINT and backups. When in FULL recovery a CHECKPOINT occurs the data is saved into the mdf file too? When doing a full backup, the .bak file is like the mdf file only? A log backup in SIMPLE recovery model has no sense, right? –  Eduard Sep 20 '12 at 21:29
I found this two links very informative. msdn.microsoft.com/en-us/library/ms191164.aspx and msdn.microsoft.com/en-us/library/ms190217.aspx –  Eduard Sep 20 '12 at 21:52
@Eduard: CHECKPOINT always writes dirty data pages to disk, regardless of the recovery model. In SIMPLE recovery, a CHECKPOINT also clears the log. A full backup contains all the data extents, and the required portion of the log to make the data in the backup transactionally consistent as of the time the data-reading portion completed (all recovery models). Correct, a log backup in SIMPLE recovery is not possible. –  Jon Seigel Sep 20 '12 at 22:11

If LDF only contains transactional info, when the database server is stopped and the database detached I don't see any reason for the ldf file to have anything at all.

It sounds like you are missing the importance of historical transaction logs.

The transaction log contains enough information to undo all changes made to the data file as part of any individual transaction.

If you have a record with a value of X, which is modified to a value of Y, this change is recorded in the transaction log. If you simply backup your data file and empty the logs, you have lost the original value of X forever, as there is now only a record of Y. The transaction log file allows you to roll back this change if required.

The transactional log is a record of how you built up the current contents of the data file, and allows you to roll back to any point in time, rather than just the specific intervals at which you backed up your data file.

There are cases where this is important. For instance, transactional databases powering an e-commerce site. You may want to keep backups of the transaction log even if you've backed up the actual data files.

There are cases where it is not so important such as staging tables for a data warehouse, where the "raw data" can simply be loaded from the source thereby starting fresh.

-
Thanks Matt. Now I think I got the point of using FULL (or BULK_LOGGED) as a recovery model! It is not only that allows for "small" files backups (the log backup files), the main point is that allows you to rewind to any point in time! So you have a full auditable database. With SIMPLE you only get what you store in the backup at the exact time you run the backup. –  Eduard Sep 27 '12 at 14:42

The .LDF file will not necessarily be empty. Depending on several factors (including the recovery model of your database, the checkpoint interval, whether you have options like replication or CDC enabled) there could well be transactions in your log that need to either be applied or rolled back.

You could certainly do what you are asking and delete the LDF file, however you do run in to the possibility of losing transaction or ending up with a corrupt database, so do so at your own risk.

-
When database server is stopped and the database files are dettached the LDF still can still contain not finished transactions? –  Eduard Sep 18 '12 at 21:24
Sorry, I should add I'm thinking on Simple recovery model. Sorry –  Eduard Sep 18 '12 at 21:30
Even in simple recovery you could have in flight transactions that would impact things (or as I mentioned replication/CDC etc) –  Nic Sep 18 '12 at 22:18