Wrong Last Modified Date of .ndf file on SQL Server

Question

I have SQL Server Database and we have many tables. To improve performance, I have created different filegroups with files on different disks just for indexes. I have created new index today in a different file group. However, I still see last modified date of file as 1 month in the past. I have checked file names and I am worried that SQL Server is not setting Last Modified date of file. The new index works fine, but the file still does not show me a more recent modified date.

Ok I have just learned something new, none of the files in SQL Server's Data directory shows last modified date to recent date while, my database is continuously updated. All dates are 1 month old. The server's system clock is correct.

Answer 1

Think of the data file as a container, like your fridge. The fridge doesn't tell you when the milk is bad (ok, maybe the Jetsons' fridge does) - you need to check inside.

For SQL Server, the timestamp in Windows has nothing to do with the data you are changing inside the file; it has to do with the last time the file itself has changed, such as a service restart, or - more commonly - a growth or shrink event (either user- or system-initiated). Which could happen when you've changed data inside the file, but won't happen every time (unless you've set autogrowth to 1 MB and you are constantly adding a lot more data than that). If you created an index and there was available space in the file, it can use that existing space without changing the container, and thus Windows does not have any reason to know about it, never mind update the timestamp.

In short, you should stop worrying. This is just how SQL Server works. And here is a demonstration of the timestamp updating without shutting down the SQL Server service:

enter image description here

Answer 2

SQL Server does not use the default access path when opening the database files. Instead it uses its own low-level driver to get the highest performance possible. As a result of that you cannot rely on any information that windows is displaying about the files. instead consult the appropriate system views and DMVs.

I am however not aware of any way to determine the last write-time for a particular file through SQL.