sql-serverldf

SQL Server: Why isn't the log file up-to-date with the database file on full recovery mode?

Using SQL Server 2012 on full recovery and looking at the directory:

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\

I see .mdf and .ldf files.

For a particular database, the modified times are very different? Why is this? I would have thought that as you modify a database your transaction would be recorded in the ldf file (regardless of the type of query)?

Solution

  • This is correct behavior. The modified dates change when SQL Server closes the files (SQL Server shut down or the database is detached), or when the file is grown (either automatically or manually). All other times, SQL Server essentially bypasses the file system when performing the writes, and so the modified dates aren't updated.