Horkay Blog
The postings on this site are my own and do not represent my Employer's positions, advice or strategies.
Sunday, 18 February 2018

From a moth no less, no digital camouflage pattern needed here !

Sunday, 18 February 2018 11:02:09 (Central Standard Time, UTC-06:00) | Comments [0] | Web_Blog#
Saturday, 17 February 2018

      These speak for themselves.

Saturday, 17 February 2018 11:55:01 (Central Standard Time, UTC-06:00) | Comments [0] | Web_Blog#
Friday, 16 February 2018

Been a Cold Winter !

Friday, 16 February 2018 11:21:27 (Central Standard Time, UTC-06:00) | Comments [0] | SQL Server#
Monday, 29 January 2018

I track stored procedure counts and plans for reporting on volume, when a plan changes and data mining the plans for missing indexes and other information.

The trick to this is you have to track it over time as the DMV tracks cumulative count. An issue I ran into recently was that the cumulative count was negative. I don't know and found a few links, but no real explanation. So now when I find a negative count I usually mark it for removal from the query cache so that it resets. I'm guessing that the count reached the maximum value and "rolled" over to a negative values (similar to reseeding an identity to a negative value when the maximum value of an integer data type has been reached).

Monday, 29 January 2018 09:06:33 (Central Standard Time, UTC-06:00) | Comments [0] | SQL Server#
Friday, 19 January 2018

Learning new things all the time.  Historically deadlocks have been hard to troubleshoot unless you were able to have a user recreate them or had some type of monitoring tool that was running an extended events session and/or a trace.  

Turns out that is not always true.  As usually you get the call, our application had a deadlock at 4am this morning, can you tell us what happened?  Of course you can, your the DBA, you were awake at 4am and monitoring their application for them.  BUT you can help them.

