USE Tfs_DefaultCollection

SELECT
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
    sys.tables t
INNER JOIN
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    TotalSpaceKB desc

    public static class TestLogSetup
    {
        public static void Logger()
        {
            Console.WriteLine(Environment.MachineName);
            if (Environment.MachineName.Contains("<ServerName>"))
            {
                Console.WriteLine("No Op Logger");
                LogManager.Adapter = new Common.Logging.Simple.NoOpLoggerFactoryAdapter();
            }
            else
            {
                Console.WriteLine("Console Logger");
                LogManager.Adapter = new Common.Logging.Simple.ConsoleOutLoggerFactoryAdapter();
            }
        }
    }

param ($serverName = 'http://<snip>:8080/tfs/DefaultCollection')

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.TeamFoundation.Client")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.TeamFoundation.Build.Client")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.TeamFoundation.VersionControl.Client")
$tfs = [Microsoft.TeamFoundation.Client.TeamFoundationServerFactory]::GetServer($serverName)

$buildServer =  $tfs.GetService([Microsoft.TeamFoundation.Build.Client.IBuildServer])
$vcs =  $tfs.GetService([Microsoft.TeamFoundation.VersionControl.Client.VersionControlServer])

$projects = $vcs.GetAllTeamProjects($true) # We will run this for all team projects

foreach ($project in $projects){
    Write-Host $project.Name

    # Get all the build definitions for a given project
    $buildDefs = $buildServer.QueryBuildDefinitions($project.Name)

    foreach ($buildDef in $buildDefs){
        Write-Host "-" + $buildDef.Name
        # Delete all builds older than 14 days. This is a bit aggressive and can be pushed to a month once the logs are smaller on automated builds.
        $endDate = (Get-Date).AddDays(-14)
        # Keep going back until we hit dec 2014
        while($endDate.Year -ge 2014){
            $year = $endDate.Year
            $month = $endDate.Month
            write-host $year $month
            # Create a search object for the project/build def
            $buildDetailSpec = $buildServer.CreateBuildDetailSpec($project.Name, $buildDef.Name)
            $startDate = $endDate.addMonths(-1)
            $buildDetailSpec.MaxFinishTime = $endDate # Search criteria
            $buildDetailSpec.MinFinishTime = $startDate # Search criteria
            # only build info, not workitems, labels, etc
            $buildDetailSpec.InformationTypes = $null

            ## This is important for the query, builds that are deletd by retention
            ## or the ui are only logically deleted, not destroyed in the db
            $buildDetailSpec.QueryDeletedOption = [Microsoft.TeamFoundation.Build.Client.QueryDeletedOption]::IncludeDeleted

            # Search all the things
            $builds = $buildServer.QueryBuilds($buildDetailSpec)
            if($builds.Builds.Length -ge 1){
                Write-Host "before update" $builds.Builds
                foreach($build in $builds.Builds){
                    # Need to turn off the Keep Forever (Retain) flag set by Release Manager.
                    if ($build.KeepForever -eq $true){
                        Write-Host "Updating build " $build.Uri
                        # Gets an updatable ref to the build
                        $buildToEdit = $buildServer.GetBuild($build.Uri)
                        # Edit the build flag
                        $buildToEdit.KeepForever = $false;
                        # save the build back to the tfs
                        $buildServer.SaveBuilds(@($buildToEdit))
                    }
                }
                # refresh the query given that some of the builds have been updated
                $builds = $buildServer.QueryBuilds($buildDetailSpec)
                Write-Host "after refresh" $builds.Builds
                # Delete the build, test results, symbols, drop, etc.
                $buildServer.DeleteBuilds($builds.Builds,
                [Microsoft.TeamFoundation.Build.Client.DeleteOptions]::All)
                # Destroy the db records
                $buildServer.DestroyBuilds($builds.Builds)
            }
            # rolling back the years.
            $endDate = $endDate.addMonths(-1)
        }
    }
}