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.
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.
Sep 20, 2012 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.
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.
.ldflog 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.