locked
MDF & LDF files last modified date not increasing as expected RRS feed

  • Question

  • Hi,

    I have a SQL 2005 database whose restore model is set to simple.  This is because there will not be many changes to the data, usually just once per week or occasionally during the week.  A backup is taken once per day using the INIT option in order to overwrite.

    The restore model was set to full but but the log file was getting large so this was changed to simple and the log file shrunk. 

    Just stating this here in case it is relevant.

    When I perform an update table command on a row in one table just as a test, the modified dates for the MDF and the LDF files are not changing, although the update does appear to have been applied when I re-query the table. 
    The MDF modified date is for about one week ago and the LDF modified date about an hour prior to the test update statement.  So (finally) my question is, is this correct behaviour?

    Many thanks!
    Wednesday, November 5, 2008 8:12 PM

Answers

  • This is correct behaviour.  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.
    Wednesday, November 5, 2008 11:10 PM
    Answerer

All replies

  • This is correct behaviour.  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.
    Wednesday, November 5, 2008 11:10 PM
    Answerer
  • Thanks Jim for pointing it out. Many of them have this question in mind and they are wrongly understood that db not used based on last modified date.
    Thursday, November 6, 2008 9:26 AM
  • Thanks very much, that's a great explanation and I can relax about this now.

    Thursday, November 6, 2008 3:20 PM
  • Just to add a bit of reasoning to my explanation, in an ideal disk configuration, the log and data files are on separate physical disks, and (especially for the log) you want to avoid jumping all over the disk.  Assuming no fragmentation in the log file, the log is written to disk in a continuous fashion, and the disk head doesn't need to move.  If you wanted the Modified datetime to be updated whenever the file is changed, the disk head would need to move to the position in the FAT where this value is stored and change it after each update, and then move back into position to write the next entry to the log.  

    I suppose SQL Server could update it every 10 minutes, but it's an overhead that doesn't really give you anything.
    Thursday, November 6, 2008 10:03 PM
    Answerer
  • Hi, 

    In my application, I am taking full backup of database files (mdf and ldf)by SQL writer. and in incremental backup I am checking modified database files (mdf and ldf)and backup it. newly added databases get backed up, but system databases not, so after recovery on another machine, newly added databases are detached.  I want to modify database files when performing the writes to SQL server.

    Thursday, July 27, 2017 3:46 PM
  • This post is very old, but the behaviour is still the same, so just to point out: i had the same concern but thought modification date would not be current due to buffer/cache/unwritten data.

    Let's say sql server crashes, will there be unwritten data that would leave the database corrupted or inconsistent?

    I see in a daily used database, modification time is 3 days ago, really don't care about the date, like as you said it's not updated to save disk heads, but can i be confident all the data is written down to the disk?

    Regards

    • Proposed as answer by Olaf HelperMVP Friday, October 4, 2019 8:12 AM
    • Unproposed as answer by Olaf HelperMVP Friday, October 4, 2019 8:12 AM
    Wednesday, October 2, 2019 6:58 PM
  • Let's say sql server crashes, will there be unwritten data that would leave the database corrupted or inconsistent?

    That can happen e.g. on a power outtake without having a USV, but that's the way computer works.

    SQL Server Bypass the OS to read& write to disk, so the file update timestamp is no indicator.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, October 4, 2019 8:14 AM