Monday, January 20, 2014

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.

image

Wednesday, December 11, 2013

Force a Windows Reboot When the OS Says No

I know how to reboot the box from the cli, you say.  I can use shutdown.exe , psshutdown.exe , or the PowerShell cmdlet Restart-Computer with the -Force parameter.

Those methods normally suffice, but there are events when a server won’t cooperate.  Maybe it was a hotfix install, or a pending service shutdown.  Or just a lack of patience.  Forcing a reboot of a machine is akin to yanking the power cord out of the wall and plugging it back it in afterward, pushing the physical reset button on a desktop computer, or holding down the power button for five seconds to power down, then pushing it again to power up.

The aforementioned methods of shutting down a system are very dangerous.  The methods proposed hereafter are also quite unsafe.

The file system might be damaged and unbootable, your company’s data might be lost, unicorns and leprechauns might cry, or some other type of unplanned horribleness could ensue from a less-than-graceful restart.

With all that being said here’s how you bend a machine to your restart will:

Scenario A:  The machine already has a pending reboot or shutdown, but can’t be restarted.

Solution: Kill the winlogon process.  The logon session will end and the machine will restart.

Here’s an example of what that might look like:

image[5]

shutdown.exe /a typically aborts a pending shutdown.  I typed it here knowing it would display the error 1115 message for the screenshot.  I had already tried running shutdown.exe /r without success.

Winlogon can be killed with your tool of choice ( pskill, for example ).  Two PowerShell examples follow:

Get-Process winlogon | Stop-Process -Force

Get-Process | where Name -match winlogon | Stop-Process –Force

Note:  In the absence of a pending reboot, killing the winlogon process can just kill a session/log off users.

 

Scenario B:  The machine does not have a pending reboot or shutdown, but for some reason you want to force a hard reboot immediately in a very ugly way, potentially causing a bluescreen in the process.  I successfully tested the following method against Windows Server 2012, forcing a bluescreen reboot.

Solution: Kill the csrss process.  The machine will then restart.

Get-Process csrss | Stop-Process -Force

image

Note:On Windows 8.1 image killing csrss failed.

When you can, reboot responsibly.  It’s not the law.  It’s just a good idea.


Credit for the winlogon idea in Scenario A goes to oasysadmin.  Killing csrss is an idea I got from Chris B(aka Otis).

Monday, July 22, 2013

Finding SQL Server Cluster Failover Events using PowerShell v3

Here’s a 2 node cluster running Windows Server 2008R2 for the OS and a single instance of SQL Server.  If the name of the clustered service/application is known, querying the event log using PowerShell’s remoting features implicitly with Invoke-Command (aliased to icm) makes finding cluster failover events pretty quick.  In this case matching against string  MSSQLSERVER is used, as the full name of the clustered service/app is 'SQL Server (MSSQLSERVER)'.

What’s the cluster look like now?

7/22/2013 2:01:35 PM :: user@deadair :: D:\Dropbox\bin
[6307] #   icm SQL00 {Get-ClusterGroup} | ft Name, OwnerNode, State, PSComputerName –a   

Name                     OwnerNode  State  PSComputerName
----                     ---------  -----  --------------
PORTAL_DTC               sql01      Online SQL00
SQL Server (MSSQLSERVER) sql01      Online SQL00
Cluster Group            sql01      Online SQL00
Available Storage        sql01      Online SQL00


Event ID 1201 is logged when resource groups are brought online within the cluster, so we’re limiting the results to that Event ID.  Running the Get-WinEvent cmdlet against all cluster nodes (just two nodes in this case – SQL00 and SQL01) and assigning the results to a variable allows sorting of entries from all nodes.  Otherwise results would be sorted within the context of the the current remote target(PSComputerName) node.

7/22/2013 2:14:19 PM :: user@deadair :: D:\Dropbox\bin
[6316] #   $FailoverEvents = icm SQL00, SQL01 {Get-WinEvent -FilterHashtable @{LogName='Microsoft-Windows-FailoverClustering/Operational';ID = 1201} | where Message -match MSSQLSERVER }  


Now to sort the event log entries…

7/22/2013 2:15:04 PM :: user@deadair :: D:\Dropbox\bin
[6317] #   $FailoverEvents | sort TimeCreated -desc | ft –a   


   ProviderName: Microsoft-Windows-FailoverClustering

TimeCreated              Id LevelDisplayName Message                                                                                                          PSComputerName
-----------              -- ---------------- -------                                                                                                          --------------
7/19/2013 10:26:24 PM  1201 Information      The Cluster service successfully brought the clustered service or application 'SQL Server (MSSQLSERVER)' online. SQL01
7/13/2013 10:33:05 PM  1201 Information      The Cluster service successfully brought the clustered service or application 'SQL Server (MSSQLSERVER)' online. SQL00
7/13/2013 10:09:08 PM  1201 Information      The Cluster service successfully brought the clustered service or application 'SQL Server (MSSQLSERVER)' online. SQL01
7/6/2013 10:57:32 PM   1201 Information      The Cluster service successfully brought the clustered service or application 'SQL Server (MSSQLSERVER)' online. SQL00
7/6/2013 10:45:58 PM   1201 Information      The Cluster service successfully brought the clustered service or application 'SQL Server (MSSQLSERVER)' online. SQL01
6/1/2013 10:40:18 PM   1201 Information      The Cluster service successfully brought the clustered service or application 'SQL Server (MSSQLSERVER)' online. SQL00
6/1/2013 10:18:50 PM   1201 Information      The Cluster service successfully brought the clustered service or application 'SQL Server (MSSQLSERVER)' online. SQL01

Summary: Combining Get-WinEvent with Remoting allows for a very quick recon of events in a cluster.

Prefer a one-liner instead of using a variable? Just perform the sort outside the Get-WinEvent statement block.  Let’s add the day of the week, too! 

  <# Cluster Failover Events #> icm SQL00, SQL01 `
{ Get-WinEvent -FilterHashtable @{LogName='Microsoft-Windows-FailoverClustering/Operational';ID = 1201} `
| where Message -match MSSQLSERVER } `
| sort TimeCreated -desc `
| ft @{N='DayofWeek';E={($_.TimeCreated).DayofWeek}} , TimeCreated, ID, Message -a  

image