SQL Server MDF Last Modified Date – Impact of Transaction Log Backup

sql server

This is possibly a dumb question, but it has been triggered by a conversation I'm having with a colleague in our Dev team.

My understanding is that the 'Last Modified Date' of the MDF file (in Windows) is only changed when the Database is Closed/Reopened or data is written to the MDF file causing it to grow (eg. by a Full Backup or Transaction Log Backup, assuming database recovery mode is full)

However I've just noticed our hourly log backups are not changing the date of my .mdf files – they are stuck at the date/time of the last full backup. Shouldn't they be changing hourly for active databases?

What if there was no write activity captured in the log during that hour? Would it not affect the .mdf modified date? And if this is the case, would the modified date for readonly databases (databases that are defunct for write purposes but still used for read purposes) ever change?

Best Answer

My understanding is that the 'Last Modified Date' of the MDF file (in Windows) is only changed when the Database is Closed/Reopened or data is written to the MDF file causing it to grow (eg. by a Full Backup or Transaction Log Backup, assuming database recovery mode is full)

You are correct for the first part but why would full backup or log backup change "Last Modified" time of mdf file. Its actually reading from the data file its not making any changes. Normally in daily operation SQL Server reads quite often from data file but for every physical read it is not going to update the time stamp or modifiy the date that would be unnecessary.

Moreover the way SQL Server access the data and log file are not that straight it uses different mechanism so I guess its useless worrying about modified date.

What if there was no write activity captured in the log during that hour? Would it not affect the .mdf modified date?

NO it would not

And if this is the case, would the modified date for readonly databases (databases that are defunct for write purposes but still used for read purposes) ever change?

Well it can change when database is taken offline and brought online and after service restart

Related Solutions

Sql-server – SQL Server transaction log backups: test whether tail log follows last known log backup

I turned to my copy of SQL Server 2008 Internals and the DMV sys.database_recovery_status was pointed out to find the first LSN of the next log backup. Which going by BOL the column last_log_backup_lsn provides you with:

Log sequence number of the most recent log backup. This is the end LSN of the previous log backup and the starting LSN of the next log backup.
NULL= No log backup exists. The database is offline or the database will not start.

Just to mention as well that Kalen also brings up the point that you will get a NULL value if the database is in SIMPLE recovery mode (autotruncate mode) or if no log backup exist.

But how can I obtain the FirstLSN of the tail log?

Without actually backing up the tail log of a database (don't have an test instance to try this on) you could logically conclude that the value returned in the column mentioned would be the first LSN of the next log backup, in your case the tail.

So executing the following will return the value I believe you are looking for:


SELECT 
   last_log_backup_lsn
FROM 
   sys.database_recovery_status
WHERE 
   databse_id = DB_ID('MyDb')

This DMV is available starting in SQL 2005.

EDIT
Unless you read the BOL link, please note that this DMV will only return values to databases that are online, or opened as BOL references it. If a failure occurs that requires you to take a tail log backup of a database you will not be able to verify this value through the above code unless the database is accessible; which in a failure it probably would not be.

SQL Server – Managing Transaction Log Backups from Two Different Sources

I realized that there are two sources taking full back ups overnight and hourly transaction log backups: TDP (part of IBM's TSM) and a regular SQL job.

If this is the case then, you will end up breaking your log chain and a point in time recovery would not be possible.

Either use TDP (part of IBM's TSM) or a regular SQL job to back-up Transaction logs.

Also, I would suggest you to simulate a recovery scenario on a test server with either TDP or using SQL Native backups (Full backup, differential (if any) and then subsequent log backups) to ensure that in an unfortunate situation of disaster, you would be able to recover your databases.

Note: Full backup wont break the log chain.

Best Answer

Related Solutions

Sql-server – SQL Server transaction log backups: test whether tail log follows last known log backup

SQL Server – Managing Transaction Log Backups from Two Different Sources

Related Question