Need Help: TFS tbl_Content Table and Database growth out of control

Recently our TFS Database size has peaked at over 570GB. Granted we do have a lot of people working against it and use it fully for Source Control, Work Items and Builds. We used to have this problem with 10s of GB being added each week. The cause then on TFS 2010 was the Test Attachments and a run of the Test Attachment Cleaner would clean it up. Kinda. We found after a while although the tables were reporting smaller, we needed two SQL Server hotfixes to allow the space to actually be freed. After that though 100s of GB flowed free and things were good. These details are covered well in a post by Anutthara Bharadwaj.

We continued running the tool and then upgraded to TFS 2012 and were told (TFS 2010 SP1 Hotfix) the problem had now gone away. We stopped running the test attachment cleaner and later upgraded to where we are now on TFS 2013.

image

This year however our system administrator noticed we were running out of space again. However, looking at the Disk Usage By Top Tables report the tbl_Attachment table was not the problem. It was the tbl_Content table.

image

image

From Grant Holliday’s post he tells us that the Content table is the versioned files. In the forums there is this,

“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.”

This got me to check out my source into a clean workspace and running Space Sniffer against it to spot if anything big had been added. Our entire source is about 50 GB. Which sounds like the total size isn’t too far off. But Main is only 820 MB and the whole team is working on there. We have been doing lots and compared to a 4 months ago it was 730 MB. We have many branches but that should only be less than a GB per branch and being a delta it should be next to nothing. Checking the tbl_Content table itself showed that the biggest rows were years old and no new large binaries have been added.

I then came across the comprehensive post by Terje Sandstrom. The also contained some queries for TFS 2012 to determine the attachment content size. Here’s where it doesn’t make sense. The report table size from the disk usage report, does not match up, whatsoever, against what these queries returned. And the query from Grant Holliday showing monthly usage again show huge amounts of data (60GB per month) in a table that is 680MB. Who is correct, SQL Server reports or table queries?

image

clip_image002

I then ran the Test Attachment Cleaner in preview mode, and sure enough it said it was cleaning up GBs of files. So I ran a Delete against the TFS database. While the cleaner was running the queries were showing the size in those tables dropping, and the Disk Usage report was showing drops in the tbl_Attachment table, albeit at a much much smaller scale. The total database size however was unchanged and the available space was getting less! On completion it said: Total size of attachments: 206030.5 MB!

image

image

The size reported by the database properties when I began was:

image

After cleaning apparently 200GB it is now:

image

Another suggestion was to delete old workspaces, which we have done. To my surprise this released about 5 GB from the content table. Git TFS can create a lot of workspaces.

image

Hence the problem. We are growing at up to about 5 GB per day. Our System Administrator is doing an awesome job keeping up. But we need to know if this is to be expected to continue or if there is something to tell us how we can use less space.

Update 3 April 2014: I have put the question on the forums.

Update 4 April 2014: Something has happened overnight. I’m assuming the workspace clean has caused it. We now have 115 GB space available! What’s odd though is that the tbl_Content size has dropped that space. What does that table have to do with workspaces? Some insights into how this is working so we can manage our systems would be appreciated.

image

image

Update 7 April 2014: More space has flowed free over the weekend without doing anything else. A shrink is in progress. Pretty crazy that 34% of the content size was local workspaces?

image

image

image

Update 8 April 2014: Shrink complete. Looking much better now. Notice however that the tbl_Content table in a day has gone up a few gigabytes, which is quite concerning.

image

image

Update 11 Apr 2014: By the end of the week, we have consumed around 5 GB in 3 days. I don’t see any significant amount of new workspaces created either. Unless the build server with TFS Service is creating them. I’m going to clean up TFS Service’s workspaces at the risk of breaking some builds so that I can monitor them carefully. I now have 51 server workspaces for TFS Service. Which does seem like a lot but we do have 18 Build Agents and many build definitions.

image

image

Update 14 Apr 2014: I don’t know what has happened Friday and over the weekend. The content has grown 2 GB but the database expanded a massive 60 GB! Event though the autogrowth is set to 1%. So a shrink is in progress.

image

image

image

Update 14 Apr 2014 #2: After the shrink it is back to the 2 GB growth for Friday and the weekend.

image

Update 16 Apr 2014: A couple of days growth.

image

image

Technorati Tags:

Web Test playback does not use concurrent requests as recorded

I have been running performance tests using Visual Studio Web Tests and Load testing. The goal has been to prove the raw load time and the scalability under increasing users. The web test recorder is quite good for simple request-replay. However, beware a bunch of work is required to parse previous requests for keys to be passed back in subsequent requests, especially for more complex web applications but not as much on simple web sites.

Here’s the results for the single user playback with requests grouped into relevant transactions that we needed to measure.

Web Test results

