SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Checking Tempdb with dbatools

I really like the dbatools project. This is a series of PowerShell cmdlets that are built by the community and incredibly useful for migrations between SQL Servers, but also for various administrative actions. I have a short series on these items.

In SQL Server 2016, the setup program was altered to better configure tempdb at installation time. This was in response to the observation that few people actually make any changes to the default configuration, which was suboptimal in SQL Server 2014-.

Going through and checking all of the configurations you have isn’t easy, and isn’t necessarily the type of work that anyone wants to do. dbatools makes this really easy and quick with Test-DbaTempDbConfiguration.

Using this cmdlet is easy. I’ll call this with an instance and get results of a number of checks that are useful for your tempdb configuration:

2018-04-20 09_25_07-cmd - powershell

This isn’t necessarily easy to read, so let’s add a Format-Table.

2018-04-20 09_24_57-cmd - powershell

That’s not great, as I’m missing the CurrentSetting field. I’ll add a SELECT and include the fields I want. I can even add multiple instances in here:

2018-04-20 09_30_56-cmd - powershell

Now I can scan through here, looking to see if my settings have deviated from the recommendations and best practices. This could easily be used to filter the results for items that don’t match, save the results as a CSV, and you now have a picklist of items to work on as you find time.

dbatools is an essential tool for me. I’d urge you to download the module and experiment with the cmdlets.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Leave a comment on the original post [voiceofthedba.com, opens in a new window]

Loading comments...