SQL Server – Use Powershell to find what trace flags are running

Bit of a quick post this morning, continuing the theme of using powershell to standardise your SQL Server estate.  This one is on using PS to collect data on which global trace flags are running on your servers.

Continue reading


SQL Server – Using powershell to start and stop all SQL Server services on a server

We recently had some issues with a bunch of load balanced VMs, where one of our ESX hosts had died and the other was struggling to take up the slack.  As such, I made a decision to take a bunch of SQL Server instances down to alleviate some stress.  A bunch being some 40 odd instances across 20 servers.

Continue reading

Replacing SQL Text within a script file using regular expressions and Powershell

I recently needed to update a load of scripts in our Source Control to remove the filegroup declaration from ON [PRIMARY] to just not be declared, so that new objects would be deployed to the default filegroup on some new databases we had created. Personally, I like to keep user tables away from the system tables to assist with being able to do piecemeal restores, and allow easier management of database files.

From looking at the scripts, written by lots of different devs, there were various different standards and styles that had been applied, from not declaring a filegroup (yey no work for me!), to differing numbers of spaces between the ON and [PRIMARY] keywords:-

  • ON   [PRIMARY]

All in all, there were a few hundred scripts that I needed to make this change to and I had absolutely no intention of doing that manually. So: to Powershell!

Continue reading

Standardising new database creation using Powershell and SMO

Personally, I think that standardisation of a SQL Server is an important part of the DBA role; it should start by ensuring that instances are standardised, with the same naming convention and set of configurations added, and go all the way through to coding practices and object naming convention. It’s important not to miss off database creation from this list.
Continue reading

Scripting Indexes using Powershell and SMO

As part of a review of the indexes across my new workplace’s estate, I wanted to get all the indexes scripted up into re-runnable scripts which could then be loaded into source control.  However, considering the number of databases, tables and indexes, doing this manually would be both time consuming and mind numbingly boring; as such, I decided to create a powershell function to do this.
Continue reading