Skip to main content

Questions tagged [sql-server-2019]

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

Filter by
Sorted by
Tagged with
0 votes
0 answers
13 views

In extended events, event sqlserver.sql_batch_completed, client_connection_id do not match sys.dm_exec_connections for the same session_id

My goal is the following. I want to get client_net_address for every query traced (the same of sys.dm_exec_connections for matching session_id). Extended events system do not provides this straight. I ...
Maurice Pelchat's user avatar
-2 votes
0 answers
14 views

Minimum RAM and Processor Requirements for Server with 70GB Database and Data Warehouse (SQL Server 2019)

I’m seeking advice on the minimum hardware specifications required for a server to efficiently handle a database setup we have. Here are the specifics: Database Size: 70GB Number of Users: ...
Rizfan Hasnur's user avatar
0 votes
0 answers
17 views

Why is SQLPackage suddenly including every scalar function in my SSDT project for ALTER in my deployment?

I have a SQL 2019 SSDT project with a large number of scalar and table-valued functions. Suddenly yesterday, my Windows TFS (on prem) deployment project's SQLPackage scripting step is suddenly ...
John Hurtubise's user avatar
0 votes
1 answer
20 views

SQL Server Standard and Enterprise editions in AlwaysOn Availability Groups

Can two different versions of SQL Servers participate in AlwaysOn Availability Groups? I am specifically talking about SQL Server 2019 Standard and SQL Server 2019 Enterprise.
Frank Martin's user avatar
-3 votes
1 answer
44 views

SQL Server: query with many fields returning null; query with many fields commented out returning expected data [closed]

This question is concerning a "large" stored procedure in SQL Server. There is an issue with one of the SSRS reports I administer, which I have tracked down to the main stored procedure that ...
nomen's user avatar
  • 97
7 votes
1 answer
225 views

Filter by partition number when the table is partitioned by computed column

I've created 2 identical tables partitioned by an integer column. In one of the tables the column is computed, in the other table the column is not computed. When I query the table with computed ...
Artashes  Khachatryan's user avatar
0 votes
0 answers
73 views

SQL Server 2016 upgrade

We need to upgrade our SQL Server 2016 to SQL Server 2019. We are also using replication for our test and production environments. What my question is: what are the downfalls of upgrading from SQL ...
Andrea Jared's user avatar
1 vote
1 answer
89 views

Insufficient memory in resource pool 'internal'

I've had my SQL Server crash twice in the last week. The most recent was while executing a Database Integrity Check DBCC CHECKDB. The check failed and generated this error: There is insufficient ...
Don's user avatar
  • 171
0 votes
1 answer
49 views

Why does "Work Offline" turn itself on in SSIS?

I have an SSIS package that is currently working perfectly on my server. Whenever I open it in Visual Studio, "Work Offline" turns itself on for two of the SQL connections. This produces ...
J. Mini's user avatar
  • 445
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
0 votes
1 answer
94 views

how to convert nvarchar(max) or varchar(max) to xml in sql server

I am trying to convert msdb jobs messages to xml. I am using Ola Hallengren backup routines. when I run the following routine it works amazing and converts everything. now, if I change the number 3333 ...
Marcello Miorelli's user avatar
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
0 votes
0 answers
35 views

SQLAgent high number of handles

We have an Azure VM with SQL Server (SQL Server on Azure Virtual Machines). When investigating small disconnections from our signalR service running on that server we have seen that SQLAGENT.EXE has ...
juFo's user avatar
  • 299
-1 votes
2 answers
56 views

2x SQL Server 2019 in AlwayOn group - how to remove transaction log [duplicate]

We have a problem with our Microsoft SQL Server 2019. We have 2 servers in an Always On group with one database. Problem is the actual (real) database size which is about 1 GB, but it takes up around ...
user avatar
0 votes
1 answer
42 views

SQL Agent Job failing after uninstall of SQLPS and install of the new module

Uninstalled the old PS module and replaced it with the new SQLServer but now third step of system job syspolicy_purge_history, which is a Powershell step, fails with the following message: The error ...
Stackoverflowuser's user avatar
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
2 votes
2 answers
1k views

Is it possible to register two SSL certificates on SQLServer?

In SQLServer 2019, is there a way to install multiple SSL certificates in parallel? I have the following situation: The division the server belongs to was sold and therefore new DNS aliases were ...
Christoph's user avatar
  • 123
0 votes
1 answer
125 views

Alternate T-SQL Approach for Creating a PATINDEX Funtion to Find Multiple Occurrences of a Pattern

I'm working on a project to parse through numerous varchar max fields for multiple occurrences of specific patterns. For example, a cemetery plot was originally sold on 2/14/1928, then deeded on 7/4/...
JasonVL's user avatar
0 votes
1 answer
48 views

Preferred seconday backup in AG - what about the primary ? Shows no backups taken

I did setup the Ola Scripts sticking to https://www.brentozar.com/archive/2015/06/how-to-configure-alwayson-ag-backups-with-ola-hallengrens-scripts/ Options are: @Databases = 'USER_DATABASES', @...
Thomas D's user avatar
4 votes
2 answers
159 views

Index seeks and predicate push down

I have a query of the following form: IF EXISTS ( SELECT 1 FROM ( SELECT RowID, OETID FROM @InMemoryTableTypeTable i UNION ...
Dan Def's user avatar
  • 163
0 votes
1 answer
44 views

How to Increase The maximum number of allowed pending SQLServerAgent notifications

I have a large CI pipeline, it runs a series of deployments against a large set of databases(75 to be specific) on a (mostly) single SQL Server(2019). As part of this CI pipeline, and post deployment ...
David Rogers's user avatar
0 votes
1 answer
48 views

How is Memory Grant Desired Memory calculated

I have the following query in the StackOverflow2010 database: SELECT Id, OwnerUserId, CreationDate, Body FROM Posts ORDER BY LastEditorDisplayName The execution plan shows me ...
SE1986's user avatar
  • 1,990
4 votes
2 answers
81 views

How do I fully qualify event predicates for [backup_restore_progress_trace] Extended Events?

I'm attempting to add an Extended Events session to trace backup completion events. I'm doing this a precursor to performing RESTORE HEADERONLY ... LOADHISTORY on the secondary node of an Availability ...
Hannah Vernon's user avatar
  • 70.2k
0 votes
1 answer
27 views

Invoke-PolicyEvaluation with the new SqlServer module generates security error

Started getting error messages after uninstall of PS and install of the new module SqlServer when evaluating policies on remote servers using Registered Servers and Invoke-PolicyEvaluation cmdlet. It ...
Stackoverflowuser's user avatar
6 votes
1 answer
105 views

Running sp_repldone on a secondary node of an Availability Group with @reset = 1 not working

I have a two-node SQL Server 2019 Availability Group with a database that has CDC enabled for several tables. The CDC is used to keep track of transactions that have not been captured for population ...
Hannah Vernon's user avatar
  • 70.2k
0 votes
1 answer
54 views

High free memory and Lazy Writer triggered by IndicatorsPool

I'm experiencing a strange issue on one of our SQL Server (SQL Server 2019 Enterprise CU18). The server has 12 cores and 96GB physical memory and is also part of a failover cluster. Max memory has ...
Frederik Vanderhaegen's user avatar
0 votes
3 answers
190 views

Can using UPDLOCK and ROWLOCK help me fight of deadlocks in my situation?

I have a table tblImportData that looks like this CREATE TABLE [dbo].[tblImportData] ( [ImportDataID] INT IDENTITY(1,1) NOT NULL, [ImportFileNumber] VARCHAR(50) ...
GuidoG's user avatar
  • 129
1 vote
1 answer
59 views

Getting sum for each day based on most recent timestamp entry for each entity

I have a table that contains an ID, timestamp, and integer. Each row is a "snapshot" of the entity at a given time: TableA ID timestamp num A 2024-02-04 5 B 2024-02-04 3 C 2024-02-07 8 ...
Timothy Fisher's user avatar
0 votes
1 answer
27 views

What is best practice when performing manual failover in a 2 node FCI

Using SQL Server 2019 with 200+ databases in a 2 node FCI, what is considered best practice regarding SQL Server Agent when performing manual failover for SQL Server CU patching. Should we manually ...
H. Engmark's user avatar
0 votes
0 answers
64 views

What does dollar sign $ mean in a view definition? [duplicate]

I wanted to see the definition of system view sys.configurations. I ran query as follows: EXEC sp_helptext 'sys.configurations'; and I get the result: CREATE VIEW sys.configurations AS SELECT ...
user avatar
0 votes
1 answer
89 views

SQL Server Assertion error On Availability Groups

We are having constant memory dumps from our SQL 2019 cu21 instance. We upgraded this instance to cu26 (latest patch) hoping this can resolve the issue but it did not. The error log is filled with ...
SqlData's user avatar
  • 39
3 votes
1 answer
257 views

What exact error messages do UMPPC*.DLL and SCRIPTCONTROL*.DLL cause SQL Server Agent when it fires SSIDB packages?

Microsoft's maintains a list of bad DLLS. This is one item on that list (emphasis mine) UMPPC*.DLL and SCRIPTCONTROL*.DLL These DLL files are loaded in to the address space of SQL Server related ...
J. Mini's user avatar
  • 445
-4 votes
1 answer
59 views

What reasons are there to not enable the service broker? [closed]

I like to enable the Service Broker on all of my non-system databases. I'd probably put it in model, but I recall something preventing that. What reasons are there, no matter how far-fetched, for not ...
J. Mini's user avatar
  • 445
-1 votes
1 answer
62 views

How is basic availability group able to auto failover without a cluster?

I have recently learnt that the SQL server standard edition's basic availability group doesn't require a windows cluster. Basic AG is considered equivalant to the deprecated mirroring technology. Like ...
variable's user avatar
  • 3,644
0 votes
1 answer
58 views

Does client timeout send the execution of a stored procedure from the TRY block to the CATCH block?

What happens when a client timeout occurs during the execution of a stored procedure that raises XACT_ABORT and wraps its body in TRY/CATCH blocks? I am looking at a bunch of procedures that generally ...
Betty Liv's user avatar
0 votes
1 answer
54 views

SQL Server Agent Alert to Detect deadlocks

Some SQL Server Agent jobs failed recently with deadlock errors. I wanted to keep an eye on the issue and created a monitoring alert in SSMS. However, the event never fires.. which seems wrong. Alert ...
wp78de's user avatar
  • 317
0 votes
0 answers
24 views

How to By-Pass sysadmins or DB-owners from RLS policy

I have written RLS policies which will filter the users(impersonated from Tableau) logged in against a USER_ACCOUNT table inside the database, so in short if the logged in user have an entry in ...
vikranth reddy's user avatar
0 votes
0 answers
18 views

Creating a server audit specification through SSMS: what is the correlation between Audit Action Type and other columns?

Having gone through https://learn.microsoft.com/en-us/sql/relational-databases/security/auditing/create-a-server-audit-and-server-audit-specification?view=sql-server-ver15 where it explains how to ...
Betty Liv's user avatar
0 votes
1 answer
77 views

How does the arithmetic for how many bytes are saved by row-compressing a datetime add up?

Everything that follows is quoted from the datetime row here. I have broken the quote to give commentary on my understanding. Uses the integer data representation by using two 4-byte integers. The ...
J. Mini's user avatar
  • 445
0 votes
0 answers
29 views

How can I attempt to row-compress a table type?

The documentation for row compression states that it has "No effect." on table types. This suggests that it's possible to attempt it, but ineffective. How can it be attempted? I find it ...
J. Mini's user avatar
  • 445
0 votes
1 answer
67 views

Why does data compression not affect backups?

The documentation is very clear on this point Compression doesn't affect backup and restore. but how is that possible? Surely, if my table is smaller (due to compression), then that should have some ...
J. Mini's user avatar
  • 445
0 votes
1 answer
41 views

Will NVARCHAR(max) columns benefit from row compression?

I have a table that I believe owes most of its size to a huge NVARCHAR(max) column. I do not know how to test where most of its size comes from. Regardless, would such a table benefit from row ...
J. Mini's user avatar
  • 445
0 votes
1 answer
44 views

What happens when you try to use more than 32 GB of memory-optimized data in a database on Standard Edition?

In SQL Server 2019, you cannot put more than 32 GB of memory-optimized data in a database on Standard Edition. What happens if you try anyway? I'd try it myself, but I don't want to risk discovering ...
J. Mini's user avatar
  • 445
0 votes
0 answers
55 views

AWS RDS SQL Server 2019 Standard edition, everyday 3 am getting errors "insufficient system memory in resource pool 'internal' to run this query"

I have AWS RDS SQL Server 2019 Standard edition with 128GB RAM, everyday 3 am getting errors insufficient system memory in resource pool 'internal' to run this query There are few jobs that run at a ...
Praveen Suhalka's user avatar
2 votes
1 answer
119 views

SQL Server 2019 Memory-Optimized Tempdb Metadata doesn't eliminate PAGELATCH_* waits on GAM

We are using SQL Server 2019 with a third party application that makes heavy use of the tempdb database. And we have lot of PAGELATCH_xx waits on tempdb. Memory-Optimized Tempdb Metadata is enabled. ...
edo1's user avatar
  • 121
1 vote
1 answer
105 views

Non-yielding Scheduler Error in SQL Server 2019 on Docker with CDC Enabled

I have deployed an instance of SQL Server 2019 on Kubernetes, and I’ve enabled Change Data Capture (CDC) on the SQL server. Periodically, I encounter a “Non-yielding Scheduler” error in the logs, ...
Макс Самшит's user avatar
1 vote
0 answers
53 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
0 votes
0 answers
34 views

SSRS scale-out deployment, adding third AG replica

We have two SQL Server 2019 Enterprise instances each running on Windows Server 2022 Standard (let’s call them DB01 and DB02). We have two SSRS servers (RS01 and RS02) each running SSRS 2019. They’re ...
S M's user avatar
  • 196
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
0 votes
0 answers
27 views

Statistics update - worthwhile on a high activity table or not?

I have a table with 1.5 billion records which is sampled at 1%. Said table receives over 200-300k insert/update/deletes per hour. Within a couple of hours, the statistics are auto updated as it ...
DB-Ay's user avatar
  • 11

1
2 3 4 5
21