T-SQL Tuesday #046: hosted by Rick Krueger

This month the T-SQL Tuesday invitation is from Rick Krueger (B|T), who want us to dust out the closet and tell how how we escaped from the jungles of Borneo, swam across the vast Indian ocean and saved the tiger with nothing but a few clips and rubber band a.k.a Rube Goldberg Machine.

While working for a Big blue box electronic retailer, we had to support a few SQL 2000 instances. And we had to figure out a way to accurately determine if certain jobs had finished or were still executing. There were other jobs that needed to be put on hold until the job of interest was executing.

The constraints placed were:

1. Conclusively determine if the job has started execution.
2. Without the use of undocumented features we should be able to determine the current execution state of the job.
3. Once a job’s current state is determined, we should wait until the state changes.
4. Depending on the execution state, we should decide either to wait for a further delta minutes and check the state or determine if the job finished successfully.
5. Return the final result to the calling code block.

This is how I went about it:

1. Wait for a delay of 1 minute before even checking.
2. Checking if we actually had a running job, was done in a roundabout way. The last step_name of the previous execution of the job always contains ‘(Job outcome)’ in sysjobhistory table. Because of this, we can pick an instance_id of this job that has an instance_id greater than the MAX(instance_id) when the step_name was ‘(Job outcome)’.
3. I had to BCP the results of sp_help_job to a file because I kept getting ‘Error Message:Msg 8164, Level 16, State 1, Procedure InsertIntoSp, Line 10’ – “An INSERT EXEC statement cannot be nested.”
4. Use BULK INSERT to load data into a temp table.
5. Check if the execution status of the job was 4 i.e. waiting for next scheduled execution.
   1. If the status was not 4, then wait for a minute and then repeat steps 1-5.
   2. If the status was 4 then move to next stage.
6. If we find that the job completed successfully then validate with sysjobhistory and check if the job really succeeded. Didn’t want false positivies.
7. If we did not find a job executing on Step 2., return a non-zero value.

This whole thing, was put in a store procedure and then called from a job step for a long running job. For example, like a database backup job. And additional logic was written around the return value to write proper error numbers to application log. These event-ids where then picked up by monitoring applications and alerts where raised.

I have mixed feelings about this solution. Although, it worked without a problem (I am secretly proud that I was able to write something like this); I would have loved to just upgrade the servers to SQL 2008 and be done with it.

12. Better Get-Random for small ranges (0-1000). Test for randomness of Get-Random.

Oh boy! was I wrong. What started as an exercise to generate small random sets led me around Wikipedia pages and finally showed me the Fisher-Yates Shuffle.

This was my first solution. And it absolutely sucks, generates loads of zeros and the reason why this happens is not due to the lack of the randomness of the running ticks but the fact that we modulus the result with 1000 to get the value into our range of interest. The basic problem with this is that we can fix it but; at a cost to speed and we may also need a cache the intermediate states to guarantee uniqueness in the set.

Function Get-SmallRandom
{
$procArch = $env:PROCESSOR_ARCHITECTURE
if($env:PROCESSOR_ARCHITECTURE -match “\d{2}$”){$procArch = $Matches[0]}
if($procArch -eq 64){
[Int](((Get-Date).Second * (Get-Date).Millisecond * (Get-Date).Ticks) / ([Int64]::MaxValue + 1))
}elseif($procArch -eq 32){
[Int]((((Get-Date).Ticks/(Get-Date).Millisecond)/([Int32]::MaxValue + 1.0))/1000)
}else{
[Int]((((Get-Date).Ticks/(Get-Date).Millisecond)/([Int32]::MaxValue + 1.0))/1000)
}
}

$a = 1..1000 | %{Get-SmallRandom}
$b = 1..1000 | %{Get-SmallRandom}
Compare-Object -ReferenceObject $a -DifferenceObject $b -ExcludeDifferent

Then it struck me that I may have approached the problem in a wrong direction. Given a set of 1000 numbers, how do I shuffle the set in an unbiased way so that it has the appearance of a randomly generated set? Cool, so how the hell do I do it; the obvious solution was to pick two random numbers less than size of the array the swap the numbers. But, I was not sure if this was such a good idea and started looking if there were any algorithms that did this and surely there is, the ‘Fisher Yates shuffle’. It is easy to implement and works great. So, here is my crack at it:

Function Get-ShuffledArray
{
param(
[Array]$gnArr
)
$len = $gnArr.Length;
while($len)
{
$i = Get-Random ($len --);
$tmp = $gnArr[$len];
$gnArr[$len] = $gnArr[$i];
$gnArr[$i] = $tmp;
}
return $gnArr
}

Not sure if this is an acceptable solution for what Sunny was looking for but, it was a wonderful learning experience. As always, working with PowerShell is fun.

To work around this problem, we added extended properties to all the user databases that we have on the instances. After I did this on about 5 databases in an instance; I was bored and wanted to see if there was any other way of doing this other T-SQL. Sure, there is and it’s in PowerShell, I couldn’t ask for more. It was good to walk off the beaten path and see explore new things but, it did not take me too long to figure out why documentation is boring; even PowerShell could not help me out much here.

The script is pretty straightforward though:

“Connect to the SQL*Server instance, Loop through all users databases and if a database does not have extended properties; Create a new extended properties object, Attach the object to the current DB and type in the values required.”

# Load required assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null;
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMOExtended")| Out-Null;

try
{
	$smoConn = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sqlServer
	$smoConn.ConnectionContext.ConnectTimeout = $commandTimeout
	$smoConn.ConnectionContext.Connect()

	$sqlServerDB = $smoConn.Databases

	foreach($db in $sqlServerDB){
		#Write-Host "Processing DB: " $db -Fore Magenta
		[String]$dbName = $db.Name
		if(  "master", "model", "msdb", "tempdb", "pubs", "NorthWind", "AdventureWorks" -notcontains  $dbName  )
		{
			Write-Host "Working on DB ->" $db  -Fore Green
			$xPropCount = $db.ExtendedProperties.Count
			if($xPropCount  -eq 0){
				$ownrExtProp = New-Object Microsoft.SqlServer.Management.Smo.ExtendedProperty
				$ownrExtProp.Parent = $db
				$ownrExtProp.Name = 'Owned By'
				$ownrExtProp.Value =  Read-Host "Enter the Name of the team that owns the DB: "
				$ownrExtProp.Create()

				$cntctExtProp = New-Object Microsoft.SqlServer.Management.Smo.ExtendedProperty
				$cntctExtProp.Parent = $db
				$cntctExtProp.Name = "Contact"
				$cntctExtProp.Value =  Read-Host "Enter the E-mail address of the group that owns the DB: "
				$cntctExtProp.Create()
			}

			foreach ($xProperty in $db.ExtendedProperties){
					Write-Host  " " $xProperty "->" $xProperty.Value -Fore Yellow
			}
		}
	}
}
catch
{
	$_ | fl * -Force
}

<# 	name	value 	OwnedBy	SqlChow 	Contact [email protected] #>

Since, we put in so much work documenting this stuff I would have liked to add a nightly job that would read these properties and keeps a central repository updated with the latest changes to the DB. Maybe…that is an overkill.