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
1 answer
110 views
+100

When patching SQL FCI, how to ensure there will not be a failover to the node being patched?

This is about patching a 2 node SQL FCI. I'm referring to both OS patching and SQL patching. Say node1 is the current active node. When I execute the KB patch on node2, how do I ensure that there won'...
0 votes
2 answers
3k views

Database was shutdown due to error 9001 in routine 'XdesRMFull::CommitInternal' [closed]

I have encountered this situation on one of our production server. The database came backup and DBCC Check reported no errors. Also, there were no other errors logged in Event Viewer. I know I am ...
1 vote
2 answers
2k views

SQL Server 2019 using Linked server to Oracle 19c (19.9) using Oracle 19c client on Windows - DBCOLUMNFLAGS_ISNULLABLE error

Having issues selected data for some Oracle tables over the linked server from SQL Server: select type, count(*) from RMTDB..JOB1308.HOST_INTERFACE group by type order by 1 desc; Msg 7356, Level 16, ...
0 votes
1 answer
490 views

How to manage patching/reboot of secondary node to prevent auto failover when file share is unstable?

In a 2 node FCI cluster with file share witness, assuming the file share is unstable (inaccessible) due to network issues. Case 1: File share is down (inaccessible) and during this time I patch/reboot ...
3 votes
1 answer
123 views

Is sys.dm_os_memory_clerks a good source to use when setting max server memory (MB)?

According to sys.dm_os_memory_clerks, the total RAM usage of my server was 30 GB. Using this information, I set max server memory (MB) to 31,000. I was shocked to then see sys.dm_os_memory_clerks ...
0 votes
1 answer
25 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 ...
2 votes
1 answer
298 views

How should MAXDOP be configured on stacked instances?

There is good general advice for how to configure Maximum Degree of Parallelism (MAXDOP). However, I am in a special circumstance. There are two instances on my box. If I have 16 cores, how should I ...
0 votes
0 answers
19 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 ...
-2 votes
0 answers
17 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: ...
3 votes
1 answer
191 views

why sys.dm_db_persisted_sku_features shows results that compatible with Standard edition?

I have SQL Server 2016 Enterprise edition, I restored the DB's to SQL Server 2019 Enterprise edition. But I want to check if I can restore it in SQL Server 2019 Standard. if I run the DMV sys....
-3 votes
5 answers
257 views

When should I inspect the Error Log?

I've had a varied career, but I have never found relevant information in the SQL Server Error Log. Under what circumstances should I consider inspecting it?
0 votes
1 answer
24 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.
0 votes
2 answers
829 views

SQL Server replication between standard and developer editions

I have one SQL Server Standard (2019), I want to add one server for development (not used for production so the Developer edition); is it possible to replicate data from the Standard edition? What is ...
-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 ...
7 votes
1 answer
232 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 ...
1 vote
1 answer
548 views

SCCM Database on the SQL Server instance shared with other applications

My company uses SCCM with a dedicated SQL Server instance that is using version 2016. I noticed that this VM is completely idle for most of the time. I barely see any CPU activity, wait statistics ...
1 vote
1 answer
96 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 ...
0 votes
0 answers
74 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 ...
user avatar
0 votes
1 answer
53 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 ...
2 votes
1 answer
552 views

What is the best choose for PK in Partitioned Table?

I have one large table that is partitioned (table name: Trans). At the moment this table is to be created on 32 partitions. This table will contain approximately 300 million records and data older ...
0 votes
1 answer
100 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 ...
4 votes
4 answers
2k views

Actual and Estimated rows differ greatly

The full Actual plan is here. Prior to executing the plan (because I'm debugging a poorly functioning plan) I have this block of variable assignments: DECLARE @Days INT = 180 DECLARE @DateRangeFrom ...
1 vote
1 answer
157 views

How can I verify if Read Committed Snapshot Isolation is causing application problems that Read Committed would not?

Suppose that I've turned on RCSI and my server is doing just fine; There have been no spikes in resource usage and the increased load on tempdb has been minimal. This is all good, but how can I tell ...
1 vote
1 answer
37 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 ...
2 votes
0 answers
52 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 ...
0 votes
1 answer
358 views

ADF: TempDb to user table very slow

I have a Azure Data Factory Data Flow that writes ~250 Mio. rows (~100 GB) to a MS Sql Server 2019. Currently, I have set the 'Use TempDB' option to true: The first part of the job, i.e. writing all ...
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 ...
0 votes
1 answer
317 views

How to enhance performance when compare two table feature value to run faster?

I work on sql server 2019 i face issue when compare feature values between two tables Based on RecomendationId . it take too long time to display result time that take it 5 minute . total rows on ...
-1 votes
2 answers
60 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 ...
0 votes
1 answer
46 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 ...
1 vote
1 answer
52 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 ...
1 vote
1 answer
662 views

Bulk operation prerequisites for non-sysadmins

A developer need to use OPENROWSET function. The steps I did to allow him: Enable ad hoc distributed queries Server Configuration Option sp_configure 'show advanced options', 1; RECONFIGURE; GO ...
0 votes
1 answer
417 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 ...
0 votes
2 answers
69 views

SSIS Package hanging with two Constraints pointed at one Task: Can you have multiple constraints pointing to one Task?

In the example I pasted in the image, I have 5 Tasks. I don't think it's important what kind of Tasks I'm using--but I could be wrong, of course--because the main issue seems to be how the constraints ...
1 vote
2 answers
344 views

Automatic Failover for Basic Availability Group

This will be my first project with Basic availability group so I thought would check before implementing . As per Microsoft documentation below it says we will need to have Software assurance for ...
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 ...
1 vote
1 answer
118 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, ...
0 votes
1 answer
129 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/...
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 ...
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 ...
0 votes
1 answer
50 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', @...
4 votes
2 answers
167 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 ...
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 ...
1 vote
1 answer
752 views

SQLCMD Using Powershell Gives No Response

I have SQL Server 2019 Standard installed on my Server 2019. I also have installed the SQL CMD Line tools V21.1.18246. When I open my PowerShell in Administrator mode and do a sqlcmd -? the command ...
0 votes
1 answer
57 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 ...
0 votes
1 answer
331 views

Cannot find sqlserver service

I installed Sql Server 2019 in Windows PC. In Sql Server Configuration Manager into SQL Server Service I stopped all the instances. But still I can loggin to Sql Server via SSMS.why is that? Where is ...
0 votes
1 answer
50 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 ...
0 votes
1 answer
45 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 ...
0 votes
1 answer
28 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 ...
6 votes
1 answer
109 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 ...

1
2 3 4 5
21