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.
Tag: 2016
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!
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.
- What can we do to enable metadata changes on the moved files?
- Is there a way to execute this move that does not cause these problems?