Quick One-Liner – Search in Files

Today’s one liner is all about how to search file contents in Powershell, recursively.  Bring on the code!

Get-ChildItem -recurse -filter *.cs | Select-String -pattern “SqlConnection” | group path | sort count | select name > sqlconnection.txt

First, let’s just get all C# source files:  Get-ChildItem -recurse -filter *.cs

Second, let’s select all lines with our search string: Select-String -pattern “SqlConnection”

Third, let’s group all the matchinfo objects returned by path: group path

Fourth, let’s sort the grouping objects by count ascending:  sort count

Fifth, let’s select only the name (full file path): select name

Finally, let’s pipe it into a file:  > sqlconnection.txt

Yay!  Easy and simple.  I’m going to turn this into a function that I can have at the ready.


Git Tip: Deleting Old Local Branches With PowerShell

So I was reading Git Tip: Deleting Old Local Branches, but the code was for Bash/Linux. My first instinct in all of these cases is to translate it into PowerShell.  I am shamelessly stealing the format of the original post as I liked the way he laid it out.

0) Prune Remote Branches

>git remote prune origin

Read more about git remote prune here

Deletes all stale remote-tracking branches under . These stale branches have already been removed from the remote repository referenced by , but are still locally available in “remotes/”.

1) List local git branches

git branch -vv will list all local branches along with some additional information such as their related upstream/remote branch and latest commit message

> git branch -vv
feature/some-local-only-feature cba8191 Some commit message
feature/some-old-feature cba2191 [origin/feature/some-old-feature: gone] Some commit message about some old feature
feature/some-active-feature wba2191 [origin/feature/some-active-feature: ahead 40, behind 10] Some active feature branch

2) Filter git branches down to only those with deleted upstream/remote counterparts

Next, we pipe the output from git branch -vv into grep ‘origin/.*: gone]’. This filters our list down to only lines that match the regex origin/.*: gone] leaving us with

> git branch -vv | where {$_ -match '\[origin/.*: gone\]'}
feature/some-old-feature cba2191 [origin/feature/some-old-feature: gone] Some commit message about some old feature
Read more about using regular expressions with PowerShell here.

3) Pluck out branch names from output

Piping that into split()[0] cleans up our output so we end up with a branch name per line.

> git branch -vv | where {$_ -match '\[origin/.*: gone\]'} | foreach {$_.split(" ", [StringSplitOptions]'RemoveEmptyEntries')[0]}'
feature/some-old-feature cba2191 [origin/feature/some-old-feature: gone] Some commit message about some old feature
This is because split translates the string to an array (items being separated by space(s)) and [0] is the first item, which is the branch name. We also use the RemoveEmptyEntries option so that any leading/trailing spaces are removed.  Example:

> git branch -vv | where {$_ -match '\[origin/.*: gone\]'} | foreach {$_.split(" ", [StringSplitOptions]'RemoveEmptyEntries')}
[0]: feature/some-old-feature
[1]: cba2191
[2]: [origin/feature/some-old-feature:
[3]: gone]
[4]: First
[5]: words
[6]: in_commit_message…

4) Delete the branches!

Next, we pipe our filtered down, cleaned up git branches list into git branch -d (short for –delete) and say our final goodbyes.

>git branch -vv | where {$_ -match '\[origin/.*: gone\]'} | foreach {git branch -d ($_.split(" ", [StringSplitOptions]'RemoveEmptyEntries')[0])}


You should always run step 0 and step 3 to validate the branches you are going to be deleting before running step 4.  Don’t say you weren’t warned.

This does not clean up any local branches that were never pushed to the remote server.  That’s a script for another day.

sp_AllNightLog (or how I learned to stop worrying and love log shipping)

I never really got into log shipping.  I didn’t have to.  I was lucky enough to deal with only one instance of SQL Server early in my career, and then I moved almost instantly into Microsoft where I worked exclusively on Failover Cluster instances.  After a couple years, we finally moved into using Availability Groups with Database Mirroring.   Still, never had to deal with anything below that.

Our migrations were fairly straightforward.  Stop all services, set all databases to read-only, take a backup, restore to the new server, set to read-write, and move the DNS name to point to the new cluster.  Easy as pie, even when there were dozens of databases.

However, migration is not as easy when you are talking terabytes of data.  Which is the case when I began migrating our System Center Operations Manager (SCOM) main database and data warehouse.  Suddenly, I’m having to deal with 3 TB of data.  That is not so easy to deal with.

