Insight

This error is encountered when backing up transaction logs in an alwaysOn environment. When this occurs, it means the databases were not fully restored in you secondary nodes.

I was revisiting automation scripts that I had written back in the day and wanted to automate some steps to determine all the steps running on SQL Server. With all the resources on net, I didn’t want to write one from scratch so I went on a hunt and the best solution I found was by Robert Pearl @PearlKnows from an article he wrote on https://www.mssqltips.com/sqlservertip/2610/sql-services-status-check–an-evolution-part-2/ great script and it would do the job but I need to change a few things.

1. Main change was determining the version of SQL Server without having to keep updating the script as new versions are released so I made the following change specifically for SSIS Service which changes the name on each release

--IF CHARINDEX('2008',@@Version) > 0 SET @DTS='MsDtsServer100'
--IF CHARINDEX('2005',@@Version) > 0 SET @DTS= 'MsDtsServer'

select @DTS = 'MsDtsServer'+substring(cast(value_data as varchar(10)), 1,2)+'0' from sys.dm_server_registry where value_name = 'CurrentVersion'
SET @REGKEY = 'System\CurrentControlSet\Services\'+@DTS

2. Change the Temp tables cleanup – very minor

Below is my entire script, again thanks to Robert for such a great work

--Check SQL Server Services Status
SET NOCOUNT ON
IF EXISTS (SELECT * FROM TEMPDB.DBO.sysobjects
WHERE NAME LIKE 'ServicesServiceStatus')
DROP TABLE tempdb.dbo.ServicesServiceStatus
IF EXISTS (SELECT * FROM TEMPDB.DBO.sysobjects
WHERE NAME LIKE 'RegResult')
DROP TABLE tempdb.dbo.RegResult
CREATE TABLE tempdb.dbo.RegResult
(
ResultValue NVARCHAR(4)
)
CREATE TABLE tempdb.dbo.ServicesServiceStatus
(
RowID INT IDENTITY(1,1)
,ServerName NVARCHAR(128)
,ServiceName NVARCHAR(128)
,ServiceStatus VARCHAR(128)
,StatusDateTime DATETIME DEFAULT (GETDATE())
,PhysicalSrverName NVARCHAR(128)
)
DECLARE
@ChkInstanceName NVARCHAR(128) /*Stores SQL Instance Name*/
,@ChkSrvName NVARCHAR(128) /*Stores Server Name*/
,@TrueSrvName NVARCHAR(128) /*Stores where code name needed */
,@SQLSrv NVARCHAR(128) /*Stores server name*/
,@PhysicalSrvName NVARCHAR(128) /*Stores physical name*/
,@DTS NVARCHAR(128) /*Store SSIS Service Name */
,@FTS NVARCHAR(128) /*Stores Full Text Search Service name*/
,@RS NVARCHAR(128) /*Stores Reporting Service name*/
,@SQLAgent NVARCHAR(128) /*Stores SQL Agent Service name*/
,@OLAP NVARCHAR(128) /*Stores Analysis Service name*/
,@REGKEY NVARCHAR(128) /*Stores Registry Key information*/
SET @PhysicalSrvName = CAST(SERVERPROPERTY('MachineName') AS VARCHAR(128))
SET @ChkSrvName = CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128))
SET @ChkInstanceName = @@serverName
IF @ChkSrvName IS NULL /*Detect default or named instance*/
BEGIN
SET @TrueSrvName = 'MSSQLSERVER'
SELECT @OLAP = 'MSSQLServerOLAPService' /*Setting up proper service name*/
SELECT @FTS = 'MSFTESQL'
SELECT @RS = 'ReportServer'
SELECT @SQLAgent = 'SQLSERVERAGENT'
SELECT @SQLSrv = 'MSSQLSERVER'
END
ELSE
BEGIN
SET @TrueSrvName = CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128))
SET @SQLSrv = '$'+@ChkSrvName
SELECT @OLAP = 'MSOLAP' + @SQLSrv /*Setting up proper service name*/
SELECT @FTS = 'MSFTESQL' + @SQLSrv
SELECT @RS = 'ReportServer' + @SQLSrv
SELECT @SQLAgent = 'SQLAgent' + @SQLSrv
SELECT @SQLSrv = 'MSSQL' + @SQLSrv
END
/* ------------------------ SQL Server Service Section ----------------------*/
SET @REGKEY = 'System\CurrentControlSet\Services\'+@SQLSrv
INSERT tempdb.dbo.RegResult ( ResultValue ) EXEC MASTER.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= @REGKEY
IF (SELECT ResultValue FROM tempdb.dbo.RegResult) = 1
BEGIN
INSERT tempdb.dbo.ServicesServiceStatus (ServiceStatus) /*Detecting staus of SQL Sever service*/
EXEC xp_servicecontrol N'QUERYSTATE',@SQLSrv
UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'MS SQL Server Service' WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE tempdb.dbo.RegResult
END
ELSE
BEGIN
INSERT INTO tempdb.dbo.ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'MS SQL Server Service' WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE tempdb.dbo.RegResult
END
/* -------------------- SQL Server Agent Service Section ----------------*/
SET @REGKEY = 'System\CurrentControlSet\Services\'+@SQLAgent
INSERT tempdb.dbo.RegResult ( ResultValue ) EXEC MASTER.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= @REGKEY
IF (SELECT ResultValue FROM tempdb.dbo.RegResult) = 1
BEGIN
INSERT tempdb.dbo.ServicesServiceStatus (ServiceStatus) /*Detecting staus of SQL Agent service*/
EXEC xp_servicecontrol N'QUERYSTATE',@SQLAgent
UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'SQL Server Agent Service' WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE tempdb.dbo.RegResult
END
ELSE
BEGIN
INSERT INTO tempdb.dbo.ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'SQL Server Agent Service' WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE tempdb.dbo.RegResult
END
/* ---------------------------------- SQL Browser Service Section ------------*/
SET @REGKEY = 'System\CurrentControlSet\Services\SQLBrowser'
INSERT tempdb.dbo.RegResult ( ResultValue ) EXEC MASTER.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= @REGKEY
IF (SELECT ResultValue FROM tempdb.dbo.RegResult) = 1
BEGIN
INSERT tempdb.dbo.ServicesServiceStatus (ServiceStatus) /*Detecting staus of SQL Browser Service*/
EXEC MASTER.dbo.xp_servicecontrol N'QUERYSTATE',N'sqlbrowser'
UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'SQL Browser Service - Instance Independent' WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE tempdb.dbo.RegResult
END
ELSE
BEGIN
INSERT INTO tempdb.dbo.ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'SQL Browser Service - Instance Independent' WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE tempdb.dbo.RegResult
END
/* ---------------------------------- Integration Service Section -------------*/
--IF CHARINDEX('2008',@@Version) > 0 SET @DTS='MsDtsServer100'
--IF CHARINDEX('2005',@@Version) > 0 SET @DTS= 'MsDtsServer'
select @DTS = 'MsDtsServer'+substring(cast(value_data as varchar(10)), 1,2)+'0' from sys.dm_server_registry
where value_name = 'CurrentVersion'
SET @REGKEY = 'System\CurrentControlSet\Services\'+@DTS
INSERT tempdb.dbo.RegResult ( ResultValue ) EXEC MASTER.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= @REGKEY
IF (SELECT ResultValue FROM tempdb.dbo.RegResult) = 1
BEGIN
INSERT tempdb.dbo.ServicesServiceStatus (ServiceStatus) /*Detecting staus of Intergration Service*/
EXEC MASTER.dbo.xp_servicecontrol N'QUERYSTATE',@DTS
UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'Integration Service - Instance Independent' WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE tempdb.dbo.RegResult
END
ELSE
BEGIN
INSERT INTO tempdb.dbo.ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'Integration Service - Instance Independent' WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE tempdb.dbo.RegResult
END
/* ---------------------------------- Reporting Service Section ----------*/
SET @REGKEY = 'System\CurrentControlSet\Services\'+@RS
INSERT tempdb.dbo.RegResult ( ResultValue ) EXEC MASTER.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= @REGKEY
IF (SELECT ResultValue FROM tempdb.dbo.RegResult) = 1
BEGIN
INSERT tempdb.dbo.ServicesServiceStatus (ServiceStatus) /*Detecting staus of Reporting service*/
EXEC MASTER.dbo.xp_servicecontrol N'QUERYSTATE',@RS
UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'Reporting Service' WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE tempdb.dbo.RegResult
END
ELSE
BEGIN
INSERT INTO tempdb.dbo.ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'Reporting Service' WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE tempdb.dbo.RegResult
END
/* ---------------------------------- Analysis Service Section ---------------*/
IF @ChkSrvName IS NULL /*Detect default or named instance*/
BEGIN
SET @OLAP = 'MSSQLServerOLAPService'
END
ELSE
BEGIN
SET @OLAP = 'MSOLAP'+'$'+@ChkSrvName
SET @REGKEY = 'System\CurrentControlSet\Services\'+@OLAP
END
INSERT tempdb.dbo.RegResult ( ResultValue ) EXEC MASTER.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= @REGKEY
IF (SELECT ResultValue FROM tempdb.dbo.RegResult) = 1
BEGIN
INSERT tempdb.dbo.ServicesServiceStatus (ServiceStatus) /*Detecting staus of Analysis service*/
EXEC MASTER.dbo.xp_servicecontrol N'QUERYSTATE',@OLAP
UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'Analysis Services' WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE tempdb.dbo.RegResult
END
ELSE
BEGIN
INSERT INTO tempdb.dbo.ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'Analysis Services' WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE tempdb.dbo.RegResult
END
/* ---------------------------------- Full Text Search Service Section ---------*/
SET @REGKEY = 'System\CurrentControlSet\Services\'+@FTS
INSERT tempdb.dbo.RegResult ( ResultValue ) EXEC MASTER.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= @REGKEY
IF (SELECT ResultValue FROM tempdb.dbo.RegResult) = 1
BEGIN
INSERT tempdb.dbo.ServicesServiceStatus (ServiceStatus) /*Detecting staus of Full Text Search service*/
EXEC MASTER.dbo.xp_servicecontrol N'QUERYSTATE',@FTS
UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'Full Text Search Service' WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE tempdb.dbo.RegResult
END
ELSE
BEGIN
INSERT INTO tempdb.dbo.ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
UPDATE tempdb.dbo.ServicesServiceStatus SET ServiceName = 'Full Text Search Service' WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET ServerName = @TrueSrvName WHERE RowID = @@identity
UPDATE tempdb.dbo.ServicesServiceStatus SET PhysicalSrverName = @PhysicalSrvName WHERE RowID = @@identity
TRUNCATE TABLE tempdb.dbo.RegResult
END
/* ---------------------------------------------------------------*/
SELECT ServiceName AS 'SQL Server Service'
,ServiceStatus AS 'Current Service Status'
,StatusDateTime AS 'Date/Time Service Status Checked'
FROM tempdb.dbo.ServicesServiceStatus
/* ----------------------------------------------------------------------*/