There are new things in SQL that make this easier, so learn them.  Links are below, script is in my script vault, and it's possible to see what happened with deadlocks at 4am, even though it's now 6am.  [on a real busy system it may not, but so far, it's been nice to me !].

Querying Deadlocks From System_Health XEvent



Tracing Deadlock Graphs: Extended Events or Server Side Trace


DECLARE @SessionName SysName

@SessionName = 'system_health'

SELECT  Session_Name = s.name, s.blocked_event_fire_time, s.dropped_buffer_count, s.dropped_event_count, s.pending_buffers
FROM sys.dm_xe_session_targets t
INNER JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address
WHERE target_name = 'event_file'


@Target_File NVarChar(1000)
@Target_Dir NVarChar(1000)
@Target_File_WildCard NVarChar(1000)

SELECT @Target_File = CAST(t.target_data as XML).value('EventFileTarget[1]/File[1]/@name', 'NVARCHAR(256)')
FROM sys.dm_xe_session_targets t
INNER JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address
WHERE s.name = @SessionName
AND t.target_name = 'event_file'

SELECT @Target_Dir = LEFT(@Target_File, Len(@Target_File) - CHARINDEX('\', REVERSE(@Target_File)))

SELECT @Target_File_WildCard = @Target_Dir + '\'  + @SessionName + '_*.xel'

--Keep this as a separate table because it's called twice in the next query.  You don't want this running twice.
SELECT DeadlockGraph = CAST(event_data AS XML)
DeadlockID = Row_Number() OVER(ORDER BY file_name, file_offset)
INTO #Events
FROM sys.fn_xe_file_target_read_file(@Target_File_WildCard, null, null, null) AS F
WHERE event_data like '<event name="xml_deadlock_report%'

;WITH Victims AS
SELECT VictimID = Deadlock.Victims.value('@id', 'varchar(50)')
FROM #Events e
CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/victim-list/victimProcess') as Deadlock(Victims)
DeadlockObjects AS
, ObjectName = Deadlock.Resources.value('@objectname', 'nvarchar(256)')
FROM #Events e
CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/resource-list/*') as Deadlock(Resources)
SELECT e.DeadlockID
, TransactionTime = Deadlock.Process.value('@lasttranstarted', 'datetime')
, DeadlockObjects = substring((SELECT (', ' + o.ObjectName)
FROM DeadlockObjects o
WHERE o.DeadlockID = e.DeadlockID
ORDER BY o.ObjectName
3, 4000)
, SPID = Deadlock.Process.value('@spid', 'int')
ProcedureName = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]', 'varchar(200)')
LockMode = Deadlock.Process.value('@lockMode', 'char(1)')
Code = Deadlock.Process.value('executionStack[1]/frame[1]', 'varchar(1000)')
ClientApp = CASE LEFT(Deadlock.Process.value('@clientapp', 'varchar(100)'), 29)
WHEN 'SQLAgent - TSQL JobStep (Job '
THEN 'SQLAgent Job: ' + (SELECT name FROM msdb..sysjobs sj WHERE substring(Deadlock.Process.value('@clientapp', 'varchar(100)'),32,32)=(substring(sys.fn_varbintohexstr(sj.job_id),3,100))) + ' - ' + SUBSTRING(Deadlock.Process.value('@clientapp', 'varchar(100)'), 67, len(Deadlock.Process.value('@clientapp', 'varchar(100)'))-67)
ELSE Deadlock.Process.value('@clientapp', 'varchar(100)')
, HostName = Deadlock.Process.value('@hostname', 'varchar(20)')
LoginName = Deadlock.Process.value('@loginname', 'varchar(20)')
InputBuffer = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)')
FROM #Events e
CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/process-list/process') as Deadlock(Process)
Victims v ON v.DeadlockID = e.DeadlockID AND v.VictimID = Deadlock.Process.value('@id', 'varchar(50)')
X --In a subquery to make filtering easier (use column names, not XML parsing), no other reason

Friday, 19 January 2018 16:39:00 (Central Standard Time, UTC-06:00) | Comments [0] | SQL Server#
Thursday, 18 January 2018

I guess SQL Server Tokens have been around since SQL 2005, but I just learned of them.

I needed this because I had a Transaction Log backup job, running every 4 minutes that was randomly failing.  The sql agent text was longer than was captured so I couldn't see the error in the log history, I went to the job to view the "output" file, and of course it was already overwritten by the next successful run.

I didn't want to change the job to "append" as I didn't want to have it grow "forever" and end up with a large job output text file on the server.

After some research I found this: 



So I just simply modified the output step to "append" and added the token:


This uses a token in the job output file to create a file for each day, so the job is still set to "append", but you get one job file per day.

To manage the "daily" log files, I have a job that creates a sub-directory by day (yymmdd) and "sweeps" all the log files into it.  So I have one subdirectory per day with all the log files in.   Once a year I delete some directories, here's the code that does that, the whole job is in my script vault as well, under SQL Agent.

declare @xp_command varchar(8000)

declare @directory varchar(8)

declare @path varchar(1000)

declare @month varchar(2)

declare @day varchar(2)

declare @year varchar(4)

set @month = Right('00' + convert(varchar(2),DatePart(m,getdate())),2)

set @day = right('00' + convert(varchar(2),DatePart(d,getdate())),2)

set @year = convert(varchar(4),DatePart(yyyy,getdate()))

set @directory = @month + @day + @year

print @directory

set @xp_command = 'mkdir G:\CY_SQL_JOB_LOGS\' + @directory

print @xp_command

exec master..xp_cmdshell @xp_command

set @xp_command = 'move G:\CY_SQL_JOB_LOGS\*.* G:\CY_SQL_JOB_LOGS\' + @directory

print @xp_command

exec master..xp_cmdshell @xp_command

That's all there is too it !

Thursday, 18 January 2018 08:16:54 (Central Standard Time, UTC-06:00) | Comments [0] | SQL Server |  SQL Agent#
Thursday, 16 November 2017

Pretty common operation to migrate SQL Server from one cluster to another.

Generally it is advisable to build and test the new environment in advance, possibly even using replication and/or backup and restore with differential and tlogs to keep downtime to a very minimum when moving it.

There are a lot of little steps and i'm not going to document them, but one issue we recently ran into was an SPN on the old cluster was not "released / removed" from active directory when it was renamed and taken offline and online with the new name.  So... when we tried to bring the name online the new cluster it would not come online.

And of course the error message was not nice and clear like "Hey stupid, there is already an SPN in active directory with this name", no it wasn't, so it took me a good hour to find.

Also don't forget the properties of the cluster (virtual name is in there) and to sp_dropserver sp_addserver after rename.

Thanks to Vipin for helping find this !


Thursday, 16 November 2017 06:58:58 (Central Standard Time, UTC-06:00) | Comments [0] | SQL Server#
Sunday, 29 October 2017

Thomas First Deer

Nice 8 point buck, 2 antlers a bit broke off, but well done job by a young hunter.

Rossi 243, trifecta.

These bucks have been stripping the trees around my property for years, probably the reason the only thing growing back there is hedge and locust !

Sunday, 29 October 2017 09:25:28 (Central Standard Time, UTC-06:00) | Comments [0] | Web_Blog#
Monday, 21 August 2017

Solar Eclipse 2017


Monday, 21 August 2017 09:29:35 (Central Standard Time, UTC-06:00) | Comments [0] | Web_Blog#
Wednesday, 14 June 2017

Faith has first calf of 2017, a Bull calf.


Wednesday, 14 June 2017 19:41:36 (Central Standard Time, UTC-06:00) | Comments [0] | Web_Blog#
Tuesday, 13 June 2017

Configure read only routing for always on.

Necessary so that applicationintent=readonly goes to correct server.

Use SQLCMD to test, if it doesn't work (meaning return the read only replica server name), than you still need to configure the readonly routing, here is sqlcmd command:

sqlcmd -Svlnpaoam -E -d EAM -K READONLY -Q "Select @@servername"

Here is example to configure,

N'lnpclxsql11\EAM11' WITH 

N'lnpclxsql10\EAM10' WITH 

N'lnpclxsql10\EAM10' WITH 

N'lnpclxsql10\EAM10' WITH 

N'lnpclxsql11\EAM11' WITH 

N'lnpclxsql11\eam11' WITH 

N'lnpclxsql10\eam10' WITH 
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('lnpclxsql11\eam11','lnpclxsql10\eam10')));

N'lnpclxsql11\EAM11' WITH 
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('lnpclxsql10\eam10','lnpclxsql11\eam11')));
Tuesday, 13 June 2017 11:35:20 (Central Standard Time, UTC-06:00) | Comments [0] | SQL Server#
Monday, 15 May 2017

John Deere Gator 625 review.

A good machine, replaces an EZ-GO STS 4x4, many reviews of the specs on these so I won't re-iterate them, but it's basically the same as the ez-go with better ground clearance, Fuel injected (nice to not need to manually choke it), a design more updated and more like a car than a piece of lawn equipment (with a price to match), an updated design (vs EZGO), but basically still the same engine horsepower 18 vs 20, twin cylinder, Kawasaki vs. Honda engine, 2 and 4 wheel drive, with lever for locking differentials (EZ-GO had electronic locking differential switches).


This thing is loaded with power winch, roof with lights, power dump bed, brush guard with front storage, tool bag across back, and bucket holder, impressive towing power.  Would prefer a USB plug as opposed to the cigarette lighter accessory outlet (who uses a cigarette lighter anymore ?)


Transmission seems good, makes a weird whining noise when I go down hills (usually at very low speeds < 5), than makes a "bucking / catching" noise at the bottom when you hit the gas, probably the clutch engaging, not sure, we'll see how it holds up, I've learned to come to a stop at the bottom of hills and then proceed forward to avoid that "bucking / catching" noise.


Feature x feature and price, I found the gator 625 was better than others out there (Actually my wife did !).


Now I just need to rebuild the EZGO motor!


Only a few complaints so far after two months, about 60 some miles is

  1. Tail gate needed to be worked, not sure the dealer put that together right, wouldn't close and the "bar" would slide / fall right out that secures to the catch mechanism of the bed, took me an hour or so to find the thing in the grass after it fell out while riding around in the back pasture, PIA, but easy to fix, and most likely the dealer just didn't do it right.
  2. a tinny metallic sound coming from somewhere in the front right fender while riding, just a little annoying, still can't find what is rattling.
  3. It's a LOT louder than the EZGO, not as loud as some people that complain about (it's a common complaint on these).  There is someone that sells a suppressor for this thing, a MUST BUY.
  4. Of course it's just wide enough it won't fit in my trailer, so hopefully I don't need the warranty, as getting it back to the dealer will require a flat bed tow or borrowing / renting a trailer.  [ more of an observation than a complaint ].
  5. Floor needs more aggressive texturing, too smooth and when working with muddy boots it's slippery.


Options and Accessories.

  • Winch is nice, but you must use wheel chucks, as it's so powerful that it just drags the gator closer to whatever your winching, so far I think the winch is probably only useful for pulling the gator out of the mud if it's stuck, everything I've tried to winch, I had to go get the tractor and chains instead, so the winch is a waste for me, YMMV.
  • The power dump bed is the best option, as if you've ever tried to manually dump rocks, dirt or wood chips you quickly realize how much you need that, BEST OPTION, BUT, don't forget you have to lift the bed to check the oil, and remember to turn the key to the off position or you may wind up with a dead battery the next morning.
  • Definitely nice to have a BUZZER linked to the Parking Brake, can't tell you how many times I drove that EZGO around without realizing the parking brake was on (there is an idiot light, but hard to see in bright sun).
  • The fuel injected Kawasaki engine seems "thirstier" than the EZGO, no scientific way to measure that, feels like I fill it up more.
  • Bed Extender is nice, though the design to remove it with steel roll pins and springs is about unworkable, so far I've broken two of the steel roll pins and cursed up and down trying to get the stupid pins lined up, probably best if you need a bigger bed, buy a piece of plywood and lay it in there, or at least, don't ever plan on easily removing and putting it back in, once it's in, it's in, save the money on this accessory and buy some plywood and get a radio !
  • Doors made of netting, not sure about these yet, I may remove them.
  • Easy enough the kids can drive it with no issues (has retractable seat belts even).
Monday, 15 May 2017 09:27:56 (Central Standard Time, UTC-06:00) | Comments [0] | Web_Blog#
Monday, 01 May 2017

Absolutely the best hot water heater.

Rinnai Hybrid Tank-Tankless Water Heater - https://www.rinnai.us/hybrid-tank-tankless-water-heater 

Bought at Lowes for a very good price.

Rinnai Hybrid 40-GPM 87300-BTU 1-Year Limited Liquid Propane Hybrid Water Heater


Recently installed this hot water heater, 5 people in the house and it keeps up, have done laundry, dish washer and showers simultaneously and one after the other, so far, it's awesome !

Installed exactly where the old electric was, and was simpler than a "true tankless" hot water heater, which would have required re-doing propane regulator, cutting ceiling / floor joist to route intack and exhaust, re-routing water lines, hang hooded thing on outside of house to cover intake and exhaust and the hyprid was only 700 more than a regular hot water heater, so cheaper too, installation cost is / was the same as regular hot water heater.

We'll see how long it holds up, and the long term cost difference between propane vs. electric, but so far this thing is good, and no longer have to time our showers, do dishes or wash around who wants to shower !



Monday, 01 May 2017 09:00:49 (Central Standard Time, UTC-06:00) | Comments [0] | Web_Blog#
Friday, 28 April 2017

Asplundh delivers, wood chipper mulch dropped right to you by the truck load.


Friday, 28 April 2017 19:51:13 (Central Standard Time, UTC-06:00) | Comments [0] | Web_Blog#
Thursday, 20 April 2017

Aaron's cast removal 2017, the year of the bone !


Thursday, 20 April 2017 05:32:53 (Central Standard Time, UTC-06:00) | Comments [0] | Web_Blog#
Popular Posts
Unpatched Vulnerabiltiy discovered ...
Spring Fornicator brewed...
Calculate Stock Break Even Price
SQL Server Security, not where it n...
Skylights installed.
Recent Posts
February, 2018 (3)
January, 2018 (3)
November, 2017 (1)
October, 2017 (1)
August, 2017 (1)
June, 2017 (2)
May, 2017 (2)
April, 2017 (2)
March, 2017 (1)
February, 2017 (1)
December, 2016 (2)
October, 2016 (2)
September, 2016 (1)
August, 2016 (1)
July, 2016 (1)
March, 2016 (2)
February, 2016 (3)
December, 2015 (4)
November, 2015 (6)
September, 2015 (1)
August, 2015 (2)
July, 2015 (1)
March, 2015 (2)
January, 2015 (1)
December, 2014 (3)
November, 2014 (1)
July, 2014 (2)
June, 2014 (2)
May, 2014 (3)
April, 2014 (3)
March, 2014 (1)
December, 2013 (1)
October, 2013 (1)
August, 2013 (1)
July, 2013 (1)
June, 2013 (2)
May, 2013 (1)
March, 2013 (3)
February, 2013 (3)
January, 2013 (1)
December, 2012 (3)
November, 2012 (1)
October, 2012 (1)
September, 2012 (1)
August, 2012 (1)
July, 2012 (4)
June, 2012 (3)
April, 2012 (1)
March, 2012 (3)
February, 2012 (3)
January, 2012 (4)
December, 2011 (3)
October, 2011 (2)
September, 2011 (2)
August, 2011 (8)
July, 2011 (4)
June, 2011 (3)
May, 2011 (3)
April, 2011 (1)
March, 2011 (2)
February, 2011 (3)
January, 2011 (1)
September, 2010 (1)
August, 2010 (2)
May, 2010 (2)
April, 2010 (3)
March, 2010 (1)
February, 2010 (4)
January, 2010 (1)
December, 2009 (3)
November, 2009 (2)
October, 2009 (2)
September, 2009 (5)
August, 2009 (4)
July, 2009 (8)
June, 2009 (2)
May, 2009 (3)
April, 2009 (9)
March, 2009 (6)
February, 2009 (3)
January, 2009 (8)
December, 2008 (8)
November, 2008 (4)
October, 2008 (14)
September, 2008 (10)
August, 2008 (7)
July, 2008 (7)
June, 2008 (11)
May, 2008 (14)
April, 2008 (12)
March, 2008 (17)
February, 2008 (10)
January, 2008 (13)
December, 2007 (7)
November, 2007 (8)
Admin Login
Sign In