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,to be exact. However, as I checked today, the situation is still unchanged.
The snapshot above was taken from the user databases. I checked the system databases which are residing in a separate folder on a separate drive, and for them, the situation was slightly different:
Now, these databases are certainly not super active. But, I am certain there are transactions running in them all the time. Besides, I looked at the "Schema Changes History" report for a few of them, and confirmed our maintenance jobs have been rebuilding indexes in as recently as yesterday - 26th Dec.
The databases are all in FULL recovery model (although that may not be relevant - regardless that's just a fact). The OS is Windows NT 6.0 (i.e. Windows Server 2007) and SQL Server version is 2008 SP3. Both the OS and SQL Serve are due for upgrade next year Q1. Also, the configuration on this server which I have inherited is not the best, with data files and log files residing on the same folder. So, hopefully all will be fixed during the upgrade/migration activity in a few months.
Now the question for me is, why despite having transactions, these files are not changing? Even I manually ran "Checkpoint" but that didn't change anything. So, why is that? and then, IF the transaction log files are not changing, then are my backups good? Do they really contain what they are supposed to?
or, am I missing something, perhaps due to getting older? :)