2026-01-01

Top-ten posts 2025

2025 was with way les posts than several prior years. That could partly due to a new focus in my daily job activities such as more abstract architecture and non-technical challenges.

This is the top-ten list of my posts on the 2025 statistics.

RankMoveRank 2024TitleViewsCreated
101 SqlBulkCopy with PowerShell9942017-07-30
2> 9> 10 xp_readerrorlog 5782020-01-07
3-12 ISO 8601 date formatting using PowerShell 4142008-08-14
4+3 7 MSDTC Operations4072022-11-29
5-23 Audit Log for Analysis Services3562014-01-08
6+2 8 xp_instance_regwrite syntax 2822010-09-15
7-2 5 Add SQL Agent job step with tokens in output file name 2712023-02-14
8-26 DBCC CHECKDB with PowerShell 2442014-10-09
9+48 Start shared HTA with PowerShell 2582012-09-13
10(N/A) Set Windows dump file1802025-06-01

With a total of 70400 views in 2025 the top-ten posts is with 3966 views about 6 % of the views this year. The all time number of views is 473292.

This year there are both repeats and new posts on the top-ten. The number one post on SqlBulkCopy with PowerShell is sovereign again this year.

Former Top-ten

My first post on this blog was published 2008-08-13, and many posts are irrelevant today. But I still think it is a good exercise to write each post as it gives me a option to review, refine and improve a description or solution.

 

2025-11-30

Restore WideWorldImporters

For many years AdventureWorks was the primary sample database from Microsoft for SQL Server. But with many new features and new ways to structure a database WideWorldImporters was created. And this is in general recommended as the SQL Server sample database from Microsoft.

The restore script is a evolvolution from the script to Restore AdventureWorks and the script to Put SQL Server PVS in own filegroup

All scripts are in my SQLAdmin repository (link).

The first version of these scripts are made for SQL Server 2025, and I intent to update them with new major versions of SQL Server.

2025-10-20

New Windows Storage

Sometimes one just has to add new storage to a Windows installation. And in some cases like adding physical storage there are a few simple steps like below.

Values like disk number you can get from the disk handling GUI in Windows (right-click Windows).

 

1) Initialize disk

Initialize-Disk -Number 2 -PartitionStyle GPT

2) Create partition

New-Partition -DiskNumber 2 -UseMaximumSize -DriveLetter S

3) Format volume

Format-Volume -DriveLetter S -FileSystem NTFS -NewFileSystemLabel Spare -Full:$false -UseLargeFRS

If you are adding more than 8 TB or know that the storage mostly is for a few large files then add the CmdLet parameter -AllocationUnitSize 64KB. Add the CmdLet parameter -UseLargeFRS to use large File Record Segment (FRS) when files are created, altered and deleted often.

 

If the storage unit is reused from another Windows installation, then the unit can be wiped like this

Clear-Disk -Number 2 -RemoveData

If the disk holds one or more OEM recovery partition then add the CmdLet parameter -RemoveOEM.

 

2025-06-01

Set Windows dump file

Investigating a memory leak we have to get a full memory dump under certain conditions. Setting the Windows pagefile is descriped in the post „Set Windows pagefile size“.

Dump File

  • Relocate; configuration, test
  • Registry (PS)
  • CIM/WMI (PS)

There are six differet levels of Windows debugging information, which can be viwed and set in Windows System Properties:

(Windows System Properties > Advanced > Startup and Recovery)

Windows Registry

The current path to the Windows dump file is in the Registry. This can be viewed and changed in Registry Editor (RegEdit):

(Registry Editor: Windows Dump File path)

The path to the registry keys is:
HKLM\SYSTEM\CurrentControlSet\Control\CrashControl\.

Get and Set

Get the Dump File with path:
$DumpFile = (Get-ItemProperty -Path Registry::HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\CrashControl -Name DumpFile).DumpFile
The result is a simple string (System.String) with the default value „%SystemRoot%\MEMORY.DMP“.

Set the Dump File by a string with the complete value
[string]$DumpfileName = 'F:\MEMORY.DMP'
Set-ItemProperty -Path Registry::HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\CrashControl -Name DumpFile -Value $DumpfileName

The new value will be effective with the next boot of the computer (host).

Generate dump

Get SysInternals NotMyFault.exe and start a console as administrator. Run notmyfault64.exe

... and click "Crash". This will create a "Blue Screen Of Death" (BSOD)

The collection will take several minutes.

When Windows start again and you log on there is a message from the Shutdown Event Tracker

It has no technical consequense so you can just click "Cancel".

The dump file is placed on the custom location given above. To analyze the dump file you should move it to a workstation with tools like WinDBG.

Reference

Microsoft: Overview of memory dump file options for Windows

SevenForums.com: Dump File – Change Default Location

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