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?

(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.

StackExchange Database Administrators Q#24475, answer score: 6