Hunka.Junk Files

Filed under: Microsoft, Movies, Technology — Tags: , , , , , , — Developer42 @ 17:21

A few times, even with capacity planning in place, we’ve hit disk capacity limits.
When this occurs you search for content which can be deleted to free up some space to get you moving again.
Here’s my new quick fix for this: HunkaJunk files.
Create dummy files which take up space, but can be quickly & easily deleted without risk; giving you teh wiggle room you need to get moving again.

There are various ways this could be done, but a useful tool is FSUtil.
This allows you to create blank files which take up a defined number of bytes.
e.g. to create a 1GB file you could run the following code from the Windows command line.

fsutil file createnew c:\PurgeForDiskSpace\Hunka.Junk 1073741824

– c:\PurgeForDiskSpace\Hunka.Junk – is the filename of your junk file (you can call it anything; just make it obvious)
– 1073741824 – is the filesize in bytes. NB: 1GB = 1024MB = 1024 * 1024 KB = 1024 * 1024 * 1024 bytes = 1,073,741,824 bytes.


T-SQL: Generate Series: Getting a list of numbers in a given range.

Filed under: Microsoft, SQL Server, Technology — Tags: , , , , , , , , , — Developer42 @ 01:27

I recently came across the Postgres generate_series function whilst reading a blog post.
So far as I can tell, there’s no equivalent in T-SQL. To make up for this, I coded my own, making use of the recursive nature of common table expressions:

create function dbo.generate_series
	  @start bigint
	, @stop bigint
	, @step bigint = 1
	, @maxResults bigint = 0 --0 = unlimited
