locked
mdf and ldf file dates are not current RRS feed

  • Question

  • I have a general question about the date stamp on SQL Server database files as I've noticed the Date Modified value is no where near the current time.
    If I check in the \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data directory the master, model and msdb mdf and ldf files are they are all old with the exception of the templog.ldf file.
    Is that how it works?  Even the templog.ldf file is from 8:56 am on an active server.

    Just puzzled as I'm used to oracle's datafiles always having the current time.
    Friday, July 31, 2009 10:06 PM

Answers

  • I think this is how it works:

    The 'Date Modified' attribute of the file changes if the physical file (not the data inside!) has to grow or it's being shrinked. Also it changes when you stop SQL Server AND the database has been accessed. The last part is important because if the database has its 'Auto Close' option set to 'True', then it won't be opened unless you access it after which it gets closed again.
    Again, you won't see that attribute changing during data modifications unless the changes trigger file size change. I understand it's a little confusing because you consider that data modifications still change the physical file even if it doesn't trigger file size change so the attribute should get updated too. TEMPDB is a little different - the same rules apply, also it gets re-created every time you start your SQL Server.

    To summarize:
    - when you start your SQL Server, usually only TEMPDB data and log files will have the above mentioned attribute changed
    - during SQL Server operations, the attribute will change if the database files have to grow or to shrink. If the database has 'Auto Close' set to 'True' then it will be updated when SQL Server closes the database which happens when no process is using it anymore.
    - when you stop SQL Server , the attribute will change for all databases which were opened with Write access and not closed yet.

    SQL Server ERRORLOG records the event when SQL Server starts the database. The entry looks like this: "Starting up database '...'.".


    Thanks,
     
    Varsham Papikian, New England SQL Server User Group Executive Board, USA: www.nesql.org; http://www.linkedin.com/in/varshampapikian
    • Marked as answer by mjdbhouse Tuesday, August 11, 2009 3:08 PM
    Saturday, August 1, 2009 5:04 AM

All replies

  • I think this is how it works:

    The 'Date Modified' attribute of the file changes if the physical file (not the data inside!) has to grow or it's being shrinked. Also it changes when you stop SQL Server AND the database has been accessed. The last part is important because if the database has its 'Auto Close' option set to 'True', then it won't be opened unless you access it after which it gets closed again.
    Again, you won't see that attribute changing during data modifications unless the changes trigger file size change. I understand it's a little confusing because you consider that data modifications still change the physical file even if it doesn't trigger file size change so the attribute should get updated too. TEMPDB is a little different - the same rules apply, also it gets re-created every time you start your SQL Server.

    To summarize:
    - when you start your SQL Server, usually only TEMPDB data and log files will have the above mentioned attribute changed
    - during SQL Server operations, the attribute will change if the database files have to grow or to shrink. If the database has 'Auto Close' set to 'True' then it will be updated when SQL Server closes the database which happens when no process is using it anymore.
    - when you stop SQL Server , the attribute will change for all databases which were opened with Write access and not closed yet.

    SQL Server ERRORLOG records the event when SQL Server starts the database. The entry looks like this: "Starting up database '...'.".


    Thanks,
     
    Varsham Papikian, New England SQL Server User Group Executive Board, USA: www.nesql.org; http://www.linkedin.com/in/varshampapikian
    • Marked as answer by mjdbhouse Tuesday, August 11, 2009 3:08 PM
    Saturday, August 1, 2009 5:04 AM
  • Thanks Varsham.  Sorry to post a question and then go away on vacation
    • Edited by mjdbhouse Tuesday, August 11, 2009 4:49 PM typo
    Tuesday, August 11, 2009 3:08 PM