Figured out the problem last weekend. After trying to run a repair on my SQL Server 2012, it reported that the user Administrator doesn't have the proper privileges in order to proceed with the installation, so I had to do the following:
Provide Full Control through Group Policy Management Editor in Computer configurations -> Policies -> Windows Settings -> Security Settings -> Local Policies -> User Rights Assignment to:
- Back Up files and directories
- Debug programs
- Manage auditing and security log
More on this error here:
SQL Server installation fails
As you can see on this answer, have a look at which databases have service broker enabled (on both target and source servers)
you can use the following query to check which databases have the broker enabled:
--=====================================================================
-- checking what we have and where we point to
--=====================================================================
SELECT @@SERVERNAME
-- my_target_server
USE [master]
GO
SELECT [name]
,[is_broker_enabled]
,[service_broker_guid]
FROM [sys].[databases]
WHERE 1=1
AND is_broker_enabled = 1
ORDER BY NAME
GO
SELECT name,is_broker_enabled,service_broker_guid,
is_db_chaining_on, is_trustworthy_on FROM sys.databases
order by 2 desc, 3 desc, 1
I see you mention the security update and patching - well done for applying it!
Depending on the environment we sometimes use db chaining ownership on, and some times trustworthy on, instead of module signing which is the best way in most cases.
anyway, if you are not using the broker, please switch of all of these features for each database, i.e. disable the broker, and switch it off trustworthy and db ownership chain.
--one of my examples of enabling some of these features and renewing the broker id:
ALTER DATABASE ORCASTG SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE
ALTER DATABASE ORCASTG SET NEW_BROKER with rollback immediate
ALTER DATABASE ORCASTG SET trustworthy on with rollback immediate
You want something like these (provided you are not using any of these features):
USE MASTER
ALTER DATABASE [APCore] SET DISABLE_BROKER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [APCore] SET TRUSTWORTHY OFF WITH ROLLBACK IMMEDIATE
ALTER DATABASE [APCore] SET DB_CHAINING OFF WITH ROLLBACK IMMEDIATE
and keep monitoring the tempdb usage, I don't think you need neither to restart the service, nor do a failover.
In any case if you are on alwaysOn - Availability Group you may need to remove the database from the availability group before you can disable the broker.
Best Answer
With confirmation from Pix(from Chat) the correct way to remove cumulative update is to use
/RemovePatch
instead of using/uninstall
. The way to remove updates is documented in Installing Updates from Command Prompt.The script would be