I'm looking for ideas on how to determine a plausible root cause for DB_CHAINNG problems encountered when we recently performed a planned AG failover. We're using SQL Server 2019 Standard Edition with "Basic" AGs (meaning that every database is in it's own AG). In "Basic" AGs the Secondary Replicas are not readable, only one Primary and one Secondary is possible per-AG, and the commit-mode must be synchronous. Due to licencing (and cross-database queries), all AGs always have their Primary Replicas on the same Instance at any one time. The Windows cluster is two Windows Server 2019 nodes, with a File Share Witness on a third server. The purpose of our planned failover was to apply SQL Server 2019 CU10 (from CU8) to both servers, this went without issue. Patch SRV2, failover to SRV2, patch SRV1. The planned failover of all AGs was fine, no problems at all. However, the IIS application immediately started failing with permissions problems due to DB_CHAINING being lost. Altering the databases to enable DB_CHAINING addressed the issues. Patching the second server proceeded without incident. 'cross db ownership chaining' is (and has always been) disabled at the server-level on both servers. This is as per the MS recommendation:- I'm aware that restore or attach can affect this property, please see this article and the comments:- , and that DB_CHAINING can only be changed on the Primary Replica. The configuration of the servers, databases and AGs, etc. predates my time. I know DB_CHAINING can compromise security, it wasn't my choice, it's an old application. To pre-empt some potential suggestions, this was not an issue with Login passwords, SIDs, Server Role membership, database Users, Roles or permissions (anything User/Role/permission related is stored in the databases and would not have been affected by an AG failover). This was not an orphaned database User issue. This was not a problem with client connections to a 'stale' Listener address/IP or similar. Both servers are on the same subnet and all clients reconnected via their Listener network names without any issues. The problem was when the clients successfully connected, they could not see tables, or execute procedures, etc. As soon as DB_CHAINING was enabled, everything worked as expected - no other changes were needed. I've confirmed on a test environment that failing over an AG has no effect on DB_CHAINING, it neither enables it nor disables it, on either Replica. This implies that the option was not enabled before I failed-over. I would normally accept this and ensure that checking, and if necessary, enabling this option is added to the failover plan for the future, however about six weeks ago, we experienced an unplanned automatic failover caused by a switch reboot at the hosting company. On this occasion, all AGs failed-over and the application continued to function without any issues or intervention. I've reviewed the ERRORLOG files and I have a complete history of when DB_CHAINING was enabled on which databases since the two servers were installed, I can see that no-one re-enabled DB_CHAINING after the unplanned failover. Furthermore, no-one has ever disabled DB_CHAINING on any database on either server. Has anyone experienced a SQL Server update removing DB_CHAINING from User databases? I've reviewed the patching (setup) logs and can find no mention of anything like this being recorded. The closest I can find is someone reporting chaining issues sometime after applying SQL Server 2016 SP2:- . But the answer suggests changing the application behaviour to workaround it, which I'm not able to do. is_db_chaining_on currently returns 1 for all (necessary) databases on both servers (which is good evidence that patching doesn't alter this option, otherwise databases on the last patched currently passive might report 0). I cannot explain how an unplanned failover from SRV2 to SRV1 was OK, but a planned failback six-weeks later to SRV2 from SRV1 had DB_CHAINING issues (when there is no evidence in the logs that anyone/anything disabled the option). It seems like either the update silently removed the property or the property was not set correctly on SRV2 before the planned failover - in which case how was the application functioning (for months) before the unplanned failover six-weeks ago? Any ideas where I can look, or what I can test for, to identify a plausible root cause would be appreciated. **EDIT:** thanks for your response AMTwo, but no, there have been no backup/restore or detach/attach events. The databases were created from native backups on both SRV1 and SRV2 in early Dec-2020, since then, there have been no restores of the any databases in question. I'm the only DBA and there's no-one else who could/would have done this, msdb.dbo.restore_history is empty. We also have Redgate SQL Monitor coverage over this pair of servers which would have generated a lot of alerts if anything like a restore was attempted which broke the AG replication.

I have a 2017 SQL Server, and I have tried applying the latest update to the instance (CU31 GDR, 14.0.3485.1) However, when running the update wizard, it shows that the instance is greyed out and "not applicable" for the update, even though it is running an older version. There are a few other SQL Servers that took the update just fine, while two others would not take it. The other two are at builds 14.0.3480.1 and 14.0.3475.1 (also CU31 GDR). The other servers/instances that took the 14.0.3485.1 builds were from the same previous builds. Anyone have any idea why this might be happening, and any suggestions on how to fix this so I may apply the updates?

I'm trying to determine if the CU+GDR updates installs the CU in addition to the GDR. I think I understand the differences between CUs and the GDR security fixes. And the different paths you can go down, CU or GDR. However, for SQL Server 2019 this site shows SQL Server 2019 GDR (15.0.2125.1 - October 2024) CU28 for 2019 (15.0.4385.2 - August 2024) CU28 + GDR (15.0.4395.2 - October 2024) The KB for 15.0.4395.2 shows KB5046060 - Description of the security update for SQL Server 2019 CU28: October 8, 2024 "To apply this update, you must have SQL Server 2019 or any SQL Server 2019 CU release through this SQL Server 2019 CU28 GDR installed." So it can be installed on top of any previous CUs. Also, this does not list the fixes in CU28, only the GDR fixes. After installing 15.0.4390.2 my version shows Microsoft SQL Server 2019 (RTM-CU28-GDR) (KB5042749) - 15.0.4390.2 (different to the 15.0.4395.2 updates, but this is the September CU28+GDR KB5042749 - Description of the security update for SQL Server 2019 CU28: September 10, 2024) But it does indicate that CU28 has been installed. But, CU28 has never been installed individually. Previous CUs have been installed. And if I try and install CU28 after CU28+GDR it says no updates are required. So my question is 'Does CU28+GDR update install CU 28 as well as the GDR security update?' If it doesn't, the version information is very misleading. And do I have to uninstall CU28+GDR to install CU28? Thanks for your help.

What sort of changes are made to System tables (master / model / msdb) by Cumulative Updates? For context, I have SQL servers that have AlwaysOn Groups, and am looking at a way to roll back updates if it causes issues. All data and log files are on dedicated volumes, and SQL is installed on the OS drive. I have been testing a rollback process where I take a snapshot of the OS volume before installing the CU, and then update. After updating, I am able to restore the OS snapshot image back, and SQL shows the pre-update patch version. It looks like everything is working as expected after the rollback (i.e. the program files / DLLs / registry are all reverted), and not seeing any errors, but not sure if I am missing something or there is something that i should be aware of that might break something or be a risk. Thanks in advance. SQL Server version 2017 (im testing this is an old environment i had set up somewhere), and have tested this on a CU22 -> CU31 update + rollback.

SQL Server is still working smoothly but I noticed that I have an alert on the Windows Update that says: Servicing Update for SQL Server 2019 RTM GDR (KB4517790) Install error - 0x80070643 I tried to reboot and update sever times but it's still not installing. So I downloaded the KB4517790 but when I try to install the service stops with the error: There are no SQL Server instances or shared features that can be updated on this computer. Strange. I click back twice and I check the **View detailed report** and everything is fine: Microsoft SQL Server 2019 - System Configuration Check Report | Rule Name | | Rule Description | Result | Message/Corrective Action | |-------------------------------------------------------------------------------------------------|--------------------------------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|----------------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | PatchGlobalRules: SQL Server 2019 Setup configuration checks for rules group 'PatchGlobalRules' | | | | | | # | Engine_ResourceDLLUpdateRestartCheck | Checks whether shared resource DLLs will be updated, causing restarts for clustered SQL Server instances active on this node. | Not applicable | This rule does not apply to your system configuration. | | # | AclPermissionsFacet | Checks if the SQL Server registry keys are consistent. | Passed | SQL Server registry keys are consistent and can support SQL Server installation or upgrade. | | # | Cluster_IsLocalNodeGroupOwner | Checks if the local computer is an owner of an online cluster group for a failover cluster instance that contains the SQL Server service, Analysis Services service or a generic service. | Passed | The local computer is not an owner of an online cluster group for a failover cluster instance that contains the SQL Server service, Analysis Services service or a generic service. The SQL Server instances do not need to be taken offline. | | # | Cluster_IsOnlineIfClustered | Verifies that the computer is not clustered or that the computer is clustered and the cluster service is online. | Passed | The computer is either not clustered or the cluster is up and online. | | # | RebootRequiredCheck | Checks if a pending computer restart is required. A pending restart can cause Setup to fail. | Passed | The computer does not require a restart. | | # | ThreadHasAdminPrivilegeCheck | Checks whether the account running SQL Server Setup has administrator rights on the computer. | Passed | The account running SQL Server Setup has administrator rights on the computer. | | # | WmiServiceStateCheck | Checks whether the WMI service is started and running on the computer. | Passed | The Windows Management Instrumentation (WMI) service is running. | So I found a fantastic command in PowerShell that allows me to recover all Windows Update logs. I type Get-WindowsUpdateLog and a file called WindowsUpdate.log is now on my Desktop. When I open it I find: 2022/07/12 11:35:06.5502777 14516 14848 ComApi Byte length of the input buffer for deserialization: 36024 2022/07/12 11:35:06.5518950 14516 14848 ComApi Deserialized installable update Servicing Update for SQL Server 2019 RTM GDR (KB4517790), UpdateID = {CA10020A-BECD-4535-B06D-E30E92F01AD6.200} 2022/07/12 11:35:06.5521232 14516 14848 Test AUTest.cab validation: Test keys are not allowed 2022/07/12 11:35:06.6192658 14516 8864 ComApi Serializing CUpdate CA10020A-BECD-4535-B06D-E30E92F01AD6.200, Last modified time 2022-07-12T09:25:10Z 2022/07/12 11:35:06.6198416 14516 8864 ComApi Update serialization complete. BSTR byte length = 36024 2022/07/12 11:35:21.4414511 21868 24160 Agent Earliest future timer found: 2022/07/12 11:35:21.4414806 21868 24160 Agent Timer: 29A863E7-8609-4D1E-B7CD-5668F857F1DB, Expires 2022-07-12 12:48:15, not idle-only, not network-only 2022/07/12 11:35:22.4513120 21868 25304 Shared UninitializeSUS 2022/07/12 11:35:22.4513184 21868 25304 Misc CSusClientGlobal::DoServicePreShutdown 2022/07/12 11:35:22.4517025 21868 25304 IdleTimer Idle timer disabled in preparation for service shutdown 2022/07/12 11:35:22.4517139 21868 25304 Misc WUTaskManager uninit 2022/07/12 11:35:22.4517196 21868 25304 Agent Earliest future timer found: 2022/07/12 11:35:22.4517386 21868 25304 Agent Timer: 7971F918-A847-4430-9279-4A52D1EFE18D, Expires 2022-07-12 10:37:14, not idle-only, network-only 2022/07/12 11:35:22.4683034 21868 25304 Misc CreateSessionStateChangeTrigger, TYPE:2, Enable:No 2022/07/12 11:35:22.4683300 21868 25304 Misc CreateSessionStateChangeTrigger, TYPE:4, Enable:No 2022/07/12 11:35:22.4916515 21868 25304 Misc Agent uninit 2022/07/12 11:35:22.4916711 21868 25304 Agent *FAILED* [8007139F] file = onecore\enduser\windowsupdate\client\engine\handler\cbs\lib\uhcbs.cpp, line = 4848 2022/07/12 11:35:22.4916832 21868 25304 DownloadManager Exiting CDownloadHandlerCallbackHandler::Uninit within timeout bounds 2022/07/12 11:35:22.4925943 21868 18656 Agent Deployment session (host process 21868) signalled to exit... 2022/07/12 11:35:22.4926590 21868 18656 Deployment Deployment job Id 3A2949A0-E56C-4FA1-AB01-8282F1D469DA : Destructor invoked for deployment job. 2022/07/12 11:35:22.4926639 21868 18656 Deployment Deployment job Id 3A2949A0-E56C-4FA1-AB01-8282F1D469DA : WaitForEnd invoked for deployment job. Beginning the wait now! 2022/07/12 11:35:22.4926702 21868 18656 Deployment Deployment job Id 3A2949A0-E56C-4FA1-AB01-8282F1D469DA : WaitForEnd for deployment job completed. Job state = Complete 2022/07/12 11:35:22.4942066 21868 25304 Misc Reporter uninit 2022/07/12 11:35:22.4943410 21868 25304 Misc network cost manager uninit 2022/07/12 11:35:22.4943651 21868 25304 Misc Eventer uninit 2022/07/12 11:35:23.5095262 21868 25304 Misc ServiceManager uninit 2022/07/12 11:35:23.5098089 21868 25304 Misc PersistentTimeoutScheduler uninit 2022/07/12 11:35:23.5098136 21868 25304 Misc datastore uninit 2022/07/12 11:35:23.5630608 21868 25304 Misc setting cache uninit 2022/07/12 11:35:23.5630619 21868 25304 Misc security checker uninit 2022/07/12 11:35:23.5630660 21868 25304 Misc Test Hook uninit 2022/07/12 11:35:23.5630667 21868 25304 Misc IdleTimer uninit 2022/07/12 11:35:23.5635012 21868 25304 Shared * END * Service exit Exit code = 0x240001 I see something is falling but I don't know how to move. I have already tried to SQL Server Repair and it didn't help. I'm on the latest version: Microsoft SQL Server 2019 (RTM-CU16-GDR) (KB5014353) - 15.0.4236.7 (X64) May 29 2022 15:55:47 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 22000: ) (Hypervisor)

I am in the process of upgrading our SQL 2017 servers to the current CU. I have ran across two systems that won't allow me to apply the update. In the SQL installer, I see below text when I click on the SQL Instance name. >The version of SQL Server instance MSSQLSERVER does not match the version expected by the SQL Server update. The installed SQL Server product version is 14.0.3381.3, and the expected SQL Server version is 14.0.1000.169." I am unsure where the installer pulls the version info from. I have compared the registry against a couple of other systems and nothing is standing out as being incorrect, so not sure where it is pulling the 14.0.1000.169 value for the "expected sql version". I tried removing the previous CU so I could reapply it, but it didn't appear that it removed fully as it was still in the add/remove programs screen. Everything I have found online points to older versions where people were trying to install CUs on top of the wrong Service Packs, which doesn't apply in this case since SQL 2017 doesn't have service packs. Any ideas would be greatly appreciated.

I'm relatively new to CU's and SQL server. We have a few SQL Servers with dev, qa and prod instances (SQL 2017) on them. This is probably not the best idea, but that's what we have right now. Currently, we routinely have the GDR updates applied by our network folks. I have been asked to start taking over the SQL updates and switch us over to the "CU train" to get and keep us more up to date for the more non-security type upgrades. 1. My plan is to install the CU's first in the dev and qa instances to test and monitor for any issues before updating production. Because we have prod instances living on the same box as dev and qa, would installing updates to dev and/or qa instances possibly have any effect on the production instance? I'm wondering if any common components to all instances would get modified and cause issues. 2. Should the server get rebooted after applying a CU? My preference would be to reboot the server after applying a CU. But that would mean I would be bouncing the production instance for dev/qa updates and wondering if the reboot is actually needed, or if I would be good with the SQL service being restarted? Thanks for any feedback on this. I really would like to have our prod instances live in their own house. But, like I said, this is what I have been handed for the time being and just wanted to see what more experienced folks had to say.

Need to update primary from 2019-CU14 to 2019 CU15 and a few hours/days later update secondary to CU 15 once the primary is deemed stable. Can you safely log ship between primary and secondary servers on different cumulative update versions (CU-15 to CU-14 in this case)?

New instance of SQL Server 2017. I chose to install up to CU25 during the initial installation. Been using the database for a few months, but it seems to have a memory leak when using memory-optimized tables which causes it to crash overnight sometimes. I have another instance with the same jobs and hardware on CU20 and it has never had this problem. Leads me to believe this is a CU25 issue. I'd like to downgrade to CU20 to see if this fixes the issue. What is the best way of doing this?

So when updating a two node cluster, our procedure is this: 1. Backup all databases. 2. Failover all SQL cluster roles to Node A. 3. Install the patches on Node B (passive). 4. Restart Node B. 5. Failover all SQL cluster roles to Node B. 6. Install the patches on Node A (passive). 7. Restart Node A My understanding is that in step 5, the updated node, will perform some updates before taking on incoming connections, as can be seen in the log. What happens if another failover occurs before Node A is updated? If, for example, I have updated to a higher CU on Node B. Will the failover fail or just fail over and start itself on a lower CU level?

Microsoft just released this security update CVE-2021-1636 for a Security Vulnerability. Not much information is available at the moment. Any input on the following points will be highly appreciated - How critical is this update. Do we need to apply this update as soon as possible? - If the SQL server is not available over internet, will it still be vulnerable? We generally apply updates on Test environments and wait for a month before moving to production if no problem is found. But since we have limited information available for this update, is someone planning to install this update on production servers soon considering it could be critical? TIA.

I am using Postgis to: - calculate the cumulative distance in metres along the line - store the cumulative distance in "m" I have a table with points on this line and each row holds lat and lon coordinates. The "m" value is 0 for all rows. The following code nicely gets me the distance between the first point and the next.

SELECT a.geom
FLOOR(
ST_Distance(ST_Transform(a.geom, 3857), ST_Transform(b.geom, 3857)) * cosd(42.3521))
AS dist
FROM line a
LEFT JOIN line b
ON a.id = b.id + 1

however, it does not add up to a total nor does it update the "m" value yet. I tried a set of permutations of the following, but none of them store a cumulative sum of the previous row's value for "m" plus the calculation of the distance between this point and the previous.

WITH next AS (
	SELECT
	ST_Distance(ST_Transform(a.geom, 3857), ST_Transform(b.geom, 3857)) * cosd(42.3521)
	AS dist,
	a.id AS rowid,
	FROM line a
	JOIN line b
	ON a.id = b.id + 1
)
UPDATE line a
SET m = FLOOR(next.m + next.dist)
FROM next
WHERE a.id = next.rowid
RETURNING a.m, next.dist;

or

update line a
    set m = FLOOR(a.m + prev.dist) 
    from (select
		  	l.*,
		  	ST_Distance(ST_Transform(
				lag(geom) over (order by l.id asc)
			, 3857), ST_Transform(geom, 3857)) * cosd(42.3521) as dist
			from line l
		  ) AS prev
	where prev.id = a.id - 1
	returning prev.m, prev.dist, a.m

Any ideas?

After a reboot and latest CU for sqlserver 2017 I get this annoying message (the xxxxx was a number) > SSBDT: Dialog timer delete during registration (did: > xxxxxxxxxxxxxxxxxxxxxxxxxxxx:Initiator, OpType: CloseDialog > OpResult: Null) I've read that it isn't to worry : true? This message is filling my sql server logs every second: how can I make it stop?

According to this Microsoft link , SQL Server 12.0.6372.1 requires any system with at least SQL 2014 SP3, so my predecessor installed it over SP3. Now the version is "2014 (SP3-CU-GDR) (KB4535288) - 12.0.6372.1". Since that security patch only fixes a specific problem with Reporting Services, and is smaller than the CU4 package, ISTM that I should also install the CU4 patch. But how? Do I first uninstall KB4535288, then install KB4500181 (CU4) and then reinstall KB4535288, or can I just slap KB4500181 on top of KB4535288 (which seems like it would overwrite the KB4535288 files.

We were running on SQL Server 2016 Standard Edition 2016 SP2-CU4 on our ALWAYS-ON HA cluster. Due to some recent issues one of our nodes got corrupted and we had to replace it with a new node. Since this was a fresh image from AWS Marketplace it came with SP2-CU13 (latest) out of the box. The client wants this to be rolled-back to the original SP2-CU4 (no plans/requirement of upgrading to CU13). As a primary step I tried to install the SP2-CU4 KB article on the instance but that does not work with the attached error message. (No SQL Server instance or feature can be updated on this computer) Q1 - How can SP2-CU4 installed on the server. Q2 - What is the impact if the cluster runs on different CU's.

So this week I was patching our SQL Servers that host our availability groups. Never had an issue with them in the past, fairly standard process. I have two servers (call them SQL1 and SQL2) in the cluster and one Availability Group (AG1) uses SQL1 as the primary, and three Availability Groups (AG2-4) uses SQL2 as its primary. They were running v15.0.4013.40, so Server 2019 and this week I had to install CU3 and CU4. My process was: 1. I moved AG1 to use SQL2 as the primary 2. Proceed to patch SQL1 3. Switch all availability groups to async commit 4. Reboot SQL1 5. Return all availability groups to sync commit 6. Return AG1 to use SQL1 as the primary In step 2 I patched both CU3 and then CU4 successively. Then, when trying to switch all AGs to sync commit, the databases threw errors in the Availability Group Dashboard and they wouldn't allows access to their databases. I couldn't see any logged reason to why it refused to do this. It was only resolved when I then patched SQL2 with both CUs, and it again allowed to sync commit. My question is did anyone else have this issue? Was this likely due to me trying to be efficient and patch two CUs in the same interval without doing it one by one? Just trying to work out how I don't do this again as it caused me quite the headache

After installing SQL Server 2019 CU2, SQL Server Agent starts consuming too much CPU resulting into skip job schedules. Although the agent service is running but it looks like hang and didn't process / kick off any jobs. Once I re-start the Agent service it starts working again (cpu = 0), but then again after some time 1-2 hours CPU intake in high and jobs do not run. Is anybody else facing same issue with SQL Server 2019 CU2? Any help would be much appreciated. Thanks,

Going to set up a new database instance from scratch. Debating between SQL Server 2016 and 2019. I've never installed a service pack or cumulative update on previous instances, mostly because everything was working fine. Since this will be a clean install, should I patch right away before it goes into production? Is there any downside?

We have a SQL Server: >Microsoft SQL Server 2017 (RTM-CU14-GDR) (KB4494352) - 14.0.3103.1 (X64) Mar 22 2019 22:33:11 Enterprise Edition (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor) Tried to install "Cumulative Update Package 18 for SQL Server 2017 - KB4527377" https://www.microsoft.com/en-us/download/details.aspx?id=56128 Setup was doing something for 15 min, installing and updating files, stopping / starting services, it looked like its going to succeed, but in the end it showed this: > Action required: > >Use the following information to resolve the error, and then try the setup process again. > >Feature failure reason: > >An error occurred during the setup process of the feature. > >Error details: > >Error installing SQL Server Database Engine Services Instance Features The User Log directory in the registry is not valid. Verify DefaultLog key under the instance hive points to a valid directory. Error code: 0x851A0044 Visit https://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=14.0.3257.3&EvtType=0xD8FB5EBA%400x97A656BB%401306%4068&EvtType=0xD8FB5EBA%400x97A656BB%401306%4068 to get help on troubleshooting. What's preventing CU18 from installing ?