July 6, 2009 at 1:40 am
Check out that Whitepaper I mentioned. I don't know it's location offhand, but a search for "Paul Randal" and filestream should turn it up.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 6, 2009 at 6:16 am
Thanks for all the responses. After a bit more hunting around I found some info about the recovery mode. I had set it to "Full" and didn't realise that this would prevent the files being deleted, although that does make sense.
Anyway I've tried doing a full-backup, as well as differential, and log backup, and still the files persist. However I have only just now tried running "checkpoint", so hopefully that will do the trick.
That is the issue because most employers don't give developers access to running log backup during a delete operation because that is the work of the data team and when there are large files simple recovery mode may not be acceptable. Microsoft needs to change the implementation so it will not be recovery mode and log backup dependent.
Kind regards,
Gift Peddie
July 6, 2009 at 6:34 am
Gift Peddie (7/6/2009)
That is the issue because most employers don't give developers access to running log backup during a delete operation because that is the work of the data team and when there are large files simple recovery mode may not be acceptable.
For good reason. Ad-hoc log backups taken by developers would make database recovery almost impossible if a restore needs to be done.
If the db is in full recovery, there need to be regular log backups anyway.
Microsoft needs to change the implementation so it will not be recovery mode and log backup dependent.
How would that be possible when the old and new versions are needed within the log backup for standby systems/database recovery?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 6, 2009 at 7:04 am
How would that be possible when the old and new versions are needed within the log backup for standby systems/database recovery?
One important factor to point out is the buffer size used by the SMB protocol that is used for buffering reads of FILESTREAM data. In testing using the Windows Server® 2003 operating system, larger buffer sizes tend to get better throughput, with buffer sizes of a multiple of approximately 60 KB. Larger buffer sizes may be more efficient on other operating systems.
This is not the first time feature implementation is changed to improve development use the .NET filestream gets actually garbage collected SSIS uses it, this is for development move it out of the relational engine. The above is from Pauls article which by the way addressed most of what I am talking about and also made clear the other issues are beyond the scope of the article. I think the above is relevant because I have said this is almost like Shadow volume and I still think it is because development and SMB the Microsoft and Intel developed protocols are usually not related.
Why is SMB port required to be opened for something in SQL Server relational engine? There are many unanswered questions Microsoft needs to answer.
http://msdn.microsoft.com/en-us/library/cc949109.aspx
Kind regards,
Gift Peddie
July 7, 2009 at 7:08 am
Something still appears to be wrong. The files are not being removed, despite having run log, differential, and full backups, as well as running checkpoint.
Has anyone else encountered this? Is it something to do with the Express version, or perhaps Vista instead of 2003 or 2008 Server?
July 7, 2009 at 9:39 am
Something still appears to be wrong. The files are not being removed, despite having run log, differential, and full backups, as well as running checkpoint.
Has anyone else encountered this? Is it something to do with the Express version, or perhaps Vista instead of 2003 or 2008 Server?
I am sorry I did not get this post I don't think it is related to either try a log backup again because it is related to database recovery and consistency. Now I have seen if you are in simple recovery model it actually goes away in Vista x86 SP1.
Kind regards,
Gift Peddie
July 9, 2009 at 1:50 am
I tried changing from full, to simple recovery mode, and immediately the files were removed. Worthwhile to know, but it would be good to get it working correctly in full recovery mode.
August 11, 2010 at 12:49 pm
I realize this thread is over a year old now, but in case its of help to anyone; in full recovery mode the filestream files get removed after a checkpoint after a backup as Gila Monster said. If you check the SQL Errorlog, you might find that the folder was unable to be removed because it was in use by another process - this could be a command prompt, explorer window, av software, etc.
The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'RemoveDirectory' on 'C:\dumps\testfs1\7127d589-d9ed-4f4a-8ca5-ee2d8d758250' at 'fsdelhdlr.cpp'(500).
-Ken
June 18, 2024 at 6:43 pm
I realize this is an old post, but it might help someone who is just starting with filestream. I found this to force garbage collection:
--clean up any delete files
EXEC sp_filestream_force_garbage_collection @dbname = N'FileStreamTest'
, @filename = N'FileStreamTestFStream';
I found it in this document
June 18, 2024 at 6:49 pm
I realize this is an old post, but I found this and it might help anyone who comes across this question. Here's an example to force garbage collection:
--clean up any delete files
EXEC sp_filestream_force_garbage_collection @dbname = N'FileStreamTest'
, @filename = N'FileStreamTestFStream';
From this website:
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply