Checking TempDB
Woensdag 29 Oktober 2014 at 11:49 am. Gebruikte Tags: powershell, sqlserverOn SQLServer forums you will often find advice on how to configure Tempdb. How many files should you add, what should be the sizes, and also what Traceflags should be enabled to correctly enhance the performance of your TempDB?
Paul Randal wrote this:
http://www.sqlskills.com/blogs/paul/correctly-adding-data-files-tempdb/
I created a PowerShell script to check the TempDB files and the startup parameters on one or more SQL Server instances in one go.
Here's the script:
<# .SYNOPSIS Checks if TempDB configuration is OK. .DESCRIPTION Shows the data files used by TempDB ( as long as they are on the PRIMARY FG! Adapt script if not ), and the startup parameters. For TempDB, look for TraceFlags T1117 and T1118. .EXAMPLE Check-TempDB -Instances 'mysql1','mysql2\sqlexpress','mysql3' .LINK http://www.sqlskills.com/blogs/paul/correctly-adding-data-files-tempdb/ .NOTES Name : Check-TempDB.ps1 Author : Klaas Vandenberghe Date : 2014-10-29 #> param( [Parameter(Position = 0)] [string[]]$Instances = @('localhost') ) $SUPQuery = 'SELECT PropertyStrValue FROM SqlServiceAdvancedProperty WHERE PropertyName = "STARTUPPARAMETERS"' foreach ($sqlinst in $Instances) { Write-Host "`n`n$sqlinst`n" -ForegroundColor Yellow $SMOserv = New-Object Microsoft.SqlServer.Management.Smo.Server $sqlinst # create SMO-Object for this instance $sqlserv = $sqlinst.split("\")[0] # take the computername out of the instancename $Version = $SMOserv.VersionMajor # the version dictates which Wmi Class to use $SUP = Get-WmiObject -Namespace "root\Microsoft\SqlServer\Computermanagement$Version" -computername $sqlserv -Query $SUPQuery $SUP.PropertyStrValue.ToString().split(';') # print the startup parameters $SMOserv.Databases["TempDB"].FileGroups["PRIMARY"].files | Select Filename,usedspace,size,maxsize,@{l='Growth';e={$_.growth.tostring() + ' ' + $_.growthtype}}| Format-Table -AutoSize # print the TempDB files }
Geen reacties