A couple months before, the first version of sp_AllNightLog had been announced.  I hadn’t paid much attention to it, because it didn’t fulfill a need of mine.  Now, suddenly, maybe it could.  I decided to give it a try.

I already had Ola Hallengren’s maintenance scripts running backups on the source server, so I didn’t see the need to install sp_AllNightLog there.  I was setting up a new availability group, so I installed it on both of my target servers.  From now on, I will talk only about one server, but everything I show, and did, applied to both servers.  I ran the following code to set it up:

  EXECUTE sp_AllNightLog_Setup  @RPOSeconds = 900,  @RTOSeconds = 900,  @BackupPath = ‘S:\Databases’,  @RestorePath = ‘\\SourceServer\Backups\SOURCESERVER’,  @Jobs = 2,  @RunSetup = 1;

This generated six SQL Agent Jobs, two backup jobs, two restore jobs and two polling jobs.


I enabled the sp_AllNightLog_PollDiskForNewDatabases job, the two restore jobs, and then went to look at the source code to see which tables were being populated.  It turns out restore information actually go into the msdb.dbo.restore_worker table, while backups go to an msdbCentral database that is created as part of the setup sproc.  So I took a gander in there.


Uh oh.  We have system databases in there.  What the heck??  I took a look at the job history of the two restore jobs.  For some reason, nothing was in the history.  Even more what the heck??

Digging around in the source code for sp_AllNightLog, I found out a couple interesting things.

First, there was no filtering based on existing databases.  This meant that the version of msdbCentral would get overwritten.  I’m not so sure about that.  Maybe that needs to be filtered on?  I discovered later (documentation ftw) that this was by design.  msdbCentral holds the backup information and they wanted to have that information available on the currently valid node to make sure they were meeting RTO/RPO metrics.

Secondly, there was no filtering on system database.  I stopped the restore jobs, put into the steps to push out to a log file, and then restarted them.  Ahah!  It was attempting to restore system databases, but thankfully failing.  Not good at all.

Thirdly, the restore agent job didn’t run and return.  It looped continuously.  This meant I wouldn’t see the output unless the job was stopped.  Not good.

I undertook two things immediately.  I added a line to the sp_AllNightLog that filtered out system databases (and created an issue on Github to track this bug).  Then I added output to a file logging for the agent jobs.  Now I could finally see what was going on.

Lo and behold, it worked! I could leave it be for days at a time and it would keep everything up to date automatically.  If I needed to reseed the database, I would disable the polling and restore agent jobs, truncate the msdb.dbo.restore_worker table, delete the existing databases, and then enable the polling and restore agent jobs.  It just worked!!

As a way to help me monitor what was going on, I also created a set of monitoring queries that I have posted here.  They show various configuration options, including what databases will be restored, and which ones will be backed up.

I want to highlight just the last line.


This query allows you to see, in real time, what restore and backup operations are happening right at this second.  It is invaluable to see how far along, with an actual percentage value, those jobs are.  It helps when you are restoring multi-terabyte databases and log files.

I used it to initially seed both nodes in the AG.  However, when it came time to switch over, I needed to drop all the databases on the secondary node due to issues with the AG itself.  Going forward, I would only do the initial seeding to one node.  Then I would cut over to that node, making it primary, and using sp_AllNightLog to seed any secondary nodes before joining the databases to the AG.  This is what I eventually did with my current setup.  Cutover was smooth, and joining the AG was seamless.  Just make sure to disable the agent jobs before joining a node to the AG.

Thanks go out to Brent and the team at Brent Ozar Unlimited for some awesome tools.

Updated Management Packs

Recently, we were looking into when someone had updated some management packs and when.  I went digging a bit and found the following query helped me out.  Run it against the OperationsManager database.


Here is the plaintext code:

SELECT TOP (1000) [MPName]
FROM [OperationsManager].[dbo].[ManagementPack]
ORDER BY [MPLastModified] DESC

Visual Studio 2015 TFS and Powershell

I found a really great article about using the TFS Client dll’s here.  However, that only worked for VS2013.  I had to fix it for VS2015.

For some interesting reason, they are no longer GACing the assemblies, nor are they in a default location.  Now they are sym-linked to the existing version.

Here is my example PowerShell code:

$vs2015Path = “C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\CommonExtensions\Microsoft\TeamFoundation\Team Explorer”
Add-Type -Path “$vs2015Path\Microsoft.TeamFoundation.Client.dll”
Add-Type -Path “$vs2015Path\Microsoft.TeamFoundation.Common.dll”
Add-Type -Path “$vs2015Path\Microsoft.TeamFoundation.Build.Client.dll” 
Add-Type -Path “$vs2015Path\Microsoft.TeamFoundation.Build.Common.dll”
Add-Type -Path “$vs2015Path\Microsoft.TeamFoundation.WorkItemTracking.Client.dll”
Add-Type -Path “$vs2015Path\Microsoft.TeamFoundation.VersionControl.Client.dll”
Add-Type -Path “$vs2015Path\Microsoft.TeamFoundation.ProjectManagement.dll”

Git in the Enterprise

As we move forward in DevDiv, our next steps are to move to http://visualstudio.com as our default instance of TFS.  This also comes with a switch to Git as the default VCS.

Here are some of the caveats we’ve discovered.

1. Git does not do binaries well.  There is a size limitation within Git.  See here and here.  This means we need to componentize our source tree.  A lot of pruning and splitting apart.

2. Workflow changes.  I have this one command to make sure my local repo is up to date and merged (git pull origin master). You will need to understand that branches are cheap, disposable, and far easier to use than in normal TFS.  I can create and destroy branches locally as many as I want, and then merge back into master, and only push those changes back up.

3.  You have to read the documentation.  Yes, it’s a VCS, but it’s not a centralized one like SVN or TFS.  Even though it may be TFS on the back end, you still need to RTFM.

Computer Owner In PowerShell

I frequently need to figure out who the owner of a computer object is in Active Directory.

Enter PowerShell to the rescue!


Easy as pie.  Here is the full code for you to use.

$oADObject = Get-ADComputer <ComputerName> -Properties *
$oAceObj   = Get-Acl -Path (“ActiveDirectory:://RootDSE/” + $oADObject.DistinguishedName);

Gist Link: https://gist.github.com/dalehhirt/3f03b9ee27a245933e1a

Mandatory Configuration Options

I just read an article titled The 3 Configuration Options that you always have to change in SQL Server! by Klaus Aschenbrenner (@Aschenbrenner).  I went and looked over my SOP script that I keep for all of my SQL servers.  The full version can be found here.

My script does several things:

  1. Sets 3 of the 4 most important config settings I need in my environment (more on these later).
  2. Sets a standard size for the Model data and log files (1024MB with 1024MB growth).
  3. Sets all database owners to sa.  This is so we’re not reliant on Windows Authentication if someone leaves the company.
  4. Sets all databases to PAGE_VERIFY CHECKSUM.  This is vital to make sure data is valid within the page when reading and writing from disk (See Kendra Little’s blog here).
  5. Computes the value of max server memory (#4 of 4 most important config settings) based on Jonathan Kehayias’ blog post.

So I only do one of three that Klaus recommends.  Well, let me rephrase that.  I only script out one of three.  The other two, Max DOP and Cost Threshold of Parallelism, I tend to leave alone unless I see actual performance issues.

The configuration options I do set, lines 25-33 of my script are:  backup compression default, optimize for ad hoc workloads, and remote admin connections.

Backup compression is needed because we do a lot of network shipping of backup files, both at creation and for secondary backup purposes (testing restores, dev environments, and offsite tape backups.)   I know this has been a lifesaver when needing to restore some 600GB monster database, but the backup file is less than 30GB.  In SQL Server 2014, this feature is enabled from Standard Edition on up.  All versions can restore a compressed backup.  So use it often.

Optimize for adhoc workloads won’t ever hurt you, and most likely will only help, as it’s more about storage of the compiled query plan that anything else.  I found this made a big difference in several of my OLTP workloads.

Remote Admin Connections.  I can’t tell you the number of times this has saved my bacon when I can’t log in to the Windows server but need to perform some random admin function to get data back.  The DAC is a lifesaver.

What are your mandatory configuration options?

SCOM, Data Warehouse Moves, and Get-SCOMDataWareHouseSetting

My operations team runs System Center Operations Manager 2012 R2 internally.

I host the OperationsManager database on a SQL Server 2012 Availability Group, and for the most part, it works well.  However, for space reasons, we needed to move the Data Warehouse database to its own server backed by SAN storage.

After following this guide (https://technet.microsoft.com/en-us/library/hh268492.aspx), the database was successfully moved, and the data is flowing correctly.  In addition, all reporting is working as expected.  It was moved from scomsql (SQL 2012 Availability Group) to scomsql3 (standalone instance).

Everything worked out except for the Get-SCOMDataWareHouseSetting PowerShell cmdlet.  It would return the name of the old server.


To find the current value, I ran the following:


And now it returns the correct value.

Update: due to the vagaries of WordPress, I’m linking to the SQL script here.