Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

PowerShell Script for Paul’s Survey: how is your tempdb configured?

Paul Randal (b | t) posted a survey how is your tempdb configured? Since I just happen to be at a SQLskills event this week I went ahead and created a PowerShell script to get the info across all your servers.

You’ll need a list of servers in a text file ($serverfile), one per line. It will output the list to ’sqlskills – tempdb proc, file count results.txt’ in the directory from which it ran. Send that file, or the results to Paul.

$serverfile = 'SQL_Servers_PROD.txt'
$query = "SELECT os.Cores, df.Files
FROM
   (SELECT COUNT(*) AS Cores FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE') AS os,
   (SELECT COUNT(*) AS Files FROM tempdb.sys.database_files WHERE type_desc = 'ROWS') AS df;"
 
$dt = $null
 
#Get list of servers from SQL_Servers_PROD.txt
#Exclude any lines that start with # or are blank
gc $serverfile | ?{-not $_.StartsWith("#") -and $_ -ne "" -and $x -lt 2 } | %{
    $tempdbstats = Invoke-Sqlcmd -Query $query -ServerInstance $_ -SuppressProviderContextWarning
    if ($dt -eq $null) {
        $dt = , $tempdbstats
    } else {
        $dt += $tempdbstats
    }
}
$dt | ft -AutoSize | Out-File 'sqlskills - tempdb proc, file count results.txt' -Encoding UTF8

The output looks like this:

Cores Files
----- -----
   16     1
   16     8

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.