Who Rebooted?

Run this PowerShell and substitute the actual server name instead of SQLTACOPS-05 to find out who rebooted in the last 155 days.  This code was posted by Tompa on Technet before I tweaked it for my use.

Las Vegas SQL Server User Group

Thanks, Jason and Stacia, for allowing me to present tonight!

Here are the materials from the presentation, including bonus favorite scripts/functions.

PowerShell: The Way of the DBA Dragon – presentation scripts and slide deck.

I used Start-Demo to play back the commands to avoid typing.

Favorite Function for Checking Disk Space in PowerShell–includes Mountpoints and System Reserved Volumes

Found a superior function and added a parameter to check disk space compared to the old script I was using.

function df { Param([string[]]$ComputerName) Get-WMIObject Win32_Volume -filter "DriveType=3" -computer $ComputerName | Select SystemName,Caption,Label,@{Name="DiskSize(GB)";Expression={[decimal]("{0:N1}" -f($_.capacity/1gb))}},@{Name="freespace(GB)";Expression={[decimal]("{0:N1}" -f($_.freespace/1gb))}},@{Name="PercentFree(%)";Expression={"{0:P2}" -f(($_.freespace/1gb)/($_.capacity/1gb))}}}

Here’s an example of omitting system reserved volumes and passing multiple servernames in a text file:

Did I commit that transaction?

“Hey, can you run this for me in prod?”

There are many reasons the wording in this request is a red flag/indicator of broken processes, but let’s put those aside for a moment.
The dev that asked you to run this query may have a legitimate reason.

Let’s say you check the estimated execution plan, and the query seems to be low-impact, and unlikely to cause blocking.  You run the query.  Yep, it only modified a few rows and ran in less than a second.  A few minutes later an alert indicates that the query has become a long running statement and is still active.

What the wha…?!?

It turns out there were two BEGIN TRAN statements in the t-sql, and a COMMIT was only executed once.  The transaction is still open – uncommitted!

In the future you can guard against this scenario by just checking @@TRANCOUNT after the commit and the time of the commit.  If @@TRANCOUNT has a non-zero value, a transaction is still open.

Placing the following two commented lines in SQLfile.sql or pasting the second line at the end of the query can help:

--BEGIN TRAN
[paste query to be executed here, and uncomment the above line if the query modifies data and lacks a BEGIN TRAN]

After successfully running the t-sql, commit the transaction with the below statement by selecting and executing the remainder of the line after the two dashes:

--COMMIT;SELECT @@TRANCOUNT [Open Transactions], Getdate() [timestamp]

A zero value in the Open Transactions column indicates this session has committed the transaction, and the timestamp lets you know when the transaction was committed, just in case you return to this session later on.