Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

closed as unclear what you're asking by billinkc, Mark Storey-Smith, StanleyJohns, dezso, Marian Sep 11 '13 at 9:18

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

    
What are you asking exactly? If the old dates shown by Windows is a bug of SQL-Server or some other software/hardware failure and you should be worried? Or how to find the last time a database was modified by SQL-Server? Or something else? –  ypercube Sep 11 '13 at 10:50

3 Answers 3

up vote -4 down vote accepted

Since the data files are open while the SQL Server service is running, the last modified date will not change until the SQL Service is restarted.

share|improve this answer
2  
@Akash ok, so, if last modified date only updates when the file handle is closed, do me a favor and force one of your files to grow using DBCC SHRINKFILE or ALTER DATABASE ... MODIFY FILE. Let me know if the timestamp doesn't update. –  Aaron Bertrand Sep 10 '13 at 19:25
2  
@Akash I think it is up to you to prove to everyone else that SQL Server is closing and opening files and this is the only way the timestamp will be updated. See my updated answer. –  Aaron Bertrand Sep 10 '13 at 19:30
2  
Sorry, I have to down-vote this, it is just wrong. The timestamp of the files will change when SQL Server restarts, sure, but "not change until" is completely inaccurate. –  Aaron Bertrand Sep 10 '13 at 19:35
3  
@Akash I have to agree with Aaron Bertrand. My answer was incomplete and misleading. –  KenWilson Sep 10 '13 at 22:09
6  
@AaronBertrand thank you for a more complete, and correct, answer. –  KenWilson Sep 10 '13 at 22:54

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

share|improve this answer
1  
@Akash No. What you state might be true for a Word document or a .txt file, but not for a SQL Server data or log file. –  Aaron Bertrand Sep 10 '13 at 19:14
2  
@Akash I have no idea what you're talking about. SQL Server can absolutely work within its own files without the OS being aware of it. How else do you explain that you've changed contents in the file but the timestamp has not updated? You've selected an answer that isn't accurate - yes SQL Server will update the timestamp when the service restarts, but did the size actually change? No. –  Aaron Bertrand Sep 10 '13 at 19:21
4  
@Akash I don't need to argue with you. You're wrong, plain and simple. –  Aaron Bertrand Sep 10 '13 at 19:25
3  
@Akash why do I have to write a Windows program to convince you that a restart of the SQL Server service is not the only way that a timestamp on a file can change, and that obviously the data file does not change just because you've modified data inside the file (as your question seems to think you assumed)? I think I've provided a pretty detailed answer that explains what is going on, but if you want to go on believing that the file won't update in the OS until the service restarts, go ahead. <shrug> –  Aaron Bertrand Sep 10 '13 at 19:33
8  
@AkashKava - I don't understand why you are arguing about this. It is easy to test that the modified date changes when you grow the file. The process monitor calls for the MODIFY FILE on my machine were as here –  Martin Smith Sep 10 '13 at 19:59

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.

share|improve this answer

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