locked
Filestream bug? RRS feed

  • Question

  • I am currently running on SQL Server 2012 RC0 (11.0.1750)

    What i did was to create the following database and FileTable table
    CREATE DATABASE [myDB]
     CONTAINMENT = NONE
     ON  PRIMARY
    ( NAME = N'myDB', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\myDB.mdf' , SIZE = 51200KB , FILEGROWTH = 51200KB),
    FILEGROUP [FileStreamGroup] CONTAINS FILESTREAM (name = FileStreamGroup, FILENAME = N'G:\DocumentFileStream')
     LOG ON
    ( NAME = N'myDB_log', FILENAME = N'F:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\myDB_log.ldf' , SIZE = 10240KB , FILEGROWTH = 10%)
    WITH FILESTREAM( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'FileTable' )
    GO

    USE [myDB];
    GO

    CREATE TABLE myFileTable AS FileTable
    WITH
     (
      FILETABLE_DIRECTORY = 'MyFiles',
      FileTable_Collate_FileName = database_default
     );
    GO

    Then i added som files through the file system (share \\server\mssqlserver\FIleTable\MyFiles\ )

    It all works but i discovered that when i start deleting files and I have tried both through the filesystem and t-sql. The files disappears from the share and the filetable as I would expect them to but when I look into the underlying library of my filestream filegroup the documents is still there and is taking up storage space !!

    And note that a select from my filetabel is empty



    Is this a bug or are more tools needed for maintaining or/and maybe even recovery (beside backup/restore)

    Which is the only reason why I would think it would make sense not have everything deleted and space reclaimed.

    Friday, January 13, 2012 2:37 PM

Answers

  • If you delete rows from a table with FILESTREAM, the files will remain on disk, until the log has been backed up. I think you may even have to back up the log twice. Or at least checkpoint after the log backup, I don't remember exactly.

    This is needed to make it possible to do a point-in-time restore to the moment before you deleted the files.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by MortenDvinge Monday, January 16, 2012 1:26 PM
    Friday, January 13, 2012 10:48 PM

All replies

  • If you delete rows from a table with FILESTREAM, the files will remain on disk, until the log has been backed up. I think you may even have to back up the log twice. Or at least checkpoint after the log backup, I don't remember exactly.

    This is needed to make it possible to do a point-in-time restore to the moment before you deleted the files.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by MortenDvinge Monday, January 16, 2012 1:26 PM
    Friday, January 13, 2012 10:48 PM
  • Thanks Erland,

    I didn't recon that filestreaming was a logged operation and didn't think of that.
    Think i read somewhere that securing the files was done by full and fg backups only - but it will not be the first time reading something misleading.

    Tried to create a couple of translog backups but it seems that the files remain.
    It might just be me missing something.

    I'll drop the database and start my testing all over again - at least i now know what to look for.

    Cheers
    /M

     

    Monday, January 16, 2012 1:26 PM
  • Doing some testing I found out that it requires a full backup followed by a translog backup for the files to be removed from the system folder. After the files was deleted from the FileTable.

    Having done several translog backups after the deleting from the FileTable and before the full backup. I can't really figure out why an addtional translog backup after the full backup is needed for the files to be wiped from the system folder (in the file group folder). But it seems to be the case and now that I know I can live with that.

    - or changing the recovery model and do a full backup will do the trick as well - but you all know what that means ;-)

     

    Wednesday, January 18, 2012 12:19 PM
  • I know that Paul Randall has written about this. Check out
    http://www.sqlskills.com/BLOGS/PAUL/category/FILESTREAM.aspx

    Particularly this post is of interst:
    http://www.sqlskills.com/BLOGS/PAUL/post/FILESTREAM-garbage-collection.aspx


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, January 18, 2012 10:34 PM