Skip to content

Manas Bhardwaj's Stream Posts

Shrink all databases on SQL Server

This post is intended to be used as a hack for Developers to release some disk space. Please do not use on actual production environments.

Usually, the database transaction logs also takes a lot of space.

What I also usually do is set the Recovery Mode of all the local databases on Dev Machines to Simple instead of Full. That way the growth of log files is also reduced for future.

Next to free up the space (or shrink all databases), you can use the query below. What it will do is basically set the recovery mode to simple for all databases and then shrink them.


INSERT #DataBases
SELECT NAME FROM sys.databases WHERE NAME NOT IN ('master','model','msdb','tempdb')

DECLARE @Count INT = 1

SELECT @NrOfDBs = COUNT(0) FROM #DataBases


WHILE (@Count < @NrOfDBs)
     SELECT @DBName = Name FROM #DataBases WHERE ID = @Count



     --Shrink Database
     SET @Count = @Count + 1


A PowerShell alternative to SharePoint 2013 AppRegNew.aspx

If you are reading this post then chances are that you are already aware of the SharePoint 2013 App Model and especially the SharePoint 2013 Provider Hosted Apps.

A provider-hosted app for SharePoint consists of both an app for SharePoint that is deployed directly to a SharePoint 2013 site and a separately deployed web application. If the provider-hosted web application is an ASP.NET web application, you can use the Office Developer Tools for Visual Studio 2013 to create both components of a provider-hosted app for SharePoint.

Packaging and publishing a SharePoint 2013 provider-hosted app can be a lengthy process. MSDN has a detailed article here explaining the process and steps required to publish a SharePoint provider-hosted App.

