Check time elapsed and time remaining for running SQL job

Just thought I'd share this handy SQL statement that allows you to check for the time elapsed and time remaining for the running SQL job. It would help you to get a better idea how much more time you'd have to wait for the script to complete. Anyway, here is a script that checks time elapsed and time remaining for the running backup SQL job

SELECT db_name(database_id), command, percent_complete, 'elapsed' = total_elapsed_time / 60000.0, 'remaining' = estimated_completion_time / 60000.0

FROM sys.dm_exec_requests

WHERE command like 'BACK%'


Docker for Windows Server 2016 requires update KB3176936

If you try to play with Docker and containers on new Windows Server 2016, you do the following:

  1. Add Containers feature using Server Manager, which is easy to do
  2. Then you open PowerShell with elevated privileges
  3. First install the OneGet PowerShell module:
    Install-Module -Name DockerMsftProvider -Repository PSGallery -Force
  4. Next you use OneGet to install the latest version of Docker.
    Install-Package -Name docker -ProviderName DockerMsftProvider

At this point, you get an error that "The docker installer requires update KB3176936". What is that about?!? Weird. What you need to do is to run sconfig, then choose option 6 and then A and A to install all updates. It might take a few minutes to download and install updates, so be patient.

This works for Server 2016 in no-desktop installs as well as with the UI.

  1. After this you need to restart your server using UI or using Restart-Computer -Force command.
  2. No you're ready to try to use OneGet to install the latest version of Docker.
    Install-Package -Name docker -ProviderName DockerMsftProvider
  3. After this you need to restart your server using UI or using Restart-Computer -Force command.

Now, you're ready to use Docker. For example, download a pre-created .NET sample image from the Docker Hub registry and deploy a simple container running a .Net Hello World application by running the following command:

docker run microsoft/sample-dotnet

This command will use Docker run to deploy the .Net container. It will take a few minutes to download the container image though.

Update Portal Settings for multiple team projects

