April 2023
M	T	W	T	F	S	S
	1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30

Archive for the ‘Troubleshooting’ Category

How to fix SQL Patch Error: No valid sequence could be found for the set of updates.

Posted by blakhani on December 1, 2019

DISCLAIMER : THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.

Note: It is recommended to take backup of all the files before making any changes.

Recently we worked on a support case where we were having trouble installing SQL Server Patch. This was SQL Server 2016 SP2 and we were trying to install KB4524334 CU10 for SQL Server 2016 SP2. There was failure while upgrading PolyBase feature. (This can happen with other features also so same can be followed to fix them as well). The screenshot was something similar.

Let me start by explaining how do we troubleshoot installation/path failure issues. Whenever we troubleshoot SQL Server Setup/Patch related issue, we start looking at the log files to find exact error message which caused the failure. In case of patching, we first look at Summary.txt file which gives overall picture for all instances which were attempted to patch. Based on instance which had failure, we need to look at Summary file located under the instance name folder and dig further. Here is the article from online documentation:

View and Read SQL Server Setup Log Files

Lets go step by step in our scenario.

1. We looked at Summary_<MachineName>_<DateTimeStamp>.txt which is located under the folder having date-time stamp. I have highlighted important information which you need to review.

Overall summary:
Final result:                  The patch installer has failed to update the following instance: MSSQLSERVER. To determine the reason for failure, review the log files.
Exit code (Decimal):           -2068052368
Start time:                    2019-11-26 11:57:40
End time:                      2019-11-26 12:00:01
Requested action:              Patch

Instance MSSQLSERVER overall summary:
Final result:                  The patch installer has failed to update the shared features. To determine the reason for failure, review the log files.
Exit code (Decimal):           -2068052368
Start time:                    2019-11-26 11:58:49
End time:                      2019-11-26 11:59:51
Requested action:              Patch

2. We can see “Overall summary” and “Final result” which tells the instance which got failure, if any. In this case “MSSQLSERVER” which is a default instance. Below “Overall Summary” we can also see each instance overall summary.

3. Our failure was for instance MSSQLSERVER hence we need to go inside MSSQLSERVER folder and then look at Summary_<MachineName>_<DateTimeStamp>.txt file there. The information in this file would tell exact component which failed. Here are the various sections in that file and their meaning.

Overall summary:
<This section shows Action requested (like Install, Patch, Repair, EditionUpgarde etc.) and overall outcome of the setup. This would also tell if reboot is needed>

Machine Properties:
<Various Details about the machine like name, operating system, language etc.>

Product features discovered:
<This section would show details about SQL features which are already installed on this machine>

Package properties:
<This section would show information about the media which we are using to install/patch, its version, patch level etc.>

User Input Settings:
<This section would show action requested, Install, Patch, EditionUpgarde etc.>

Detailed results:
<This section would show information about each features being installed and whether it was Passed or Failed. In case of Failure, it would also so information about how to move forward>

Other sections were not relevant in this troubleshooting so ignoring them.

We know the sections and let us use the information available

Overall summary:
Final result:                  The patch installer has failed to update the shared features. To determine the reason for failure, review the log files.
Exit code (Decimal):           -2068052368
Start time:                    2019-11-26 11:58:49
End time:                      2019-11-26 11:59:51
Requested action:              Patch

Setup completed with required actions for features.
Troubleshooting information for those features:
Next step for Polybase:        Use the following information to resolve the error, and then try the setup process again.

Detailed results:
Feature:                       PolyBase Query Service for External Data
Status:                        Failed: see logs for details
Reason for failure:            An error occurred during the setup process of the feature.
Next Step:                     Use the following information to resolve the error, and then try the setup process again.
Component name:                SQL PolyBase
Component error code:          1648
Component log file:            C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log\20191126_115725\MSSQLSERVER\sql_polybase_core_inst_Cpu64_1.log
Error description:             No valid sequence could be found for the set of updates.

Feature:                       R Services (In-Database)
Status:                        Passed

Feature:                       Database Engine Services
Status:                        Passed

Feature:                       Integration Services
Status:                        Passed

Feature:                       Analysis Services
Status:                        Passed

Feature:                       SQL Browser
Status:                        Passed

Feature:                       SQL Writer
Status:                        Passed

Feature:                       LocalDB
Status:                        Passed

Feature:                       Setup Support Files
Status:                        Passed

From above information, we can easily understand that Polybase component had failure and other components got installed (shown as Passed). The message also points us to log which should have more details. When I opened sql_polybase_core_inst_Cpu64_1.log . The last few lines before failures are shown below.

MSI (s) (BC:90) [11:59:39:817]: Opening existing patch ‘C:\Windows\Installer\39187852.msp’.
MSI (s) (BC:90) [11:59:39:818]: Note: 1: 2205 2: 3: MsiPatchSequence
MSI (s) (BC:90) [11:59:39:818]: Opening existing patch ‘C:\Windows\Installer\6419f1dc.msp’.
MSI (s) (BC:90) [11:59:39:820]: Opening existing patch ‘C:\Windows\Installer\10894c.msp‘.
MSI (s) (BC:90) [11:59:39:838]: File will have security applied from OpCode.
MSI (s) (BC:90) [11:59:42:428]: Original patch ==> G:\d43ad554dc7e18fa425c07eed0\x64\setup\sql_polybase_core_inst.msp
MSI (s) (BC:90) [11:59:42:428]: Patch we’re running from ==> C:\Windows\Installer\3d61881.msp
MSI (s) (BC:90) [11:59:42:429]: SOFTWARE RESTRICTION POLICY: Verifying patch –> ‘G:\d43ad554dc7e18fa425c07eed0\x64\setup\sql_polybase_core_inst.msp’ against software restriction policy
MSI (s) (BC:90) [11:59:42:429]: SOFTWARE RESTRICTION POLICY: G:\d43ad554dc7e18fa425c07eed0\x64\setup\sql_polybase_core_inst.msp has a digital signature
MSI (s) (BC:90) [11:59:44:784]: SOFTWARE RESTRICTION POLICY: G:\d43ad554dc7e18fa425c07eed0\x64\setup\sql_polybase_core_inst.msp is permitted to run at the ‘unrestricted’ authorization level.
MSI (s) (BC:90) [11:59:44:784]: SequencePatches starts. Product code: {0877B0AD-CF2A-4079-BDC7-E1EE287F6B9A}, Product version: 13.0.1601.5, Upgrade code: {3577537A-A081-4B6F-8CA3-43315ED52B32}, Product language 1033
MSI (s) (BC:90) [11:59:44:784]: Note: 1: 2205 2: 3: MsiPatchSequence
MSI (s) (BC:90) [11:59:44:784]: Note: 1: 2203 2: RTM.1 3: -2147287038
MSI (s) (BC:90) [11:59:44:784]: PATCH SEQUENCER ERROR: failed to open RTM.1 transform in {6A8DFAB4-23AC-4C79-B509-DF1CC39A58CD} patch! (1: 2203 2: RTM.1 3: -2147287038 )
MSI (s) (BC:90) [11:59:44:784]: SequencePatches returns error 1648.
MSI (s) (BC:90) [11:59:44:785]: Product: SQL Server 2016 SQL Polybase – Update ‘{F0B7DA4A-7ACB-424E-A44E-11F27090AABA}’ could not be installed. Error code 1648. Additional information is available in the log file C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log\20191126_115725\MSSQLSERVER\sql_polybase_core_inst_Cpu64_1.log.

MSI (s) (BC:90) [11:59:44:786]: Windows Installer installed an update. Product Name: SQL Server 2016 SQL Polybase. Product Version: 13.0.1601.5. Product Language: 1033. Manufacturer: Microsoft Corporation. Update Name: {F0B7DA4A-7ACB-424E-A44E-11F27090AABA}. Installation success or error status: 1648.

MSI (s) (BC:90) [11:59:44:786]: Note: 1: 1708
MSI (s) (BC:90) [11:59:44:786]: Product: SQL Server 2016 SQL Polybase — Installation failed.

MSI (s) (BC:90) [11:59:44:787]: Windows Installer installed the product. Product Name: SQL Server 2016 SQL Polybase. Product Version: 13.0.1601.5. Product Language: 1033. Manufacturer: Microsoft Corporation. Installation success or error status: 1648.

Now the solution of the issue. If you look closely at the log file, it is referring few msp files, highlighted in blue color (39187852.msp, 6419f1dc.msp, 10894c.msp) . These files are cached version of sql_polybase_core_inst.msp files in C:\Windows\Installer folder. These files are for various previous patches applied on PolyBase feature. How would you find which MSP belongs to which patch i.e. which service pack, which CU, which KB? Fortunately there is a detailed KB available for this.

How to restore the missing Windows Installer cache files and resolve problems that occur during a SQL Server update

In the article go with “Procedure 1.b.: Use the FindSQLInstalls.vbs script” and run VBScript given. Once you run the script, open the output file and search for the msp files which are listed (highlighted by blue) in previous snippet. Here is are the relevant sections from VBScript output file. (Search for 39187852.msp)

Display Name:    Service Pack 2 for SQL Polybase (64-bit) (KB4052908)
KB Article URL: http://support.microsoft.com/?kbid=4052908
Install Date:    20190129
Uninstallable:   1
Patch Details:
HKEY_CLASSES_ROOT\Installer\Patches\4BAFD8A6CA3297C45B90FDC13CA985DC
PackageName:   sql_polybase_core_inst.msp
   Patch LastUsedSource: n;1;g:\c1d6c477712a2972547d\x64\setup\
Installer Cache File Path:     C:\Windows\Installer\39187852.msp
    Per SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Patches\4BAFD8A6CA3297C45B90FDC13CA985DC\LocalPackage

   Package exists in the Installer cache, no actions needed.
   Package will update automatically if needed assuming that
   the LastUsedSource exists.

   Should you get errors about C:\Windows\Installer\39187852.msp or g:\c1d6c477712a2972547d\x64\setup\sql_polybase_core_inst.msp then you
   may need to manually copy missing files, if file exists replace the problem file,
   Copy and paste the following command line into an administrative command prompt.

    Copy "g:\c1d6c477712a2972547d\x64\setup\sql_polybase_core_inst.msp" C:\Windows\Installer\39187852.msp

It clearly tells this MSP is from “Service Pack 2 for SQL Polybase (64-bit) (KB4052908)”. Lets have look at one more msp output. (6419f1dc.msp)

Display Name:    Hotfix 5337 for SQL Polybase (64-bit) (KB4495256)
KB Article URL: http://support.microsoft.com/?kbid=4495256
Install Date:    20190605
Uninstallable:   1
Patch Details:
HKEY_CLASSES_ROOT\Installer\Patches\8EE0896B83D2A1246AD903269C85495A
PackageName:   sql_polybase_core_inst.msp
   Patch LastUsedSource: n;1;G:\5645cedfa7be15cb95b0aedb1d6d05\x64\setup\
Installer Cache File Path:     C:\Windows\Installer\6419f1dc.msp
    Per SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Patches\8EE0896B83D2A1246AD903269C85495A\LocalPackage

   Package exists in the Installer cache, no actions needed.
   Package will update automatically if needed assuming that
   the LastUsedSource exists.

   Should you get errors about C:\Windows\Installer\6419f1dc.msp or G:\5645cedfa7be15cb95b0aedb1d6d05\x64\setup\sql_polybase_core_inst.msp then you
   may need to manually copy missing files, if file exists replace the problem file,
   Copy and paste the following command line into an administrative command prompt.

    Copy "G:\5645cedfa7be15cb95b0aedb1d6d05\x64\setup\sql_polybase_core_inst.msp" C:\Windows\Installer\6419f1dc.msp

It should be easy for you to identify that this one is from Hotfix 5337 for SQL Polybase (64-bit) (KB4495256)

Have a look at the file in C:\Windows\Installer folder and make a note of the size (39187852.msp, 6419f1dc.msp, 10894c.msp). Next download the KB which is mentioned in the “Display Name”. Once downloaded, extract in the path mentioned in last line, the copy command. Following above two examples, KB4052908 needs to be extracted in g:\c1d6c477712a2972547d and KB4495256 should be extracted in G:\5645cedfa7be15cb95b0aedb1d6d05. Note that this location is NOT fixed and you need to use the one in your script output. Now compare the files mentioned in last line (same copy command). Mostly you would find that at least one of the file would not be correct and that is the problem.

To fix the issue, we need to make sure that MSP file in C:\Windows\Installer is same as the one in the media.

Here is sql_polybase_core_inst_Cpu64_1.log file from another patch failure for SQL Server 2017. This one shows only one MSP instead of three which we have seen in previous logs. It all depends on which all patches have been installed previously. I would like you at analyze below file and then move further.

MSI (s) (D4:B4) [14:43:47:265]: Opening existing patch ‘C:\windows\Installer\5640be.msp‘.
MSI (s) (D4:B4) [14:43:47:265]: Note: 1: 2205 2: 3: MsiPatchSequence
MSI (s) (D4:B4) [14:43:47:265]: Note: 1: 1402 2: HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Policies\Explorer 3: 2
MSI (s) (D4:B4) [14:43:47:265]: File will have security applied from OpCode.
MSI (s) (D4:B4) [14:43:47:812]: Original patch ==> f:\05c89661f21d4215ddc41e36f60a\x64\setup\sql_polybase_core_inst.msp
MSI (s) (D4:B4) [14:43:47:812]: Patch we’re running from ==> C:\windows\Installer\611c45.msp
MSI (s) (D4:B4) [14:43:47:812]: SOFTWARE RESTRICTION POLICY: Verifying patch –> ‘f:\05c89661f21d4215ddc41e36f60a\x64\setup\sql_polybase_core_inst.msp’ against software restriction policy
MSI (s) (D4:B4) [14:43:47:812]: SOFTWARE RESTRICTION POLICY: f:\05c89661f21d4215ddc41e36f60a\x64\setup\sql_polybase_core_inst.msp has a digital signature
MSI (s) (D4:B4) [14:43:49:062]: SOFTWARE RESTRICTION POLICY: f:\05c89661f21d4215ddc41e36f60a\x64\setup\sql_polybase_core_inst.msp is permitted to run at the ‘unrestricted’ authorization level.
MSI (s) (D4:B4) [14:43:49:062]: SequencePatches starts. Product code: {41E37C92-1A68-4909-95A1-D9D59B0C0548}, Product version: 14.0.1000.169, Upgrade code: {3CCB3D90-D0AD-40F6-9C81-836CFE259EEE}, Product language 1033
MSI (s) (D4:B4) [14:43:49:062]: Note: 1: 2205 2: 3: MsiPatchSequence
MSI (s) (D4:B4) [14:43:49:062]: Note: 1: 2203 2: RTM.1 3: -2147287038
MSI (s) (D4:B4) [14:43:49:062]: PATCH SEQUENCER ERROR: failed to open RTM.1 transform in {76ACE427-ED4D-480E-BA64-8E09038A428B} patch! (1: 2203 2: RTM.1 3: -2147287038 )
MSI (s) (D4:B4) [14:43:49:062]: SequencePatches returns error 1648.
MSI (s) (D4:B4) [14:43:49:077]: Product: SQL Server 2017 SQL Polybase – Update ‘{533B2368-AC10-4078-A3AA-DA9F1B33E9FE}’ could not be installed. Error code 1648. Additional information is available in the log file C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\Log\20191129_143647\MSSQLSERVER\sql_polybase_core_inst_Cpu64_1.log.

MSI (s) (D4:B4) [14:43:49:077]: Windows Installer installed an update. Product Name: SQL Server 2017 SQL Polybase. Product Version: 14.0.1000.169. Product Language: 1033. Manufacturer: Microsoft Corporation. Update Name: {533B2368-AC10-4078-A3AA-DA9F1B33E9FE}. Installation success or error status: 1648.

MSI (s) (D4:B4) [14:43:49:077]: Note: 1: 1708
MSI (s) (D4:B4) [14:43:49:077]: Product: SQL Server 2017 SQL Polybase — Installation failed.

MSI (s) (D4:B4) [14:43:49:077]: Windows Installer installed the product. Product Name: SQL Server 2017 SQL Polybase. Product Version: 14.0.1000.169. Product Language: 1033. Manufacturer: Microsoft Corporation. Installation success or error status: 1648.

We were able to use VBScript to get the MSP from patch media and replaced it in C:\Windows\Installer. After that we ran patch again and it went fine.

As I have mentioned earlier, you need to be little careful while playing with the files. Always take a backup – Better safe than sorry!

Cheers,

Balmukund Lakhani

Twitter @blakhani

Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

Posted in patch failure, Setup, SQL Server, Troubleshooting | Tagged: No valid sequence could be found for the set of updates | 2 Comments »

Tips and Tricks: Useful Parameters of Get-ClusterLog

Posted by blakhani on May 20, 2016

Working with Root Cause Analysis (RCA) is also part of my work at Microsoft. In case of cluster failover RCA, it is very important to get cluster log. Sometimes there are situation where we want to generate cluster log for last few minutes for quicker analysis of live issues. This blog explains some common parameters which I used in my day-to-day troubleshooting.

I have 4 nodes cluster in my lab named SRV1, SRV2, SRV3, SRV4.

Default command – generates Cluster.log file on ALL nodes in C:\Windows\Cluster\Reports folder. File name would be Cluster.log

Get-ClusterLog

if we want the cluster log to be generated for specific node(s) then we can use –Node parameter. We can put comma separated node names as shown below.

Get-ClusterLog -Node SRV1, SRV3

You might know that the time shown in cluster log is UTC be default. Sometimes its difficult to translate UTC time to local time, specially for time-zones which has daylight saving. Luckily, cluster log can be generated in local time using parameter UseLocalTime . Here is the sample code.

Get-ClusterLog –UseLocalTime

Another useful parameter is to copy the files to specific location. This command would generate logs and also dump on specified location. in below example, I am dumping logs from all nodes to C:\Temp folder.

Get-ClusterLog –Destination “C:\Temp”

TimeSpan is another parameter which can generate cluster log for last number of minutes specified. By default it would generate Cluster.log for complete time. I find it useful when I repro’ed a problem and I want to look at cluster log for last 2 to 3 minutes. Here is the command to generate log for last 3 minutes.

Get-ClusterLog –TimeSpan 3

So, this is my favorite command after reproducing cluster issue on local node.

Get-ClusterLog -Node SRV1 -TimeSpan 2 -UseLocalTime -Destination C:\

Hopefully it would be useful.

Cheers,
Balmukund

Posted in Cluster, Tips and Tricks, Troubleshooting | Tagged: get-clusterlog, parameters, Tips and Tricks | 6 Comments »

SQL SERVER – SSMS Database Expand Hang – High waits on PREEMPTIVE_OS_LOOKUPACCOUNTSID

Posted by blakhani on April 7, 2015

Recently I have had a friend who reported below issues.

When I expand database, it takes a lot of time.
When I expand Jobs under SQL Server Agent node in SQL Server Management Studio, it freezes and finally it fails with error
“An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding”

The symptom on the server that we were working on was that from Management Studio, when trying to browse SQL agent job, the interface would hang. I have asked to capture Profiler trace to find out which query is taking time and what is the waits for those query which are stuck.

In profiler trace, and the query to capture currently running queries (refer this blog) I found that it was running sp_help_job stored procedure from MSDB database. This is the procedure to get high level details about all jobs in MSDB database using msdb.dbo.sysjobs_view. When we looked further, we found that SQL Server is running function dbo.SQLAGENT_SUSER_SNAME and got stuck at SELECT @ret = SUSER_SNAME(@user_sid) statement. The wait for the session is was PREEMPTIVE_OS_LOOKUPACCOUNTSID and wait time was increasing. This wait is related to the communication/validation from Active Directory.

When we debugged further, here is the chain of reaching to function.

sp_help_job
             >> sp_get_composite_job_info
                     >> Query having – owner = dbo.SQLAGENT_SUSER_SNAME(sjv.owner_sid)
                                >> SELECT @ret = SUSER_SNAME(@user_sid)

This is getting stuck at PREEMPTIVE_OS_LOOKUPACCOUNTSID. We was identified that the function is used to convert SIDs stored in SQL Server table to the name by making call to Active Directory. Now the challenge was to find why and also was to identify if its happening with particular logins or all login. The complexity here was that SQL Server stores SID in varbinary format not in the format which OS would understand.

Luckily, I have had a blog post having script to convert the varbinary to well known format. So I have used that to convert SIDs obtained from below query

select    owner_sid 
from    msdb.dbo.sysjobs_view
where    owner_sid <> 0x01

Once we have SID value in OS understandable format, I used PsGetSID tool from sysinternals to get Windows account name. While running that it was taking a long time and finally it failed with below error.

Error querying SID:

The trust relationship between the primary domain and the trusted domain failed

So, it was something to do with two domains trust which seems to be broken. I asked him to work with this Windows Domain Admin team and networking team to get the issue resolved.

Same issue might happen during database expand also as, database owner is a SID stored in sys.databases and that has to be converted to name.

Hope this helps.

Cheers,

Balmukund Lakhani

Twitter @blakhani

Posted in SQL Server Management Studio, SSMS, Troubleshooting | Tagged: PREEMPTIVE_OS_LOOKUPACCOUNTSID, Waittype | 4 Comments »

« Previous Entries

Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Blog Stats

Blogroll

Select GETDATE()

Subscribe

Follow Blog via Email

Date < getdate()

Select location from world

Meta