BitBucket samples repository

For the past several years I’ve enjoyed getting to know SQL Server at my old job. However, I’ve recently switched jobs to a company which uses Oracle as their database server for a wide variety of applications.  As such, I’m trying to learn all I can about Oracle. I’ve began to read a book on PL/SQL programming, and I’ve been building samples in PL/SQL in order to ensure that I’m not just reading, but also putting what I’m putting it into practice.

I’ve decided to put these samples in a repository on BitBucket, in hope they will help others in the future as well.  My goal is to make this a repository for any samples I have in my blog. The samples I put out in this repository that aren’t currently in my blog will likely be made into blog posts sometime in the future as well.


Upgrading a Custom Build from TFS 2010 to TFS 2012

While TFS 2012 contains many useful features for the development team, one of the difficulties upon upgrading is the lack of a simple way to convert pre-existing custom build templates. It’s not a difficult process by itself, it just takes time, and some trial and error.

First, the XAML from Visual Studio 2010 contains many references to the old assemblies and some duplicate entries.  The duplicates need to be removed and any version references to the 10.0 version of the assemblies need to be changed to the 11.0 version of the assemblies.  After removing the duplicates there may be no version references to update.

Second, make sure visual studio isn’t reporting any import errors in the XAML. Extra namespace may be referenced and need to be imported.

Third, if the build process is using any custom assemblies, the references in the projects need to be updated to the 11.0 version of the assemblies, and they need to be recompiled to be .NET 4.5.  Check in the modified assemblies to source control for the build controller to pick up.

Finally, I’ve ran into problems with some of the activities from the old TFS 2010 default template which is what my custom build process was based on.  The LabelSources activity seems to give a null reference error when trying to label the workspace.  However, the workspace can be be labeled using the LabelWorkspace activity which seems to work.

I can’t guarantee these are all the problems you may encounter, but hopefully having a good listing of the issues I encountered will help out some others.  Here’s a list of the blogs and msdn references that were useful in helping to upgrade the TFS builds.

SQL Tips: Does SQL server have a memory leak?

Perhaps this is more of a misconception than a tip.  Nevertheless, it was something that tripped me up the first time I used SQL server, and I’ve gotten asked about high memory utilization by SQL server quite few times by others.

Often times, after use, if you go in Task Manager, you’ll notice that SQL Server’s memory footprint has grown to take up most of the free memory on a machine.  In many scenarios for application developers, this triggers the thought “memory leak”.  Well, in this case it isn’t.

SQL server expands to use up all the memory it can on the server.  It will continually poll the server to determine if more free memory is needed for other programs and will relinquish it so as not to starve them.  SQL server relies more on itself for memory management than underlying OS.

In the end, if the company paid for all that RAM on your SQL box, why not use it all?

Of course these options are configurable.  This can be done by using the sp_configure stored procedure with the “max server memory” and “min server memory” options.

On a dedicated SQL box, I’ll set the max server memory to the size of the RAM available on the box minus 1 to 2 gigabytes. This gives some extra cushion for the OS even though it is not necessary.

Here’s an article listing the options that are available for sp_configure.

Here’s a great article to read to get up to speed on SQL server and it’s memory usage and some different options to take into consideration when setting it up.

Creating backlog items for your scrum project in TFS 2012

In my last post, I showed how to programmatically create areas for a scrum team project in TFS 2012. In this post, I’m going to show how to add product backlog items.

Once you understand how retrieve services from a TfsTeamProjectCollection object, interacting with TFS is just a matter of knowing which services to use.  For adding work items into TFS this can be accessed through the WorkItemStore service.  This service gives you access to any work items in a team project and the ability to manipulate them.

In order to create a work item, it’s required to know what type of item it is.  In the scrum process template, items in the backlog are work items of the “Product Backlog Item” type.  It’s possible to also iterate through the work item types to see what’s available in your process as well.  If you’re working with a  different process template, there could be any variety of work item types.  After retrieving the type, it’s possible to finally create the product backlog item.

Now, in this example, I did not go through the work of actually validating the work item.  However, there is a Validate method.  When using the Validate method, if there is any validation problem with the WorkItem, it will return an error for exactly which field in the work item failed to validate.  Of course, the Save method will throw an exception if the WorkItem is invalid as well.

Here’s a sample set of code to add items to the product backlog.

public static void AddProductBacklog(TfsTeamProjectCollection projectCollection, 
    string projectName, string title, 
    string description, string areaPath=null)
    if (projectCollection == null)
        throw new ArgumentNullException("projectCollection");

    if (projectName == null)
        throw new ArgumentNullException("projectName");

    if (title == null)
        throw new ArgumentNullException("title");

    if (description == null)
        throw new ArgumentNullException("description");

    var collectionStores = projectCollection.GetService<WorkItemStore>();
    var store = collectionStores.Projects[projectName];

    if (!store.WorkItemTypes.Contains("Product Backlog Item"))
        throw new InvalidOperationException("The project " 
            + projectName + " does not support product backlog items.");

    WorkItem item = new WorkItem(store.WorkItemTypes["Product Backlog Item"]);
    item.Title = title;
    item.Description = description;

    if (areaPath != null)
        item.AreaPath = areaPath;


