2

As part of the nightly maintenance plan, my working MS SQL Server 2012 databases are backup-ed with options of simple recovery and full backup. Transaction log is not backup-ed. Also, at nights the MSSQLSERVER service or SQL Server is restarted

Checking the the properties of corresponding files in operating system (Windows Server 2008), I observe that "Modified" time of .mdf files correspond to the time of maintenance plan (making backup) run but .log files modification time is one-two weeks earlier.

Why is such difference and why the operating system fails to update modification time?

So, what is the time and reason of .mdf file modification time?
Is it restart of SQL Server or the time of backup?

How can I estimate when a database was last written to a physical disk, that is how recent is .mdf file of a corresponding database?
How can I force SQL Server to flush the database to hard disk in order to have most actualized .mdf file in case of seeing problems in SQL Server functioning?

Improve this question
6

The mdf and ldf files display the last date and time the SQL Server opened the file in question. This is most likely to be the time SQL Server was last restarted.

There is no reason to force SQL Server to flush to disk, it does that automatically during its checkpoint mechanism.

Even if the entire machine crashed during the middle of a write-to-disk operation, when SQL Server is restarted it will run through the log file, rolling forward and backward any operations that were not fully committed to the .mdf file. This is one of the primary tenets of an atomic, consistent, isolated and durable database server.

If you want to manually force a checkpoint operation, you can execute the following command in SQL Server Management Studio, or SQLCMD, etc:

CHECKPOINT

For further information on the CHECKPOINT command, see http://technet.microsoft.com/en-us/library/ms188748.aspx

Regarding your statement at the beginning of your question that you backup the database and not the log file, if your data is business-critical you should enable full recovery on the database, and ensure your log file is backed up several times a day (at least). Backing up the log file assures that you can restore the database to a given point-in-time (most likely the point at which you last performed a log backup). Depending on your business requirements for recovery point and recovery time, you may want to backup the log file as often as every 5 minutes!

For further information on how to correctly implement business-critical backup for SQL Server see http://technet.microsoft.com/en-us/library/hh393536.aspx

Improve this answer
  • Thanks, I know that "Even if the entire machine crashed during the middle of a write-to-disk operation, when SQL Server is restarted it will run through the log file" but it is not obvious in my case since. I suspect, the SQL Server and .mdf are on different hard disks. That could fail separate (in different combinations) – Fulproof Sep 29 '13 at 0:55
  • Where the database files are stored makes no difference to the protective capabilities of SQL Server. – Max Vernon♦ Sep 29 '13 at 0:57
  • I can loose and reinstall SQL Server if I loose disk C: and I am thinking of reusing .mdf file, which is on different hard disk, as the most fast option. Is it wrong? – Fulproof Sep 29 '13 at 1:02
  • That sounds like a separate question. If it is, you should post another question instead of discussing it in comments! Thanks! – Max Vernon♦ Sep 29 '13 at 1:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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