DBA Stack Exchange Archives - Andy M Mallon

AG secondary is out of sync for hours after index maintenance

February 21, 2023 Andy 0

Question We have a 3TB OLTP database running Siemens Opcenter MES software at one of our manufacturing plants in China. This is setup in an Always-On Availability group with one secondary node. As this is a manufacturing plant, we are only provided 11 1/2 hours … [Read More]

Using OPENJSON, can’t get results to GROUP BY

December 24, 2022 Andy 0

Question I’m passing a JSON string to a stored procedure in order to insert warehouse inventory. Sometimes, there will be multiple line items with the same product, going to the same shelf, on the same pallet (LPN), with the same date. I would like to … [Read More]

SQL Modulo Function gives the wrong value?

November 18, 2022 Andy 0

Question In Javascript, Excel, Python, and a scientific calculator, the modulo operation gives a value of 113.84230371659942. (Or something very close to that, depending on number of decimal places and rounding.) But in T-SQL, returns a value of -246.1576962834. I’ve tried using As I was … [Read More]

Is SQL Server affected by OpenSSL 3.0 Vulnerabilities: CVE 2022-3786 and CVE 2022-3602

November 8, 2022 Andy 0

Question I am trying to establish whether SQL Server is affected by the recently discovered OpenSSL vulnerabilities. For example, are SQL Servers that are using TLS/Certificates to encrypt connections between client and server affected to? I believe SQL Server uses Open SSL libraries for encryption … [Read More]

What’s the use case of disabling an index?

September 25, 2022 Andy 0

Question I just learned that indexes can be disabled on a table. Even the clustered index can be disabled. After that, users can’t access the index or the data if clustered index. When will we disable an index? I just don’t understand the use case. … [Read More]

What ports to open for sql server behind secure domain firewall

August 25, 2022 Andy 0

Question We are trying to solve connectivity issues for one of the app trying to pull data from very secured sql server behind firewall. I am not that expertise in security but need help on how to proceed in opening ports for that app to … [Read More]

snapshot replication – corrupted BCP files

August 18, 2022 Andy 0

Question Each night my SQLServer replication failed on a big table with the following error. It is failing on a different table each night but always on a relatively big table ( > 200,000 rows) The process could not bulk copy into table ‘"dbo"."Table01"’. (Source: … [Read More]

GRANT ADMINISTER Database Bulk Operations – Incorrect syntax near ADMINISTER

May 23, 2022 Andy 0

Question I am trying to import a bacpac file to my SQL Server Express 2016. An exception is thrown during the import with Returns: Incorrect syntax near ‘ADMINISTER’. I am running the import as the sa user. Later when I try to run the SQL … [Read More]

How do I generate the private key needed to restore the encrypted backup?

April 22, 2022 Andy 0

Question I’m simulating the situation where I take a backup from instance A and restore it on instance B. I followed the doc to Backup to Disk with Encryption. Since I’m using only my desktop to simulate the whole process, after taking the backup of … [Read More]

Temporarily turn on Cascade Delete

April 20, 2022 Andy 0

Question This has probably been asked before, but I couldn’t find the answer. I have a complex datamodel in sql-server-20XX (more likely to be something old) in which cascade delete has been turned off (by design). A large chunk of our database has been sold … [Read More]