I hope this helps, and get’s you a step closer to being able to use the scrum template in TFS.

Creating areas for your scrum project in TFS 2012

Organizing and dividing work into logical areas in your project can be a great way to track and add traceability to a process where certain changes are made.  Adding these areas one at a time can be a time consuming process.  In this post, we’ll set up a small method to make it easier to create the areas in the project.

The first step is getting a connection open to the team project collection.  I talked about that in my previous post. Once the connection is open, we need to obtain a reference to the ICommonStructureService.  Obtaining references to particular services in TFS is done through the GetService method on the TfsTeamProjectCollection object.

So, we will do the following:

var css = projectCollection.GetService<ICommonStructureService>();

I only mention this separately because this can also be used to get various other services to perform operations against the project collection.  For example, version control and work item services can be obtained through the GetServices method.

Now that we have a reference to the css, we’ll use a couple of methods to look up if nodes are already created or if they need to be created.  Now some of this is tricky and involves going through some of the paths in the node to determine what the correct path formats are.  However, once the infrastructure is in place, that will be relatively simple.

The areas in a project are stored in a hierarchy structure in code with each node containing a reference to it’s parent and all it’s children.  So, in order to start building the areas, we need to find the root structure node for the areas. Once that’s found, we can begin adding areas.

The full set of code is here.

public static void AddArea(TfsTeamProjectCollection projectCollection, string projectName, params string[] areas) {

	if (projectCollection == null)
		throw new ArgumentNullException("projectCollection");

	if (projectName == null)
		throw new ArgumentNullException("projectName");

	// Get the structure service to define areas for the project. 
	var css = projectCollection.GetService<ICommonStructureService>(); 

	// Find the information for the project to add the area 
	var project = css.ListProjects().FirstOrDefault((proj) => proj.Name == projectName); 
	if (project == null) 
		throw new InvalidOperationException("Could not find project."); 

	// List all the structures available in the project. 
	var nodes = css.ListStructures(project.Uri); 
	var curNode = nodes.FirstOrDefault(node => node.StructureType == "ProjectModelHierarchy"); 
	if (curNode == null) 
		throw new InvalidOperationException("The project specified does not contain an Area structure."); 

	// Loop through the list of areas and create each one. 
	foreach (var area in areas) { 
		// After a null is included in the list, don't evaluate any more areas 
		// assume the list is completed. 
		if (string.IsNullOrWhiteSpace(area)) 
		// Try find the node, if it doesn't exist, create it. 
		try { 
			curNode = css.GetNodeFromPath(curNode.Path + "\\" + area); 
		catch (CommonStructureSubsystemException csse) {
			// Not a very elegant way to deal with a node not existing, but the interface 
			// does not have a check for if something exists. 
			if (csse.Message.StartsWith("TF200014")) { 
				var curNodeUri = css.CreateNode(area, curNode.Uri); 
				curNode = css.GetNode(curNodeUri); 
			} else throw; 

The project can now be added in a simple line of code. This will create an area hello with a sub-area of world.

ScrumProjectBuilder.AddArea(projectCollectionHere, "example project", "hello", "world");


Connect to Team Foundation Server 2012 via C#

Team foundation server is a great tool for managing projects.  However, sometimes working through the UI just isn’t efficient enough for large operations.  However, with new tooling comes the concern of how to port over all those work items from an older technology.  In the next few posts, I’ll be be showing some code to connect to TFS 2012, to add areas to the scrum template, and import information in the product backlog. It’s not complicated, but often time coming across the information in a single source is hard to do.

var projectCollection = new TfsTeamProjectCollection(
    new Uri("http://myserver:8080/tfs/DefaultCollection"), 
    new NetworkCredential("youruser", "yourpassword"));


At this point you’ll have a connection to TFS ready to go!  In the next posts, we’ll use the GetServices<> method on the projectCollection variable to retrieve specific services in order to make changes to the project collection.

Dynamic Management View: sys.dm_exec_sessions

Have you ever wondered who is connected to SQL server?  sys.dm_exec_sessions is an excellent DMV to be able to determine who is logged on and where they are logged on from.

Often times I’ll use the view to narrow down how many connections a certain application is using, or to narrow down what machines connections are coming from.  A very common usage of this view is when I query on sys.dm_exec_requests.  I’ll join by session_id on this DMV in order to determine where a query is coming from.  Based on the host name, I can usually find out who is working with the query.

Another common situation I’ve used this query is to kill outstanding sessions.  I’ll load the output into a table variable and go through the sessions one by one with the “kill” command to terminate them.  This is very useful when I need to bring down the server, or I know there is a rogue application causing mischief on the server.

When you first select from the view you may notice some odd entries with a null host_name, and several other null columns.  It’s completely normal and just some internal SQL tasks being reflected in the view.

— get the most connections by host name
SELECT host_name,Count(*)
FROM   sys.dm_exec_sessions
GROUP  BY host_name

Check out more on this DMV on the MSDN site: