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

  • Pergunta

  • 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!
    quarta-feira, 5 de novembro de 2008 20:12

Respostas

  • 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.
    quarta-feira, 5 de novembro de 2008 23:10
    Usuário que responde

Todas as Respostas

  • 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.
    quarta-feira, 5 de novembro de 2008 23:10
    Usuário que responde
  • 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.
    quinta-feira, 6 de novembro de 2008 09:26
  • Thanks very much, that's a great explanation and I can relax about this now.

    quinta-feira, 6 de novembro de 2008 15:20
  • 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.
    quinta-feira, 6 de novembro de 2008 22:03
    Usuário que responde
  • 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.

    quinta-feira, 27 de julho de 2017 15:46
  • 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

    • Sugerido como Resposta Olaf HelperMVP sexta-feira, 4 de outubro de 2019 08:12
    • Não Sugerido como Resposta Olaf HelperMVP sexta-feira, 4 de outubro de 2019 08:12
    quarta-feira, 2 de outubro de 2019 18:58
  • 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]

    sexta-feira, 4 de outubro de 2019 08:14