DBATools Favourite functions - Why use your hands, when you have a hammer...

DBATools is the best thing since sliced bread, i'm sure alot of us gluten tolerant people can all agree on that. Powershell makes automation and multiserver management a lot faster and easier, and DBATools just makes that easier still as the community has written many, many wonderful functions for us.

Created by Chrissy LeMaire originally (http://www.linkedin.com/in/chrissylemaire) , the main contributors page can be found at: team – dbatools . All credit to these folks.

Refer to download – dbatools for installation instructions, its remarkably simple.

I often build solutions around the dbatools functions, the below is just some of my Operational favourites. With some I've included the output pipe that I use most frequently, but obviously, you can view and use the output however you choose to. Clearly, DBATools has many functions to add/remove/update SQL as well, but i'm just folking on the 'gets' here.

Naturally, as mentioned...its powershell, you can programmatically use this for any of your automation needs. Marvellous.

The ever growing list of commands can be found here: command index – dbatools . This can prove daunting to new users of the toolset, so here's a starter for 10.

Connectivity

test-dbaconnection servername

This little gem will test server connectivity, SQL connectivity, PSRemoting connectivity, and even return the Authentication method used, ie.. KERBEROS or NTLM.

Backups

get-dbalastbackup servername | ft

This one shouldn't need explaining. Make sure backups are occurring (and test restore them elsewhere, which DBATools can also help with).

Disk and Database Capacity

get-dbadiskspace servername

See how your server space is looking.

get-dbadbspace servername | out-gridview

Also how large your databases are currently.

Really Useful: I've included the out-gridview (ogw) pipe in this one. What this pipe output gives is a sortable and filterable grid for simple viewing.

Need to quickly see what DB Log file has suddenly used the space? No problem. Simply type LOG in the filter bar, and all rows with LOG are shown, then sort the rows by File Size. You can see in 2 seconds flat.

Configuration

get-dbainstanceproperty servername| ft

Get all of the properties you could possibly be interested in at the server instance level.

Installed Features

get-dbafeature servername | ft

You need to check what SQL features are installed, which can be a bit trickier that you'd like. This allows a fast check, no fuss, lovely. (Yes, I need to patch, but this is my 'throwaway' instance.)

Server Details

get-dbacomputersystem servername

Need to check the server spec, no problem. Usefully, this also gives an indication if the server has a reboot pending. Very useful to avoid those failed attempted installations as the server needs a bounce.

Tempdb

get-dbatempdbusage servername | ft

My tempdb is vast, who's causing it?

Memory

Get-DbaMemoryUsage servername | ft

Quick breakdown of the main memory brokers.

Jobs

get-dbaagentjob servername | ft

Essentially this gives information similar to activity monitor.

And a subset of that:

Get-DbaRunningJob servername | ft

What jobs are running right now.

CheckDB

get-dbalastgoodcheckdb servername | ft 

Days since the last good checkdb by database, and a status column, and if DataPurity is enabled or not.

Ports

get-dbatcpport servername

Returns the IP and TCP port for the SQL Engine.

Network Latency

test-dbanetworklatency servername

This runs a basic command against the target, and returns a breakdown of the execution and round trip times. As ever, i'll put my usual disclaimer on this one, don't take just this information and start swearing at your local network techie.

Services

get-dbaservice servername | ft

Useful to see what services are installed, their status and service accounts in use.

Startup Parameters

get-dbaStartupParameter servername

get-dbatraceflag servername

Quick look into how SQL server is configured to start up, or just the running traceflags. Saves a trip to SQL Configuration Manager.

Operating System

get-dbaoperatingsystem servername

Returns useful information about the OS, including timezone for troubleshooting those strange date formats.

Central Management Server

get-DbaRegServer -SqlInstance cmsserver  -Group 'Live\UK'

This will return all servers within a specific CMS group.

get-dbaregserver -sqlinstance cmsserver -servername servername

This will return details of which folders a server is within your CMS. Really useful if you break your CMS down into Application name folders and have a servername that you don't know what it relates to.

Files

Find-DbaOrphanedFile servername

Ever have a worry that DBs have been detached and the files left behind, or a DB offlined and then deleted, also leaving files behind? The above checks for exactly that and reports any files that aren't currently attached to a database, wasting your precious disk resource. Combine that with the CMS command above, you can sweep your estate in a single command. Nice!

Patches

get-dbainstalledpatch servername

Quick run down of what SQL updates were installed when.

Bonus Connectivity.

This one isn't actually DBATools, but I love it, and really useful when you need to test connectivity between 2 remote servers, you can pass in a TCP port to test against too. Useful for debugging those pesky firewalls.

Enter-PSSession servername
Test-Netconnection -computername servername2 -port 1433
Exit-PSSession

The above is just a quick rundown of what I seem to use most in Dbatools. Its a magnificent resource, if you don't use it, I strongly urge you to take a look.

Thanks for reading

Rod the Tool.

Title image courtesy of brgfx on FreePik.