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