returns @results table(n bigint)

	--avoid infinite loop (i.e. where we're stepping away from stop instead of towards it)
	if @step = 0 return
	if @start > @stop and @step > 0 return
	if @start < @stop and @step < 0 return
	--ensure we don't overshoot
	set @stop = @stop - @step

	--treat negatives as unlimited
	set @maxResults = case when @maxResults < 0 then 0 else @maxResults end

	--generate output
	;with myCTE (n,i) as 
		--start at the beginning
		select @start
		, 1
		union all
		--increment in steps
		select n + @step
		, i + 1
		from myCTE 
		--ensure we've not overshot (accounting for direction of step)
		where (@maxResults=0 or i<@maxResults)
			   (@step > 0 and n <= @stop)
			or (@step < 0 and n >= @stop)
	insert @results
	select n 
	from myCTE
	option (maxrecursion 0) --sadly we can't use a variable for this; however checks above should mean that we have a finite number of recursions / @maxResults gives users the ability to manually limit this 

	--all good	

Example Usage:

--check we get expected results
select * from generate_series(1, 10, default, default)
select * from generate_series(10, 5, -1, default)
select * from generate_series(1, 10, 4, default)
select * from generate_series(1, 10, default, 2)
select * from generate_series(1, 10, 4, -1)

--check we don't get results if we have "invalid" input
select * from generate_series(1, 10, 0, default)
select * from generate_series(10, 1, default, default)
select * from generate_series(10, 5, 1, default)
select * from generate_series(1, 10, -4, default)

NB: Should you wish to generate a series of dates instead of a series of numbers, check my comments here:


Powershell Script: Get Server Inventory

Filed under: Microsoft — Tags: , , , , , , , , , , — Developer42 @ 16:36

**Update 2017-02-05**

This script is now available on GitHub:, where it will be kept updated with functionality requests and refactored to make it easier for you to adapt this script to meet your requirements through parameters.

The following script (heavily based on polls all servers in a given text file, returning information about their disk capacity, # of cpus (sockets & cores), amount of RAM, and OS info.

$serverList = ".\Servers.txt"
$outputCSV = ".\ServerInventory.csv"

$scriptpath = $MyInvocation.MyCommand.Path
$dir = Split-Path $scriptpath
pushd $dir

[System.Collections.ArrayList]$sysCollection = New-Object System.Collections.ArrayList($null)
foreach ($server in (Get-Content $serverList))
    "Collecting information from $server"
        [wmi]$sysInfo = get-wmiobject Win32_ComputerSystem -Namespace "root\CIMV2" -ComputerName $server -ErrorAction Stop
        [wmi]$bios = Get-WmiObject Win32_BIOS -Namespace "root\CIMV2" -computername $server
        [wmi]$os = Get-WmiObject Win32_OperatingSystem -Namespace "root\CIMV2" -Computername $server
		#[array]$disks = Get-WmiObject Win32_LogicalDisk -Namespace "root\CIMV2" -Filter DriveType=3 -Computername $server
		[array]$disks = Get-WmiObject Win32_LogicalDisk -Namespace "root\CIMV2" -Computername $server
		[array]$procs = Get-WmiObject Win32_Processor -Namespace "root\CIMV2" -Computername $server
        [array]$mem = Get-WmiObject Win32_PhysicalMemory -Namespace "root\CIMV2" -ComputerName $server
        [array]$nic = Get-WmiObject Win32_NetworkAdapterConfiguration -Namespace "root\CIMV2" -ComputerName $server | where{$_.IPEnabled -eq "True"}
        $si = @{
			Server			= [string]$server
			Manufacturer	= [string]$sysInfo.Manufacturer
			Model			= [string]$sysInfo.Model
			TotMem			= "$([string]([System.Math]::Round($sysInfo.TotalPhysicalMemory/1gb,2))) GB"
			BiosDesc		= [string]$bios.Description
			BiosVer			= [string]$bios.SMBIOSBIOSVersion+"."+$bios.SMBIOSMajorVersion+"."+$bios.SMBIOSMinorVersion
			BiosSerial		= [string]$bios.SerialNumber
			OSName			= [string]$os.Name.Substring(0,$os.Name.IndexOf("|") -1)
			Arch			= [string]$os.OSArchitecture
			Processors		= [string]@($procs).count
			Cores			= [string]$procs[0].NumberOfCores
		$disks | foreach-object {$si."Drive$($_.Name -replace ':', '')"="$([string]([System.Math]::Round($_.Size/1gb,2))) GB"}
    catch [Exception]
        "Error communicating with $server, skipping to next"
        $si = @{
			Server			= [string]$server
			ErrorMessage	= [string]$_.Exception.Message
			ErrorItem		= [string]$_.Exception.ItemName
       [void]$sysCollection.Add((New-Object PSObject -Property $si))   
$sysCollection `
	| select-object Server,TotMem,OSName,Arch,Processors,Cores,Manufacturer,Model,BiosDesc,BiosVer,BiosSerial,DriveA,DriveB,DriveC,DriveD,DriveE,DriveF,DriveG,DriveH,DriveI,DriveJ,DriveK,DriveL,DriveM,DriveN,DriveO,DriveP,DriveQ,DriveR,DriveS,DriveT,DriveU,DriveV,DriveW,DriveX,DriveY,DriveZ,ErrorMessage,ErrorItem `
	| sort -Property Server `
	| Export-CSV -path $outputCSV -NoTypeInformation    

Servers.txt should be held in the same directory as the script, & would look something like this:


Output looks something like this:

"MyServer1","8 GB","Microsoft Windows Server 2003 R2 Enterprise Editio","","4","","HP","ProLiant DL380 G5","Default System BIOS","P56.2.4","CZC8924LQ6      ","","","68.33 GB","0.58 GB","683.5 GB","0 GB","","","","","","","","","","","","","","","","","","","","","",""
"","48 GB","Microsoft Windows Server 2012 Datacente","64-bit","8","1","VMware, Inc.","VMware Virtual Platform","PhoenixBIOS 4.0 Release 6.0     ","","VMware-42 3b 8b d9 d3 92 b3 8e-9a 43 b1 b5 e6 a8 b2 74","0 GB","","31.66 GB","99.87 GB","3.63 GB","","","","","","","99.87 GB","","","","49.87 GB","","","","19.87 GB","","","","","","","",""
"YetAnotherServerName","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))",""
"","48 GB","Microsoft Windows Server 2012 Datacente","64-bit","8","1","VMware, Inc.","VMware Virtual Platform","PhoenixBIOS 4.0 Release 6.0     ","","VMware-42 3b 8b d9 d3 92 b3 8e-9a 43 b1 b5 e6 a8 b2 74","0 GB","","31.66 GB","99.87 GB","3.63 GB","","","","","","","99.87 GB","","","","49.87 GB","","","","19.87 GB","","","","","","","",""


Intelligently Splitting an Address

Filed under: SQL Server, Technology, Uncategorized — Tags: , , , , , , , , — Developer42 @ 08:05

An issue recently arose at work where I was asked to take a street address from one field, and split it into two for use with another system. The simple solutions available were:

  • simply splitting on the first comma to appear in the string, and putting each side of this split into the respective fields.
  • breaking the string down the middle.
  • putting everything I could into the first field, then any remainder into the second.

However, none of these seemed to feel right; the first solution would work well, but didn’t take the destination fields’ sizes into account, and the second and third solutions could break half-way through a word / in the case of the third, may not use the second field.
As a result, I came up with the following function; it’s probably not the best solution (since this was written in half an hour on getting home from a night out, and with just a quick check that all worked OK this morning), but hopefully it’ll be useful to some people.

The function takes 3 arguments; the string to be split, the length of the first field to be populated, and the length of the second. It returns a table with an ID field to give the order of the elements (not really required), and a String field to give the two return values in separate rows. This function will always return exactly 2 rows.

The function is written in T-SQL for SQL Server, though I believe it should also work with Sybase.





CREATE FUNCTION dbo.SplitSpecial


      @stringToSplit nvarchar(max)

      ,@len1 int

      ,@len2 int


RETURNS @returnVal table (id int identity(1,1),string nvarchar(max)) 



      declare @i int = 0                        --used as a counter when searching for chars

      , @j int = 0                              --another char counter

      , @ldiff int = 0                    --says how many characters we have spare to play with

      , @mark int = 0                     --a marker

      , @tempmark int   = 0                     --a marker

      , @back nvarchar(max)               --stringToSplit in reverse

      , @temp nvarchar(max)               --for storing works in progress

      , @forward bit = 0                        --used to indicate the direction of the string from the marker


      --remove any starting, ending or duplicate spaces should they exist, to try to cram in the characters

      select @stringToSplit = ltrim(rtrim(@stringToSplit))

      while (charindex('  ',@stringToSplit)&gt;0)

            select @stringToSplit = replace(@stringToSplit,N'  ',N' ')


      --find out what we have to play with / reduce max size of strings to size of input

      select @tempmark = len(@stringToSplit)

      select @len1  = 0.5*((@len1 + @tempmark) - abs(@len1 - @tempmark))

            ,  @len2  = 0.5*((@len2 + @tempmark) - abs(@len2 - @tempmark))

      select @ldiff = (@len1 + @len2) - @tempmark


      if @ldiff &gt; 0


            --get a good starting point / break point: no longer required

            --select @mark = round(len(@stringToSplit)/2,0) --assume we can get away with halving the string

            --select @mark = 0.5*((@mark + @len1) - abs(@mark - @len1)) --account for first field being too short (least function math hack)

            --select @mark = len(@stringToSplit) - 0.5*(((len(@stringToSplit)-@mark) + @len2) - abs((len(@stringToSplit)-@mark) - @len2)) --account for 2nd field being too short (least function math hack)


            --the two fields are big enough for us to try some funky stuff with

            --is there a delimeter we can use?

            select @temp = substring(@stringToSplit,len(@stringToSplit)-@len2,@ldiff+1)--get crossover chars; the +1 accounts for dropping the delim

            while patindex('%[a-z0-9]%', @temp) &gt; 0

                  set @temp = stuff(@temp, patindex('%[a-z0-9]%', @temp), 1, '')

            if (len(@temp)+charindex(N' ',@temp)) &gt; 0 --a suitable delim exists in the crossover area


                  if charindex(N',',@temp)&gt;0 --if commas are available, these are the preferred delimeter

                        select @temp = N','


                        if charindex(N' ',@temp)&gt;0  --spaces are the second favourite

                              select @temp =N' '

                  select @back = reverse(@stringToSplit)

                  , @i=1

                  , @j = ceiling(len(@stringToSplit)/2)-1

                  , @forward = 1

                  , @mark = len(@stringToSplit)

                  while @i&lt;=(len(@temp)+charindex(N' ',@temp))--try out all available delimeters (if comma or space are present, others are ignored)


                        while @j&gt;0


                              select @tempmark = charindex(substring(@temp,@i,1),@stringToSplit,@j)

                              if @tempmark &gt; 0 and @tempmark &lt; @mark

                                    select @mark = @tempmark, @forward = 1

                              select @tempmark = charindex(substring(@temp,@i,1),@back,@j)

                              if @tempmark &gt; 0 and @tempmark &lt; @mark

                                    select @mark = @tempmark, @forward = 0

                              if(@mark&lt;=@len1 and (len(@stringToSplit)-@mark)&lt;=@len2)


                              select @j = @j - 1


                        select @i = @i + 1


                  --select @mark = case @forward when 0 then len(@stringToSplit)-(@mark+1) else @mark end

                  select @mark = case @forward when 0 then (1+len(@stringToSplit))-@mark else @mark end



      --catch any issues &amp; account for when we can't find a good break point / there isn't space

      if (@mark &lt;= 0 or @mark &gt; @len1)

            select @mark = @len1


      insert into @returnVal (string)     values (ltrim(rtrim(substring(@stringToSplit,1,@mark))))

      insert into @returnVal (string)     values (ltrim(rtrim(substring(@stringToSplit,1+@mark,@len2))))








--examples of the function in action

select * from dbo.SplitSpecial('33 Rue De Maisons, Paris',100,100)

select * from dbo.SplitSpecial('33 Rue De Maisons, Paris, France 123456',100,100)

select * from dbo.SplitSpecial('33,Rue,De,Maisons,Paris,France,123456',100,100)

select * from dbo.SplitSpecial('33 Rue De Maisons Paris France 123456',100,100)

select * from dbo.SplitSpecial('33 Rue De Maisons, Paris, France 123456',8,50)

select * from dbo.SplitSpecial('this string is too big for the fields',10,10)

At some point, I hope to update this to allow it to take any number of fields of different lengths, to make it more general (e.g. should you ever have a full address to be split into 4 or 5 lines), but for now, it works (so far, at least)

Blog at

%d bloggers like this: