Horkay Blog
The postings on this site are my own and do not represent my Employer's positions, advice or strategies.
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#
Monday, 06 March 2017

Cast removal day !


Monday, 06 March 2017 07:53:42 (Central Standard Time, UTC-06:00) | Comments [0] | Web_Blog#
Monday, 20 February 2017

Troubleshooting tlog backup failure in always-on environment, backups on read-only / passive node.

This was an odd failure, as it occurred 4 or 5 times, right after the full backup, than it would "resolve itself", but the next day, right after the full backup, it would happen again, repeating itself daily in this fashion.

Tracked this back to an issue with the disk, where it seems we had a 605 error during a checkdb, that also "resolved itself" and the next days checkdb was good, so no action was taken, but this is what started the chain of events.

Eventually I did find someone with a similar issue that a system spid was hung, I resolved my issue by rebooting the environment(s).  Link and text below my error messages.

Log backup for database "{db name here}" on secondary replica created backup
files successfully but could not ensure that a backup point has been
committed on the primary.  This is an informational message only.
Preserve this log backup along with the other log backups of this
database.          [SQLSTATE 01000]

Msg 35250, Sev 16, State 11, Line 39 : The connection to the primary
replica is not active.  The command cannot be processed. [SQLSTATE 42000]
Msg 3013, Sev 16, State 1, Line 39 : BACKUP LOG is terminating abnormally.
[SQLSTATE 42000]

The operating system returned error 665(The requested operation could not
be completed due to a file system limitation) to SQL Server during a write
at offset 0x00001cb940e000 in file
'{drive letter\db.ndf file}:MSSQL_DBCC12'. Additional
messages in the SQL Server error log and system event log may provide more
detail. This is a severe system-level error condition that threatens
database integrity and must be corrected immediately. Complete a full
database consistency check (DBCC CHECKDB). This error can be caused by
many factors; for more information, see SQL Server Books Online




This error surprised me when it showed up in the error log so I decided to dig into it a bit more.

“Log backup for database “MyDB” on secondary replica created backup files successfully but could not ensure that a backup point has been committed on the primary.  This is an informational message only. 

Preserve this log backup along with the other log backups of this database.”

The message appears on the secondary replica, where the backup was taken, and indicates one of two possible conditions.  To understand the conditions it helps to understand the state of the backup operation.

The error occurs after the point where the backup was successfully streamed to the backup media. The first concern I had was “is my backup valid” and the answer is YES!  The backup has been written to the backup media properly.

Now the secondary sends a message to the primary to update the backup position in the database.  If this message fails the error is logged.

1. The secondary sends the completed message to the primary.  The primary records the backup position but for some reason fails to respond the secondary (I.E. lost network connection.)   In this case the next backup will pick up were the current backup completed.

2. The secondary sends the completed message to the primary.  The primary never receives the message or fails in some way before it can record the current backup position.  (I.E. network, primary shut down, etc…).  In this case the next backup will acquire the same information as the last log backup and any additional log records generated.

As you can see the message may be a bit alarming but it is harmless to your backup strategy other than the fact that you might get extra log records in a backup but that can be properly handled during restore.  NO DATA LOSS!


Diane Sithoo says:

I'm seeing a third condition, trying to perform native log backups, where for some reason, although the backup has been written properly to the backup media, all further attempts to backup fail with Msg 35250, Level 16, State 11, "The connection to the primary replica is not active.  The command cannot be processed.".  This is then followed by Msg 3013, Level 16, State 1, "BACKUP LOG is terminating abnormally."

So far, even in dealing with MS support, there is no resolution to this.  What happens is that at that point, the background process is blocked.  When I use DMV view sys.dm_exec_requests, it shows as HADR BACKUP LOCK HOLDER.  The wait type on the blocked process is LCK_M_U and the wait resource is DATABASE: <id> [BULKOP_BACKUP_LOG].  On the blocking process, the wait type is HADR_BACKUP_QUEUE.  Until I either restart SQL Server Agent on the secondary, or run DBCC STACKDUMP, it continues to block.

Monday, 20 February 2017 12:33:34 (Central Standard Time, UTC-06:00) | Comments [0] | SQL Server | Web_Blog#
Tuesday, 13 December 2016

Still trying to get some Red Baron Peach trees growing.

