Ghost Transaction Log File

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