There is a great Codeplx project that allows you to update Portal Settings for multiple team projects at the same time: Whenever you upgrade TFS, and have to re-enable and re-point team projects in your upgraded TFS project collections. If you have a lot of team projects to update, it becomes very tedious task. Feature4TFS tool allows you to update all of those items in one step. Very handy tool. The only issue with this tool is that it does not work with TFS 2015 Update 3 (only Update 2 or earlier.

I have downloaded the source code and updated the solution to make it work with TSF 2015 Update 3. You can download updated solution from!AoNW-kvNWJ9ygsNWPA5LHTJ5fYREXQ. There two files: binaries only and source code and binaries.

Getting output from Remote PowerShell on Target Machines

Remote PowerShell on Target Machines task in TFS/VSTS is awesome. Easy to use and it works. The only things that bugs me about that task is that I don't see the output of the script on the build console output. Luckily, this can be easily fixed. All you have to do that is to use Write-Verbose -verbose instead of Write-Output command inside your PowerShell script.

For example, instead of:

Write-Output "Remote script is executing step 1-2-3"

use the following

Write-Verbose "Remote script is executing step 1-2-3" -verbose

You should now see the remote script output in build console.

Persisting PowerShell variables between steps

In VSTS and in TFS 2015 Update 1 (and higher), you can use the following command to persist PowerShell variables between steps by simply writing it to standard out (Write-Host) in powershell

Write-Host ("##vso[task.setvariable variable=testvar;]testvalue")


This will make the variable available to be consumed by the later steps in an input via the $(testvar) syntax and the replacement will happen as expected. It's that easy.

You can see the full list of the commands you can use from the scripts at

Customize TFS build steps on the fly based on build trigger

I have the following scenario. The client currently has multiple build definitions, which are very similar (not identical, but similar), but have different triggers. For example, nightly builds run more/different tests, while gated or CI builds run less tests and perhaps run less steps. And, they want to combine/merge those build definitions into one. With TFS 2015 you can create multiple triggers on the same build definitions, so we should be able to merge those build definitions into one. The problem is that the build steps must be the same, or we need to figure out the way to update those steps on the fly somehow. Having the same steps is not an option, since we want gated/CI builds to be smaller/faster. So, we need to figure out a way to modify build steps on the fly. Somehow. PowerShell to the rescue.

I wrote a small PowerShell script that checks if the running build is gated build and update certain build variables (in my case, I used a variable called TestFilterCriteriaToSet) in memory, which are later consumed by next steps in the build definition.


function Is-This-Gated-Build ($buildid)


    $buildDetails=Invoke-RestMethod -Uri "http://TFSSERVER:8080/tfs/TFSCOLLECTION/TEAMPROJECT/_apis/build/builds/$($buildid)?sapi-version=2.0" -UseDefaultCredentials -Method Get

    $buildReason = $buildDetails.reason

    if ($buildReason -eq 'CheckInShelveset')


        return $true




        return $false




if (!$gatedBuild)


Write-Output "Running non-gated build..."




    $TestFilterCriteriaToSet = "(Name!=TestProcessQueue)"

    Write-Host ("##vso[task.setvariable variable=TestFilterCriteria;]$TestFilterCriteriaToSet")




    Write-Output "Running gated build..."

    $TestFilterCriteriaToSet = "(TestCategory!=LongRunning & TestCategory!=Commit & TestCategory!=IntermittentFailure & TestCategory!=ExecutableLaunch)"

    Write-Host ("##vso[task.setvariable variable=TestFilterCriteria;]$TestFilterCriteriaToSet")



Next steps: Save the script. Check it in. Add this script as a step in the build definition. Consume TestFilterCriteriaToSet variable in the next step as such $( TestFilterCriteriaToSet). For example, in Visual Studio Test step. That's it.

Downgrade SQL Server database from Enterprise to Standard Edition

I've seen quite a few clients who have SQL Server Enterprise Edition installed, but even though they don't use any of the enterprise features of SQL Server. For example, TFS does not require SQL Server Enterprise Edition, unless of course you need encryption or compression or SQL clusters. It works just fine with SQL Server Standard Edition. So, if you're not using enterprise features, having SQL Server Enterprise Edition installed seems like an overkill. An expensive overkill. Fortunately, this can be fixed. If you need to downgrade SQL Server database from Enterprise Edition to Standard Edition.

First, let's check what edition of SQL Server you're running. To do that open SQL Server Management Studio, connect to your SQL Server and create/run new SQL query:

SELECT @@version

Let's assume that you're running SQL Server Enterprise Edition. To downgrade database from Enterprise Edition to Standard Edition, we need to disable compression on that database. Before we do that we need to make sure we do the following:

  • BACKUP your database because you know… it's smart thing to do
  • Make sure that your SQL Server has enough disk space available to accommodate larger database sizes. Remember that uncompressing a database might/will increase the size of the database.

To disable compression, create/run the following script against the database where you want to disable compression.


FROM sys.partitions p

join sys.objects o

on p.object_id = o.object_id

WHERE o.TYPE = 'u'

and data_compression_desc != 'NONE'



FROM sys.partitions p

join sys.objects o

on p.object_id = o.object_id

WHERE o.TYPE = 'u'

and data_compression_desc != 'NONE'


If any of the tables in the database have compression enabled, this script will generate more SQL scripts as an output. When that happens, copy the generated SQL scripts and execute those scripts against the same database to disable compression. Do that for all the databases that have compression enabled. That's it.

DevOps Days Hackathon Toronto

One day DevOps Hackathon event is coming to Toronto on May 25th. It's a full day event where you will get a chance to chance for some hands-on learning experience with DevOps concepts and automation using tools from Microsoft & Chef. Here is an agenda for hackathon event:


9:00 AM – 9:30 AM Registration, Breakfast & Introduction

9:30 AM – 10:00 AM Session I – DevOps and the Cloud: Microsoft Azure

10:00 AM – 10:45 AM Session II – Chef + Microsoft Azure, Managing your cloud consistently, securely and transparently

11:00 AM – 12:00 PM Hackathon Begins

12:00 PM – 1:00 PM Working Lunch

1:00 PM – 3:00 PM Hackathon Continues

3:00 PM – 4:45 PM Hackathon Ends & Group Presentations (5 min/group)

4:45 PM – 5:00 PM Closing Remarks & Winners Announced


If you are interested in participating, fill free to register at This event will take place a day before DevOps Days Toronto kicks off. It's an excellent event, which is unfortunately already all booked, but hackathon event has only recently opened up and promises to be very informative. So, go ahead and register. It will be fun… See you there.


TFS chart size limits

TFS is an excellent ALM/DevOps tool. Very flexible ALM/DevOps tool. You can do a lot with it. A lot. Once in a while though, like with any other tool, you hit a hurdle that prevents you from doing what you and/or the customer is trying to do with TFS. For example, recently I encountered the following error: VS402395: The daily number of work items returned exceeds the trend chart size limit of 1000. An error was caused because the customer was trying to build a chart from a query that returns more than 1,000 work items. I questioned why would they need a chart for a query that big, and the customer seemed to have a good reason to have chart for that many items. I might not agree with that reason, but the customer thought it was important, so I've promised to look into it.

And, of course, if there is a will, there is a way. Especially, if you're dealing with a flexible and feature rich TFS. TFS has a very rich API. And, almost always, if there is something that you cannot do in UI, you can do via API. Just like in this case. Even though, there was no way to by part chart size limit, there is a very straightforward to get it done using API. OK, enough talk. Here is how you can change chart size limit using PowerShell and TFS API:


# Load TFS snapin

if ( (Get-PSSnapin -Name Microsoft.TeamFoundation.PowerShell -ErrorAction SilentlyContinue) -eq $null )


Add-PSSnapin Microsoft.TeamFoundation.PowerShell






$tfsCollection = New-Object -TypeName Microsoft.TeamFoundation.Client.TfsTeamProjectCollection TFS_COLLECTION_URL

$hiveCollection = $tfsCollection.GetService([Microsoft.TeamFoundation.Framework.Client.ITeamFoundationRegistry])


Write-Output "The current value of chart size limit is:"


Write-Output "Changing the chart size limit to 1500 (maximum)…"

$hiveCollection.SetValue("/Service/WorkItemTracking/Settings/MaxTrendChartTimeSliceResultSize", 1500)

Write-Output "The new setting for a chart size limit is:"




That's it. Have a good day J

Excluding weekends in Azure Automation Runbooks

Getting Azure Automation runbooks to shut down your virtual machines (or turn them on) automatically is not new. There are a lot of scripts out there that could do it for you. You can also write one yourself. It's not that complicated. I did it J Just kidding…

There are a couple of ways my PowerShell scripts are different:

  1. First, the scripts that automatically start/stop Azure virtual machines take the weekend into account. Scripts will not turn your machines on or off on the weekends. After all, you probably do not want to automatically turn on your virtual machines in Azure early in the morning on the weekend, just so that you can turn them off at the end of the day on the weekend. Seems like a waste, right? Anyways, this small change should save you a few bucks.
  2. Second, the scripts adjust the schedule from UTC to the time zone you need. It looks like when the scripts that are part of Azure Automation runbooks run, they use UTC time. So, if you're in Toronto, script will think that the weekend starts 5 hours earlier. It's not bad, I guess. But, it also means that the weekend will end 5 hours earlier, and that just not right and need to be fixed.

Below is a code snippet that makes the above mentioned happen:

$UTCTime = (Get-Date).ToUniversalTime()

$TZ = [System.TimeZoneInfo]::FindSystemTimeZoneById("Eastern Standard Time")

$LocalTime = [System.TimeZoneInfo]::ConvertTimeFromUtc($UTCTime, $TZ)

$day = $LocalTime.DayOfWeek

if ($day -eq 'Saturday' -or $day -eq 'Sunday')


Write-Output ("It is " + $day + ". Cannot use a runbook to start VMs on a weekend.")




The complete scripts that start or stop Azure virtual machines can be downloaded from OneDrive. Enjoy.