I was doing some experiments with new filetable feature introduced in SQL 2012 and I noticed something strange which is clarified in this blog.
The filetable has an internal schema table which gives us the details on the files and folders that is contained in the directory which it points to. We could use a simple t-sql select select statement like below
SELECT name,file_type,cached_file_size,last_access_time,is_directory FROM MyFileTable
To get details on filestream data stored inside it. I've included only subset of columns which is of our interest in above query. A typical resultset would be as below
As you see from above it gives you list of files and folders with their size,type etc attribute details. One field to note here is the last_access_time which is supposed to give you last datetime value when this particular file/folder was accessed.
I was playing around this column to identify recently accessed files within a folder when I realised that value of this column seemed to be unaffected regardless of whether I tried to access files either through file system or through t-sql. This really baffled me as I expected the field to reflect correct the values based on file access. After some research and asking in forums I finally got the reasoning behing this behaviour (thanks to Paul White). This is in accordance with the setting at the operating system (Windows) level. By default logging of last access timestamps on NTFS file systems is disabled in most of recent OS versions like Win Vista, Win Server 2008 R2 etc and that has to be enabled for us to track the access times of files and get the details through above schema table.
The method to enable this is explained in below link using fsutil feature
http://technet.microsoft.com/en-us/library/cc785435(WS.10).aspx
So please keep in mind that unless you enable this you cant rely upon data returned by the above column for any internal file manipulations.
The filetable has an internal schema table which gives us the details on the files and folders that is contained in the directory which it points to. We could use a simple t-sql select select statement like below
SELECT name,file_type,cached_file_size,last_access_time,is_directory FROM MyFileTable
To get details on filestream data stored inside it. I've included only subset of columns which is of our interest in above query. A typical resultset would be as below
As you see from above it gives you list of files and folders with their size,type etc attribute details. One field to note here is the last_access_time which is supposed to give you last datetime value when this particular file/folder was accessed.
I was playing around this column to identify recently accessed files within a folder when I realised that value of this column seemed to be unaffected regardless of whether I tried to access files either through file system or through t-sql. This really baffled me as I expected the field to reflect correct the values based on file access. After some research and asking in forums I finally got the reasoning behing this behaviour (thanks to Paul White). This is in accordance with the setting at the operating system (Windows) level. By default logging of last access timestamps on NTFS file systems is disabled in most of recent OS versions like Win Vista, Win Server 2008 R2 etc and that has to be enabled for us to track the access times of files and get the details through above schema table.
The method to enable this is explained in below link using fsutil feature
http://technet.microsoft.com/en-us/library/cc785435(WS.10).aspx
So please keep in mind that unless you enable this you cant rely upon data returned by the above column for any internal file manipulations.
No comments:
Post a Comment