I’ve encountered a very intriguing case with transaction log files in SQL Server 2014. Maybe it is present in earlier version.
I open a database properties, click Files and then I see two transaction log files. But when I look at Windows folder I see only one. Is it funny?
Now I’ll tell you how it was happened.
I’ve created a database with a fixed size transaction log file, then add a second log file. I’ve inserted some stuff data, made some actions for the second log files would be truncated. After that I’ve removed the second log file.
As a result physical log file was really deleted, but it’s still present in SQL Server. When you look at sys.database_files you can notice that state for the second log file is different from the first.
Script to repeat this case is ghost_transaction_log_file