MelDBA (10/20/2016)
Hi All,I have experienced a very bizarre issue which caused a big mess in my production environment.
There are a number of databases on one SQLServer instance. But 3 database are impacted
DB1 and DB2 are in always on group and has FileStream. FileStream has different logical name and it is on different drives with different folder name. DB1 is log shipped to another server. Application used to store documents/images on the FileStream of DB1. But now same application store documents/images on DB2 but DB1 still has old documents/images in there. Point here is FileStream of DB1 wasn’t supposed to grow at all because new documents/images are going to DB2.
DB3 which was restored from backup of DB1 hence logical name for FileStream is same as of DB1 but physical name is different and it is on different drive.
Now what happened FileStream folder for DB1 got filled up so do the log shipped DB’s folder. Which wasn’t supposed to grow. Than FileStream folder for DB2 and DB3 got filled up as well. Basically I can see new sub folders within FileStream folder.
Interestingly when I check properties of the database it still shows FileStream file is 500GB but drive is 700GB and it is full. This is the case with all 3 databases. If total size of the files doesn’t add up in the properties of the database than how these files impacted the log shipped database?
Also I can delete that new subfolder from FileStream folder at filesystem level and database is functioning as normal.
Also one more thing I would like to mention SQL Agent job for log backup for log shipping has status failed but I can see the backup file at filesystem. In SQL Logs
The operating system returned the error '112(There is not enough space on the disk.)' while attempting 'FsLogMgr::LogInternal:CreateFile' on '\?\X:\XXX_Filestream\$FSLOG\fffd7c1f-ffffb60b-ff60.000283e0-000049f4-009f.1e295b92-01e0-4381-9a8a-e8439db1e3da.0-0.1000016'.
This is the FileStream for DB1 which should be used to store new documents. Also log backup were 2GB + moment I made some free space in the FileStream folder next log backup was only 4MB why is that? what is the role of FileStream Folder in log backup?
When I moved that extra folder status of log backup job is success
Please help me understand what happened there.
Thanks
FWIW...It seems just to be a disk space issue a lot of people run into when using Filestreams.
With Filestreams, an update will be a delete and insert. And the creation of a new file. The old files get cleaned up but it's a bit of a delay, managed by garbage collection. It's not unusual to see the database file size smaller than what's on disk due to those other files.
This chapter from Microsoft SQL Server 2012 Internals explains it pretty well - this link starts on page two, go to the Logging FILESTREAM changes section and read on from there. It does explain the garbage collection process as well:
https://www.microsoftpressstore.com/articles/article.aspx?p=2225060&seqNum=2
They do explain how to do the checkpoints with the log backups but I can't remember if they mention that there is also a stored procedure new in 2012 to force the garbage collection: sp_filestream_force_garbage_collection
That's something you may want to look at as well.
Sue