Search code examples
sql-serversqlfilestream

SQL Server : deleting FIELSTREAM files and num_unprocessed_items

I'm trying to delete the actual binaries from the file system while using SQL Server FILESTREAM.

When doing so (and after removing all records that refers the BINARIES table) and running EXEC sp_filestream_force_garbage_collection the num_unprocessed_items is increasing and the actual binary still exists in the FS.

For the sake of this post BINARIES is the table holding the blobs.

I'm calling 

DELETE FROM BINARIES

and then I'm twice calling 

EXEC sp_filestream_force_garbage_collection

What can be the reason and if possible, I'll be glad to get a step by step explanation (I'm not a DBA).

Thanks, R.

Solution

  • I verified my comment is correct. You have to perform a backup on your database before you can successfully garbage collect on it. See https://technet.microsoft.com/en-us/library/gg492195(v=sql.110).aspx

    "Items may be unprocessed for various reasons, including the following: Files that need to be pinned down because Log backup or CheckPoint has not been taken."