I vaguely remember hearing (maybe ~2007) that Microsoft was starting to use an actual real database for their filesystem, and that they then abandoned the idea. Could this be related to the transaction API you mention, or a separate project? Either way, seeing none of the major OSes go in that direction in the past 10-20 years gives me confidence that it's probably just not practical.
Two different things. Transactional NTFS actually shipped as part of Vista, and still exists in Windows 10, but was rarely used, and is now deprecated.
Vista/Longhorn was also supposed to feature WinFS, which was integrating NTFS with an embedded version of SQL Server, so you could do SQL queries to find files on your filesystem. This was pulled from Vista before release, because it wasn't ready. Some of the enhancements to SQL Server developed as part of the project were released as part of SQL Server 2008 (e.g. FILESTREAM data type), but the core idea was abandoned.
That wasn't the first time Microsoft had tried something like that. In the 1990s, their Cairo project had an object-based filesystem (OFS). That got abandoned as well, although just like WinFS, some of the technologies developed for it actually got released and used. (e.g. COM Structured Storage, which was used as the basis of the file format in older version of Office.)
