Time stamps of MDF and LDF Database files

Posted on 2007-07-20
Last Modified: 2012-08-13
I found one thing needs to be clarified in SQL 2005 server is when the time stamp of the MDF and LDF file being updated.

The reason why I am asking is that I noticed in a busy SQL environment, the time stamps of all the databases's MDF and LDF are many days behind, or even hardly updated since it is created over month ago. To my understand the LDF file should be updated once there is a log committed and the MDF file should be updated once the transactional log file backed up or the log truncated,  therefore the MDF and LDF files' time stamps should be updated accordingly.

This include the system databases and user databases.

Can you explain why the time stamps of these files are far behind?
Question by:fshguo
    LVL 40

    Expert Comment

    by:Vadim Rapp
    because timestamp is updated when the file is closed. If your server is up all the time, it keeps those files open, so timestamp is not updated.

    Author Comment

    But I do see the time stamp on one of the MDF files was updated, however the server was not reboot during that period. what else could cause the time stamp changed rather than reboot or stop the SQL service?
    LVL 40

    Accepted Solution

    The timestamp is changed not by sql server but by windows. If it's changed, it means that sql server closed the file. Why - only sql server programmer might tell. For example, it regularly opens and closes tempdb.

    One guess would be that this database has option "auto-close", i.e. sql server closes it automatically after period of inactivity.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now