Skip to main content

Questions tagged [sql-server-2019]

SQL Server 2019 (major build version 15.00.xxxx). Please also tag sql-server.

284 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
8 votes
0 answers
142 views

SQL Server: how Row Goal affects the same index operation?

I am tuning some SQL performance in the SQL Server 2019, and I ran into issues with one query that involves the issue with the row goals. The simplified query is: select TOP 1 ISNULL(RUNID,-1) from ...
Jan Drozen's user avatar
7 votes
0 answers
204 views

Find a previous statement or held locks in a blocking situation

I'm using XE blocked_process_report to detect and analyse the blocking. But since it's a point-in-time situation, I only see the blocking leader's statement currently being run and the incompatible ...
Zikato's user avatar
  • 4,812
5 votes
0 answers
723 views

Hash join hint returns an error when using local variable

The query below is using the StackOverflow2010 sample database: DECLARE @Id INT = 18471 SELECT c.UserId FROM dbo.Comments AS c INNER HASH JOIN dbo.Users AS u ON c.UserId = u.Id AND c.UserId = @Id ...
JieLong's user avatar
  • 167
4 votes
0 answers
564 views

Non parallel plan with row level security predicate to check server role

While exploring Row Level Security I picked up that whenever I had it enabled it was not creating any parallel query plans. In SQL Server 2019 this gave me NonParallelPlanReason="...
bla9x's user avatar
  • 41
4 votes
3 answers
1k views

Named instance throwing error 26 and won't connect

I've installed 3 new named SQL Server 2019 instances on a standalone server. The named install seems to have defaulted to computername\network name. Ex: BHMDB06\sseprod. During the install I had it ...
bwilliamson's user avatar
4 votes
0 answers
576 views

Can backups done through external clients cause memory pressure on SQL Server?

Developer in DBA role here, we recently migrated from 2008 to 2019 and are having memory related problems on the new server. Every 3 to 6 days the OS will start paging and CPU usage would spike to 99%,...
diffie's user avatar
  • 41
3 votes
0 answers
82 views

Double registration when using select into

SQL Server 2019 CU15, Database Audit: When auditing a SELECT on a database, everything looks fine, but if I SELECT INTO a #myTempDB, I get double registration in the AUDIT LOG. I created a test ...
Torben iisager's user avatar
3 votes
0 answers
1k views

SQL Server 2019 update CU8 to CU9 - lots of messages in errorlog file... DBID: [9] No DEK update for File Id [4] while existing DEK is null

This is SQL Server 2019 CU9 Developer edition on my 16GB laptop: I updated from CU8 to CU9: I ran checkDB on all of my DBs no issues. Lots of these messages in my SQL Server Log file viewer: DBID: [9]...
sherveyj's user avatar
  • 111
3 votes
1 answer
171 views

how to mirror an azure storage account to a non-paired zone to enable fast file_snapshot backup/restore and database file access?

Wow, that title was a mouthful. Scenario My dev server is restoring a copy of production data daily, anonymizing it, and creating a dev-grade backup for my developers to restore locally. We're running ...
GMassDBA's user avatar
  • 335
2 votes
0 answers
50 views

SQL Server Underestimating cardinality on a Filter operator in a Left Anti-Join

I am tuning a query which is slow, I have narrowed the root of the problem to be at the very beginning of the execution plan, where SQL Server makes a bad estimate on a WHERE IS NULL filter that ...
SE1986's user avatar
  • 1,990
2 votes
0 answers
109 views

How can Last_Execution_time be before Creation_time?

sp_HealthParser (made by Erik Darling) crashed on me, because Last_Execution_time was way before Creation_time. How is that possible? The columns : Last_execution_time in sys.dm_exec_query_stats ...
Henrik Staun Poulsen's user avatar
2 votes
0 answers
92 views

SQL Server High memory at startup

I have Microsoft SQL server 2019 installed on my Windows Server 2019 server. The memory on the server is 512 GB. When the MSSQLSERVER service starts, MS SQL 2019 immediately consumes 100 GB of memory ...
PetroIvanov's user avatar
2 votes
0 answers
31 views

What is the meaning of principal that has access to the machine's credentials?

The service master key can only be decrypted by the service account under which it was created or by a principal that has access to the machine's credentials. What is the meaning of ...principal that ...
variable's user avatar
  • 3,644
2 votes
0 answers
432 views

SQL Server 2019 CPU spikes with no usage

We've configured two SQL Server 2019 VM one it's primary and other secondary, both take part in an Availability Group. The primary node have CPU spikes every 30 min and there isn't any job, heavy ...
CCR's user avatar
  • 21
2 votes
1 answer
1k views

Have to regenerate SQL Server Master Key after every server reboot

We recently migrated our database over from SQL Server 2012 to a new SQL Server 2019 box. As soon as the database was migrated, I regenerated the master key, but whenever our SQL Server 2019 box ...
Timothy Ritter's user avatar
2 votes
0 answers
106 views

Are tempdb allocations released after transaction is commited?

In addition to this I am again wondering if the code in a transaction and loop release the tempdb allocations. For the template below: SET NOCOUNT ON; WHILE EXISTS(....) BEGIN BEGIN TRY; ...
gotqn's user avatar
  • 4,026
2 votes
0 answers
52 views

Does sql config manager changes (example: service account) on 1 FCI node auto update it onto the other node?

Link: https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/always-on-failover-cluster-instances-sql-server?view=sql-server-ver15#FCIelements Each node in the resource group ...
variable's user avatar
  • 3,644
2 votes
0 answers
23 views

What is the approach to handle failover and compatibility issues when patching FCI nodes?

I have FCI with 2 nodes. Following is steps to patch the nodes: Remove node from Possible owners Patch the inactive node Add the node to the Possible owners Failover to inactive node Leave it running ...
variable's user avatar
  • 3,644
2 votes
0 answers
677 views

SQL Server DBMail post 1 October 2022 with Oauth 2.0 requirements

I cannot get a straight answer from anyone on whether or not SQL Server mail will continue to work after 1 Oct with OAUTH 2.0 implementation by Microsoft. From what I understand basic authentication ...
Michael Orechoff's user avatar
2 votes
0 answers
352 views

Enable COPY_ONLY Backup Via DISKSHADOW Command Prompt

Introduction I'm experiencing weird behaviour on a virtualised (ESXi) Windows Server 2022 running SQL Server 2019 when the VMware conducts a snapshot of the server. It doesn't log the snapshot in the ...
John K. N.'s user avatar
2 votes
0 answers
79 views

Default roles ticked when creating new login through SSMS GUI

When creating a new login via the GUI: In the User Mapping section, certain databases will have some roles pre-ticked in the Database Role Membership section. What controls this behavior? It's ...
soapy smith's user avatar
2 votes
0 answers
207 views

Full text index search with character at the beginning

I am looking at tables like this (I did not design them): CREATE TABLE EventIDsToSearch (id int not null) insert into EventIDsToSearch values (1) insert into EventIDsToSearch values (92) insert into ...
w0051977's user avatar
  • 503
2 votes
1 answer
54 views

Is there any harm in storing other rows' int PKs in a hierarchyID instead of an arbitrary number?

I'm looking at common uses of hierarchyID, and most examples use some sort of arbitrary numbering for the HID nodes: HID, Name /, World /1/, USA /1/1/, Texas /1/1/1/, Houston /1/1/2/, San Antonio /1/2/...
XOR cyst's user avatar
2 votes
0 answers
121 views

MS-SQL and HierarchyID

I have been using HierarchyID for the last 2 years in some of my tables for obvious purposes And I have kept a Column (int) for the last node number for example if I have these rows so the int column ...
Y.G.J's user avatar
  • 119
2 votes
0 answers
333 views

SQL server 2019 RTM Cumulative Update (CU) 12 KB5004524 (2) Last failed install attempt on 9/19/2021-0x80070643

