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

Incredible, but true.

SQL Server 2017, specific build = 14.0.3015.40, on an Azure Cloud server, IAAS.

While restoring a database it just sits there forever what felt like forever (it was 80gb), and after awhile saw this in the error log:

The operating system returned the error '21(The device is not ready.)' while attempting 'GetDiskFreeSpace' on 'A:\'.

Guess what, you fix this by disabling your floppy drive in device manager. Absolutely amazing.

 http://www.sqlservercentral.com/blogs/sql-geek/2017/05/15/sql-server-2017-the-operating-system-returned-the-error-21/

I actually still have some 5 1/2 and 3 1/4 floppy disks and a USB Floppy drive, though I doubt there would be a use for a "floppy drive" controller on a cloud machine, but it's good to see the floppy's in the cloud.

Thursday, 17 May 2018 20:10:34 (Central Standard Time, UTC-06:00) | Comments [0] | SQL Server#

Setting up always-on availability group with no cluster.

So far works good, though some of our backup and monitoring queries began failing, specifically the function, sys.fn_hadr_backup_is_preferred_replica

Msg 35222, Level 16, State 0, Line 1 Could not process the operation. Always On Availability Groups does not have permissions to access the Windows Server Failover Clustering (WSFC) cluster. Disable and re-enable Always On Availability Groups by using the SQL Server Configuration Manager. Then, restart the SQL Server service, and retry the currently operation. For information about how to enable and disable Always On Availability Groups, see SQL Server Books Online.

We ended up working around this by checking the sys.availability_groups which has a column for cluster type, this way we can check if the always-on group has a cluster or not.

-- This leads to error
select [master].sys.fn_hadr_backup_is_preferred_replica('ao_test')
go
select
CASE WHEN dbrs.is_primary_replica = 1 and ag.cluster_type = 1 then 1
when dbrs.is_primary_replica = 0 and ag.cluster_type = 1 then 0
WHEN ag.cluster_type is null then 0
else Convert(int,[master].sys.fn_hadr_backup_is_preferred_replica(sd.[name])) end
as preferred_replica_backup
From sys.databases sd
LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs
ON sd.group_database_id = dbrs.group_database_id and dbrs.is_local = 1
left outer join master.sys.availability_groups ag
on ag.group_id = dbrs.group_id where sd.[name] = 'ao_test'

 

Thursday, 17 May 2018 11:57:24 (Central Standard Time, UTC-06:00) | Comments [0] | SQL Server#

Horkay Family Farms first heifer calf, 3 bull calves in row up until now !

5/17/2018, Not yet named

 

Thursday, 17 May 2018 11:26:42 (Central Standard Time, UTC-06:00) | Comments [0] | Web_Blog#
Monday, 23 April 2018

Planting Tree's for 2018.

Threw in some new cheap plants from Farmer Seed (aka burgess, plants are of questionable quality, hit and miss, but they are cheap).

Concentrating on removing invasive trees (locust, hedge apple, thorny Bradford pear) and replacing with Asian Pears, Arkansas Black Apple, Wealthy Apple and a few others.

We'll see what lives, success is usually pretty good with Stark Brothers and Nature Hills Nursery, though I've had terrible luck getting Sassafras trees to grow, 5 more this year.

QTY Description From Picture
2 Korean Fir Japanese Maples and Evergreens
2 Dolgo Crabapple Stark Brothers 1
Farmer Seed and Nursery 1
 
1 Starking Hardy Giant Asian Pear Stark Brothers  
1 Chojuro Asian Pear Stark Brothers  
1 Hosui Asian Pear Dwarf Startk Brothes  
1 Grimes Golden Apple Stark Brother's Nursery  
1 Arkansas Black Apple Nature Hills Nursery  
1 Sweet Chestnuts Farmer Seed and Nursery
2 Wealthy Apple Nature Hills Nursery  
1 American Holly Tree Form Nature Hills Nursery  
2 Flavor King Pluot Nature Hills Nursery  
1 Thunder Cloud Plum Nature Hills Nursery  
2 Scarlet Halo Peach Nature Hills Nursery  
2 Ichi-Ki-Kei-Jiro Asian Persimmon Stark Brother's  
1 Sunflower Paw Paw Stark Brother's  
1 Peruque Pecan Stark Brother's  
1 Chestnut Crabapple Stark Brother's  
1 Auburn Homestead Chestnut Stark Brother's  
5 Black Hills Spruce Chief River Nursery  
2 Green Giant Arborvitae Chief River Nursery  
5 White Pine Chief River Nursery  
5 White Spruce Chief River Nursery  
5 Concord Seedless Grape Chief River Nursery  
5 Somerset Seedless Grape (white) Chief River Nursery  
1 Heartnut Farmer Seed and Nursery  
1 Hall's Hardy Almonds
Farmer Seed and Nursery  
5 Sassafras Nature Hills Nursery - 2
Farmer Seed and Nursery - 3
 
1 American Persimmon Farmer Seed and Nursery  
4 Dwarf American Hazelnuts Farmer Seed and Nursery  
2 American Butternut Farmer Seed and Nursery  
2 Old Fashioned Lilac Farmer Seed and Nursery  
6 Canadian Hemlock Farmer Seed and Nursery  
1 Paw Paw Tree Farmer Seed and Nursery  
24 Rose of Sharon Hedge Farmer Seed and Nursery  
6 Purple Leaf Plum Hedge Farmer Seed and Nursery  
16 Forsythia Farmer Seed and Nursery  
1 Harry Lauder's Walking Stick
Farmer Seed and Nursery  
1 Golden Chain Tree Farmer Seed and Nursery  
4 Purple Smoke Tree Farmer Seed and Nursery  

Monday, 23 April 2018 13:48:59 (Central Standard Time, UTC-06:00) | Comments [0] | Web_Blog#
Monday, 09 April 2018

Horkay Family Farms

Breakfast laid daily !

 

Monday, 09 April 2018 13:12:02 (Central Standard Time, UTC-06:00) | Comments [0] | Web_Blog#
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).
https://social.technet.microsoft.com/Forums/en-US/abd274a7-5a6d-487c-a1df-01fb10e5b84b/dmexecrequests-showing-a-negative-value-in-the-logical-reads-column?forum=sqldatabaseengine
and
https://social.technet.microsoft.com/Forums/en-US/abd274a7-5a6d-487c-a1df-01fb10e5b84b/dmexecrequests-showing-a-negative-value-in-the-logical-reads-column?forum=sqldatabaseengine

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

https://simplesqlserver.com/2016/01/25/querying-deadlocks-from-system_health-xevent/

and

Tracing Deadlock Graphs: Extended Events or Server Side Trace

http://littlekendra.com/2016/12/27/tracing-deadlock-graphs-extended-events-or-server-side-trace/

DECLARE @SessionName SysName

SELECT
@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'
--*/

IF OBJECT_ID('tempdb..#Events') IS NOT NULL BEGIN
DROP TABLE
#Events
END

DECLARE
@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)')
,
e.DeadlockID
FROM #Events e
CROSS APPLY e.DeadlockGraph.nodes('/event/data/value/deadlock/victim-list/victimProcess') as Deadlock(Victims)
)
,
DeadlockObjects AS
(
SELECT DISTINCT e.DeadlockID
, 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 *
FROM
(
SELECT e.DeadlockID
, TransactionTime = Deadlock.Process.value('@lasttranstarted', 'datetime')
,
DeadlockGraph
, DeadlockObjects = substring((SELECT (', ' + o.ObjectName)
FROM DeadlockObjects o
WHERE o.DeadlockID = e.DeadlockID
ORDER BY o.ObjectName
FOR XML PATH ('')
),
3, 4000)
,
Victim = CASE WHEN v.VictimID IS NOT NULL
THEN 1
ELSE 0
END
, 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)')
END
, 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)
LEFT JOIN
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
ORDER BY DeadlockID DESC

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: 

http://mumblestiltskin.blogspot.com/2011/03/sql-server-job-step-output-file-using_08.html

https://docs.microsoft.com/en-us/sql/ssms/agent/use-tokens-in-job-steps 

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

O:\MSSQL11.BCTAX03\MSSQL\JOBS\SQLAGENT_JOB_Backup_TRN(ALWAYSON)_StepID-1_$(ESCAPE_SQUOTE(STRTDT)).txt 

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,

ALTER AVAILABILITY GROUP AG_LNP_EAM
MODIFY REPLICA ON
N'lnpclxsql11\EAM11' WITH 
(PRIMARY_ROLE (ALLOW_CONNECTIONS = all));

ALTER AVAILABILITY GROUP AG_LNP_EAM
MODIFY REPLICA ON
N'lnpclxsql10\EAM10' WITH 
(PRIMARY_ROLE (ALLOW_CONNECTIONS = all));
-----------------------------------------------------------------

ALTER AVAILABILITY GROUP AG_LNP_EAM
MODIFY REPLICA ON
N'lnpclxsql10\EAM10' WITH 
(SECONDARY_ROLE (ALLOW_CONNECTIONS = Read_Only));

ALTER AVAILABILITY GROUP AG_LNP_EAM
MODIFY REPLICA ON
N'lnpclxsql10\EAM10' WITH 
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://LNPCLXSQL10.HRBINC.HRBLOCK.NET:1600'));
-----------------------------------------------------------------

ALTER AVAILABILITY GROUP AG_LNP_EAM
MODIFY REPLICA ON
N'lnpclxsql11\EAM11' WITH 
(SECONDARY_ROLE (ALLOW_CONNECTIONS = Read_Only));

ALTER AVAILABILITY GROUP AG_LNP_EAM
MODIFY REPLICA ON
N'lnpclxsql11\eam11' WITH 
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://lnpclxsql11.HRBINC.HRBLOCK.NET:1600'));
-----------------------------------------------------------------

ALTER AVAILABILITY GROUP AG_LNP_EAM 
MODIFY REPLICA ON
N'lnpclxsql10\eam10' WITH 
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('lnpclxsql11\eam11','lnpclxsql10\eam10')));

ALTER AVAILABILITY GROUP AG_LNP_EAM 
MODIFY REPLICA ON
N'lnpclxsql11\EAM11' WITH 
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('lnpclxsql10\eam10','lnpclxsql11\eam11')));
GO
Tuesday, 13 June 2017 11:35:20 (Central Standard Time, UTC-06:00) | Comments [0] | SQL Server#
Search
Popular Posts
Unpatched Vulnerabiltiy discovered ...
Spring Fornicator brewed...
SQL 2008 R2 License / Cost = Open S...
Pied Piper of Grandview II
Buick drops the ball, er...muffler
Recent Posts
Archive
May, 2018 (3)
April, 2018 (2)
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)
Links
Categories
Admin Login
Sign In
Blogroll