The odd thing that was happening though is that when we were timing the action Open Report with a stopwatch manually in the browser we were getting typically getting under 5 seconds for completion. The reason for the difference became clear when you watch the web test running. You will see each request going sequentially. If you compare it to the timing from the browser, the actually running is not like that. Below is the timings column from network monitoring from the browser. The two highlighted rows are the requests that take the longest amount of time, but are run together.

Browser Timings

Adding up these request sequentially gets us to similar total time as to what the web test replay presented. So to make the request parallel in the web test you need to set the requests as dependent requests to the first. Right-click the request and select Add Dependent Request.

image

This will add http://localhost/. Delete that request after you have drag-and-dropped the requests that you want to run concurrently into the Dependent Requests folder.

image

Rerunning the web test after this change resulted in timings much like what we experienced manually.

Improved Web Test results

Other things to look for is that Think Times are not on and set to 0 for all requests and turn on cache control for all requests.

Update: So when does the web test recorder get the dependent requests correct? Taking into account the Initiator column in the Network tab of the development tools made it clear:

Initiator

These link and script tags are by default dependent requests for the page.

image

These XMLHttpRequest JavaScript calls are not seen by the web test recording as running in parallel and need to be manually be put in as dependent requests as above.

Technorati Tags:

TF30063: You are not authorized to access Microsoft-IIS/7.5

This is the second time we have hit this error so this time it was a quick fix. But if you don’t know what it is you might be led astray by the message. When we have received this error is has nothing to do with permissions. This typically occurs on a build failure:

TF30063

The real reason is found in the Event Viewer on the TFS Web Server in all these Errors:

Event Viewer Errors

If you scroll through the jumbled Web Request Details within the event details you will see:

Exception Message: There is not enough space on the disk.
 (type IOException)
Exception Stack Trace:    at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
   at System.IO.FileStream.WriteCore(Byte[] buffer, Int32 offset, Int32 count)
   at Microsoft.TeamFoundation.Framework.Server.TeamFoundationFileService.InternalCopyTo(Stream source, Stream destination, Int32 bufferSize)
   at Microsoft.TeamFoundation.Framework.Server.TeamFoundationFileService.CopyStreamToTempStream(TeamFoundationRequestContext requestContext, Stream stream, Int64 compressedLength, CompressionType& compressionType, Boolean compressOutput, Boolean useFileStream)
   at Microsoft.TeamFoundation.Framework.Server.TeamFoundationFileService.RetrieveFile(TeamFoundationRequestContext requestContext, Int32 fileId, Boolean compressOutput, Byte[]& hashValue, Int64& contentLength, CompressionType& compressionType, String& fileName, Boolean failOnDeletedFile, Boolean returnNullIfDelta, Boolean forceFileStream, Boolean readIncompleteData)
   at Microsoft.TeamFoundation.Framework.Server.TeamFoundationFileService.RetrieveFile(TeamFoundationRequestContext requestContext, Int32 fileId, Boolean compressOutput, Byte[]& hashValue, Int64& contentLength, CompressionType& compressionType)
   at Microsoft.TeamFoundation.Server.Core.MidTierDownloadState.CacheMiss(FileCacheService fileCacheService, FileInformation fileInfo, Boolean compressOutput)
   at Microsoft.TeamFoundation.Server.Core.FileCacheService.RetrieveFileFromDatabase(TeamFoundationRequestContext requestContext, FileInformation fileInfo, IDownloadState downloadState, Boolean compressOutput, Stream databaseStream)
   at Microsoft.TeamFoundation.Server.Core.GenericDownloadHandler.DownloadFile(TeamFoundationRequestContext requestContext, DownloadContext downloadContext, HttpRequest request, HttpResponse response, HandleErrorDelegate errorDelegate)

The reason we ran out of disk space is due to IIS Logging. We were having giant transaction logs being recorded for all requests to the TFS Web Services. Archiving (deleting Winking smile) these restored our space quickly.

Technorati Tags:

Adding Microsoft PubCenter Ads with AdDuplex fall-back on Windows Phone 8

DVLUP has a challenge Shamelessly promote your apps with AdDuplex. AdDuplex doesn’t earn you any income so why would you want to give ads to your users without any benefit to yourself? AdDuplex answers this succinctly “According to reports the average fill rate in mobile ad networks could be as low as 10%. AdDuplex helps you utilize 100% of your ad space, so even when you are not making money from the other ad network you get free promotion for your app.”

It is encouraged to use an ad network with fallback to AdDuplex and It only takes 5 minutes to add to your app.

  1. Add references to Microsoft Advertising and AdDuplex
    a. Install Microsoft Advertising SDK, if not already, and add reference
    Reference to Microsoft Advertising SDK
    b. Install AdDuplex via NuGet Package. Enter into the Package Manager Console:
    Install-Package AdDuplexWP8
  2. Add references to the namespace in your Xaml page you want to display the ads

    xmlns:ad="clr-namespace:Microsoft.Advertising.Mobile.UI;assembly=Microsoft.Advertising.Mobile.UI"
    xmlns:adduplex="clr-namespace:AdDuplex;assembly=AdDuplex.WindowsPhone"
  3. Add controls so that the AdDuplex banner is under the Microsoft Advertising banner.  
    <adduplex:AdControl x:Name="adDuplexAd" AppId="12345" VerticalAlignment="Bottom" />
    <ad:AdControl Name="photoFoodDiaryAdControlRecordPage" Width="480" Height="80" VerticalAlignment="Bottom"
            IsAutoRefreshEnabled="True" IsAutoCollapseEnabled="True" 
            ApplicationId="test_client" AdUnitId="Image480_80" />

    AdDuplex requires that the complete banner is not hidden. With this setup, if the Microsoft Advertising banner fails to get an ad the IsAutoCollapseEnable will set Visibility Collapsed showing the AdDuplex banner.

  4. Generate Ad ids

    a. In WP Dev Center publish or update. Add in-app advertising and Generate ad unit ID. 
    Add in-app advertising

    b. Sign up to Ad Duplex if you haven’t and add a new App.

    AdDuplex New app

  5. Add the required capabilities to your WMAppManifest.xml

    Capabilities
  6. Since the ad network doesn’t always return an ad, to test your ads check for any errors occuring, and set the ApplicationId and AdUnitId to the test values from here. Simple add a break point in the method and inspect e.Error.Message if the break point is hit.

    ErrorOccurred="AdControlRecordPage_ErrorOccurred"
    private void AdControlRecordPage_ErrorOccurred(object sender, Microsoft.Advertising.AdErrorEventArgs e)
    {
    }

    e.Error.Message

Now you should see the Microsoft test ad. Remember to switch you ApplicationId and AdUnitId back to what you got from the Dev Center and you are done.

Microsoft Test Ad

Technorati Tags:

Adding Localization to a Windows Phone app

If you have any Windows Phone 7 apps from the original templates, unlike the Windows Phone 8 app templates they did not come with localization setup already. So lazy me just wrote the strings inline. Fortunately adding localization after updating to Windows Phone 8 is easy.

  1. Add a Resources folder
  2. Add new Resources resx to the folder named AppResources.resx
    AppResources.resx
  3. Ensure Custom Tool is set to PublicResXFileCodeGenerator
    AppResources.resx Properties
  4. Add a new class named LocalizedStrings to the root of your project
    using YourAppNamespace.Resources;
    
    namespace YourAppNamespace
    {
        /// <summary>
        /// Provides access to string resources.
        /// </summary>
        public class LocalizedStrings
        {
            private static AppResources _localizedResources = new AppResources();
    
            public AppResources LocalizedResources { get { return _localizedResources; } }
        }
    }
  5. Add an Application.Resource of your LocalizedStrings class to your App.xaml

        <Application.Resources>
            <local:LocalizedStrings xmlns:local="clr-namespace:YourAppNamespace" x:Key="LocalizedStrings"/>
        </Application.Resources>
  6. Add strings in AppResources.resx

    AppResources.resx Content

  7. Use localized strings anywhere in your Xaml

    CompanyName="{Binding Path=LocalizedResources.CompanyName, Source={StaticResource LocalizedStrings}}"
  8. To Add other languages and for more details follow How to build a localized app for Windows Phone

New Work Item State on TFS Kanban Board

Continuing our Quest we currently have the states To Do, In Progress and Done. We need to add another for when it goes into testing so we will call that Boss Battle.

Firstly we need to add the new state to the work item type. Export the Quest work item type:

witadmin exportwitd /collection:http://vsalm:8080/tfs/FabrikamFiberCollection /p:FabrikamFiber /n:Quest /f:%userprofile%\desktop\QuestWitd.xml

Add a new state under witd > WORKITEMTYPE > WORKFLOW > STATES:

<STATE value="Boss Battle"/>

Modify the transitions to go through the new state:

<TRANSITION from="In Progress" to="Done">
<TRANSITION from="In Progress" to="Boss Battle">

<TRANSITION from="Done" to="In Progress">
<TRANSITION from="Done" to="Boss Battle">

In practice you might want to add more transitions around the state. This is much easier using the TFS Power Tools Process Template Editor. This will now have the workflow in place with the new state, but when that state is selected the work item will not appear on the board.

Export the process configuration definition:

witadmin exportprocessconfig /collection:http://vsalm:8080/tfs/FabrikamFiberCollection /p:FabrikamFiber /f:%userprofile%\desktop\ProcessConfiguration.xml

Under the TaskBacklog add a new InProgress state:

<States>
  <State type="Proposed" value="To Do" />
  <State type="InProgress" value="In Progress" />
  <State type="InProgress" value="Boss Battle" />
  <State type="Complete" value="Done" />
</States>

Import the process configuration definition:

witadmin importprocessconfig /collection:http://vsalm:8080/tfs/FabrikamFiberCollection /p:FabrikamFiber /f:%userprofile%\desktop\ProcessConfiguration.xml

You now have a new column on your iteration board:

New Column on Board

Technorati Tags:

Rename Category for TFS 2013 Agile Portfolio Management

We have come from originally a TFS 2008 CMMI Work Item Template and have upgrade through 2010 and 2012. When we upgraded to TFS 2013 we were keen to use the Agile Portfolio Management features. After enabling it we ended up with the categories Features and Requirements. These categories names we find are often use interchangeably so having them mean distinctly different  things was confusing.

Agile Portfolio Management Categories

By default if you are the Scrum template you get something far less confusing, Features and Backlog items. Initiatives can be added following Agile Portfolio Management: Using TFS to support backlogs across multiple teams guide.

Scrum Template Categories

So when it came to use agile portfolio management the hierarchy is was not clear. Fortunately this is easily fixed. I assume the TFS Team did not what to end up with another area like Team Projects that is not able to be renamed. To begin open the Developer Command Prompt which has the paths configured for witadmin.
Developer Command Prompt for VS2013

The examples I have done below have been done on the Visual Studio 2013 ALM Virtual Machine provided by Brian Keller. I recommend anyone who is a TFS Admin have the appropriate Visual Studio ALM Virtual Machines for testing changes on. Especially if you are managing a small team since you are unlikely to have a staging environment and setting one up is too much work. Consider these staging and test environments. With the error I got below no one was effected while I worked out the solution.

Renaming Categories

  1. Export the process configuration definition to an xml file: 
    witadmin exportprocessconfig /collection:http://vsalm:8080/tfs/FabrikamFiberCollection /p:FabrikamFiber /f:%userprofile%\desktop\ProcessConfiguration.xml 
  2. Modify the export xml setting the name to something more appropriate:

    <!-- Feature to Saga -->
    <PortfolioBacklog category="Microsoft.FeatureCategory" pluralName="Features" singularName="Feature">
    <PortfolioBacklog category="Microsoft.FeatureCategory" pluralName="Sagas" singularName="Saga">
    
    <!-- Backlog Item to Journey -->
    <RequirementBacklog category="Microsoft.RequirementCategory" parent="Microsoft.FeatureCategory" pluralName="Backlog items" singularName="Product Backlog Item">
    <RequirementBacklog category="Microsoft.RequirementCategory" parent="Microsoft.FeatureCategory" pluralName="Journeys" singularName="Journey">
    
    <!-- Task to Quest -->
    <TaskBacklog category="Microsoft.TaskCategory" parent="Microsoft.RequirementCategory" pluralName="Tasks" singularName="Task">
    <TaskBacklog category="Microsoft.TaskCategory" parent="Microsoft.RequirementCategory" pluralName="Quests" singularName="Quest">
  3. Import the process configuration definition file: 
    witadmin importprocessconfig /collection:http://vsalm:8080/tfs/FabrikamFiberCollection /p:FabrikamFiber /f:%userprofile%\desktop\ProcessConfiguration.xml 

Refreshing the browser after renaming the categories, my URL is wrong since the category no longer exists but I get this very good message:

Most comforting error message ever.

After messing with the process configuration and then getting an error message containing the line, “Don’t worry, the system is not broken”, is very comforting.

Renaming Work Item Types

This is appears easy with just one command.

Rename Feature to Saga:

witadmin renamewitd /collection:http://vsalm:8080/tfs/FabrikamFiberCollection /p:FabrikamFiber /n:Feature /new:Saga

A confirmation prompt appears:

Are you sure you want to rename the work item type Task to the new name of Quest? (Yes/No)

Rename Task to Quest:

witadmin renamewitd /collection:http://vsalm:8080/tfs/FabrikamFiberCollection /p:FabrikamFiber /n:Task /new:Quest

However after doing this you may get this nasty error message when viewing the backlog essentially telling you this time your system is broken and not really useful information on how to fix it:

Broken Configuration Error message

If you export and then import the process configuration definition it will tell you a reason why this could be.

image

Correcting this color reference in the xml as such however gives you this error.

<!-- Update Task to Quest for the new work item type name -->
<WorkItemColor primary="FFF2CB1D" secondary="FFF6F5D2" name="Task" />
<WorkItemColor primary="FFF2CB1D" secondary="FFF6F5D2" name="Quest" />

image

Regardless neither of them are the error. This issue is an application pool recycle of TFS is required. After the application pool recycle, the backlog will be working again.

Now you can upload your modified process configuration definition correcting the work item colors.

Result

Now I have a much more interesting hierarchy:

Final Hierarchy

 

Technorati Tags: