Showing posts with label wmi. Show all posts
Showing posts with label wmi. Show all posts

2025-02-24

Set Windows pagefile

When troubleshooting memory issues it sometimes is necessary to change the Windows pagefile to something different that the default settings. In this case I would like the pagefile to have the same size as the amount of physical memory and be placed on another drive.

(Default Windows pagefile)

Add custom pagefile

To disable „Automatically manage paging file size for all drives“ WMI can be used with the Win32_ComputerSystem class. I use it with a WMI CmdLet and not a CIM CmdLet as the put-method does not work with a CIM CmdLet.

$PageFile = Get-WmiObject -Class Win32_ComputerSystem -EnableAllPrivileges
$PageFile.AutomaticManagedPagefile = $false
$PageFile.put() | Out-Null

When the automation is disabled then the pagefile can be configured. In this case I am setting Initial and Maximum size both to the amount of physical memory. It is recommend (1) that the max pagefile is set to the amount of physical memory plus 257 MB. All this can be done with CIM and WMI CmdLets. The memory size from Win32_PhysicalMemory is in Bytes where the properties to Win32_PageFileSetting are in MB and this is why I convert to MB in the script.

$PhysicalMemory = Get-CimInstance -ClassName Win32_PhysicalMemory
[int]$PageFileSize = 0
foreach ( $Device in $PhysicalMemory ) { $PageFileSize += $Device.Capacity/1MB }
$PageFileSize += 257
"Setting PageFile size to $PageFileSize MB..."

$PageFileSet = Get-WmiObject -Class Win32_PageFileSetting
#$PageFileSet | fl *
$PageFileSet.InitialSize = $PageFileSize
$PageFileSet.MaximumSize = $PageFileSize
$PageFileSet.Put() | Out-Null

(Windows pagefile custom configuration)

If the server is with NUMA configuration then Win32_PhysicalMemory will return an array of memory devices. This is why I traverse the output with foreach. In my case the computer is a virtual machine with eight vCores on eight vSockets and it looks like Windows Server (2016) split that in four vNUMA nodes as I get an array of four elements from Win32_PhysicalMemory. My workstation is the host and runs with one 12C/24T processor.

There must be enough free storage. If that is not available the Win32_PageFileSetting.Put() will fail with this not usefull message:

Exception calling "Put" with "0" argument(s): "Value out of range "
At line:12 char:1
+ $PageFileSet.Put() | Out-Null
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

To activate the configuration the server needs a reboot.

(Windows pagefile custom configuration activated)

The custom pagefile is added in the right place. But the default pagefile still exist.

Remove default pagefile

The default pagefile is removed with the method Delete() to the specific selected default pagefile by a WQL statement.

$defaultPageFile = Get-WmiObject -Query "SELECT * FROM Win32_PageFileSetting WHERE Name = 'C:\\pagefile.sys'"
#$defaultPageFile | fl *
$defaultPageFile.Delete()

This takes effect with another reboot. This reboot also remove the file "pagefile.sys" from the root of the C-drive.


(Windows default pagefile deleted)

The methods to a WMI object like from Win32_PageFileSetting above does not show with Get-Member. But the documentation to the interface IWbemClassObject lists and describe the general methods to a WMI object.

Notes

(1) Microsoft: Overview of memory dump file options for Windows > Complete memory dump.

History

2025-06-01 : Custom pagefile and default pagefile are differelt.

2025-04-29 : 257 MB addition to pagefile size with reference to (1).

2025-02-24 : Post created

2015-11-05

SQL Server Native Client WMI name

I am developing a SQL Server deployment package. And again I do not hit the nail in the first stroke.
This makes me install - and uninstall - SQL Server components several times. Rather quickly I get tired by clicking through Uninstall in Windows Control Panel. This can usually be fixed with a PowerShell script, but uninstalling SQL Server Native Client (SNAC) gave me some trouble.

To get the metadata on the installed programs through CIM I created a variable to work on
$CimProduct = Get-CimInstance -ClassName Win32_Product -Filter "Name LIKE 'Microsoft SQL Server 2012 Native Client'"
But the result in $CimProduct was empty.