What causes DEV SQL 2019 to failed when patching, this is the second time it has happened and had to restore the master and backup the entire databases. Script level upgrade for database 'master' ...
Ben's user avatar
  • 21
2 votes
0 answers
94 views

SQL Server RECEIVE statement ordering issues

I'm seeing some unexpected message ordering behavior running a RECEIVE statement against a Service Broker queue. According to the documentation: For a given conversation, a RECEIVE statement returns ...
kirchner's user avatar
  • 318
2 votes
1 answer
587 views

DB_CHAINING lost on on planned Availability Group failover for SQL Server 2019 cumulative update

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 ...
Rob Dalzell's user avatar
2 votes
1 answer
662 views

Unable to use SSL Certificates in SQL Server on Linux

Hello everyone so here is the problem: SQL Server 2019 installed on Ubuntu 20.04 (working before setting SSL certificates) I was following the Official guide of Microsoft to secure my SQL server with ...
ollie10's user avatar
  • 121
2 votes
1 answer
3k views

SSMS Not showing Status and Scheduled Jobs Not Running

Recently in SSMS, I have started getting a blue question mark next to the database and SQL Server Agent, when previously all was showing running. At the same time my scheduled jobs have stopped ...
C. K. Miller's user avatar
2 votes
0 answers
2k views

SQL Server 2019 and Transparent Data Encryption - Intermittent (silent) Error Log Messages

I recently migrated a SQL Server 2017 TDE database to SQL Server 2019 CU4. I was browsing the Error Log and noticed intermittent messages about An error occurred while processing log encryption. The ...
Scott Hodgin - Retired's user avatar
2 votes
1 answer
140 views

Synchronize 2 sql server 2019 database over intermittent connection (no replication)

I'm a PostgreSQL DBA learning SQL Server 2019. The setup : 1 Desktop, 1 Laptop Both running SQL Server 2019 Developer Windows 10 Home (nothing else, no active directory, nas, san, ...) a database in ...
ker2x's user avatar
  • 121
2 votes
2 answers
71 views

Incorrect results when combining full-text search and regular comparison

