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)


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.


[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!

Wednesday, June 29, 2016

[Presenting] SQL Saturday San Antonio, here I come!

Woot!  August 13th, see you there!  Looking through the sessions, there's going to be a ton of awesome.  R services, Hekaton, Window Functions, BIML, IaaS, Query Store, Event Notifications(plug, plug) and more! 

And looking at just part of the lineup makes me happy - Amy Herold, Tim Costello, Tim Mitchell, Steve Jones, John Sterrett... holy crap, Boles is going to be there?! And Kalen Delaney?!!?!! And I'm not presenting against them?!  Squee!!

@SQLSatSanAntone and #SQLSatSA

Powershell - kill processes remotely

Simple problem: we had to replace the config files for an app that hits the database, pointing it at a CNAME. 

However, the file could be in use because the app was running.  And the app only loads the file on startup.  So we came up with this, which worked quite well.  Note that we use WMI (see my other posts on it!) to grab the application info then a WMI method to terminate the app.

Thursday, May 19, 2016

[Presenting] Know what your machines are doing (besides SQL Server) - WMI, WQL, & Powershell.

Howdy, NTSSUG!  Here's the slide deck for the presentation tonight (May 19th 2016).  Holler if questions!
(Note: live slide deck below; may change without notice as I update it)

Tuesday, May 10, 2016

[Presentation] A Cancellation and a Presentation

Bad news first:
Due to personal issues, I have had to cancel my presentation at SQLSaturday #514, Houston 2016.  I really hate cancelling.  But family comes first, and the team in Houston has been super understanding.

For some good news:
I am presenting the same material (WMI, WQL, PoSH, & SQL Server) at the North Texas SQL Server Users Group on May 19th.  The issues that prevent me from making Houston are manageable for a smaller commitment.  I said I'd speak there a while back (I think they finally cornered me at SQLSat Austin, IIRC), and realized that while I can't do Houston, I _can_ make this one.

After this, the next SQLSat for me is going to be several months.  I will submit my current two presentations for SQLSat San Antonio, which would be cool.  Unsure if I'll submit for Denver again - while their event and town is a blast (Voodoo Doughnuts! Tattered Cover!), that drive is a killer and I'm too much a tightwad to fly.  Finally, I do want to go to Kansas City again (their SQLSat is honestly the high point of all the ones I've been to, which means it's exceptionally high), which means I am going to have to come up with another presentation in the next 2.5 months.  Wonder what problem I'm solving that I can speak on.....