The standard process of registering an app on a SharePoint 2013 environment is by using the appregnew.aspx page (http://sitecollection/_layouts/15/appregnew.aspx) and generating a Client Id which can be used to communicate between the SharePoint and the provider-hosted app to establish a high-trust.


However, this approach of generating Client Id can be cumbersome when you have different environments (DTAP street)  with-in your organization. I did not want to burden my application administrators by creating complexity of the installation process  to generate the Client Id per environment and change it in several places (the .app package, web.config etc.)

Not only this, it also adds a lot of confusion when multiple developers are working together and all of them have to generate their own Client Ids for respective machines. The checked-in web.config, app packages in source controller can really create unnecessary confusion.

A similar blog about this process is posted here.

However, PowerShell can come to your rescue to generate the Client Id once and use the same Client Id to register your SharePoint App in different environment. Yes, not only in DTAP street but also various developer machine working together on a product.

The small PowerShell snippet below can automate the whole process of registering an App with a given Client Id and after that installing it in SharePoint.

For those who are interested in details, it makes use of Register-SPAppPrincipal command. This command lets an on-premise or SharePoint Online administrator register an app principal which means you can also use it for Office 365.

$clientID = "74599670-eb74-4348-9e7a-f9dc07c576a2"
$appFile = "C:\Temp\"
$siteCollection = ""
$appName = "My App"
$web = Get-SPWeb -Identity $siteCollection
$realm = Get-SPAuthenticationRealm -ServiceContext $web.Site;
$appIdentifier = $clientID  + '@' + $realm;
#Register the App with given ClientId
Register-SPAppPrincipal -DisplayName $appName -NameIdentifier $appIdentifier -Site $web | Out-Null
$app = Import-SPAppPackage -Path $appFile -Site $siteCollection -Source ObjectModel -Confirm:$false 	
#Install the App
Install-SPApp -Web $siteCollection -Identity $app	| Out-Null

Download Script

How to create custom SharePoint 2013 list using PowerShell?

SharePoint provides an interface called AddFieldAsXml. This basically creates a field based on the specified schema. Nothing fancy. Right?

However, this can be very handy when you want to create your own custom lists in SharePoint programmatically. I will be using PowerShell as an example to demonstrate how you can simply create SharePoint Lists based on plain, simple xml definitions.

To start with, I created an xml template based on which I want to create my custom list using PowerShell.

<!--?xml version="1.0" encoding="utf-8"?-->

            0 - 10000
                0 - 10000
                10000 - 50000
                50000 - 100000
                100000 or more

In this example, I have used Text, Number, Date and Choice as the Field Types. But this could be anything which is supported by SharePoint or even your own content types. Check the documentation on MSDN for Field Element.

Next step is to read this xml file, parse it and use the AddFieldAsXml method to create fields in this list. The PowerShell snippet below does the trick. Straight and Simple. Isn’t it?

Add-PSSnapin Microsoft.SharePoint.PowerShell 

function CreateList($siteCollectionUrl, $listName, $templateFile){
	$spWeb = Get-SPWeb -Identity $siteCollectionUrl 
	$spTemplate = $spWeb.ListTemplates["Custom List"] 
	$spListCollection = $spWeb.Lists 
	$spListCollection.Add($listName, $listName, $spTemplate) 
	$path = $spWeb.url.trim() 
	$spList = $spWeb.GetList("$path/Lists/$listName")
	$templateXml = [xml](get-content $templateFile)
	foreach ($node in $templateXml.Template.Field) {
		$spList.Fields.AddFieldAsXml($node.OuterXml, $true,[Microsoft.SharePoint.SPAddFieldOptions]::AddFieldToDefaultView)

$siteCollectionUrl = ""
$listName = "New Custom List"
$templateFile = "template.xml"

CreateList $siteCollectionUrl $listName $templateFile

And here is the result, just by configuration in xml file you can create lists using PowerShell.

SharePoint Create List

Download Example

Happy Coding!

How to pass Professional Scrum Master (PSM I) Certification?

So, Finally, I am a Professional Scrum Master (PSM I). It was kind of overdue to have an attempt on Professional Scrum Master Certification conducted by I have been using Agile (and Scrum) in my projects in various capacities for many years now. And to be honest, the simplicity of the framework and the empirical process behind the Scrum has fascinated me to pursue the subject further.

Scrum Master Badge

I registered for the exam last week. The process is relatively simple. Register on the and pay $100 for the PSM I exam. They would send your password to access the exam within a business day. And you have a period of 14 days to give the exam. I kind of used 7 days just to make sure I don’t waste my $100 as the score you need to get is fairly high (85%). That means from a set of 80 questions, you need to get at least 68 right.


I did not do excellent and just made sure to pass the exam on border by having 69 answers correct i.e. 86.6 % as score. Well, not really bad for first attempt. After the exam, you do not get report of the question which you did not answer correct but you do get a consolidated report on the areas with your scores. Mine looked something like this. This at least helps me to focus on specific areas for Scrum.

Scrum Score

Tips and tricks to pass Professional Scrum Master Certification

Let’s be honest, there is no ready-made formula for success in any field and same applies here while you try to attempt get the certificate for Professional Scrum Master. And in fact, the certificate value just drops to nothing if you have not learned anything during the process.


Nevertheless, here are my two cents based on my own experience.

  1. Make sure you go through the official Scrum Guide written by Ken Schwaber & Jeff Sutherland thoroughly. The guide is very concise, but covers the essence of Scrum.
  2. I would suggest you to go through the Scrum Open Assessments (both and Scrum Master and Scrum Developer) multiple times before you are scoring 95% or more consecutive time. The open assessment has a set of approximate 40 questions. Out of which 30 questions are presented in an assessment.The assessment will give you an idea of questions which would be asked in the actual assessment. Additionally, you would find some of the questions from open assessment repeated in the actual assessment. This gives you surplus time and confidence during the examination.Personally, I found 10-15 questions being repeated in the actual exam.
  3. Don’t look around on internet for dumps of questions. You are not going to find any. And even if you do, then what’s the point of giving exam and get credentials? You could better create a Photoshop version of certificate to boss around.
  4. PSM I Simulated Exams from Management Plaza is a good tool to evaluate your preparation and readiness for the PSM I Exam. The simulator not only contains a set of 250 practice questions but also explains each and every answer.
  5. Everyone has their own preference over books, I went through the ‘A Guide to the SCRUM BODY OF KNOWLEDGE (SBOK™ GUIDE), 2013 Edition’. It’s to the point and gives you decent read before the exam.
  6. The Scrum Master Training Manual from Frank Turley and Nader K. Rad is another resource guide which compliments the original Scrum Guide. It has a lot of practical examples which are important from the exam point of view. One thing which I liked in the manual is the explanation of burn-down charts which is not explicit in the Scrum Guide. And ofcourse, it’s FREE!
  7. Go through the discussions on Scrum Forum. You would find a lot of people discussing their experience and queries on this forum. Group of folks here are willing to help you if have questions.  A great place to hang around for Scrum enthusiasts.
  8. During the exam, don’t try to Google (or Bing) around for the answers. First, you won’t find any. Second, there is no guarantee that the answer is right. Third, you would be wasting your time. Keep in mind that you need to complete 80 questions in 60 minutes. That gives you 45 seconds per question. Yes, you need to be fast.
  9. And yes, make sure you have an isolated place where you can concentrate while giving the examination. As the examination is online, you need to have a good and consistent internet connection in place. Have a glass (or two) of water with you. You would feel thirsty during the exam. Psychological? Not sure!

Good luck to those of you who are preparing and attempting for PSM!

And while you are here, you can read my earlier related posts on Scrum and Agile.

A beginner’s guide to Scrum

The curious case of Scrum Master’s role

An introduction to Agile Methodology

A beginner’s guide to various Software development methodologies

PSM Certification Guide

How to Pass Professional Scrum Product Owner (PSPO I) Certification?

Error during configuration of Scheduled Backups for Team Foundation Server

Another situation which should be easy and straight forward, but then you realise that it does not work.

I wanted to set up my own Team Foundation Server Express (yes, it’s light, free version with certain limitations but a good way to start for your small team or organisation). The installation process is quite straight forward and clear.

However when I wanted to configure the scheduling for back-ups of my TFS database, I was continuously receiving the error below.

TF401009: The TFS service account NT AUTHORITY\LOCAL SERVICE cannot access network shares. Change to an account that can access the backup path.

TF400997: SQL Server service is running as NT AUTHORITY\NetworkService. Please change this account to an account that can be granted permission on the backup path.


I had given the permissions to both the accounts on the network share. Actually, it was shared folder on the same machine as I installed the TFS in single server mode.

To add a bit of background, my Team Foundation Service was running under Local Service and SQL Server was running under Network Service. This is the reason why these two accounts come into picture.

Looking at the logs, it was more clear that for scheduling backups TFS expects the proper (preferably service) accounts to be used to run SQL Server and Team Foundation Service. The following error messages can be seen in the log.

Verify that account ‘NT AUTHORITY\NetworkService’ is not local service or local system.
Node returned: Error
TF400997: SQL Server service is running as NT AUTHORITY\NetworkService. Please change this account to an account that can be granted permission on the backup path.

And a similar one for TFS Service as well.


The resolution is to change the Service account for both SQL Server (MSSQLSERVER) and TFSJobAgent (Visual Studio Team Foundation Background Job Agent) to another account.


However, this seems to be check only during the configuration. And if you change the service log on user back to Network and Local Service after configuration, the backup procedure still runs without any hassle.

Not sure about the details why this is prevented during configuration. If you are aware of the reason, please share!