5

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.

enter image description here

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:

enter image description here

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? :)

  • the modified date only changed when the mdf / ldf files are closed. Not when transactions are inserted / updated. – Squirrel Dec 27 '19 at 9:01
  • @Squirrel Is that a feature of Windows or is it something specific to Sql Server? I always thought whenever a file gets changed, its attributes are changed, too, without needing to close the file handle (similar to how a word processor application works) – RGO Dec 27 '19 at 9:07
  • 2
    SQL Server keeps the database files open whenever it’s running, for better performance and protection against other processes changing them. So the attributes don’t get changed on every file write. – David Browne - Microsoft Dec 27 '19 at 15:12
10

In answer to your main question ("what is being backed up"), everything that is supposed to be 😀 More seriously, you can't use those time stamps as a sign of changes happening in the database files. SQL Server doesn't make any guarantees about how the modified date of database files will be changed or updated.

There is an interesting blog post here that attempts to find patterns with when and how the date modified stamp is updated on MDF files:

SQL Server Database File - Date Modified

Their observations line up with your situation I think: normal writes to the data file don't update the date modified stamp. It looks like growth events (manual or automatic) would update that value, as would restarting the service (or anything else that might "open" or "close" a database - using the autoclose feature, explicitly taking the DB offline, etc).

I expect 19/12/2019 9:52 PM corresponds with a Windows or SQL Server restart, which is how several of the date / times got sync'd up.

However, again, this behavior is undocumented. Any patterns we notice could change at any time, or have unexpected edge cases.

For SQL Server data files residing on an NTFS file system, this applies:

The only guarantee about a file time stamp is that the file time is correctly reflected when the handle that makes the change is closed.

Though, to be clear, that's a guarantee provided by Windows. We can't necessarily know a ton about how SQL Server creates and destroys file handles in every scenario.

David Browne (a Top Microsoft Man) mentioned this in a comment, which is useful to know:

SQL Server keeps the database files open whenever it’s running, for better performance and protection against other processes changing them. So the attributes don’t get changed on every file write.

  • Yes - "19/12/2019 9:52 PM" is the time when the SQL Server service was restarted last. Thanks for your answer. – RGO Dec 30 '19 at 2:01
  • 1
    @RGO You're welcome! Glad I could help. – Josh Darnell Dec 30 '19 at 21:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.