A general request showed me that SNAC is installed with the name that I filtered on
Get-CimInstance -ClassName Win32_Product -CimSession $CimSession
gave a long list which I have narrowed down here
Name             Caption                                              Vendor                                               Version
----             -------                                              ------                                               -------
...
Microsoft SQL... Microsoft SQL Server 2012 Native Client              Microsoft Corporation                                11.0.2100.60
...

Then I tried a more general filter
$CimProduct = Get-CimInstance -ClassName Win32_Product -Filter "Name LIKE '%Native Client%'"
"Name = '$($CimProduct.Name)'."

with success
Name = 'Microsoft SQL Server 2012 Native Client '.
Immediately it looked like the text that I filtered on in the beginning.
But notice the trailing space!
If I some day meet the Product Manager we have something to talk about ;-)

With the new knowledge I got the search right
$CimProduct = Get-CimInstance -ClassName Win32_Product -Filter "Name LIKE 'Microsoft SQL Server 2012 Native Client '"

Now I was able to uninstall SNAC in one line
$Uninstall = Invoke-CimMethod -Query "SELECT * FROM Win32_Product WHERE Name = 'Microsoft SQL Server 2012 Native Client '" -MethodName Uninstall
$Uninstall.ReturnValue

And with success
0

2012-01-05

PowerShell WMI - The RPC server is unavailable

I have a ongoing task of refining an automated collection (instrumentation) of computers in the organisation running a SQL Server database installation.
Most of the data are collected by WMI and the automation is done by PowerShell.
The other day I ran into a challenge when the computer is available, but the rights are insufficient. The WMI call by the cmdlet Get-WmiObject er initialised as the computer exists, but the data request fails due to the lach of rights. Actually the error does not generate a exception that is caught by the PowerShell try-catch exception handling. It looks like by various fora that it is a remoting issue.

A quick workaround is to take a look at the $Error last element with a match, and filter out the error message with the cmdlet parameter "-ErrorAction SilentlyContinue".
I know that the errormessage contains "HRESULT: 0x800706BA" and a match on this will catch the error in a robust way.


$DebugPreference = 'continue'

$ComputerName = 'SQL30.test.dn.ext'

