Please see the below snapshot, showing the timestamps of all the data files as well as transaction log files for one of the SQL Server instances under my management. This is a production instance.
Today is Dec 27th, 2019. However, as you can see, all the timestamps are showing much older times.
The truth is, I took this snapshot a few days ago on 24th Dec. However, as I checked today, the situation is still unchanged.
The snapshot above was taken from the user databases. I checked the system databases as well, which are residing on a separate drive, and took the snapshot of the files' timestamps, which were as shown below:
These databases are certainly not super active. But, I am certain that there are transactions running all the time. Additionally, I looked at the "Schema Changes History" report for a few of them, and could confirm that our maintenance jobs have been working and rebuilding indexes in as recently as yesterday 26th Dec.
The databases are all in FULL recovery model (although that may not be relevant - just a fact to mention). The OS is Windows NT 6.0 (= Windows Server 2007) and SQL Server version is 2008 SP3. Both the OS and SQL Serve are due for upgrade next year Q1. Also as is evident from the above snaps, with data files and log files both residing in the same disk drive and folder, the configuration of this server which I have "inherited" is certainly not the best. So, hopefully all will be fixed during the planned upgrade/migration activity which is going to happen in a few months.
Now the question for me is why the timestamps of these files are not getting updated, despite the databases being active anyway? (even manually running of Checkpoint didn't change anything). and then, if the transaction log files are not getting updated, will the backups be good and reliable? Do they really contain the records that they are supposed to?
modifieddate only changed when the
mdf / ldffiles are closed. Not when transactions are inserted / updated.