3

This is possibly a dumb question, but it has been triggered by a conversation I'm having with a colleague in our Dev team.

My understanding is that the 'Last Modified Date' of the MDF file (in Windows) is only changed when the Database is Closed/Reopened or data is written to the MDF file causing it to grow (eg. by a Full Backup or Transaction Log Backup, assuming database recovery mode is full)

However I've just noticed our hourly log backups are not changing the date of my .mdf files - they are stuck at the date/time of the last full backup. Shouldn't they be changing hourly for active databases?

What if there was no write activity captured in the log during that hour? Would it not affect the .mdf modified date? And if this is the case, would the modified date for readonly databases (databases that are defunct for write purposes but still used for read purposes) ever change?

Improve this question

1 Answer 1

Reset to default
1

My understanding is that the 'Last Modified Date' of the MDF file (in Windows) is only changed when the Database is Closed/Reopened or data is written to the MDF file causing it to grow (eg. by a Full Backup or Transaction Log Backup, assuming database recovery mode is full)

You are correct for the first part but why would full backup or log backup change "Last Modified" time of mdf file. Its actually reading from the data file its not making any changes. Normally in daily operation SQL Server reads quite often from data file but for every physical read it is not going to update the time stamp or modifiy the date that would be unnecessary.

Moreover the way SQL Server access the data and log file are not that straight it uses different mechanism so I guess its useless worrying about modified date.

What if there was no write activity captured in the log during that hour? Would it not affect the .mdf modified date?

NO it would not

And if this is the case, would the modified date for readonly databases (databases that are defunct for write purposes but still used for read purposes) ever change?

Well it can change when database is taken offline and brought online and after service restart

Improve this answer
5
  • Thanks for your answer. On my first point, I read on SQL Server Central that Log Backups persist the data to the .MDF, thus causing it to grow, which if pushed over the next auto-growth threshold would result in a change to the modified date? My (probably ill-informed) point about the Full Backup was based on the last modified date of the mdf's in my Cluster - all in line with the last full backup. However, I'm now wondering if this is indirectly caused by Veritas NetBackup or the VSS writer... something to explore.
    – Molenpad
    Apr 7, 2016 at 12:21
  • 2
    I must disagree with some of your answers, Shanky, as some are incorrect. Regardless of log write activity, we keep the information about the last checkpoint in the mdf - so that will be written. Log backups also stamp certain information back to the mdf. CheckDB will also cause data to be written to the mdf, full database backups will absolutely change the mdf. Please correct your answers.
    – Sean Gallardy
    Apr 7, 2016 at 17:22
  • @Molenpad We guarantee nothing with the windows timestamps on any files through SQL Server. they have their own mechanisms to update file metadata which we do not touch or tie into. Please do not use the windows file times as a basis for decisions.
    – Sean Gallardy
    Apr 7, 2016 at 17:24
  • @SeanGallardy What you are talking is not documented anywhere so I am not aware what internally various operation does. But does log backup and checkpoint, even if it writes information in mdf, changes timestamp of mdf file ?
    – Shanky
    Apr 8, 2016 at 4:32
  • @Shanky It's not called out, specifically but it is documented (ex: full backups reset differential bases, etc). To answer the windows time question I defer to my second comment which was to Molenpad - your mileage will absolutely vary. It also wasn't explicitly pointed out what filesystem was used (ntfs/refs/etc). The answer is, don't trust the filesystem timestamps for that part of the question.
    – Sean Gallardy
    Apr 8, 2016 at 18:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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