There are moments, when you need to remove all the mysites in a SharePoint environment

Quick PowerShell script

$sites = Get-SPSite | Where-Object {$_.Url -like “<host>/personal/*”}

foreach ($site in $sites)
{
$DisplaySite = $site.ToString()
write-host $DisplaySite.substring(11)
Remove-Spsite -identity $DisplaySite.substring(11) -confirm:$false
}

Schematics behind Power BI (beautiful)

Growth of Power BI, Apple watch can now provide insights at a glance

Final cert taken for Business Intelligence (MCSE)

Amongst many, one of the new exciting feature in SQL Server 2016 is the inclusion of Revolution Analytic popularly known as R or Advanced Analytic. For the seasoned SQL professionals like me R was the 18^th letter of the alphabet but not for long. The kid in me that likes new shiny things went digging to figure what this “R” was.

So what is R?

R is the sequel (pun intended) of a language called S developed by some AT&T dudes back in the 90’s which presumably “altered how people analyze, visualize and manipulated data.” R expands on this idea and it’s quite common in the data scientist field. It has very extensive and powerful graphic abilities tightly linked to analytical capabilities.

From SQL Server perspective, incorporation of R allows us to do advanced analysis without having to export the data out of SQL Server and it grows a new breed of data analysts to bring big data analytic to SQL Server.

Oohh and its open source too, so trend knowing attention may be limited.

Knowing that, combined with the love of data, we’ll try to become scientisty with the tools in our hands.

I was in the process of making all things pretty in SharePoint configuration and I got combated with the following error message The search application ‘f8cfe196-b555-4e72-bcbb-cf024cebbc24’ on server <> did not finish loading. View the event logs on the affected server for more information.

As a common practice, “google it” but what’s out there was delete the search service and recreate… that was not an option for me because I had just finished making things pretty so I took the challenge, and it lead me to different places with final destination being WSS_Admin_WPG group. So the fix was pretty simple

• Navigate to Local Users and Groups on your SharePoint Server
• Find the WSS_Admin_WPG group and ensure that you have your Search Service account and your farm account in it.
• Find the WSS_WPG group and do the same.
• Go back to your Search Service application and it’s magically working…

Happy SharePointing

Common occurrences when installing the .Net framework 3.5

Missing KB2966826; KB2966827; KB2966828

If the KB’s exist the article below is very helpful on the resolution

The article from http://www.askvg.com/fix-0x800f0906-and-0x800f081f-error-messages-while-installing-net-framework-3-5-in-windows-8/ details how you can fix the issue but I had a caveat in my encounter.

You look at the DISM error logs and you do see error as described in the article above.

DISM Package Manager: PID=1108 TID=1940 Failed finalizing changes. – CDISMPackageManager::Internal_Finalize(hr:0x800f081f)

But when you look in security updates you don’t see any of the KB’s that support .Net Framework 3.5

Usually the issue is referencing the sxs directory using a UNC path.

Solution

Copy the sxs directory to the local server, then run your DISM Powershell command as shown below.

 dism /online /enable-feature /featurename:NetFX3 /all /Source:E:\sxs /LimitAccess