select [distributionHistory].comments [Comments]
, isnull([Last Error].error_text,'') as [Last Error]
, convert(datetimeoffset, [distributionHistory].[time]) [Last Synchronized Date]
, [distributionAgent].publication [Publication Name]
, [distributionAgent].publisher_db as [Publisher Database]
when [distributionHistory].runstatus = '1' then 'Start'
when [distributionHistory].runstatus = '2' then 'Succeed'
when [distributionHistory].runstatus = '3' then 'InProgress'
when [distributionHistory].runstatus = '4' then 'Idle'
when [distributionHistory].runstatus = '5' then 'Retry'
when [distributionHistory].runstatus = '6' then 'Fail'
else cast([distributionHistory].runstatus as varchar)
) [Run Status]
, sub.name [Subscriber]
, [distributionAgent].subscriber_db [Subscriber Database]
when [distributionAgent].subscription_type = '0' then 'Push'
when [distributionAgent].subscription_type = '1' then 'Pull'
when [distributionAgent].subscription_type = '2' then 'Anonymous'
else cast([distributionAgent].subscription_type as varchar)
) [Subscription Type]
, und.UndelivCmdsInDistDB [Undelivered Commands]
[distribution].[dbo].[MSdistribution_agents] as [distributionAgent] with (nolock)
left join [distribution].[dbo].[MSdistribution_history] as [distributionHistory] with (nolock)
on [distributionHistory].agent_id = [distributionAgent].id
, sum(case when xact_seqno > MaxAgentValue.maxseq then 1 else 0 end) as UndelivCmdsInDistDB
[distribution].[dbo].[MSrepl_commands] t with (nolock)
join [distribution].[dbo].MSsubscriptions as s with (nolock)
t.article_id = s.article_id
and t.publisher_database_id = s.publisher_database_id
, max(hist.[time]) as [time]
[distribution].[dbo].MSdistribution_history hist with (nolock)
, isnull(max(xact_seqno), 0x0) as maxseq
from [distribution].[dbo].MSdistribution_history with (nolock)
group by agent_id
) as h
hist.agent_id = h.agent_id
and h.maxseq = hist.xact_seqno
) as MaxAgentValue
on MaxAgentValue.agent_id = s.agent_id
on [distributionAgent].id = und.agent_id
and und.[time] = [distributionHistory].[time]
join [master].[sys].[servers] as sub with (nolock)
on [distributionAgent].subscriber_id = sub.server_id
outer apply (
select top 1
from [distribution].[dbo].MSrepl_errors with (nolock)
where id = [distributionHistory].error_id
order by time desc
) as [Last Error];
I used to get really nervous about renaming database files, which I think is an appropriate response. Changing the attributes of the database’s physical objects does sound a bit scary on the surface, but once you’ve done it a few times you’ll realize it’s easy to do, and easy to fix if you make a mistake. This simple script template shows the basic steps of the process.
--Change logical file names.
ALTER DATABASE [My_Database]
MODIFY FILE(NAME = [old_name_data], NEWNAME = [new_name_data]);
ALTER DATABASE [My_Database]
MODIFY FILE(NAME = [old_name_log], NEWNAME = [new_name_log]);
--Disconnect all existing sessions.
ALTER DATABASE [My_Database] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
--Change database to OFFLINE mode.
ALTER DATABASE [My_Database] SET OFFLINE;
STOP! NOW RENAME THE PHYSICAL FILES ON DISK
--Point databases at new phyisical files.
ALTER DATABASE [My_Database] MODIFY FILE(NAME = 'new_name_data',
FILENAME = 'D:\MSSQL13.MSSQLSERVER\MSSQL\DATA\new_name_data.mdf');
ALTER DATABASE [My_Database] MODIFY FILE(NAME = 'new_name_log',
FILENAME = 'L:\MSSQL13.MSSQLSERVER\MSSQL\DATA\new_name_log.ldf');
--Change database to ONLINE mode.
ALTER DATABASE [My_Database] SET ONLINE;
--Reopen connections to all users
ALTER DATABASE [My_Database] SET MULTI_USER;
I came across this website that has an excellent explanation for introduction to machine learning. The visualizations are very nice and easy to understand.
I was preparing to remote into a virtual machine to do some maintenance when I encountered an error.
“An authentication error has occurred.
The function requested is not supported
Remote computer: [Computer Name]
This could be due to CredSSP encryption oracle remediation.
For more information, see https://go.microsoft.com/fwlink/?linkid=866660”
Information from the link in the error:
Credential Security Support Provider protocol (CredSSP) is an authentication provider that processes authentication requests for other applications.
A remote code execution vulnerability exists in unpatched versions of CredSSP. An attacker who successfully exploits this vulnerability could relay user credentials to execute code on the target system. Any application that depends on CredSSP for authentication may be vulnerable to this type of attack.
This security update addresses the vulnerability by correcting how CredSSP validates requests during the authentication process.
The bottom line is that in order to RDP into the target server both computers need to have the update installed. In my case, my local machine was updated, but the target machine was not yet updated. This target machine did not have automatic updates turned on and was an Azure VM. I could have contacted support, or turned on automatic updates from the portal, but the reality was I needed to get into the machine quickly to handle an emergency fix. Due to the simple architecture (stand-alone VM) I could not push the patch via SCCM or GPO.
Power BI On-Premises Data Gateway is a windows service (PBIEgwService) that moves data from on-premises to the cloud, and also is used by Power BI Report Server. The data gateway has been failing nearly daily. I’ve changed the service settings to restart upon failure with a 3 minute buffer. If this does not resolve the issue I might set up a heart-beat process that checks the health of the service and if it’s ever not running it will start it.
Strengthening my BIML skills for SSIS automation.
Microsoft recently announced at the 2017 SQL PASS event that they would be previewing a new tool for sql server that works across multiple operating systems.
“SQL Operations Studio (preview) is a free, light-weight tool, that runs on Windows, macOS, and Linux, for managing SQL Server, Azure SQL Database, and Azure SQL Data Warehouse; wherever they’re running.”
It’s now available for preview.
Read and Download:
More info (video) at TechNet:
I stumbled upon Fran Blanche’s YouTube channel today and can’t get enough, except that I have to cut my self off now and get back to some actual work. She owns a guitar effects pedal company (http://www.frantone.com), tests and builds electronics, reviews old technology, uses 3d printers, she’s an artist, musician, and the list goes on. What a neat human!
“In my opinion it is the most important circuit board in history – and I examine in detail this historic relic from my own personal collection. The LVDC is a strangely obscure footnote in the history of computing, yet it represents some of the greatest leaps that computers ever took. I have spent a good portion of my life designing printed circuit boards, so I have a perspective on this magnificent artifact that I will do my best to convey in this video. But there is so much about this board and its history that I simply do not know – and I hope that some of you who watch this can add insight into the mysteries of the Saturn V Launch Vehicle Digital Computer.” – Fran Blanche
Help Support Fran’s YouTube Channel on Patreon: http://www.patreon.com/frantone
Subscribe to Fran’s YouTube Channel: https://www.youtube.com/franblanche