Questions tagged [sql-server-2022]
SQL Server 2022 (major build version 16.00.xxxx). Please also tag sql-server.
153 questions
0
votes
1
answer
43
views
What is the practical use case for a very large (terabyte-scale) heap?
This question is the highest rated question on this website about heaps. It asks about the use case of heaps. However, I believe that very large heaps (e.g. thousands of gigabytes) are a special case ...
- 823
-1
votes
0
answers
32
views
Resource Governor Classifier Function Issue [closed]
I’m encountering the following error in the SQL Server error log:
2024-12-03 11:21:01.860 spid568 Error: 10982, Severity: 16, State: 1.
2024-12-03 11:21:01.860 spid568 Failed to run resource governor ...
- 11
-1
votes
0
answers
41
views
Does original primary need to be patched as part of CU patching or does Microsoft support a delayed patch of the original primary node?
https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/upgrading-always-on-availability-group-replica-instances?view=sql-server-ver16
This document applies for cumulative ...
- 3,499
1
vote
1
answer
95
views
Switching from async to sync mode caused apps to freeze
SQL server 2022 enterprise edition AG is currently in async mode.
This is a 2 node AG, 32 cpu, 275GB RAM server dedicated for each SQL server. Both nodes reside in the same data center with fibre ...
- 3,499
0
votes
0
answers
30
views
One step in Azure SQL job does not retry
I have an Azure SQL Database with a weekly maintenance job. The job scales the database up, performs various tasks, potentially including an index rebuild if fragmentation is high, and then scales the ...
25
votes
1
answer
1k
views
STRING_SPLIT with order not working on SQL Server 2022
I need to use STRING_SPLIT with enabled ordering:
We have upgraded all of our servers to SQL Server 2022 and change the compatibility level to 160, but the following code is not working:
SELECT * ...
- 4,318
0
votes
1
answer
46
views
Is there a way to place the AG across WSFC?
I have a 2 node production application AG. Both nodes are part of the same WSFC. Primary is read/write and secondary is read only. This has the db called APP_DB.
I also have a 2 node BI reporting AG. ...
- 3,499
0
votes
2
answers
63
views
Query to flag user: SQL2022 Update is needed
My software, compatible with all versions of SQL2016 and up including SQL Azure, crashes when SQL 2022 is lower than Version 16.0.1135.2 or lower than 16.0.4165.4 - depending on RTM, GDR, CU (which I'...
- 1,093
1
vote
2
answers
147
views
Heap with Non Clustered Index vs. Non-Sequential Clustered Index
I work with an enterprise application (code not available) that currently uses heaps. It uses a terrible pseudo-sequential varchar key that ends up not being sequential due to string sorting not ...
- 163
4
votes
2
answers
282
views
How can I create a partitioned table with the addition of a unique index in SQL Server?
I am creating a partitioned table called TestArticles, specifying several filegroups according to the year of their publication (publishDate). This code (excluding the commented parts) executes ...
- 43
0
votes
2
answers
80
views
Datetime2 in SQL 2014 Vs SQL 2022
Behaviour of column type 'DateTime2' between SQL 2014 and SQL 2022.
This was found when our application's calculation for datetime difference of columns being type DateTime2 went wrong.
Tried to find ...
- 81
0
votes
2
answers
67
views
SQL Server, user's database is not available after boot
We have a kind of strange usage of SQL Server. We develop a client app but due to network issues, each developer is working with his own instance of SQL Server, which by itself is in the container.
So ...
- 959
1
vote
1
answer
129
views
How to Find Out Which Columns in a Table are Queried by SELECT and UPDATE Statements
We have a table that has 54 columns, it is used by many applications, reports and users.
The table is from legacy system, some columns may be obsolete.
We would like to find out which exactly columns ...
- 5,964
0
votes
1
answer
69
views
Add RowVersion column to a very large table
We need to add rowversion column to a very large table. We are on SQL 2022 and came across this post where Paul White mentioned about trace flag - 4085 - How can I add a rowversion column to a large ...
- 39
1
vote
0
answers
29
views
Define Separate Security on Forwarder in SQL Server Distributed Availability Group
In short: Is it possible to define separate security specifically on the forwarder server in a SQL Server Distributed Availability Group, which crosses domains, in order to allow access to the ...
- 11
0
votes
1
answer
49
views
SQLServerAgent could not be started (reason: Unable to connect to server ‘------’; SQLServerAgent cannot start)
Firstable I would like to mention about this question.
I saw many post in Google about this incident but seems that every solution does not help me and that's why I make this question here.
So here is ...
- 21
0
votes
0
answers
24
views
Difference between "REPL-Checkup" vs. "REPL-Distribution" (for the "Category" inside Job Activity Monitor)
I am working on creating Replication inside SQL 2022.
I created Replication subscription/subscriber for two different pipelines.
I noticed that, under Category inside Job Activity Monitor, for one ...
- 251
0
votes
0
answers
33
views
SQL Server RML Utilities - installation on Windows Server 2022
Trying to run the RML Utilities on Windows Server 2022 & this is listed as compatible but when I run readtrace getting error 0x80040154 & the connection to the db server fails, which seems to ...
- 4,160
2
votes
0
answers
104
views
SQL Server left join to a view has different IO than the "same" inline left join
Left joining to a view produces higher IO than inlining the same left joins written directly into the outer query.
Below is a minimal example, executed on On SQL Server 2022 (RTM-CU14) (KB5038325) - ...
- 176
-2
votes
1
answer
88
views
How to partition into batch of records with max batch size limit
I want to batch the students into maximum batch size of 100, partitioned by School. Each teacher can have 12 students. Students of any teacher should not be split into different batches. No. of ...
0
votes
1
answer
40
views
Where can I view/manage the new SQL server 2022 server roles?
https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles?view=sql-server-ver16&redirectedfrom=MSDN#permissions-of-new-fixed-server-roles-in-sql-...
- 3,499
-1
votes
1
answer
82
views
Size of MDF file is not getting updated after adding transactional log file(s) in SQL 2022
I am working on migration of SQL Server from SQL 2016 to SQL 2022.
I have a process where after database is restored (to "Standby / Read-Only" state) from Full tape (from a third vendor), I ...
- 251
0
votes
1
answer
42
views
Given dm_db_missing_index_details output, what index(es) would support the following recommendations
FullyQualifiedObjectName
EqualityColumns
InEqualityColumns
IncludedColumns
table
[bool1], [bool2]
NULL
NULL
table
[bool1], [bool2]
NULL
[decimal1], [int1]
table
[bool1]
NULL
[int2], [int3]
table
[...
- 299
-2
votes
1
answer
95
views
Why is Database user in disabled state (member of a login group)?
Under the logins I have added a security group containing 5 users.
Then, I added one of these users into the database as a database user (db_datareader permission)
But against the user I can see a red ...
- 3,499
2
votes
0
answers
80
views
Database both joined and not joined to an AG
I have a Distributed Availability Group (DAG) from cluster Main to cluster Alt - both with two replicas.
The Main is the primary and Alt is a forwarder
One (of many) databases called MyDb in the AG on ...
- 5,512
1
vote
0
answers
45
views
SQLExpress 2022 Installation fails with the error "folder 'C:\SQL2022\Express_ENU\[DirSharedForCurrentVersion]' does not exist."
The following error has occurred:
The folder 'C:\SQL2022\Express_ENU\[DirSharedForCurrentVersion]' does not exist. The folder is expected to exist to set permission on it.
The installation fails near ...
- 111
0
votes
1
answer
80
views
SQL Server duplicate statistics
There were some tables without clustered index that, after some time, a clustered index was created on them.
I assume that with behaviour described up, the system will have statistics for table ...
Jan Přívratský
1
vote
0
answers
28
views
SQL Server replication dist latency: building up to 1 sec, then dropping to zero - why?
I'm testing a new transactional replication setup on a SQL Server 2022.
It's a push replication with only 20 tables, averaging 20 cmds/sec. It is working, but I'm seeing a strange wave pattern in the ...
- 11
1
vote
0
answers
86
views
Does NT Service\MSSQLSERVER represent the SQL engine irrespective of the actual service account?
My SQL server engine runs under mycompany\svc_sql_engine
However this account doesn't have any permissions on the SQL data and log drive folders/files.
The following account has the permissions:
NT ...
- 3,499
0
votes
0
answers
43
views
Causing SQL to prefer casted time seek in view query
We have ETL process executed by Kafka. It can only get and query by datetime2 date data type.
It generates queries like this:
EXEC sp_executesql N'SELECT *
FROM ViewName
WHERE ViewName.UpdateDate > ...
- 742
0
votes
1
answer
96
views
automating backups to S3 with SQL Server 2022
Not a DBA so please bear with me.
I have an EC2 instance running SQL Server 2022 and I'm trying to automate database backups to S3 using the native S3 connector in this version of SQL Server. The ...
- 3
15
votes
1
answer
1k
views
Broken Query Store
We have SQL Server 2022 Enterprise (16.0.4131.2) on Windows 2022 VM (8 vCPU cores) and there is an issue with the Query Store on one of the databases.
Below is the sequence of steps I can proceed to ...
- 508
0
votes
1
answer
126
views
Linked Server not connecting to SQL Server 2022 database
I have a SQL Server 2019 Enterprise database in a data center far, far away. I have a SQL Server 2019 developer edition database on my local laptop (A), and it is a Linked Server on the far away ...
- 1
1
vote
0
answers
110
views
Sql Server Availability Group database out of sync while status shows "Synchronized"
I have an availability group setup on Sql Server 2022. It was working fine for some days until some strange problem happened.
The workload that was querying data from secondary readonly replica, was ...
- 143
6
votes
1
answer
674
views
Tale of two servers. What could be limiting queries to running on two cores? MDOP set to 16
I have two servers.
A garbage picked, slapped together with duct tape and bubble gum. It is a mix of Dell R620 and Gen 8 HPE parts with 40 cores / 1.5 TB ram. Running ubuntu 22.04 and sql server 2022 ...
- 63
-2
votes
1
answer
39
views
Does sys.dm_exec_query_stats get retained post restore?
I have been given a database backup file.
After restore I ran the script in this db and it shows me the query-wise row counts:
SELECT qs.execution_count,
SUBSTRING(qt.text,qs....
- 3,499
2
votes
1
answer
160
views
How to use restored key to decode an encoded column in SQL Server?
In SQL server, I created a symmetric key and encoded a column as per An overview of the column level SQL Server encryption
use AdventureWorks2022
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SQLShack@1'...
- 145
0
votes
1
answer
210
views
Problem with many connections from RHEL 8.10 to SQL Server
I have simple script:
for i in {1..1000}
do
isql -v -k "Driver={ODBC Driver 17 for SQL Server};Server=mssqlserver,1282;Database=dbname;UID=user;PWD=xxx;Encrypt=no" >> aaa.log &
...
- 11
0
votes
1
answer
134
views
High CPU utilization on an idle SQL Server instance without user traffic [duplicate]
We have an SQL Server 2022 CU13 Enterprise on a Windows 2022 VM with 8 vCPU. The sqlserver.exe process shows a high CPU utilization between 10-15% even when there is no user generated load (meaning ...
- 508
1
vote
0
answers
44
views
How to audit server settings configuration changes other than view server state?
I want to capture the audit of server configuration settings changes.
For this I setup the SERVER_OPERATION_GROUP audit event.
This event is raised when Security Audit operations such as altering
...
- 3,499
1
vote
0
answers
31
views
Restoring of OLTP In-Memory DBs in SQL 2022 on Ubuntu really slow
We've started using OLTP In-Memory tables (schema durability only) in a small DB (DB properties show 137 MB of Row data, 1GB of In-Memory data, BAK size of 152MB) and the recovery times (either ...
- 11
0
votes
1
answer
50
views
import extended properties of sql server database from xls file
I have a list of extended properties for all databases in an excel file. How can I import that into extended properties of each database in sql server.
- 1
0
votes
0
answers
161
views
Unable to select "other data source" when creating linked server
I am unable to select the option for Other data source when creating a linked server in SSMS, because the option is completely grayed out. I have installed Microsoft OLE DB Driver (MSOLEDBSQL) for SQL ...
0
votes
1
answer
77
views
Login via SQLServer AlwaysOn "Listener" Failed (Error: 18456, Severity: 14, State: 5)
I have setup an AlwaysOn Availability Group on SQLServer 2022.
I have created a login on both replicas with same SID.
But I could not login via listener IP or DomainName via SSMS.
I could login ...
- 9
0
votes
1
answer
141
views
Error "No catalog entry found for partition ID ..."
When trying to delete disabled index or enable the same disabled index on one heavily utilised table, I am getting an error:
No catalog entry found for partition ID 72057594967228416 in database 6. ...
- 360
1
vote
1
answer
66
views
Query Optimizer Auto-updating stats before executing stored proc?
All of the evidence is stating that this is correct, but I need someone check my math on this...
I have a stored procedure that does the following:
Insert a status record into a table
Table trigger ...
- 45
0
votes
0
answers
296
views
Restore SQL Server database - getting error Msg 3287
I am restoring a .bak file to a local database for the data received from a vendor. The .bak file is around 75+ GB in size.
I do NOT know the server version using which backup is created. I do NOT ...
8
votes
3
answers
964
views
View doesn't recognise a change to an underlying table when an existing column is dropped and replaced with one with the same name but as computed
My understanding here is that when a View is created, the definition is stored in metadata sys.tables. i.e. sys.views.
Also, that if you use SELECT * FROM it will store the exact column names e.g. ...
- 513
2
votes
3
answers
336
views
High Memory Usage for SqlBulkCopy
I've started to use the QueryStore to monitor my App and one of the things I noticed is the unexpected high memory usage for what I thought a simple operation:
Which would mean the statement uses ...
- 27
1
vote
1
answer
70
views
How to configure listener when using read scale AG?
I have a cluster-less AG (read scale AG) without a listener.
Our network admin has set DNS entry to point to primary node. All applications use that DNS name for database connections.
Post failover ...
- 3,499