locked
tbl_Content SIZE on Tfs_TeamProjectColletion database on TSF 2010 is growing at very fast pace?

    Question

  •                                                                                                                                                                                                                                       

    Hi All,

    I have got a scenario where  Tfs_TeamProjectColletion (TPC)  database size is currently 100+ GB and tbl_Content is occupying 78.5+ GB.

    We started by adding “Main”  trunk of the codebase 6 months back which was about 2GB overall size of files added to Version Control and later resulted in creating more branches and currently number of branches stand to 24. My understanding  of how TFS identifies the delta and stores the source code control files is based on this blog from Bill Heys, which doesn’t explain the current version control tables sizes primarily for tbl_Content which should not be 24 x 2 GB >= 48+ GB, even this doesn’t map to the current size of 78.5+ GB. So my Qs are

    • Has anyone experience such a anomalous grown o the TPC database and specially into the version control schema of the database?
    • Has this something to do with SQL server instance base configuration , where every time we are creating a new branch, Could tbl_Content be getting allocated with huge amount of space and which is not getting reclaimed later or something  on those lines. Also the branching i.e. the delta is not working?
    • Has this anything to do with an earlier test team project being deleted from the team project collection?
    • Are there any tools \ scripts to troubleshoot this excessive SQL size growth on TPC database?
    • Is the Content field with “Image” data type  on the table tbl_Content could be the possible area of issue?

    Any inputs to troubleshoot the source of this issue would really help, Please find the attached snapshot of the current table sizes top 23 and the overall size of the TPC database for you reference.

    Thanks & Regards,

    Parag.

    Thursday, May 3, 2012 10:15 AM

Answers

  • Hi Parag,

    There are no documents that provide guidance on how large the tbl_contnet table should be.  If you have binary files, the deltafication of the files will add size to the table. For example, you might have 15 binary files and 1000 changes to the files - all that data needs to be stored somewhere.

    You did not mention any performance issues with TFS.  Is it just storage size you're worried about or are you seeing actual problems like perf. degradation or something else?

    We cannot offer much help here in the forum I'm afraid. You could open a paid support case with us (http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone) and we might be able to track down why the table is growing like this, but really this might just be normal, behind the scenes behavior.


    Trevor Hancock (Microsoft)
    Please remember to "Mark As Answer" the replies that help.

    Wednesday, May 9, 2012 6:52 PM
    Moderator

All replies

  • Do you have a lot of (large) binary files stored in source control?

    I don't think that binaries are deltafied (and someone is welcome to correct me if I'm wrong).


    DevBiker (aka J Sawyer)
    Microsoft MVP - Sql Server (StreamInsight)


    Ruminations of J.net


    If I answered your question, please mark as answer.
    If my post was helpful, please mark as helpful.

    Thursday, May 3, 2012 11:05 PM
  • Hi Parag,

    How about the issue now?

    If anything is unclear, please free feel to let me know.

    Best Regards,


    Lily Wu [MSFT]
    MSDN Community Support | Feedback to us

    Monday, May 7, 2012 2:34 AM
    Moderator
  • Hi Lily,

    The issue does very much exists and I continue to wait via this Q on the forum to get some inputs from TFS 2010 SQL experts or someone with relevant expertise from the VS Product Team?

    It shall be a great help if you could involve someone familiar with this topic to further look at this issue.

    Thanks & Regards,

    Parag.

    Tuesday, May 8, 2012 11:31 AM
  • Hi,

    I have got 1.69 GB worth of binary files (*.pdf, *.mdf, *.dll & *.exe etc ) from total of 2.29 GB of contents. Also the binaries might not be getting deltafied, but it still doesn’t explain the calculation 24 x 2.29 GB >=54.96 GB, even this doesn’t map to the current size of 78.5+ GB.

    Thanks & Regards,

    Parag.

    Tuesday, May 8, 2012 2:53 PM
  • Hi Parag,

    I am trying toinvolve someone familiar with this topic to further look at this issue. There might be some time delay. Appreciate your patience.
     
    Thanks,


    Lily Wu [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, May 9, 2012 1:28 AM
    Moderator
  • Hi Parag,

    There are no documents that provide guidance on how large the tbl_contnet table should be.  If you have binary files, the deltafication of the files will add size to the table. For example, you might have 15 binary files and 1000 changes to the files - all that data needs to be stored somewhere.

    You did not mention any performance issues with TFS.  Is it just storage size you're worried about or are you seeing actual problems like perf. degradation or something else?

    We cannot offer much help here in the forum I'm afraid. You could open a paid support case with us (http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone) and we might be able to track down why the table is growing like this, but really this might just be normal, behind the scenes behavior.


    Trevor Hancock (Microsoft)
    Please remember to "Mark As Answer" the replies that help.

    Wednesday, May 9, 2012 6:52 PM
    Moderator