Sql-server – How to flush SQL Server database from RAM to hard disk

backupsql servertransaction-log

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?

Best Answer

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

Related Question