Post reply

Garbage Collection

  • September 4, 2013 at 11:06 pm

    #74184

    Comments posted to this topic are about the item Garbage Collection

  • September 5, 2013 at 1:03 am

    #1647582

    Thanks for the question!

    🙂

  • September 5, 2013 at 1:04 am

    #1647583

    Its good that Microsoft has started Garbage Collection in SQL Server aswell,

    My question is this Garbage Collection enhancement is started from which version of SQL Server?

  • September 5, 2013 at 2:05 am

    #1647607

    Ha, didn't expect that to be a real proc!

  • September 5, 2013 at 2:08 am

    #1647609

    Nice question.....

  • September 5, 2013 at 2:21 am

    This was removed by the editor as SPAM

  • September 5, 2013 at 2:25 am

    #1647612

    Gazareth (9/5/2013)

    Ha, didn't expect that to be a real proc!

    +1 🙂

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • September 5, 2013 at 2:47 am

    #1647620

    Interesting question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • September 5, 2013 at 3:37 am

    #1647630

    Thanks for the question, but it would have been useful if it was specified that is a SQL Server 2012 environment as in previous versions this didn't exist.

  • September 5, 2013 at 7:00 am

    #1647687

    raulggonzalez (9/5/2013)

    Thanks for the question, but it would have been useful if it was specified that is a SQL Server 2012 environment as in previous versions this didn't exist.

    +1 as I don't use 2012, I responded based on what I knew from 2008. I could have looked up "sp_filestream_force_garbage_collection ", but as another poster said, that just seemed like a made up procedure name. At least I learned something.

    Thanks Steve for the question.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • September 5, 2013 at 7:24 am

    #1647704

    Thomas Abraham (9/5/2013)

    raulggonzalez (9/5/2013)

    Thanks for the question, but it would have been useful if it was specified that is a SQL Server 2012 environment as in previous versions this didn't exist.

    +1 as I don't use 2012, I responded based on what I knew from 2008. I could have looked up "sp_filestream_force_garbage_collection ", but as another poster said, that just seemed like a made up procedure name. At least I learned something.

    Thanks Steve for the question.

    + 1 I definitely learned something today.


    Everything is awesome!

  • September 5, 2013 at 8:19 am

    #1647734

    Note that sp_filestream_force_garbage_collection is only available with SQL Server 2012. In previous editions there is not way to force garbage collection 😀

  • September 5, 2013 at 11:11 am

    #1647793

    Thomas Abraham (9/5/2013)

    raulggonzalez (9/5/2013)

    Thanks for the question, but it would have been useful if it was specified that is a SQL Server 2012 environment as in previous versions this didn't exist.

    +1 as I don't use 2012, I responded based on what I knew from 2008. I could have looked up "sp_filestream_force_garbage_collection ", but as another poster said, that just seemed like a made up procedure name. At least I learned something.

    Thanks Steve for the question.

    Looks like that may have been fixed. When I answered the question, 2012 was specified.

    Glad to see that this has been added in 2012 to force GC

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • September 5, 2013 at 11:26 am

    #1647795

    This question is somewhat confused and the answer is wrong.

    It says the files "not be removed immediately" and asks how to force this garbage collection to occur. The word "this" means something, and the only thing "this garbage collection" can be using as a referent for "this" is removing the files. As the garbage collector will do nothing with the files unless both the current checkpoint LSN and the last backup LSN are both beyond the LSN of the delete operation, and the files will only be removed when the garbage collector has processed them twice, causing the garbage collector to run will do nothing at all unless there has been both a checkpoint and a log backup since the delete operation, and even if this forced run occurs when the backup and checkpoint LSNs are both high enough it will not remove the files if it is the first such run, just mark their tombstones so that the next GC run will remove them.

    So the correct answer is that there is nothing you can do (short of issuing a checkpoint , starting a log backup and waiting for it to complete, and then calling the SP twice to force two GC runs) to cause "this garbage collection" (which in context, because of the use of "this", can only mean removal of the files) to occur.

    This is fairly clear from the BoL page referenced in the explanation. The result set returned by the SP tells you how many files were marked for collection and how many were not processed at all. Why should either of these numbers be non-zero? Because when the garbage collector runs, it may not remove the files (it won't if it has not marked for collection during a previous run), and it may not even mark them for collection (it won't if the checkpoint and backup LSNs are not both later than the LSN of the delete operation).

    Tom

  • September 5, 2013 at 10:58 pm

    #1647917

    Good question -

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

Viewing 15 posts - 1 through 15 (of 19 total)

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