cannot remove FILESTREAM file

Question

cannot remove FILESTREAM file

emiranda 59653

Ten Centuries

Points: 1096
More actions
August 1, 2013 at 7:52 am

#280740

Hello, we just recently migrated to SharePoint 2013 and I'm trying to move around FILESTREAM files on all of our content databases (we're running SQL Server 2012) and I'm getting an error when deleting a FILESTREAM file even if it's empty. The content database is in simple recovery mode and I've emptied the file and confirmed that it is empty but dropping the file brings an error saying it is not empty:
USE WSS_Content_20122013_Classes;
GO
CHECKPOINT;
EXEC sp_filestream_force_garbage_collection @dbname = N'WSS_Content_20122013_Classes';
DBCC SHRINKFILE (RBSFilestreamFile, EMPTYFILE);
ALTER DATABASE WSS_Content_20122013_Classes REMOVE FILE RBSFilestreamFile
Here's the error I get:
File ID 1 of database ID 7 cannot be shrunk as it is either being shrunk by another process or is empty.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 5042, Level 16, State 13, Line 4
The file 'RBSFilestreamFile' cannot be removed because it is not empty.
Any idea on how to resolve this?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

emiranda 59653 Ten Centuries Points: 1096 More actions · Answer 1

Fixed the problem. I'm not too confident what I did is the best way to resolve the problem but since I saved snapshots of the DB, server and what I'm doing is mostly for archive purposes I went ahead with it.

I noticed that even after doing the DBCC Shrinkfile command, the files are still existing on the filesystem albeit they are all 0 bytes. I went ahead and deleted them on the file system (since the file was moved to a different FILESTREAM container with the DBCC shrinkfile(db, emptyfile) command) and was able to drop the container using alter database remove file.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

You have to remove the filestream column in the table. Or point it to a new filestream filegroup.

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 3

emiranda 59653 (8/1/2013)
I noticed that even after doing the DBCC Shrinkfile command, the files are still existing on the filesystem albeit they are all 0 bytes. I went ahead and deleted them on the file system.

Congrats, you have just corrupted your database!

In case someone else finding this thread, I like to point out that you should never meddle directly in the filestream directory, unless told so by a support professional.

For garbage collection to do its work, you need to take a couple of full backups and CHECKPOINT in between.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

emiranda 59653 Ten Centuries Points: 1096 More actions · Answer 4

Hello Erland, the couple of backups applies to full or bulk-logged recovery models which is why I changed the recovery model to simple so a single checkpoint will be enough to invoke the garbage collection. Also, I did a CHECKDB before and after the operation.

Again, what I was doing was moving from one FILESTREAM container to another for archive purposes. I verified that the files are all in the new FILESTREAM container and after garbage collection and emptying the old FILESTREAM container the size of the old container was 0, well 1kb for the filetream.hdr file. There were a bunch of guid folders with guid files all sized 0kb. I deleted those folders and files manually and performed a ALTER TABLE REMOVE FILE statement to drop the FILESTREAM container.

So I don't think my DB is corrupt, as all references to the container is gone on the database as well as it is gone on the file system. Worst case I can think of is I've lost data on the container I dropped that was not moved to the other container by the DBCC SHRINKFILE(db, EMPTYFILE) command.

emiranda 59653 Ten Centuries Points: 1096 More actions · Answer 5

Also, the database is made read-only after so no changes are made to the database but needs to be accessible for reference purposes (which is why I needed to move the filestream container in the first place).

Anyway, enough justification and back to the original question. What should you do if after doing a DBCC SHRINKFILE and multiple calls to the garbage collector and backups and checkpoints in between and you still can't remove the container because SQL Server is saying it is not empty?

Steve mentions dropping the filestream column but I don't think that applies to this case because I'm not disabling filestream and have another container. I would prefer not going to the file system but any alternatives? By the way, I looked at DMVs for the database to see if there's any tombstone and there is none. Is there a dmv or system function or value to find out how SQL knows a filestream container is empty or not? As I said, deleting the guid files and folders (not the filestream.hdr file, $FSLOG and $FSGC folders) that are sized 0 bytes worked but what is the alternative?

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 6

Ahh, sorry. I misread. didn't realize you needed the other filestream container. I was thinking you wanted it removed completely from the db.

I'll have to look at this more. I haven't tried it at all, but I'd think there's a way. You might ping Jacob Sebastian (runs beyond relational.com). He's written a lot on Filestream for a book he did.