June 20, 2023 at 4:48 pm
I have a database with 2 filestream enabled tables. I am following this article and attempting to turn off filestream in the database. I remove the filestream columns from the tables and turn off filestream for each table (or drop the table entirely) and then attempt to use DBCC SHRINKFILE to empty the file. The article has you do a couple garbage collections and checkpoints and then the error happens when trying to remove the file (note that I have 4 total filestream files each in their own file group). When I attempt to remove any of the 4 files it tells me that they cannot be removed because it is not empty. Any ideas on what additional steps I need to take in order to get that file to be empty so it can be removed?
I have tried performing a full and log backup after the checkpoints but that didn't change anything. I have also tried setting the database to simple recovery as some people reported that resolved their proble, but that didn't help.
We have multiple copies of this database running on a mix of SQL 2017/2019/2022 so I would like to build out a script that would turn off filestream in all databases. Before we perform these steps we'll be moving the data to a different database, so we'll be able to just delete the data in the original database. Thanks in advance.
Edit: Some additional info. The files show their size as 0MB in the properties of the database, but I did go on to the file system and I can see a folder for the data file which contains many files inside of it. The files are all 0kb in size. It feels like these files should not be there if the data file is completely empty, but I can't find a way to clean them up through SQL. As a test I stopped SQL and deleted the files and the database then became "recovery pending". This is just a copy of the DB on my local system so I will restore and keep trying.
June 21, 2023 at 5:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
June 28, 2023 at 4:27 pm
I wanted to post a follow up as I got a script put together to turn off filestream. It's really just a modification of the script from the article I linked in my original post. After the checkpoint statements I added a log backup to a nul device. Doing this has allowed me to remove the files when I was done. Additionally I found that our filegroup had a partition scheme in it. I was able to drop the partition scheme and then that allowed me to delete the filegroup.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply