SQL 2016 – NU/NC Index Missing From sys.partitions

Is there a reason that a non-unique, non-clustered index would have 0 corresponding records in the sys.partitions view? There is a CI on the same table and there is a record for it in the view. My understanding is that any NCI should have at least one record in sys.partitions. Thanks.

Can no longer connect to localdb after updating from Sql Server 2016 to 2019

I need to restore a backup for an assignment, but SMSS said I was running v13 while the backup was made in v15. So I download and installed SQL Server Express 2019 and uninstalled the older version.

However, SMSS can’t connect anymore to (localdb)MSSQLLocalDB.

I read somewhere to add a firewall rule for sqlservr.exe. When I try to run that file manually, it says the SQL Server installation is corrupt.

So I ran the Repair from SQL Server Installation Center, everything was okay in the output log but still no luck.
I’m pretty clueless, any help is appreciated.

Slow query SQL Server 2016

I have a query that is taking more than 7 hours and doesn’t finish, as it doesn’t finish I can’t generate the actual execution plan.

Here’s brentozar’s Paste The Plan

It has a temporary table, and then a SELECT that inserts into the Temporary table.

 CREATE TABLE #Result (
     SEQ_RESULT              INT IDENTITY(1,1),
     NUM_PROCES              CHAR (20)           NOT NULL,
     DES_SISTEM_PROCES       VARCHAR (10)        NOT NULL,
     IDE_GRAU                CHAR (1)            NULL,
     COD_CLASSE_CNJ          INT                 NULL,
     COD_ORGAO_JULGAD_CNJ    INT                 NULL,
     COD_ASSUNT_CNJ          INT                 NULL,
     NUM_CPF_MAGIST          CHAR (11)           NULL,
     IDE_TEMPO               INT                 NULL,
     COD_MATERI              INT                 NULL,
     COD_EVENTO_CNJ          INT                 NULL,
     COD_ORGAO_GABINE_CNJ    INT                 NULL,
     SEQ_DESCRI_GABINE       INT                 NULL,
     SEQ_PROCES_FASE         INT                 NULL,
     DAT_HORA_FASE           DATETIME            NULL,
     DAT_HORA_FASE_ORGAO_PRINCI          DATETIME            NULL,
     COD_EVENTO_COMPLE_CNJ   INT                 NULL,
     COD_RESULT_CNJ          INT                 NULL,
     DAT_AUX_1               DATETIME            NULL,
     DAT_AUX_2               DATETIME            NULL,
     SEQ_PROCES              INT                 NULL, 
     SEQ_PROCES_INDICA       INT                 NULL,
     SEQ_FASE                BIGINT              NULL,
    EVENTO_0001 BIGINT NULL,EVENTO_0002 BIGINT NULL,
    EVENTO_0001_LOCAL BIGINT NULL,EVENTO_0002_LOCAL BIGINT NULL,
     EVENTO_PRINCIPAL        INT NULL,
     EVENTO_ORGAO_PRINCIPAL  INT NULL,
     EVENTO_FASE_AUX1        INT NULL,
     EVENTO_FASE_AUX2        INT NULL
);





INSERT INTO #result
        (
                    num_proces,
                    des_sistem_proces,
                    ide_grau,
                    cod_classe_cnj,
                    cod_orgao_julgad_cnj,
                    cod_assunt_cnj,
                    num_cpf_magist,
                    ide_tempo,
                    cod_materi,
                    cod_evento_cnj,
                    cod_orgao_gabine_cnj,
                    seq_descri_gabine,
                    seq_proces_fase,
                    dat_hora_fase,
                    dat_hora_fase_orgao_princi,
                    cod_evento_comple_cnj,
                    cod_result_cnj,
                    dat_aux_1,
                    dat_aux_2,
                    evento_0001,
                    evento_0002,
                    evento_principal,
                    evento_orgao_principal,
                    evento_fase_aux1,
                    evento_fase_aux2
        )
SELECT num_proces,
   des_sistem_proces,
   ide_grau,
   cod_classe_cnj,
   cod_orgao_julgad_cnj,
   cod_assunto,
   num_cpf_magist,
   ide_tempo,
   cod_materi,
   cod_evento_cnj,
   cod_orgao_gabine_cnj,
   cod_descri_gabine,
   seq_proces_fase,
   dat_hora_fase,
   dat_hora_fase_orgao_principal,
   cod_evento_comple_cnj,
   cod_result_cnj,
   dat_aux_1,
   dat_aux_2,
   evento_0001,
   evento_0002,
   tabela_principal,
   tabela_orgao_principal,
   tabela_auxiliar_1,
   tabela_auxiliar_2
FROM   (
                            
    SELECT
        NUM_PROCES,
        (   SELECT
            TOP 1 DES_SISTEM_PROCES   
        FROM
            T_FASES_CALCULO  
        WHERE
            IDE_GRAU = BASE.IDE_GRAU 
            AND NUM_PROCES = BASE.NUM_PROCES 
            AND DAT_HORA_FASE <= '2021-01-31 23:59:59.997'  
        ORDER BY
            SEQ  ) AS 'DES_SISTEM_PROCES',
        IDE_GRAU,
        SEQ_PROCES,
        SEQ_PROCES_GRAU,
        COD_CLASSE_CNJ,
        COD_ORGAO_JULGAD_CNJ,
        (SELECT
            TOP 1 HA.COD_ASSUNT_CNJ 
        FROM
            HIST_PROCESSO_ASSUNTO HA 
        WHERE
            HA.IDE_ASSUNT_PRINCI = 'S' 
            AND HA.NUM_PROCES = BASE.NUM_PROCES 
            AND HA.DES_SISTEM_PROCES = BASE.DES_SISTEM_PROCES 
            AND HA.TIP_OPERA = 'E' 
            AND HA.DAT_ASSUNT_MOVIME <= BASE.DAT_HORA_FASE 
        ORDER BY
            HA.DAT_ASSUNT_MOVIME DESC) AS COD_ASSUNTO,
        NUM_CPF_MAGIST,
        1225 AS IDE_TEMPO,
        COD_MATERI,
        COD_EVENTO_CNJ,
        COD_ORGAO_GABINE_CNJ,
        (SELECT
            TOP 1 SEQ_DESCRI_GABINE 
        FROM
            D_DESCRICAO_GABINETE GAB 
        WHERE
            GAB.COD_GABINE_CNJ = BASE.COD_ORGAO_GABINE_CNJ 
            AND (
                (
                    BASE.DAT_HORA_FASE >= '2021-01-01 00:00:00.000' 
                    AND GAB.DAT_INICIO <= BASE.DAT_HORA_FASE
                ) 
                OR (
                    BASE.DAT_HORA_FASE < '2021-01-01 00:00:00.000' 
                    AND GAB.DAT_INICIO <= '2021-01-31 23:59:59.997'
                )
            ) 
        ORDER BY
            DAT_INICIO DESC) AS COD_DESCRI_GABINE,
        SEQ_PROCES_FASE,
        DAT_HORA_FASE,
        DAT_HORA_FASE_ORGAO_PRINCIPAL,
        COD_EVENTO_COMPLE_CNJ,
        COD_RESULT_CNJ,
        DAT_AUX_1,
        DAT_AUX_2,
        EVENTO_0001,
        EVENTO_0002,
        TABELA_PRINCIPAL,
        TABELA_ORGAO_PRINCIPAL,
        TABELA_AUXILIAR_1,
        TABELA_AUXILIAR_2 
    FROM
        ( SELECT
            DISTINCT AUTO_INDICADOR_0647_EVENTO_0001.NUM_PROCES,
            AUTO_INDICADOR_0647_EVENTO_0001.DES_SISTEM_PROCES,
            AUTO_INDICADOR_0647_EVENTO_0001.IDE_GRAU,
            AUTO_INDICADOR_0647_EVENTO_0001.SEQ_PROCES,
            AUTO_INDICADOR_0647_EVENTO_0001.SEQ_PROCES_GRAU,
            AUTO_INDICADOR_0647_EVENTO_0001.DAT_HORA_FASE as 'DAT_HORA_FASE',
            AUTO_INDICADOR_0647_EVENTO_0001.DAT_HORA_FASE AS 'DAT_HORA_FASE_ORGAO_PRINCIPAL',
            AUTO_INDICADOR_0647_EVENTO_0001.COD_EVENTO_CNJ,
            AUTO_INDICADOR_0647_EVENTO_0001.COD_ORGAO_JULGAD_CNJ,
            AUTO_INDICADOR_0647_EVENTO_0001.COD_ORGAO_GABINE_CNJ,
            AUTO_INDICADOR_0647_EVENTO_0001.NUM_CPF_MAGIST,
            AUTO_INDICADOR_0647_EVENTO_0001.COD_EVENTO_COMPLE_CNJ,
            AUTO_INDICADOR_0647_EVENTO_0001.COD_RESULT_CNJ,
            AUTO_INDICADOR_0647_EVENTO_0001.SEQ_PROCES_FASE,
            AUTO_INDICADOR_0647_EVENTO_0001.COD_MATERI,
            AUTO_INDICADOR_0647_EVENTO_0001.COD_CLASSE_CNJ,
            AUTO_INDICADOR_0647_EVENTO_0001.SEQ,
            AUTO_INDICADOR_0647_EVENTO_0001.DAT_CARGA,
            NULL AS 'DAT_AUX_1',
            NULL AS 'DAT_AUX_2',
            AUTO_INDICADOR_0647_EVENTO_0001.SEQ_PROCES_FASE AS 'EVENTO_0001',
            AUTO_INDICADOR_0647_EVENTO_0002.SEQ_PROCES_FASE AS 'EVENTO_0002',
            1 as 'TABELA_PRINCIPAL',
            1 as 'TABELA_ORGAO_PRINCIPAL',
            NULL as 'TABELA_AUXILIAR_1',
            NULL as 'TABELA_AUXILIAR_2',
            ROW_NUMBER() OVER (PARTITION 
        BY
            AUTO_INDICADOR_0647_EVENTO_0001.SEQ_PROCES_GRAU 
        ORDER BY
            AUTO_INDICADOR_0647_EVENTO_0001.SEQ) AS RN 
        FROM
            (SELECT
                REL.* 
            FROM
                ( SELECT
                    * 
                FROM
                    ( SELECT
                        F.COD_CLASSE_CNJ,
                        F.COD_EVENTO_COMPLE_CNJ,
                        F.COD_EVENTO_CNJ,
                        F.COD_MATERI,
                        F.COD_ORGAO_GABINE_CNJ,
                        F.COD_ORGAO_JULGAD_CNJ,
                        F.SEQ_RESULT_COMPLE AS COD_RESULT_CNJ,
                        F.NUM_CPF_MAGIST,
                        REL.DAT_FASE AS DAT_HORA_FASE,
                        REL.SEQ_PROCES_FASE,
                        REL.NUM_REGIST_JUDICI AS NUM_PROCES,
                        REL.IDE_SISTEM AS DES_SISTEM_PROCES,
                        REL.DAT_AUXILI_1 AS DAT_AUX_1,
                        REL.DAT_AUXILI_2 AS DAT_AUX_2,
                        NUM.SEQ_PROCES,
                        NUM.SEQ_PROCES_GRAU,
                        T.SEQ,
                        T.IDE_GRAU,
                        T.DAT_CARGA 
                    FROM
                        F_JUSTICA_NUMERO F WITH (NOLOCK) 
                    JOIN
                        REL_FATO_PROCESSO REL WITH (NOLOCK) 
                            ON F.SEQ_JUSTIC_NUMERO = REL.SEQ_JUSTIC_NUMERO 
                    JOIN
                        SISTEMA_PROCESSUAL S WITH (NOLOCK) 
                            ON REL.IDE_SISTEM = S.DES_SISTEM_PROCES 
                    JOIN
                        NUMERO_PROCESSO NUM WITH (NOLOCK) 
                            ON NUM.NUM_PROCES = REL.NUM_REGIST_JUDICI 
                            AND NUM.SEQ_SISTEM = S.SEQ_SISTEM_PROCES 
                    OUTER APPLY ( SELECT
                        TOP 1 * FROM
                            T_FASES_CALCULO WITH (NOLOCK) 
                        WHERE
                            SEQ_PROCES_FASE = REL.SEQ_PROCES_FASE 
                            OR ( SEQ_PROCES_GRAU = NUM.SEQ_PROCES_GRAU 
                            AND DAT_HORA_FASE = REL.DAT_FASE ) 
                        ORDER BY
                            SEQ ) T 
                    WHERE
                        1=1 
                        AND SEQ_INDICA_COLUNA = 602 
                        AND IDE_TEMPO = 1225 
                    ) UNION_0001) REL ) AUTO_INDICADOR_0647_EVENTO_0001 
            OUTER APPLY (SELECT
                REL.* FROM
                    ( SELECT
                        * 
                    FROM
                        ( SELECT
                            F.COD_CLASSE_CNJ,
                            F.COD_EVENTO_COMPLE_CNJ,
                            F.COD_EVENTO_CNJ,
                            F.COD_MATERI,
                            F.COD_ORGAO_GABINE_CNJ,
                            F.COD_ORGAO_JULGAD_CNJ,
                            F.SEQ_RESULT_COMPLE AS COD_RESULT_CNJ,
                            F.NUM_CPF_MAGIST,
                            REL.DAT_FASE AS DAT_HORA_FASE,
                            REL.SEQ_PROCES_FASE,
                            REL.NUM_REGIST_JUDICI AS NUM_PROCES,
                            REL.IDE_SISTEM AS DES_SISTEM_PROCES,
                            REL.DAT_AUXILI_1 AS DAT_AUX_1,
                            REL.DAT_AUXILI_2 AS DAT_AUX_2,
                            NUM.SEQ_PROCES,
                            NUM.SEQ_PROCES_GRAU,
                            T.SEQ,
                            T.IDE_GRAU,
                            T.DAT_CARGA 
                        FROM
                            F_JUSTICA_NUMERO F WITH (NOLOCK) 
                        JOIN
                            REL_FATO_PROCESSO REL WITH (NOLOCK) 
                                ON F.SEQ_JUSTIC_NUMERO = REL.SEQ_JUSTIC_NUMERO 
                        JOIN
                            SISTEMA_PROCESSUAL S WITH (NOLOCK) 
                                ON REL.IDE_SISTEM = S.DES_SISTEM_PROCES 
                        JOIN
                            NUMERO_PROCESSO NUM WITH (NOLOCK) 
                                ON NUM.NUM_PROCES = REL.NUM_REGIST_JUDICI 
                                AND NUM.SEQ_SISTEM = S.SEQ_SISTEM_PROCES 
                                AND NUM.SEQ_PROCES_GRAU = AUTO_INDICADOR_0647_EVENTO_0001.SEQ_PROCES_GRAU 
                        OUTER APPLY ( SELECT
                            TOP 1 * FROM
                                T_FASES_CALCULO WITH (NOLOCK) 
                            WHERE
                                SEQ_PROCES_FASE = REL.SEQ_PROCES_FASE 
                                OR ( SEQ_PROCES_GRAU = NUM.SEQ_PROCES_GRAU 
                                AND DAT_HORA_FASE = REL.DAT_FASE ) 
                            ORDER BY
                                SEQ ) T 
                        WHERE
                            1=1 
                            AND SEQ_INDICA_COLUNA = 220 
                            AND IDE_TEMPO = 1225 ) UNION_0001) REL 
                    WHERE
                        AUTO_INDICADOR_0647_EVENTO_0001.SEQ IS NOT NULL) AUTO_INDICADOR_0647_EVENTO_0002 
                    WHERE
                        (
                            AUTO_INDICADOR_0647_EVENTO_0001.SEQ IS NOT NULL 
                        ) 
                        AND (
                            AUTO_INDICADOR_0647_EVENTO_0002.SEQ IS NULL 
                        ) ) BASE 
                WHERE
                    BASE.RN = 1
                        ) CONSULTA

Can someone help me ?
Thank you so much.

Sharepoint 2016 not able to connect to database intermittently

I have a Sharepoint 2016 Farm configured with Shared Roles. Below is the Configuration details.

  • 2 X Web Front End Servers with Distributed Cache
  • 2 X Application Servers with Search
  • 1 X Domain Controller
  • 2 X SQL Servers in Failver Cluster (configured with High
    Availability)

Although I am facing an issue wherein One of the Web Front End Server (WFE1) is sometimes not able to connect to database. I see the logs and find the below Error message.

Unknown SQL Exception 53 occurred. Additional error information from SQL Server is included below.
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)

This issue happens twice in a day. How do I figure out what is happening wrong. Any support will be highly appreciated.

Prevent the (wifi) from turning off and Disable the (airmode) for Samsung tab A 2016 using adb or anything else

Prevent the wifi from turning off and Disable the airmode for Samsung tab A 2016 using adb or anything else.

  • I want if I touched the wifi bottom on the notification bar to say "security policy prevents turn off wifi"

  • and the same with airmode in the notification bar to say "security policy prevents of use airmode"

     **Android 8.1.1 (Galaxy Tab A (2016))**
    

sharepoint enterprise – InvalidOperationException: Could not get algorithm from X509AsymmetricSecurityKey to make Client Context for SP 2016 in VS 2019

I’m trying to create a context to SP 2016 site with the help of a certificate using following piece of code:

OfficeDevPnP.Core.AuthenticationManager othManager = new OfficeDevPnP.Core.AuthenticationManager();
System.Security.Cryptography.X509Certificates.X509Certificate2 cert = new System.Security.Cryptography.X509Certificates.X509Certificate2({certificate path}, {password to access Certificate});
ClientContext oContext = othManager.GetHighTrustCertificateAppOnlyAuthenticatedContext({siteurl}, {clientID}, cert, {certificate issuer id});
Web objWeb = oContext.Web;
oContext.Load(objWeb);
oContext.ExecuteQuery();
string webTitle = objWeb.Title;

However, it is running fine on Visual Studio 2015 but I have various solutions in VS 2019 so it is throwing following exception in VS 2019:

InvalidOperationException: Could not get algorithm from X509AsymmetricSecurityKey

Even tried following in App.config file of my application but issue persists:

<system.web>
<compilation debug="true" targetFramework="4.7.2"/>
<httpRuntime targetFramework="4.6.1"/>
</system.web>

Any help would be much appreciated!

Windows update is stuck at 0% Windows Server 2016 + WSUS

Since we upgraded to Windows Server 2016, updates crash at 0%;

Windows update is active, policies are applied correctly;

Other servers with Windows Server 2016 update normally, others crash at 0%.

We use a WSUS Windows Server 2016 server that provides the updates.

I have never found a definitive solution for this, many say to rename the “SoftwareDistribution” folder, but it doesn’t work!

enter image description here

enter image description here

vmware esxi – Windows 2016 Server VM – dialogs in various apps are displayed after tens of seconds when CPU usage is around 20%-23%

This issue baffles me.

I’ve been given a Windows 2016 Server VM to do development on. The specs for the VM are:

Hypervisor: VMware ESXi, 6.5.0, 13004031
Model: ProLiant BL460c Gen8
Processor Type: Intel (R) Xeon(R) CPU E5-2560 0 @2.00GHz

Specs from the Windows system information:
OS Name Microsoft Windows Server 2016 Standard
Version 10.0.14393 Build 14393
Other OS Description    Not Available
OS Manufacturer Microsoft Corporation
System Manufacturer VMware, Inc.
System Model    VMware Virtual Platform
System Type x64-based PC
System SKU  
Processor   Intel(R) Xeon(R) CPU E5-2650 0 @ 2.00GHz, 2000 Mhz, 1 Core(s), 1 Logical Processor(s)
Processor   Intel(R) Xeon(R) CPU E5-2650 0 @ 2.00GHz, 2000 Mhz, 1 Core(s), 1 Logical Processor(s)
Processor   Intel(R) Xeon(R) CPU E5-2650 0 @ 2.00GHz, 2000 Mhz, 1 Core(s), 1 Logical Processor(s)
Processor   Intel(R) Xeon(R) CPU E5-2650 0 @ 2.00GHz, 2000 Mhz, 1 Core(s), 1 Logical Processor(s)
Processor   Intel(R) Xeon(R) CPU E5-2650 0 @ 2.00GHz, 2000 Mhz, 1 Core(s), 1 Logical Processor(s)
Processor   Intel(R) Xeon(R) CPU E5-2650 0 @ 2.00GHz, 2000 Mhz, 1 Core(s), 1 Logical Processor(s)
BIOS Version/Date   Phoenix Technologies LTD 6.00, 9/19/2018
SMBIOS Version  2.4

The problem that I have is that sometimes dialogs in various applications, usually written in Delphi or Visual C++ take a long time to show up when the CPU usage is around 20%-25%.

I am using Toad Data Modeler (TDM – written in Delphi), The Journal (written in Delphi) and SAP Power Designer (PD – written probably in VC++).

When I click on a table to display the table properties, for instance, in a TDM model (this brings a dialog with lots of tabs), it can take up to 20s for the dialog to show up. In the Journal I had some weird experience where I was typing letters and they would show up one by one in slow motion. Today, I have the same issue with PD. The common pattern when I experience this slowness is a current CPU background usage of somewhere between 20 and 30%. I started to close apps and I got the CPU humming below 10% and the dialogs show up much faster.

I compared this to my Windows 10 VM running on a MBP 16″ and basic it is incomparable.My VM is much faster. I used the same TDM model to test, and I even have fewer cores assigned.

Any ideas? I know it’s not much information to go on, but I hope that someone with the knowledge of VMs and Windows internals has at least a theory. To me it looks like the dialogs are processed on a core that is (CPU) starved, even though the VM has been assigned 6 cores.

Thanks

Microsoft SQL Server 2016 Restore command many files

I have a question regarding proper syntax for restoring a 2016 SQL Server database.

One database 1 mdf no issue. But I need to restore a database that has 3 mdf files and 73 ndf files and then apply 2 differential back ups.

RESTORE DATABASE [Database]
FROM DISK = N'E:DataServer_Database_20210213221000_full_1_of_2.bak',
     DISK = N'E:DataServer_Database_20210213221000_full_2_of_3.bak'
     DISK = N'E:DataServer_Database_20210213221000_full_3_of_3.bak'
WITH FILE = 1, REPLACE,
MOVE 'Database' TO 'E:DataDatabase.mdf',
MOVE 'FactPart00' TO 'E:Data1FactPartFactDatePart00.ndf',  
MOVE 'FactPart01' TO 'E:Data1FactPartFactDatePart01.ndf',  
MOVE 'FactPart02' TO 'E:Data1FactPartFactDatePart02.ndf',
... all the way to 73

The above part works my question is now once how do I then restore diff file
File Name

Server_Database_DIFF_20210214_220209.bak

To restore the diff will I also need to reference all the ndf files as well or just restore. What should my syntax look like? I don’t have an error since I have not ran it as I am nervous about corrupting database.

SP 2016 – Move files using Content and Structure brought over extra columns

Per a user request, I just moved some files from one library to another using Content and Structure. The source library has more columns than the target library. The files brought over the extra columns. Now when the user tries to change the metadata in the columns which belong in the target library, nothing happens.

I used Content and Structure because preserving the version history is important to the user.

  1. What can we do to enable metadata changes on the moved files?
  2. Is there a way to execute this move that does not cause these problems?