Saturday, September 17, 2016

[Cooking] No bake "puppy chow" (peanut-butter-chocolate-chex)

One of my wife's sickening-sweet-but-it's-like-crack recipes.

Produces: 20 cups

1 1/8 cup butter
4 1/2 cup semi sweet chocolate chips.
1 1/2 cup peanut butter
2 lbs powdered sugar
20 cups rice crispies or chex cereal

Melt all but sugar in microwave. Dump the cereal in plastic bags (double layered, trust us on this), add chocolate, mix.  Once all coated, add sugar "a large bit at a time", so that you're coating bits of it.  Keep adding until no longer sticky.

Friday, September 9, 2016

[Powershell] getting a cmdexec step to fail with useful errors when running a PoSH script

TL;DR: put $ErrorActionPreference="Stop" at the top of your script

I feel like a dunce. 

I couldn't get a Powershell script to fail, when it had errors, when invoked as a "Operating system (CmdExec)" step in SQL Server Agent.  The first script I wound up rewriting as a PsExec (aka SQL Powershell Provider) script, but this second one I already had working in the ISE.

#save this one line as c:\blah.ps1
get-content c:\narf\poit.txt

And a job step as "Operating System (CmdExec)" (not scripting it out since it's 50 unnecessary lines):
powershell "c:\blah.ps1" 

I know the file is bad - in fact, the directory doesn't even exist!
Hence, it crashes in the ISE:
get-content : Cannot find path 'C:\narf\poit.txt' because it does not exist.
At \c:\temp\blah.ps1:4 char:1
+ get-content c:\narf\poit.txt
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (C:\narf\poit.txt:String) [Get-Content], ItemNotFoundException
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetContentCommand

However, when run via SQL Agent, it succeeds.  GAH!
I tried 50 different variations; modifying the script, various TRY..CATCH blocks found on the internet.  Nothing.  Every single one of them succeeded.

Then I remembered that by default, even though it had an error, by default errors always continue.  ($ErrorActionPreference="Continue".  So I added this line at the top:

#save these TWO lines as c:\blah.ps1
$ErrorActionPreference = "Stop"
get-content c:\narf\poit.txt

Lo and behold, now it fails.  DOH!

So, since we're here, let's try using some of the blocks I found on the internet and see what they return.

  • Just running the code, nothing fancy:
Executed as user: thebakingdba. Get-Content : Cannot find path 'C:\narf\poit.txt' because it does not exist.  At c:\this_will_fail.ps1:4   char:12  + get-content <<<<  c:\narf\poit.txt      + CategoryInfo          : ObjectNotFound: (C:\narf\poit.txt:String) [Get-C      ontent], ItemNotFoundException      + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetCo      ntentCommand.  Process Exit Code 1.  The step failed.

  • if I use a TRY..CATCH block, slightly different:

get-content c:\narf\poit.txt
} catch {
write-error $_

Executed as user: thebakingdba. c:\this_will_fail.ps1 : Ca  nnot find path 'C:\narf\poit.txt' because it does not exist.  At line:1 char:75  + c:\this_will_fail.ps1 <<  <<       + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorExcep      tion      + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorExceptio      n,this_will_fail.ps1.  Process Exit Code 1.  The step failed.

  • Thie results of this TRY..CATCH block is identical to running it without the TRY..CATCH:

} catch {
$output = $_.ErrorDetails
throw $output

  • This fourth example is where it gets interesting, but for my needs not terribly useful.  More info, but it's more "internals".  

} catch {
$ex = $_.Exception
throw "$ex.Message"

Executed as user: thebakingdba. System.Management.Automation.ItemNotFoundException: Cannot find path 'C:\narf\p  oit.txt' because it does not exist.     at System.Management.Automation.LocationGlobber.ExpandMshGlobPath(String pat  h, Boolean allowNonexistingPaths, PSDriveInfo drive, ContainerCmdletProvider pr  ovider, CmdletProviderContext context)     at System.Management.Automation.LocationGlobber.ResolveDriveQualifiedPath(St  ring path, CmdletProviderContext context, Boolean allowNonexistingPaths, Cmdlet  Provider& providerInstance)     at System.Management.Automation.LocationGlobber.GetGlobbedMonadPathsFromMona  dPath(String path, Boolean allowNonexistingPaths, CmdletProviderContext context  , CmdletProvider& providerInstance)     at System.Management.Automation.PathIntrinsics.GetResolvedPSPathFromPSPath(S  tring path, CmdletProviderContext context)     at Microsoft.PowerShell.Commands.ContentCommandBase.ResolvePaths(String[] pa  thsToResolve, Boolean allowNonexistingPaths, Boolean allowEmptyResult, CmdletPr  oviderContext currentCommandContext).Message  At c:\this_will_fail.ps1:1  8 char:6  + throw <<<<  "$ex.Message"      + CategoryInfo          : OperationStopped: (System.Manageme...ontext).Mes      sage:String) [], RuntimeException      + FullyQualifiedErrorId : System.Management.Automation.ItemNotFoundExcepti      on: Cannot find path 'C:\narf\poit.txt' because it does not exist.         at System.Management.Automation.LocationGlobber.ExpandMshGlobPath(Strin      g path, Boolean allowNonexistingPaths, PSDriveInfo drive, ContainerCmdletP      rovider provider, CmdletProviderContext context)         at System.Management.Automation.LocationGlobber.ResolveDriveQualifiedPa      th(String path, CmdletProviderContext context, Boolean allowNonexistingPat      hs, CmdletProvider& providerInstance)         at System.Management.Automation.LocationGlobber.GetGlobbedMonadPathsFro      mMonadPath(String path, Boolean allowNonexistingPaths, CmdletProviderConte      xt context, CmdletProvider& providerInstance)         at System.Management.Automation.PathIntrinsics.GetResolvedPSPathFromPSP      ath(String path, CmdletProviderContext context)         at Microsoft.PowerShell.Commands.ContentCommandBase.ResolvePaths(String      [] pathsToResolve, Boolean allowNonexistingPaths, Boolean allowEmptyResult      , CmdletProviderContext currentCommandContext).Message.  Process Exit Code 1.  The step failed.

  • Another variation, this one just throwing an error, and trying to throw the error level: (It doesn't give the level)
 } catch {
THROW 'Throw Error: no file!'; %errorlevel% #the quotes don't seem to matter; w/ and w/o both wrong

Executed as user: thebakingdba. Throw Error: no file!  At c:\this_will_fail.ps1:2  3 char:6  + THROW <<<<  'Throw Error: no file!'; %errorlevel%      + CategoryInfo          : OperationStopped: (Throw Error: no file!:String)       [], RuntimeException      + FullyQualifiedErrorId : Throw Error: no file!.  Process Exit Code 1.  The step failed.

  • One that a coworker developed that I hadn't known of:
    throw $_.Exception.Message
    exit 1

Executed as user: thebakingdba. Cannot find path 'C:\narf\poit.txt' because it does not exist.  At c:\this_will_fail.ps1:2  6 char:7  +     throw <<<<  $_.Exception.Message      + CategoryInfo          : OperationStopped: (Cannot find pat...does not ex      ist.:String) [], RuntimeException      + FullyQualifiedErrorId : Cannot find path 'C:\narf\poit.txt' because it d      oes not exist.  Process Exit Code 1.  The step failed.

Wednesday, September 7, 2016

[Presenting] SQLSaturday Dallas, September 24th, 2016!

The first SQLSaturday I ever presented at!  This time around I'm covering CIM and WMI and WQL.  Come learn why you should care, but also come get your brain melted by Bob Ward - something for everyone!

Man, this ought to be a monster of a SQLSat, with those speakers.  Now to cajole them into doing a BBQ Crawl.  Who's up for Pecan Lodge at 10:30-10:45 on Friday?

Wednesday, August 10, 2016

[statistics] Get the last 4 stat update time for every index on every table in a database.

Quickie, based off an earlier post. (

Get the last 4 stat updates for every statistic based on an index. The filter is on the auto_created; flip that to get all the system-created (aka _WA_Sys_00000003_53D770D6) stats, or index-based.

Wednesday, August 3, 2016

[Powershell] some basic blocks of code written this week.

Update 2016/08/10 since I need an anterior brain, and this blog is it, I'm now making this post my "living powershell" document.  I'll update it as I run into problems.  Today: $a vs "$a" vs $ (see end)

Mike Fal, a Powershell DBA extraordinaire, would probably shake his head at these.  But given the hard lessons learned this week, I thought I'd share.  I had to rewrite a bash script in Powershell, and some of the things I hadn't done before, so there was definitely a learning curve. And we'll see how the error trapping is - Idera has a webinar covering error-handling in Powershell next week.


First up, a basic powershell function that writes to a file. The encoding isn't strictly necessary here, but I use the code elsewhere.  For whatever reason, standard out-file adds a couple of low-ascii characters at the beginning of the file, which other utilities such as psftp choke on.    (  Doesn't matter here, but later I write to a file that I then feed to psftp as a batchfile. 

"mget TESTAREA/$FL" |out-file -append -encoding ascii $CMDS 
 I should just have the function accept a parameter.  And for whatever reason, when it writes to the log, it doesn't include newlines.

#Write whatever is in "$logmsg" to the log file $LOGFILE
function logstamp(){
    $loginfo = get-date -format "yyyyMMdd.HH:mm:ss"  #MM is MONTH, mm is minute
    "$loginfo $logmsg" |out-file -append -encoding ascii $logfile
    $logmsg="" }


I needed to parse out a Unix filename from a file.  The original "ls" looked like this:

-rw-rw-r-- 1 psabcderfg data 646621 Jul 19 16:25 myfile16071901.Z

The unix command to parse it would
cat $fl |awk '{print $9}'

And the powershell equivalent is:
$newfiles = get-content $fl | foreach {$_.split(" ")[8]}
(the number is 8 instead of 9 because powershell arrays start at 0)
(2016/09/07 but I've run into times when it IS 9.  WTF?)

A basic unix2dos-style file change. Found at

get-content $thisfile |% {$_.replace("`n", "`r`n")} | out-file -encoding ascii -filepath $thatfile -Force

Since it outputs to a new file, the timestamps won't match, but you could use set-filetimestamps -doc $doc -date $date to change them.


This next part is weird but also very handy.  Because of the way Powershell handles messages returned from applications, as well as standard error, you have to do use a trick to get out all the messages.  I needed this because I wanted all the messages from the application, 7zip here, but also stuff like psftp.

$logmsg = cmd /c 7za.exe e -aoa $thisfile 2>&1 | %{ "$_" }
    if ($LASTEXITCODE -ne 0)
         { logstamp; $logmsg = "7-zip Command to extract $thisfile returned exit code $LASTEXITCODE"; logstamp; throw "7-zip Command to extract $thisfile returned exit code $LASTEXITCODE" }
    else { logstamp; $logmsg = "7-zip Command to extract $thisfile finished successfully"; logstamp }

First, we're invoking it with cmd instead of start-process or just a dot invocation.  That's to make sure we get the messages out, but also to prevent powershell from choking if the app throws an error.  Next up is getting all the messages out from the app and stderr, which is the code from "2>&1" until the end of the line.

So now, we have a couple different things to look at.  $logmsg contains the entirety, both error messages from 7zip, and standard error.

$LASTEXITCODE returns the result code from the APP CALLED.  When it couldn't find the file to zip, $lastexitcode returned was 1.  Otherwise was 0 (success).

Next is $error[0].TargetObject, which I believe is the stderr from the app.

Was running into a problem invoking cpio.  I was having to make a unix-formatted file, and the way I'd check is by opening it in notepad.  Except...

$ldclist = get-childitem * -include *out.Z

I wanted to write those out to a file.  So I thought I'd do this to test:

That does indeed give me the directory info on each file.

But I just want the name of each file and didn't think about $myvarname.myfieldname.
So I ran this:
"$ldclist"|out-file -append -encoding ascii listoffiles

Which, superficially looks right.  My script auto-converted it to unix-format, and then when I open it in notepad, it's all on one line.  Denoting LFs, right?  Nope.  Using "$myvar" means it returns it as a list, space-separated.

Just to make it more fun, let's try this:
    $|out-file -append -encoding ascii a
    "$"|out-file -append -encoding ascii b

Those should be close, right?  We now know one would return the space-separated list (the quotes converting it to a string, IIRC), and the other the correct list?  Nope.  The first one returns just the base filenames.  The second one, in the quote, returns the full filename including path, space-separated.


Additionally, problems arise when trying to do something like this:
$logmsg = cmd/c 7za.exe -e -aoa $thisfile -o"$newdirectory"

It just doesn't work.  It actually dumped them into a folder called $newdirectory.
I wound up having to do the below, though I still couldn't use double-quotes. I know there's gotta be a way, but at this point I give up.
$command = "7za.exe -e -aoa $thisfile -o$newdirectory"
$logmsg = cmd /c $command 2>&1 | %{ "$_" }

2016/09/29 hey, a new installment! I have a ton more ,but this one drove me up a wall today, so here we go.

This time - powershell helping with small arrays.
I was trying to make sure I can hit a server and pull back some server info.
One thing I wanted to look at was the number of drives the server had.  Which worked great - on some servers.  On the others, blank.  Not 0, just... blank.

Here's the command I was running:
$drives = (gwmi -cn $compname -Class win32_logicaldisk -ErrorAction silentlycontinue | where {$_.Size -gt 0}).count

What's different?  Well, some of the servers only have 1 drive.
Turns out, if there's only 0 or 1 members in what's returned, it assumes it doesn't need to be an array.  Which means that count, for instance, won't work on it properly.

$drives = gwmi -cn $compname -Class win32_logicaldisk -ErrorAction silentlycontinue | where {$_.Size -gt 0}|measure

So the "measure" counts the number of items, even if it's only 1.  
Then when I query it, I use $drives.count, which gives me the proper number/.

[Presenting] SQL Saturday San Antonio just around the corner! #SQLSatSA

Just a friendly reminder.  SQLSaturday San Antonio 2016 is next weekend (occurs August 13th), at Texas A&M University San Antonio!

I'm looking forward to this.  I was trying to pick courses to take, and it's difficult.  Courses on Hekaton, Clusters, Performance Tuning, 2016 features, Plan Cache... even one on Service Broker, one of my favorite hidden features!  Some "big names" there, too - Kalen Delaney, who also has a pre-con!  Speakers all the way from Pittsburg, Seattle, Albuquerque and more.

I'll be speaking on the same thing I've done a bunch - Event Notifications.  Same speech, a few new tricks and tips, but mostly it's about DBA empowerment.  If you don't know what code was changed 3 weeks ago, what hope do you have for tracking down "why did this stop working"?  Be the Overlord your end-users need. 

Thursday, July 28, 2016

[WAT] Fun with table variables

Run this code.  Did you expect it to act a certain way?  Why?  Because table variables.  Thanks to (crap, who was it? *sigh*) who pointed this out in his tempDB talk a year or so ago.

Update 2016/08/15 - I heard a good use for this at #SQLSatSA!   Use it as part of an ETL.  When it fails and rolls back, you know which batch you were on.  : D

DECLARE @blah TABLE (fielda VARCHAR(20))

UPDATE @blah
SET fielda = 'c'


Friday, July 8, 2016

[Powershell] Killing that one instance of SSMS

WMI in action!  I was running SSMS 2016 and it locked up on me.  So while I waited for it to become responsive I started up SSMS 2014. Still locked, but which do I kill in taskman? (The easy answer, is, of course: change the open query and then look in Applications, not Processes). I didn't think of that, so used WMI.  You could also get fancy and figure out which is the oldest instance of SSMS and do it that way.

Pretty simple: Tell it the path (which is 130 for SSMS 2016), and kill that.

$processes = Get-WmiObject -query "select * from Win32_Process where executablepath like '%130%ssms%'"

foreach ($process in $processes) {
  $returnval = $process.terminate()
  $processid = $process.handle

if($returnval.returnvalue -eq 0) {
  write-host "The process $ProcessName `($processid`) terminated successfully"
else {
  write-host "The process $ProcessName `($processid`) termination has some problems"

[EN] Troubleshooting and fun with differential backups

Came in this morning to one of my EN alerts going off.  No new messages added in the past hour.  (I have enough servers that this is a really good failsafe)

Queue appears up.
SELECT * FROM sys.service_queues

No records in sys.transmission_queue.  Not a total failsafe, but a good start.

Sys.databases is... interesting.  "ACTIVE_BACKUP_OR_RESTORE" for my EN database.  We'll come back to that.

Any backups running?  NO.

Check the actual queue itself
sp_spaceused '[dbo].[ENAudit_SBQueue]'

Well, it's growing, which is actually a good sign (means, IMHO, that the MSDBs on other servers shouldn't be growing, since the messages are making it over here.

How about the activated stored procedure, then?  What's it doing?
SELECT * FROM sys.dm_broker_activated_tasks 
No records, which means that the activated SP isn't firing.  What the hey?  

Means that for some reason it's not activating properly.

Select * from sys.dm_broker_queue_monitors; 
The state for that database's monitor is "NOTIFIED". Huh, that seems odd.

As per , this means that it's waiting for the activated stored procedure to do a RECEIVE on the database, but won't fire it until that occurs.  Fortunately for us, possibly (I'm writing this as I diagnose the problem), ANY receive on the queue will work.  We may have to do that.

Okay, let's check what we should've started with - event logs:
The queue 517576882 in database 9 has activation enabled and contains unlocked messages but no RECEIVE has been executed for 26964 seconds.

Well, well, well.  Crap.

And then we see these from earlier:

The transaction log for database 'EventNotificationRec' is full due to 'ACTIVE_BACKUP_OR_RESTORE'.


The activated proc '[dbo].[Parse_EN_Messages]' running on queue 'EventNotificationRec.dbo.ENAudit_SBQueue' output the following:  'The transaction log for database 'EventNotificationRec' is full due to 'ACTIVE_BACKUP_OR_RESTORE'. '

Looks like we're onto something.


BACKUP failed to complete the command BACKUP DATABASE EventNotificationRec. Check the backup application log for detailed messages.

Okay, so we have several things.  Let's look at job status.  Ahhhh.  The DIFF job is "cancelled". 

I think here's what happened - someone saw a failed backup, and ran the wrong job.  The DIFF backup job is disabled - it's the standard Ola Hallengren job.  Which means it tried to do a DIFF of every DB.  Which... would take a while, since we don't do them, so it could take longer than doing full backups.  They panicked and cancelled it.  And for whatever reason, that left the stored procedure/queue in a weird state where it had been notified but wasn't running.

UPDATE: While I thought the DIFF was the culprit, I was wrong.  The FULL backup on that database started at 00:40:00, so it looks like the TLOG (which is capped) filled up somehow while the backup was running.  No idea why - the cap was 3gb which has been totally fine until now.  Yes, the TLOG can fill during a backup, but it typically doesn't have enough going on for that to matter.

Fix?  Easy, and in Rusanu's post:


Now?  Look in sys.dm_broker_queue_monitors.  All we care about is database_id 9, the other ones are the InternalMailQueue/ExternalMailQueue/syspolicy_event_queue in msdb (everybody has those).

And in sys.broker_activated_tasks.  Wow, it's using all 5 SPIDs.  I must've changed it after the last time the other team broke EN.  And I see new messages showing in my ENAudit_Events table.  Yay!