create table "Foo" ( "id" integer identity (1000, 1) not null, "name" nvarchar(100) not null ) alter table "Foo" add constraint "Foo_PK" primary key (&...
Hooman Valibeigi's user avatar
2 votes
0 answers
267 views

Sql Server 2019 Query Hint Placement confusion

I'm using SQL Server 2019 (CU4) and I'm encountering some strange issues when trying to use a query hint for the legacy cardinality estimation due to a poorly performing Table Value Function. I'm not ...
Scott Hodgin - Retired's user avatar
2 votes
1 answer
254 views

mssql suddenly cannot access physical file

SQL server 2019 on Centos 7 x86_64. It was running fine for quite long time, then got broken. I was not doing anything special, neither update or restart or whatever. At some point the database went ...
r00f's user avatar
  • 21
2 votes
2 answers
1k views

dbcc checkdb runs for interminably long time, 50% of the time

I've got a nightly maintenance job (largely taken from the ola hallengren maintenance package) that runs database backups and runs an an integrity check on every database. The integrity check part is ...
rheinz's user avatar
  • 21
2 votes
0 answers
185 views

InitPersistentVersionStore failed after installing CU2 on multiple instances

I saw this on another post, but I can't comment on it. So I am opening a new one, but I am having a similar issue that Martin Guth had posted. It is odd, but I updated about 9 of my instances to CU2 ...
Matthew Lee's user avatar
2 votes
0 answers
271 views

SQL Server Agent is not ending some jobs correctly

we observed a strange behaviour after migrating our datawarehouse from SQL Server 2012 to SQL Server 2019. Our Agent runs most of the jobs correctly after the migration. However there are a few were ...
Tom's user avatar
  • 21
2 votes
0 answers
753 views

PolyBase to CSV File

I'm trying to see if it's possible to create a PolyBase connection to a .CSV file. I'm using the Microsoft Access Text Driver and have set up a system DSN to the folder where the csv file is located. ...
MrTCS's user avatar
  • 709
2 votes
0 answers
402 views

Problem with installation SQL Server 2019

I got a problem when I wanted to install SQL Server 2019. I don't have Setup Bootstrap on this path, I only have folders SSEI and License Terms on this path. Windows Installer log file: C:\Program ...
private7's user avatar
  • 121
2 votes
1 answer
120 views

multiple percentile_cont with where clause

I'm using percentile_cont to find 50th percentile of product that is sold and product that is open to sell using where clause. Sale is a big table and it is creating performance issues. Is it possible ...
SQLProfiler's user avatar
2 votes
1 answer
2k views

Nested AD group access not working when nested more than 2 levels deep?

For the setup of the security of the databases we have created a number of nested Active Directory groups: Group Members Group type RGP-EA_PROD-RW RGP-SparxEA-RW Domain Local Security RGP-SparxEA-...
Geert Bellekens's user avatar
2 votes
1 answer
132 views

Do SQL OS Background Tasks Yield to Normal Tasks?

I have a SQL Server 2019 server that is running at about 22% CPU. I look at what's running and I see that about half of that 22% is coming from background tasks (i.e. the status is "background&...
sarme's user avatar
  • 446
1 vote
1 answer
36 views

SQL Connection With Domain Account Impersonate Error

I am trying to connect to my SQL 2019 Database with a Domain level account used for a software. The SQL Server is joined to the domain. I am trying to connect to it on the local server. My Account and ...
JukEboX's user avatar
  • 131
1 vote
1 answer
49 views

CDC Capture job fails with 'Cannot insert duplicate key row in object cdc.Table_CT' with unique index 'CT Clustered Index'

When the CDC Capture job runs, it fails with this error: Cannot insert duplicate key row in object cdc.Table_CT' with unique index 'CT Clustered Index. This index is a SQL Server CDC created index ...
Pam Moodley's user avatar
1 vote
0 answers
54 views

Anyone using CDC and having performance issues extracting net_changes? We have 8 tables tracked with CDC "@support_net_changes=1"

So 8 tables are being tracked with CDC "@support_net_changes=1". 7 of them are ok in regards to performance. As some of these tables are only 10-40 million rows and data doesn't change ...
sherveyj's user avatar
  • 111
1 vote
0 answers
76 views

SQL Server allows select on exclusive locked table

Microsoft SQL Server 2019 CU 15. In the first session I execute the following statements: begin transaction update Ledger set AmountDebit = 1000 where AccountId = 14 which creates exclusive lock on a ...
rrejc's user avatar
  • 111
1 vote
0 answers
51 views

sp_BlitzQueryStore reports "multiple plans", but I can only find one execution plan. Why?

sp_BlitzQueryStore reports a particular query on several of my rows. In each of these, it reports Unparameterized Query, Parallel, Multiple Plans, Row estimate mismatch However, each of these rows ...
J. Mini's user avatar
  • 445
1 vote
0 answers
148 views

Resumable index rebuild operation fails for filtered index

Resumable index rebuild operation fails with an exception, it is on SQL Server 2019 CU23 and the DB is in simple recovery mode. Exception: Msg 10675, Level 16, State 2, Line 54 Internal Query ...
S.D.'s user avatar
  • 746
1 vote
0 answers
73 views

How to restart SQL Server Instance after restore master

At work we have a productive SQL Server working, and we create backups of each database periodically, including system databases master, msdb and model. I'm trying to restore the whole datawarehouse ...
santiago arnesano's user avatar
1 vote
0 answers
49 views

Instead of delete trigger performance

We have 3rd party tool which is deleting data via below parametrize query: delete from [sap].[PRCD_ELEMENTS] where [rowid] = @P1 Table contains around 3 billions of records and is partitioned by ...
adam.g's user avatar
  • 355

1
2 3 4 5 6