MSSQL Shrink / Truncate transaction log

2 min read SQL Transaction logs allow you to restore a database to a specific point in time, and is a great option for a production database, but these logs must be backed up frequently enough to prevent them from filing up. However, you need to back up the transaction log itself, not just the database, if you want to use the Full or Bulk Logged recovery models. This article describes the right way to set this up: 

If you’ve already taken a full backup of your database, and find yourself in a crunch out of space, the following is a quick way of clearing the transaction log to recover space:

Log into Microsoft SQL Server Management Studio…

Installing and configuring SonarQube with Azure DevOps/TFS

8 min read Our team follows a process adapted from Microsoft’s Release Flow [see:], in which we create a branch off develop (our long-running mostly-stable product), do our work, commit it (with the PBI/Bug number in a comment), push the branch, then go into TFS and create a pull request. TFS will suggest a shortcut link to create a PR for the branch you just pushed to your default branch (in our case develop); or you can click the New pull request button and choose your source and target branches.

Application Lifecycle Management / World-Class DevOps

9 min read

Development process

Scrum team, planning work in 2 week increments, reacting fast to change, following best practices in research, planning, architecture and writing software. The team’s focus is on building great software, so we want them focused on what’s important.

  • As a developer, I take on a new feature or bug that’s in the sprint and ready for development, and marks it as development in progress.
  • Then I create a new branch off of develop, and do the work.
  • Then commit one or more times, and mention #[Work Item Number] in the commit message, along with a description of what changed
  • Once I consider the work complete and “tested” locally on my computer, I’ll push the branch, go into TFS and click the shortcut to create a PR

* develop is locked, so all changes have to enter via Pull Requests
  • On the next page I’ll make sure everything looks good, by doing one more check of the changes in the code, and click Create
  • Done and move on to greater things …

Exporting and Importing Visual Studio and Resharper Settings

4 min read Whenever I get a new computer, or reinstall Windows and such, I realize and remember that I again have to adjust all the settings to just the way I like them, and usually don’t have time for it. This consists of keyboard shortcuts for the most part on a new setup, but I’m  also a big fan of some Resharper features. Resharper can really slow down Visual Studio with defaults, because they enable a lot of features, which although cool and useful, aren’t worth the performance trade-off for me. I…

Securing a new web server

< 1 min read Ideally you already have a snapshot you maintain and have hardened, and just have to clone it when you need new servers deployed. If you have to build a clean image to use for your future VMs:
it’s usually easier to start with an existing VM you’ve configured, because it already has everything you need installed, but it might also have other stuff installed that you don’t necessarily want on every new VM, so here’s a way I quickly set up a new server image:

1. Install a clean version of Windows on a new VM

2. Set up your networking stack, and run Windows Update and install all the updates available…

Process / Memory Dumps

< 1 min read Record a memory dump
* The process is paused while the memory is being copied to the file. Doing this on a production instance can cause the application to become unresponsive and possibly crash.

Open Task Manager, right click on the process and click Create dump file

Publishing Swagger API Documentation to Confluence during Release

3 min read We use Confluence for our company’s documentation, and as such we need to keep samples and documentation up-to-date, but ideally without any work on our part, so we automated it.
The goals are:
– Have complete and up-to-date documentation of our APIs and up-to-date code samples in Confluence
– Notify stakeholders (other teams) of any breaking changes before they go into production and give them a few days to prepare or let us know so we can delay the release

We document our APIs using Swagger, which gets us a good part of the way there.
We accomplished the second goal by doing the first one twice (instructions below): deployments to our Staging server publish documentation to one confluence section, which stakeholders can subscribe to and get a diff whenever we update the page (again we don’t have to do this), and then our Production deployment publishes documentation to the production section on confluence. We also use semantic versioning for our APIs, which give our stakeholders a really quick idea of what we intend with the release.

Publishing release notes to TFS/VSTS Wiki during Release

2 min read As part of our process we create PBIs (work items) describing what needs to be done, and then during commit, our commit messages describe what we actually changed. During a release we need to keep track of this information and inform stakeholders as to what changed. This used to be fairly time-consuming step in which we had to gather information from the PBIs and commit messages and write up what we’re releasing. We managed to automate it to the point where all the information is linked cleanly into one page per release, with each work item linked directly, which allows someone to quickly get additional context.

SQL Server hide database list from a particular user

< 1 min read One of the things I’ve been searching for is to hide the list of databases a user doesn’t have access to. By default, when a user connects to SQL Server, especially a shared instance, he sees all the databases available on the server, even though he doesn’t have access to view or access any of them. The goal is to only show a user the database(s) he should see. This isn’t fully supported yet by SQL, but there’s a way to solve the problem for the majority of cases. First,…

SQL Full Backup all databases

< 1 min read Backups are one of the most important tasks when maintaining a system, and as the complexity of your environment grows, you need tools to automate tasks. Using the SQL Agent, you can schedule the following stored procedure to back up every database and database log on your server (or modify it to your needs), excluding some stuff you can live without.

    DECLARE @name VARCHAR(50) /*database name*/
    DECLARE @dbpath VARCHAR(256) /*path for backup db files*/
    DECLARE @logpath VARCHAR(256) /*path for backup log files*/
    DECLARE @dbName VARCHAR(256) /*filename for backup*/
    DECLARE @logName VARCHAR(256) /*filename for backup*/
    DECLARE @fileDate VARCHAR(20) /*used for file name*/
    SET @dbpath = 'C:Database Backup'
    SET @logpath = 'C:Database Log Backup'
    SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
    DECLARE db_cursor CURSOR FOR
    SELECT name
    FROM master.dbo.sysdatabases
    WHERE name NOT IN ('master','model','msdb','tempdb') /* Exclude any databases you don't want to backup */
    OPEN db_cursor  
    FETCH NEXT FROM db_cursor INTO @name  
           SET @dbName = @dbpath + @name + '_' + @fileDate + '.BAK'
           BACKUP DATABASE @name TO DISK = @dbName WITH FORMAT;
           SET @logName = @logpath + @name + '_' + @fileDate + '.BAK'
           BACKUP LOG @name TO DISK = @logName;
           FETCH NEXT FROM db_cursor INTO @name
    CLOSE db_cursor
    DEALLOCATE db_cursor