Date of Transaction Log File is Unchanged

Transaction log file date is not getting changed at windows level

Last week I came up with very strange experience and I would like to share this here. I was at client place and one day client asked me that application user is not able to see some of the transactions for one of the server. Applications team were sure that some transactions have been done very recently within fraction of seconds.

Let me tell you that this was the server where 10 days back I moved the data and log files to different LUNs and removed the older LUNSs. After that I renamed the new LUNs drive letter as same as older LUNs. I did this to reduce the space available in the LUNs as most of the space was getting wasted due to small size of databases.

Windows team from client went to the server and looked at the transaction log file date and they found transaction log file date at windows explorer never changed from last 10 days. Quickly they thought somehow transaction is not happening at SQL server level and there are some problems with the database.

Client came to me and started saying please check why transaction log file is not getting changed. There is definitely we missed something when we changed the location of data and log files.

This was the first time I heard this. We DBA never think about changing the date at windows level. For us only thing matters are transactions (insert/update/delete etc.) are happening at database level. To prove transaction is happening or not, quickly I created one table, inserted some data, updated something and retrieved the rows from the table. I found everything was fine but the transaction log file date did not change at that time as well. Still it was showing older date.

That time I was sure that there is no problem in the transactions, there are some other issues that application team has to look into. I asked let me know the data criteria that you are trying to retrieve to. I tried the same and SQL server was able to return the data successfully through SSMS.

Now the question is why transaction log file date is not getting changed even though we are doing some transaction in the database. I was having something in my mind and I tried shrinking the log file with very less space (It is not recommended to shrink the log files in Production until and unless you are very sure what you are doing). The moment I executed DBCC SHRINKFILE, I found transaction log file date got changed.

So here is the conclusion: Insert/update may not change the transaction log file date at windows level. The date will only be changed when there are some changes in the physical size of the file (shrink or truncate etc.). If there is not physical change at file level, date will not change.

Also this is just meaningless as far as DBAs are concerned but sometimes we have to answer the clients immediately.

Please let me know if you have any suggestions/questions/feedbacks on this as I heard this first time in my career.