Questions tagged [sql-server-2019]
SQL Server 2019 (major build version 15.00.xxxx). Please also tag sql-server.
911
questions
0
votes
0
answers
29
views
How to get NTLM version authentication with select statement in SQL Server
From my using SSMS I connect to SQL Server 2019. I would like to know what authentication is my current session connection using. I executed SQL:
select
auth_scheme
from
sys....
- 327
0
votes
0
answers
24
views
sqlserver alwayson audit setting
I am running SQLSERVER ALWAYS ON 2019 ENT VERSION.
I'm trying to STOCK an audit at the database level in SQLSERVER.
In the master, it was confirmed that the log audit was left at DATABASE LEVEL.
...
dante
0
votes
0
answers
39
views
LSRestore job is taking more than 1 hr to complete. Threshold time is 45min
LSCopy and LSRestore job has been scheduled for every 10 mins. I got a trn file on my secondary server after every 6 min from Primary server. LSCopy is synced with LSBackup. The problem is that ...
0
votes
0
answers
48
views
Curious Memory Pressure Alert - SQL Server Process Physical Memory Low
We are, on occasion, receiving a SQL Server Process Physical Memory Low alert on one of our warehouse instances. The kicker is, I can find absolutely no indication of memory pressure at the time the ...
- 121
0
votes
0
answers
73
views
When I connected SQL Server 2019 Management Studio Connection to Integration Services it shows me Error Class Not Registered
I'm installed SQL Server 2019 Enteprisse in the first server and I have other server with SQL Server 2019 Standar Edition ,both server when I tried connect with the Integration Service SQL server 2019 ...
- 1
3
votes
2
answers
142
views
How to sum up the distinct Total Time of an Event ignoring duplicate overlaps in Times?
I have the following EventTimes table:
DROP TABLE IF EXISTS dbo.EventTimes;
CREATE TABLE dbo.EventTimes
(
EventTimeKeyId INT IDENTITY(1,1) PRIMARY KEY,
EventId INT NOT NULL,
StartTime TIME NOT ...
- 35.2k
0
votes
1
answer
46
views
SQL Server remote insert fails, update works
I have created a stored procedure on ServerA (SQL Server 2019) which inserts a number of rows in a local table.
When I call the stored procedure using OPENQUERY from ServerB (SQL Server 2012), the ...
- 345
0
votes
2
answers
53
views
Replace multiple words in a single value using SQL
I have two tables, #customer and #abbr. The #customer table consists of two columns: 'id' and 'name,' while the #abbr table includes entries for abbreviations and their corresponding full forms. My ...
- 439
0
votes
1
answer
103
views
Is this Parameter Sniffing?
I have a particular stored procedure that takes 1 parameter.
This proc is executed once a day with the same parameter value everyday. The row count is also the same with each run of the proc.
Day 1 - ...
- 319
0
votes
0
answers
37
views
SQL Server 2016/2019 mixed AG causing constant DB transaction log growth
We have a SQL Server 2016 Always On Availability Group (AG) setup with 3 nodes and recently added 3 new nodes with SQL Server 2019.
After the addition of the new nodes, we noticed that the database ...
Jay
0
votes
0
answers
23
views
Issue with SQL Server 2019 SSMS connection
I have a SQL Server 2019 Express installation. It was acting weird in that even though I had set it to mixed mode auth, I could not change the sa password (I know we shouldn't use this, but it's a ...
CBrianC
0
votes
1
answer
36
views
SQL Trigger Connection Case With NT AUTHORITY
I am trying to limit the connections to my SQL using a CASE to monitor the connections as laid out here.
In the CASE I am trying to add the NT AUTHORITY\NETWORK SERVICE to the case. Yet when it ...
- 121
1
vote
1
answer
121
views
Union slow (4 minutes) - separate queries take 2-4 seconds
We have a linked server / db with asset data (not managed/owned by us) that we need to join with a local db (SQL Server 2019).
If we run the queries separately, they all run smoothly - but when doing ...
- 11
0
votes
1
answer
38
views
Allow group as public on MSSQL server and set db privileges for user's
I moved a couple of MSSQL databases to a different cluster and want to change the way privileges are distributed. I created a group in AD and created a public login for that group. Every user who ...
0
votes
0
answers
27
views
What is the risk of deleting millions rows from an offline table?
I'm a junior DBA for a medium-sized company that helps merchants exchange money during sales. Our services operate 24/7, 365 days a year, so performance and uptime is absolutely critical. We have a ...
0
votes
1
answer
50
views
In an Always On configuration, why do I have to open ports of the listener as well for the sql server engine itself for the listener ips?
We use an SQL Server 2019 Always On setup.
2 sql server 2019 nodes in same subnet : server01, server02
1 sql server 2019 node in other subnet : server03
We decided to create only 1 availability group ...
- 1
0
votes
1
answer
26
views
Does DACPAC creation is doing any blocking on database / server level?
I have to create DACPAC from production database. I would like to do this via Visual Studio 2022 (SQL Server Object Explorer and "Extract - Data-tier application"). Is it possibility to ...
- 295
2
votes
0
answers
95
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 ...
- 2,173
0
votes
1
answer
54
views
When restoring database, why would zeroing log file take a long time?
I am building a new development server that will get weekly automated restores of production backups. This server will be replacing an existing development server where I have this process running ...
- 19
0
votes
0
answers
60
views
How to activate MSDTC for SQL Server 2019 failover cluster
I have configured a failover cluster and always on SQL Server 2019 high availability group on 2 nodes and operating system with Windows Server 2019.
I am trying to configure the MSDTC properties of ...
- 1
0
votes
1
answer
164
views
SQL Server 2019, utf-8, replication problem
I know this is late to the game, but we're just upgrading to Sql Server 2019 after years and years on 2008.
I started using the Latin1_General_100_CI_AI_SC_UTF8 collation as an experiment and at least ...
- 207
0
votes
0
answers
38
views
What is the significance of failback setting in a SQL AG?
I have a 2-node AG with WSFC setting configured as allow failback between 3am and 7am.
I restarted node 1 at 1am which caused an automatic failover to node 2. However there was no automatic failback ...
- 3,442
0
votes
0
answers
172
views
Report Server WMI Provider Error: Invalid namespace when connecting to Report Server Instance on Reporting Services Config Manager
I have two SQL Server 2016 instances (Instance A and Instance B) on the same server. Both instances have SSRS. At one point I upgraded Instance A to SQL Server 2019 but then due to the changes made to ...
- 1
0
votes
1
answer
35
views
Developer with SQLAgentUserRole only has View access
I have a developer who needs to be able to create jobs in SQL Server Agent. They are part of the security group SQLDA and I granted that group SQLAgentUserRole in msdb. They still have view only ...
- 161
4
votes
0
answers
520
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="...
- 41
4
votes
1
answer
216
views
Add items to JSON
I have the following JSONs which I would like to combine. In my JSON I have the part 'kenmerken' where I would like to add some more items to. I thought this could be done by using the JSON_MODIFY.
...
- 2,146
1
vote
0
answers
23
views
Cleanup job for CDC failed
The clean up job for CDC failed with this error:
Msg 596, Level 21, State 1, Line 0
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 0
A severe ...
- 210
0
votes
1
answer
41
views
Scalar Functions Causing Plan Cache Bloat
Our plan cache is showing a large number of duplicate entries for a scalar function that is called as per the code below:
SELECT dbo.fnSomeFunction('12345678')
I have done some testing and found that ...
- 1,928
0
votes
0
answers
41
views
Cannot find the symmetric key 'e1', because it does not exist or you do not have permission. (SQL Server 2019)
It seems to be a weird error, I have tried everything from giving Control, Alter, and View Definition permission on the symmetric key but nothing seems to work out.
Tried everything from this article.
...
0
votes
1
answer
68
views
Configuring Always On Availability Groups sql Server 2019 and log backup
We want migrate to sql 2019 and I did the test and set up Hight availability with twho 2 nodes,but and the set up of AG I have four option,
I gave that option a shared path for the transaction log ...
- 1
1
vote
2
answers
138
views
SQL Server database with 100 terabyte of data
We have been assigned a new application where the vendor is expecting the database to grow to around 100 TB. They have mentioned SQL Server Standard Edition is compatible.
My question is whether SQL ...
- 1,039
0
votes
1
answer
196
views
SQL Server 2019 CU22 error Using 'dbghelp.dll' version '4.0.5' & non-yielding on Scheduler
I've had SQL Server 2019 with CU22 on Windows 11 Pro.
We use this database during hardly 1 year.
Usually between 26-28th every month we have scenario:
1.Database save in log file message "Using '...
- 1
0
votes
1
answer
43
views
When using multiple basic AGs is it recommended to use 1 listener or mirroring endpoint?
I want to setup HA DR for a single instance SQL server by adding a new instance on another machine and configuring multiple basic AGs (with manual failover mode).
I want all client apps to be able to ...
- 3,442
1
vote
2
answers
104
views
SQL Trigger Calling out Domain\Group
I am creating a trigger in SQL Server 2019 to secure the number of connections users can have into my SQL server.
I am having an issue getting my SYSDBAs access using the domain group. I would rather ...
- 121
0
votes
1
answer
56
views
How is SQL server engine account able to access disk drive folder without permission?
I have recently changed the SQL engine account to mydomain\sqlengine.
This evening I setup SQL audit into F:\audits. I'm surprised to see the SQL server is able to create a file in this folder without ...
- 3,442
1
vote
0
answers
24
views
What are the post steps after changing SQL engine and agent accounts?
In a default SQL installation the engine account is NT Service\MSSQLSERVER, and agent account is NT Service\SQLSERVERAGENT
In SQL security I can see them both listed as logins with sysadmin role.
If I ...
- 3,442
0
votes
0
answers
21
views
Is local DAC enabled by default and does it need to be accessed using -A / Admin:?
I'm trying to understand DAC. Generally this is important when a connection cannot be made to SQL server.
DAC allows and prioritises the admin to remotely connect to SQL server. To use this the remote ...
- 3,442
2
votes
0
answers
84
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 ...
- 21
1
vote
0
answers
69
views
Why is log backup not truncating AG db log file?
I have multiple dbs in AG. I'm facing an issue with one of the db, whose data file size is about 200GB, and log file size is usually about 5GB.
The AG is 2-node with readable secondary.
We have hourly ...
- 3,442
4
votes
2
answers
267
views
Create JSON elements based on table content
With the following database schema, I am trying to retrieve a JSON of the items combined with properties and an indicator if they are connected.
I started with:
DECLARE @Item TABLE
(
ItemId INT,
...
- 2,146
0
votes
2
answers
133
views
Is high logical read terrible for performance? AFAIK this is reading from memory, which is fast
I have database query that seems to have abnormally high logical read (refer below).
I am not a DBA, but from the plan it seems pretty optimized to me? There are 2 index scans but the cost isn't that ...
- 101
0
votes
1
answer
51
views
Sql server migration side by side ( 2016 to 2019)
What is the best (i.e. fastest) way (backup and restore) for migrating a 6 TB database in SQL Server 2016 (Windows 2012r2) to another server, SQL Server 2019 (Windows 2019)?
Can we, for example, ...
- 5
0
votes
1
answer
70
views
Difference between forwarded_fetch_count and forwarded_record_count
I have been scratching my head, re-reading MSFT help and I still cannot understand the difference between forwarded_fetch_count and forwarded_record_count in sys.dm_db_index_operational_stats and sys....
- 347
0
votes
0
answers
37
views
SQL Server SSIS Data Flow Task: How to enable transactions between two machines while bypassing DTC, if one machine does not have DTC support?
A bit of a conundrum here; I'm trying to survey what are my best to worst-case options.
For a given SSIS package, I have a Data Flow Task which connects via OLE DB for both the Source and Destination ...
- 275
1
vote
1
answer
65
views
Rolling up multiple varbinary(max) rows into a single row
My table looks something like this:
BinFiles
0x1101ABB1C...
0x2202DEE2F...
0x3303GHH3I...
I tried this to get a single line:
SELECT
STUFF((SELECT d.BinFiles
FROM dbo.demo d
WHERE d.ID ...
- 42
4
votes
2
answers
181
views
Can I change the owner of the cdc schema?
I've enabled Change Data Capture (CDC) on a database using exec sys.sp_cdc_enable_db
This creates a new cdc schema which is owned by the cdc user
select
s.*
, dp.name
from sys.schemas as s
...
- 3,951
0
votes
1
answer
54
views
Does checkpoint write any log records from memory into the log file disk?
A checkpoint writes the current in-memory modified pages (known as
dirty pages) and transaction log information from memory to disk, and
also records the information in the transaction log.
Log ...
- 3,442
0
votes
1
answer
64
views
SQL Server with small Cache plan size
I'm running MSSQL Server 2019 standard edition and it has 48GB of physical memory and 44GB of SQL Server's max memory.
There is nothing on the host but SqlServer.
All of request from application ...
- 111
3
votes
1
answer
520
views
SQL 2019 - Legacy Cardinality Estimation
I am running SQL Server 2019 RTM CU18
I am in the process of testing workloads against this server before migrating to it.
All databases are set to SQL 2019 compatibility level.
I am finding that ...
- 319
0
votes
1
answer
51
views
How do I filter down the latest Date from multiple columns
I've been working with it and got it to work somewhat, but my WHERE filter has errors, I need only the max date that is Actual in each category.
left JOIN ( SELECT Customer_ID
, MAX(...
