TFS 2010 Database size increase

Several of my customers have been complaining the last few months that their Collection databases have increased in size with several hundred percent.

I read Grant Holidays blog (http://blogs.msdn.com/b/granth/archive/2011/02/12/tfs2010-test-attachment-cleaner-and-why-you-should-be-using-it.aspx) and used his SQL Script to identify where the problem was.

The increase rate of the database per month:

SELECT

DATEADD(month,DATEDIFF(month,0,creationdate),0) as [Month],

SUM(CompressedLength) / 1024 / 1024 as AttachmentSizeMB

FROM tbl_Attachment WITH (nolock)

GROUP BY DATEADD(month,DATEDIFF(month,0,creationdate),0)

ORDER BY DATEADD(month,DATEDIFF(month,0,creationdate),0)

The table with most data

— Table rows and data sizes

CREATE TABLE #t ( [name] NVARCHAR(128), [rows] CHAR(11), reserved VARCHAR(18), data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18))

GO

INSERT #t

EXEC [sys].[sp_MSforeachtable] ‘EXEC sp_spaceused ”?”’

GO

SELECT

name as TableName,

Rows,

ROUND(CAST(REPLACE(reserved, ‘ KB’, ”) as float) / 1024,2) as ReservedMB,

ROUND(CAST(REPLACE(data, ‘ KB’, ”) as float) / 1024,2) as DataMB,

ROUND(CAST(REPLACE(index_size, ‘ KB’, ”) as float) / 1024,2) as IndexMB,

ROUND(CAST(REPLACE(unused, ‘ KB’, ”) as float) / 1024,2) as UnusedMB

FROM #t

ORDER BY CAST(REPLACE(reserved, ‘ KB’, ”) as float) DESC

GO

DROP TABLE #t

GO

I started to look around in the databases and found out that the attachments mostly came from test results in Continuous Integration builds. By looking at the Retention policys of the build I found out that the testresults were not removed.

I fixed this in the retention policy by selection All.

The next step was to remove the test results for already deleted build, I could probably do this with the test attachment cleaner described in Grants blog but I decided to do this with the tfsbuild.exe /delete command instead (http://msdn.microsoft.com/en-us/library/ms244360.aspx).

Open a Visual Studio command prompt and type the following (replace with your own values) for each build definition.

CD C:Program Files (x86)Microsoft Visual Studio 10.0Common7IDE

tfsbuild.exe delete /collection:http://myserver:8080/tfs/MyCollection /builddefinition:”MyTFSProjectMyBuild definition” /daterange:~2012-03-44 /deleteoptions:TestResults

 

There is a hotfix you can install to avoid the problem with publishing deployment binaries into TFS, read more about it in Anuttharas blog http://blogs.msdn.com/b/anutthara/archive/2011/10/30/gsjgd.aspx or get it here http://support.microsoft.com/kb/2608743.

 

 

 

 

Migration from ReQtest/Excel to TFS

One of my customers wanted me to migrate Test Cases with steps and expected results from ReQtest to Team Foundation Server. This is how you do it:

Export your Test Cases from ReQtest to Excel.

Download and install Test Migrator Plus from Codeplex, http://tcmimport.codeplex.com/.

Start Test Case Migrator Plus and you will get this Wizard, click “Next”.

Select the Excel source.

Select your Team Foundation Server collection, Project and Work Item Type. You can migrate requirements and bugs to.

In the settings tab you can save a settings file or create a new file.

Map your source fields to the destination fields. If you want to connect your Test Cases to and existing test plan use the Test suites drop down.

In the data mapping tab you can select to create Area and Iterations paths (default selected).

In the Links mapping tab you can create links between Work Items.

In the Miscellaneous tab you can select:

  • If multiple steps are present in a single cell
  • Other step delimiters
  • Mappings and report location.

Click Save and migrate

Review the result in Team Explorer

 

 

Manual upgrade a TFS project to a new Process template

There are several ways to migrate your TFS projects to a new process template, in this blog I am going to describe how to do it manually. It’s not easy but might be worth a try.

  1. Download the process template you want to upgrade to disk with the process template manager, for example to D: MyDownloadedTemplate

  1. Backup your old work item type in the TFS project you want to upgrade with the process template editor in Team Foundation Power Tools or commandline with Witadmin.exe.

  1. If you have custom fields, add them to the downloaded process template with the process template editor.
  2. If you are going to change the name of a work item type (for example Task to Sprint Backlog Item) you first have change the name in the new template to the name in the old template.

  1. Open a Visual Studio 2010 command prompt
  2. Upload the new linktypes
    1. CD D:MyDownloadedTemplateProcess TemplateWorkItem TrackingLinkTypes
    2. witadmin.exe importlinktype /collection:http://MyTFS:8080/tfs/Defaultcollection /f:SharedStep.xml
    3. witadmin.exe importlinktype /collection:http://MyTFS:8080/tfs/Defaultcollection /f:TestedBy.xml
    4. Upload your modified work Items to your TFS project (work item types depending on process template)
      1. CD D:MyDownloadedTemplateProcess TemplateWorkItem TrackingTypeDefinitions
      2. witadmin.exe importwitd /collection:http://MyTFS:8080/tfs/Defaultcollection /p:”MyProject” /f:”Bug.xml”
      3. witadmin.exe importwitd /collection:http://MyTFS:8080/tfs/Defaultcollection /p:”MyProject” /f:”Task.xml”
      4. witadmin.exe importwitd /collection:http://MyTFS:8080/tfs/Defaultcollection /p:”MyProject” /f:”Product Backlog Item.xml”
      5. witadmin.exe importwitd /collection:http://MyTFS:8080/tfs/Defaultcollection /p:”MyProject” /f:”Sprint.xml”
      6. witadmin.exe importwitd /collection:http://MyTFS:8080/tfs/Defaultcollection /p:”MyProject” /f:”Impediment.xml”
      7. witadmin.exe importwitd /collection:http://MyTFS:8080/tfs/Defaultcollection /p:”MyProject” /f:”SharedStep.xml”
      8. witadmin.exe importwitd /collection:http://MyTFS:8080/tfs/Defaultcollection /p:”MyProject” /f:”TestCase.xml”
      9. Change the name of Work Item types with new names
        1. witadmin.exe” renamewitd /collection:http://MyTFS:8080/tfs/Defaultcollection /p:”MyProject”  /n:”Task ” /new:” Sprint Backlog Item “
        2. Update categories
          1. CD D:MyDownloadedTemplateProcess TemplateWorkItem Tracking
          2. witadmin.exe” importcategories /collection:http://MyTFS:8080/tfs/Defaultcollection /p:”MyProject” /f:categories.xml
          3.  Remove unwanted Work Item Types with WitAdmin.exe
          4. Copy Work Item Queries from a project created with the new template

  1. Remove the old Reports and create new reports with Team Foundation Power Tools (the template name depending on the template you are upgrading to).
    1. tfpt addprojectreports /collection:http://MyTFS:8080/tfs/DefaultCollection /teamproject:”MyProject” /processtemplate:” MSF for Agile Software Development v5.0″ /force
    2. With SharePoint you have two alternatives, upgrade your old site or creating a new. The easiest way is to create a new with tfpt addprojectportal (the same syntax as addprojectreports.
      1. tfpt addprojectportal /collection:http://MyTFS:8080/tfs/DefaultCollection /teamproject:”MyProject” /processtemplate:”MSF for Agile Software Development v5.0″
      2. If you already have a project portal that you want to continue to use, open the portal, select Site Actions, Site settings and Site Features. Turn on Dashboards (depending on template).
      3. From Team Explorer select “Team Project Setting”, “Portal Settings” and verify that you are connected to your SharePoint site and reports.

  1. If you want to copy old fields to new fields use Excel. This is with one exception that is if you want to copy plain text fields to HTML fields, this is not possible with Excel. To do this you can use the TFSmoveDecriptions tool, read more about it here http://msmvps.com/blogs/vstsblog/archive/2011/03/22/enriching-your-work-item-descriptions-by-moving-them-to-a-html-field.aspx.

 

Test steps and results in TFS 2010

All SSRS reports you get out of the box with TFS 2010 are trend reports and they don’t show any details. I was recently asked to build a custom report listing all test cases in a test plan and for each test case every step should be listed with action, expected result ant test outcome. Basically the same kind of info you can get for a single test case when you view the test results in Microsoft Test Manager 2010. This turned out to be quite a bit trickier than expected but after getting some much needed info from a developer at Microsoft (thank you Sriram) I was able to get it all together.

Getting test steps and results from Tfs_DefaultCollection

Test steps are stored as XML and are found in table WorkItemLongTexts. You can use this query to get them.

declare @fldIdSteps int = (Select top 1 fldid from Fields where ReferenceName = ‘Microsoft.VSTS.TCM.Steps’)      select * from WorkItemLongTexts where FldID = @fldIdSteps

Test results for each step are found in table tbl_TestActionResult. You can use this query to get them.

select * from tbl_TestActionResult

Now, you’ll notice there are a couple of issues popping up. You’ll need to join a XML result with a table result, there might be several revisions of the test steps and there are probably many results saved for each and every test case. But most of all, there is no obvious way to link steps to results. Every test step has an ID but tbl_TestActionResult does not contain a “test step ID” column.

ActionPath explained

This is where the column ActionPath in tbl_TestActionResult comes in. This column will typically contain an empty string, 8 chars string or 16 chars string. For a specific test result in a test run there will be one line with the empty string (this line is the over all test result) and then one line for each step, containing 8 or 16 chars. These ActionPath chars are hierarchical hexadecimal representations of test step ids. The first 8 chars is the step id and the next 8 chars (if they exist) is a shared step id.

And now you have the knowledge needed to pair up test results with corresponding test steps!

Some issues to consider…

There are a few more issues you’ll have to tackle as well.

  • Step ID != Sequence number. The test step id is not the same as the numbers you see when viewing a test case in a GUI. The numbers you see in the GUI are generated in the GUI to clarify the step sequence. The step id is never shown and the sequence number is not stored in the database. You’ll have to rely on the order of the <step> elements in the XML to figure out the step sequence.
  • Revisions and result. There can be several revisions of the test steps stored in table WorkItemLongTexts and there can be several test results stored for each revision. You’ll have to compare timestamps for revisions and results when joining them to avoid errors.
  • Performance. The Database Tfs_DefaultCollection is the production database and you should always take performance hits into consideration before deploying reports that read directly from your production database.

More details

When I first tried to figure out how this all worked I put a question up at the MSDN forums. For SQL and code samples you can go to my post on MSDN and get some more details.