"{0:s}  `"$ComputerName`"." -f ([System.DateTime]::Now) | Write-Debug

$objComputer = New-Object System.Object
Add-Member -InputObject $objComputer -MemberType NoteProperty -Name Name -Value $ComputerName

$WQL = "SELECT Manufacturer,Model,TotalPhysicalMemory,NumberOfProcessors,SystemType FROM Win32_ComputerSystem"
try {
  $_ComputerSystem = Get-WmiObject -Query $WQL -ComputerName $ComputerName -ErrorAction SilentlyContinue
}
catch [System.UnauthorizedAccessException] {
  ":: ERROR: WMI access denied."
}

# The error "The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)" is not caught but still generates an error
if ($Error[0].Exception -match "HRESULT: 0x800706BA") {
  ":: ERROR: WMI COM (RPC) not available."
}

Add-Member -InputObject $objComputer -MemberType NoteProperty -Name Manufacturer -Value $_ComputerSystem.Manufacturer
Add-Member -InputObject $objComputer -MemberType NoteProperty -Name Model -Value $_ComputerSystem.Model
Add-Member -InputObject $objComputer -MemberType NoteProperty -Name MemoryInKb -Value $(if($hasWmiAccess) {($_wmi.TotalPhysicalMemory / 1KB)} else {$null})
Add-Member -InputObject $objComputer -MemberType NoteProperty -Name CpuCoreCount -Value $_ComputerSystem.NumberOfProcessors
Add-Member -InputObject $objComputer -MemberType NoteProperty -Name SystemType -Value $_ComputerSystem.SystemType

$objComputer

2010-12-22

Disk Info

I have a daily interest to know how the disks are used, and this script give some useful data from the Windows point of view.

function Get-Disk {
[CmdletBinding()]
param(
  [Parameter(Mandatory=$true,ValueFromPipeline=$true)]
  [String]$ComputerName
)
Begin {
  "Get-Disk( '$ComputerName' )" | Write-Verbose
  $Stopwatch = [System.Diagnostics.Stopwatch]::StartNew()
}

Process {
  $CimSessionOption = New-CimSessionOption -Protocol Dcom
  $CimSession = New-CimSession -SessionOption $CimSessionOption -Verbose:$false -ComputerName $ComputerName
  $SqlDisks = @()
  'Get Local Disks...' | Write-Verbose
  $Disks = Get-CimInstance -ClassName Win32_LogicalDisk -Filter 'DriveType=3' -CimSession $CimSession -Verbose:$false
  foreach ($Disk in $Disks) {
    "Disk.DeviceID = '$($Disk.DeviceID)'." | Write-Verbose
    $SqlDisk = New-Object -TypeName PSObject
    $SqlDisk.PSObject.TypeNames.Insert(0, 'SqlAdmin.Disk')
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name DriveLetter -Value $Disk.DeviceID
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name DiskFreeSpaceBytes -Value $Disk.FreeSpace
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name DiskSizeBytes -Value $Disk.Size
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name FileSystem -Value $Disk.FileSystem
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name VolumeDirty -Value $Disk.VolumeDirty
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name VolumeName -Value $Disk.VolumeName
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name VolumeSerialNumber -Value $Disk.VolumeSerialNumber

    $Partition = Get-CimAssociatedInstance -CimInstance $Disk -ResultClassName Win32_DiskPartition -Verbose:$false
    "Partition.DeviceID = '$($Partition.DeviceID)'." | Write-Verbose
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name PartitionDeviceID -Value $Partition.DeviceID
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name BlockSizeBytes -Value $Partition.BlockSize
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name BlockCount -Value $Partition.NumberOfBlocks
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name PartitionDiskIndex -Value $Partition.DiskIndex
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name PartitionSizeBytes -Value $Partition.Size
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name StartingOffset -Value $Partition.StartingOffset
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name PartitionType -Value $Partition.Type

    $Drive = Get-CimAssociatedInstance -CimInstance $Partition -ResultClassName Win32_DiskDrive -Verbose:$false
    "Drive.DeviceID = '$($Drive.DeviceID)'." | Write-Verbose
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name DrivePartitionCount -Value $Drive.Partitions
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name DriveBytesPerSector -Value $Drive.BytesPerSector
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name DriveDeviceID -Value $Drive.DeviceID
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name InterfaceType -Value $Drive.InterfaceType
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name DriveFirmwareRevision -Value $Drive.FirmwareRevision
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name DriveModel -Value $Drive.Model
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name DriveSizeBytes -Value $Drive.Size
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name DriveStatus -Value $Drive.Status
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name DriveSerialNumber -Value $Drive.SerialNumber

    $Volume = $Volumes = Get-CimInstance -ClassName Win32_Volume -Filter "DriveLetter='$($Disk.Name)'" -CimSession $CimSession -Verbose:$false
    "Volume.Label = '$($Volume.Label)'." | Write-Verbose
    'Running Defrag Analysis...' | Write-Verbose
    $DefragStopwatch = [System.Diagnostics.Stopwatch]::StartNew()
    $Report = Invoke-CimMethod -CimSession $CimSession -InputObject $Volume -MethodName DefragAnalysis -Verbose:$false
    $DefragStopwatch.Stop()
    "Defrag Analysis completed. Duration = $($DefragStopwatch.Elapsed.ToString()) [hh:mm:ss.ddd]." | Write-Verbose
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name AverageFileSize -Value $Report.DefragAnalysis.AverageFileSize
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name AverageFragmentsPerFile -Value $Report.DefragAnalysis.AverageFragmentsPerFile
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name AverageFreeSpacePerExtent -Value     $Report.DefragAnalysis.AverageFreeSpacePerExtent
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name ClusterSize -Value $Report.DefragAnalysis.ClusterSize
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name VolumeFreeSpace -Value $Report.DefragAnalysis.FreeSpace
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name FreeSpacePercentFragmentation -Value     $Report.DefragAnalysis.FreeSpacePercentFragmentation
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name LargestFreeSpaceExtent -Value $Report.DefragAnalysis.LargestFreeSpaceExtent
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name MFTPercentInUse -Value $Report.DefragAnalysis.MFTPercentInUse
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name MFTRecordCount -Value $Report.DefragAnalysis.MFTRecordCount
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name TotalExcessFragments -Value $Report.DefragAnalysis.TotalExcessFragments
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name TotalFiles -Value $Report.DefragAnalysis.TotalFiles
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name TotalFolders -Value $Report.DefragAnalysis.TotalFolders
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name TotalFragmentedFiles -Value $Report.DefragAnalysis.TotalFragmentedFiles
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name TotalFreeSpaceExtents -Value $Report.DefragAnalysis.TotalFreeSpaceExtents
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name TotalMFTFragments -Value $Report.DefragAnalysis.TotalMFTFragments
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name TotalMFTSize -Value $Report.DefragAnalysis.TotalMFTSize
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name TotalUnmovableFiles -Value $Report.DefragAnalysis.TotalUnmovableFiles
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name UsedSpace -Value $Report.DefragAnalysis.UsedSpace
    Add-Member -InputObject $SqlDisk -MemberType NoteProperty -Name VolumeSize -Value $Report.DefragAnalysis.VolumeSize

    $SqlDisks += $SqlDisk
  }
}

End {
  $Stopwatch.Stop()
  "$($SqlDisks.Count) disks found on the computer. Duration = $($Stopwatch.Elapsed.ToString()) [hh:mm:ss.ddd]." | Write-Verbose
  $SqlDisks
}

} # Get-Disk()

### INVOKE ###
$MyDisks = Get-Disk -ComputerName '.' -Verbose
$MyDisks | Out-GridView


I use the DCOM protocol due to some restrictions in some network segments.

The usage of the CmdLet Get-CimAssociatedInstance makes associating the classes mush easier compared to associating the classes in WMI.
The execution of the method DefragAnalysis is different used from CIM than WMI. The use of the CmdLet Invoke-CimMethod took some time to get, but I find it much cleaner to use.

When the script is executed, the output could look like this:

DriveLetter                   : C:
DiskFreeSpaceBytes            : 488987631616
DiskSizeBytes                 : 750153363456
FileSystem                    : NTFS
VolumeDirty                   : False
VolumeName                    : ********
VolumeSerialNumber            : ********
PartitionDeviceID             : Disk #0, Partition #0
BlockSizeBytes                : 512
BlockCount                    : 1465143296
PartitionDiskIndex            : 0
PartitionSizeBytes            : 750153367552
StartingOffset                : 1048576
PartitionType                 : Installable File System
DrivePartitionCount           : 1
DriveBytesPerSector           : 512
DriveDeviceID                 : \\.\PHYSICALDRIVE0
InterfaceType                 : IDE
DriveFirmwareRevision         : EXT0
DriveModel                    : Samsung SSD 840 EVO 750G
DriveSizeBytes                : 750153761280
DriveStatus                   : OK
DriveSerialNumber             : ********    
AverageFileSize               : 142
AverageFragmentsPerFile       : 1,15
AverageFreeSpacePerExtent     : 6483968
ClusterSize                   : 4096
VolumeFreeSpace               : 489014767616
FreeSpacePercentFragmentation : 31
LargestFreeSpaceExtent        : 334636171264
MFTPercentInUse               : 100
MFTRecordCount                : 473855
TotalExcessFragments          : 54011
TotalFiles                    : 339026
TotalFolders                  : 76795
TotalFragmentedFiles          : 12689
TotalFreeSpaceExtents         : 75369
TotalMFTFragments             : 2
TotalMFTSize                  : 485228544
TotalUnmovableFiles           : 147
UsedSpace                     : 261138595840
VolumeSize                    : 750153363456
The verbose output shows some durations on the execution:
VERBOSE: Get-Disk( '.' )
VERBOSE: Get Local Disks...
...
VERBOSE: Disk.DeviceID = 'C:'.
VERBOSE: Partition.DeviceID = 'Disk #0, Partition #0'.
VERBOSE: Drive.DeviceID = '\\.\PHYSICALDRIVE0'.
VERBOSE: Volume.Label = '********'.
VERBOSE: Running Defrag Analysis...
VERBOSE: Defrag Analysis completed. Duration = 00:00:56.9287395 [hh:mm:ss.ddd].
VERBOSE: 2 disks found on the computer. Duration = 00:00:59.5802508 [hh:mm:ss.ddd].


The script is not optimized for performance and do have a response time on some seconds. The response time increased especially when I added the defragmentation data.

Please notice that the classes Win32_Volume and Win32_DefragAnalysis are not available on Windows XP or earlier.

Reference

MSDN Library: Win32_DiskDrive, Win32_DiskPartition, Win32_LogicalDisk, Win32_Volume and „WMI Tasks: Disks and File Systems“.
Richard Saddaway: "Defrag Analysis Part 2".


History

2010-12-22 First release of the script.
2015-06-25 Second release of the script, now using CIM CmsLets.

2010-09-22

Correct Disk Alignment?

Correct alignment of the sectors on a disk is somewhat important. This is described in several places.
The PowerShell script below checks the alignment of all disks on a given computer.
$wql = "SELECT DiskIndex,Index,StartingOffset FROM Win32_DiskPartition"
Get-WmiObject -Query $wql -ComputerName '.' | Select-Object DiskIndex,Index,@{Name='Offset (KB)';Expression={$_.StartingOffset / 1024}} | Format-Table -AutoSize


The output could be something like
DiskIndex Index Offset (KB)
--------- ----- -----------
        0     0        31,5
        1     0        31,5
        2     0        31,5
        3     0        31,5
        4     0        31,5
        5     0        31,5
        6     0        31,5

If the Offset is fractional, like above, the disk is not aligned correct.

Reference
Microsoft KB929491
Jimmy May, Denny Lee (SQLCAT): „Disk Partition Alignment Best Practices for SQL Server

2010-06-30

NTFS Cluster Size

When formatting a partition for SQL Server data, it is recommended by storage vendors, MVPs and other that the allocation unit size should be the largest size possible. For NTFS this means 64 KB. Often the DBA is called in after the server is build, installed and configured, but I still want to confirm the allocation unit size. This can be done bu the command-line tool FSUTIL (%SystemRoot%\system32\fsutil.exe), and wrapping a generic command-line statement in PowerShell gives me the value itself to be viewed or used by the automation.
When using FSUTIL, the allocation unit size is typically called NTFS Cluster Size, and is named "Bytes Per Cluster". On newer Windows installations you need to execute FSUTIL with administrative privileges.

function Get-NtfsClusterSize {
param ( [char[]]$driveLetter = 'c' )

 $driveLetter | ForEach-Object {
  if ( Test-Path "$($_):" ) {
   $cs = New-Object PSObject
   $cs | Add-Member -MemberType NoteProperty Drive $_
   # Get 7th line of output from FSUTIL
   $cs | Add-Member -MemberType NoteProperty Size (fsutil fsinfo ntfsinfo "$($_):")[7].split()[-1]
   $cs
  }
 }
}

The function can be called with one drive letter as parameter value.
Get-NtfsClusterSize c

Or with several drive letters as parameter value.
Get-NtfsClusterSize c,h

The output values can be referred as object attribute values and on implicit drive reference.
$clusterSize = Get-NtfsClusterSize
$clusterSize.Drive
$clusterSize.Size


I find it a rather cumbersome way to get a simple value, but by using WMI it can be done much simpler.
$wql = "SELECT BlockSize,DriveLetter,Label FROM Win32_Volume WHERE FileSystem='NTFS'"
Get-WmiObject -Query $wql -ComputerName '.' | Select-Object DriveLetter,Label,BlockSize | Format-Table -AutoSize


Reference
SQLCAT: "Disk Partition Alignment Best Practices for SQL Server"
Brent Ozar: "Ten Things DBAs Need to Know About Storage"
Chad Miller: "Disk Alignment Partitioning: The Good, the Bad, the OK and the Not So Ugly"
VistaForums: "Getting the 'cluster size' of your hard disk?"
MSDN Library: "Win32_Volume Class"

Backlog
Get the NTFS Cluster Size of a Mount Point.

2008-09-23

List Windows Shares using PowerShell

A list of shares on a given Windows host can be created by:
Get-WmiObject Win32_Share

But the two shares "IPC$" og "ADMIN$" is not really interesting.
A more usefull list can be created by:
Get-WmiObject -Query "SELECT * FROM Win32_Share WHERE Name != 'ADMIN$' AND Name != 'IPC$'"

I know it's not correct or nice to use "SELECT *...", but this is an quick and dirty example.

The WMI class definition can be browsed in WMI CIM Studio, that can be downloaded from Microsoft.
Please use your preferred internet search site to get the current URI.