{"id":2005,"date":"2020-08-20T19:39:25","date_gmt":"2020-08-20T19:39:25","guid":{"rendered":"http:\/\/www.theSQLReport.com\/?p=2005"},"modified":"2020-08-20T19:42:28","modified_gmt":"2020-08-20T19:42:28","slug":"filestream-high-number-in-the-num_unprocessed_items-column-after-running-garbage-collection","status":"publish","type":"post","link":"https:\/\/www.theSQLReport.com\/?p=2005","title":{"rendered":"FileStream &#8211; High Number In The Num_Unprocessed_Items Column After Running Garbage Collection"},"content":{"rendered":"<p>First, I am by no means a FileStream expert.\u00a0 I am a DBA that was thrown a FileStream support ticket.<\/p>\n<p class=\"yiv7537431139MsoNormal\">Problem, the developer ran the delete from the filestream table, but the files were still on the operating system disk taking up space.<\/p>\n<p class=\"yiv7537431139MsoNormal\">When I first ran the sp_filestream_force_garbage_collection, I saw that I had a high number in the <em>num_unprocessed_items <\/em>column<em>.<\/em><\/p>\n<p class=\"yiv7537431139MsoNormal\"><em> <a href=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2020\/08\/sp_filestream_force_garbage_collection_01.jpg\" target=\"_blank\" rel=\"noopener noreferrer\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-2006 size-full\" src=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2020\/08\/sp_filestream_force_garbage_collection_01.jpg\" alt=\"\" width=\"860\" height=\"233\" \/><\/a><\/em><\/p>\n<p class=\"yiv7537431139MsoNormal\">At that point I took a full backup of the database.\u00a0 Then I ran the sp_filestream_force_garbage_collection, and saw that number moved to the num_collected_items column.<\/p>\n<p class=\"yiv7537431139MsoNormal\">\u00a0At that point I took a log backup, and again reran the sp_filestream_force_garbage_collection. \u00a0It took a couple of seconds to process, but then returned zeros for all the columns.\u00a0 Also the space was freed up on the drive.<\/p>\n<p><a href=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2020\/08\/sp_filestream_force_garbage_collection_02.jpg\" target=\"_blank\" rel=\"noopener noreferrer\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-2007 size-full\" src=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2020\/08\/sp_filestream_force_garbage_collection_02.jpg\" alt=\"\" width=\"855\" height=\"229\" srcset=\"http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2020\/08\/sp_filestream_force_garbage_collection_02.jpg 855w, http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2020\/08\/sp_filestream_force_garbage_collection_02-300x80.jpg 300w, http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2020\/08\/sp_filestream_force_garbage_collection_02-768x206.jpg 768w, http:\/\/www.theSQLReport.com\/wp-content\/uploads\/2020\/08\/sp_filestream_force_garbage_collection_02-210x56.jpg 210w\" sizes=\"(max-width: 855px) 100vw, 855px\" \/><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>First, I am by no means a FileStream expert.\u00a0 I am a DBA that was thrown a FileStream support ticket. Problem, the developer ran the delete from the filestream table, &hellip; <a class=\"readmore\" href=\"https:\/\/www.theSQLReport.com\/?p=2005\">Continue Reading &rarr;<\/a><\/p>\n","protected":false},"author":1,"featured_media":2006,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[91,192,191],"class_list":["post-2005","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-server","tag-filestream","tag-num_unprocessed_item","tag-sp_filestream_force_garbage_collection"],"_links":{"self":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/2005"}],"collection":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2005"}],"version-history":[{"count":4,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/2005\/revisions"}],"predecessor-version":[{"id":2011,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/2005\/revisions\/2011"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/media\/2006"}],"wp:attachment":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2005"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2005"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2005"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}