From Willis Orchards

  • Red Baron Peach - 4
  • Fuyu Persimmon Tree - 2
  • Arkansas Black Apple Tree - 2
  • Hall's Hardy Almond Tree - 2
  • Pawnee Pecan Tree - 2


Tuesday, 13 December 2016 16:15:07 (Central Standard Time, UTC-06:00) | Comments [0] | Web_Blog#
Friday, 09 December 2016

The Kawasaki KLX140G is great intermediate size dirt bike for teenage kids.  Good trail riding bike, electric start, choke up on the handlebars, chain is easy to adjust, oil and filter easy to get too.  Had to replace a clutch handle already when it was laid down while riding, overall a great bike.   If you take it to moto track, remember to remove the kickstand or ziptie it up, even though it has a strong spring you don't want it flopping down on a jump, seen a bad accident happen, so even though we haven't experienced that with this bike, I always take those off when the kid rides anything with jumps.


Friday, 09 December 2016 08:01:45 (Central Standard Time, UTC-06:00) | Comments [0] | Web_Blog#
Thursday, 06 October 2016

Couple different ways to get block size on windows.

Traditionally I used fsutil, previous blog post on that here, but recently I've been using powershell, and then using TSQL to run the powershell script and return it as a result set, which I then keep in a tracking system.

Here is the powershell script:

Get-WmiObject win32_volume | select SystemName, Label, name,

Here is the same script, embedded in TSQL (yes it uses xp_cmdshell).

Declare @Is_XP_CMDSHELL_Enabled Int

Set @Is_XP_CMDSHELL_Enabled = (
    SELECT CONVERT(INT, ISNULL(value, value_in_use)) AS config_value
    FROM  sys.configurations
    WHERE  name = 'xp_cmdshell');

-- not enabled
If @Is_XP_CMDSHELL_Enabled = 0 
    -- To allow advanced options to be changed.
    EXEC sp_configure 'show advanced options', 1
    -- To update the currently configured value for advanced options.
    -- To enable the feature.
    EXEC sp_configure 'xp_cmdshell', 1
    -- To update the currently configured value for this feature.
(line varchar(255))
INSERT #output
EXEC xp_cmdshell 'Powershell –command "get-wmiobject Win32_volume |select Name,Capacity,Freespace,BlockSize,Label | ConvertTo-Xml -NoTypeInformation -As string"'

-- only disable xp_cmdshell if it was found this way
If @Is_XP_CMDSHELL_Enabled = 0 
    -- immediately disable xp_cmdshell
    EXEC master.dbo.sp_configure 'show advanced options', 1
    EXEC master.dbo.sp_configure 'xp_cmdshell', 0
DECLARE @doc varchar(max)
SET @doc = ''
DECLARE @line varchar(255)
FOR SELECT line FROM #output
OPEN xml_cursor
FETCH NEXT FROM xml_cursor INTO @line
SET @doc = @doc + @line
FETCH NEXT FROM xml_cursor INTO @line
CLOSE xml_cursor
DEALLOCATE xml_cursor
DROP TABLE #output
item.ref.value('(Property/text())[1]', 'nvarchar(128)') AS VolumeName
,item.ref.value('(Property/text())[2]', 'nvarchar(128)') AS SizeBytes
,item.ref.value('(Property/text())[3]', 'nvarchar(128)') AS FreeBytes
,item.ref.value('(Property/text())[4]', 'nvarchar(128)') AS [BlockSize]
,item.ref.value('(Property/text())[5]', 'nvarchar(128)') AS Label
FROM (SELECT CAST(@doc AS XML) AS feedXml) feeds(feedXml)
CROSS APPLY feedXml.nodes('/Objects/Object') AS item(ref)


Thursday, 06 October 2016 17:03:45 (Central Standard Time, UTC-06:00) | Comments [0] | SQL Server | Web_Blog#
Wednesday, 05 October 2016
Wednesday, 05 October 2016 20:17:14 (Central Standard Time, UTC-06:00) | Comments [0] | Web_Blog#
Popular Posts
Unpatched Vulnerabiltiy discovered ...
Spring Fornicator brewed...
Calculate Stock Break Even Price
Need an H&R Block Coupon ?
Useable Space
Recent Posts
January, 2018 (1)
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