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
0answers
41 views

Disconnected replica when recovering from a forced failover

I'm evaluating disaster recovery using read-scale availability group on SQL Server 2019 and Windows 2019. The cluster type is NONE. The cluster works fine in general even when restarting replicas. The ...
0
votes
1answer
82 views

Can we enable tls 1.0 on MSSQL Server 2019 on database level? if yes then how?

i am running sql server 2019 on Linux box and trying to connect using a Windows server 2016 client, how can we enable TLS 1.0 for SQL Server 2019 on the Database/instance level. TIA.
0
votes
2answers
83 views

SQL Server 2019 Database stuck In Recovery state after restart [duplicate]

SQL Server was restarted by mistake, when it came online, database came back in "In Recovery" mode. Check from error it says "Recovery of database 'DB1' (5) is 8% complete (...
1
vote
3answers
329 views

“Cannot continue the execution because the session is in the kill state” Error?

We use SQL Server 2019 Standard edition on Windows Server 2019. We have 200 users and thousands of connections at the same time. SQL Server 2019 raises an error Cannot continue the execution because ...
0
votes
1answer
68 views

What is the best way to store optional values in table?

I have a large table that will be partitioned (about 300 million rows). There are currently 57 columns in the table. At the moment we also have an 'Option' column, which is to hold values ​​that would ...
4
votes
2answers
130 views

Cannot reclaim Index Unused Memory in In-Memory OLTP

Steps to reproduce the problem Create a database with memory-optimized filegroup and container Create schema only in-memory table with nonclustered pk Simulate insert and delete activity. My result is ...
8
votes
1answer
211 views

2019 Enterprise - NOLOCK without NOLOCK, page latch time-out on tempdb and finally a dump

We have a very strange problem with a new SQL Server 2019 on 2 new physical machines: Infrastructure: Starting a new installation of SQL Server 2019 Enterprise (15.0.2000.5 / X64 on Windows Server ...
0
votes
1answer
230 views

How to run Sql Server stored procedure in Async mode?

Is there a way to run SQL Server 2019 stored procedures with parameters asynchronously? Calls to this SP will be made several times a day, i.e. this is not one time requirement. Service Broker can be ...
-2
votes
1answer
39 views

SQL Services are off but can access and query from other server on the same domain. Why? [closed]

Using configuration manager, I disabled the SQL Server Services for a SQL Server 2008 R2 instance. Yet, I can still connect to the instance from its replacement server, a SQL 2019 box, and query the ...
0
votes
1answer
46 views

What is the best way to add optional update in procedure in transaction?

Currently I have a procedure that performs 4 operations in the Transaction: Update record in table A Update record in table B Insert record into table C Delete record from table D The first ...
2
votes
2answers
117 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
0answers
209 views

Internal error: An expression services limit has been reached [duplicate]

In debugging a problem the application is throwing the error: Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to ...
0
votes
1answer
39 views

How can I tell if TempDb is too big?

Let's say that someone wants to follow sage advice and properly setup TempDb and TempDb's log. But let's also say that the chosen sizes are almost certainly too big/aggressive. E.g. pretend we've got ...
2
votes
2answers
108 views

Check Orders that can be delievered with reducing quantity from stock

Just like any retail business we have an Orders table and an Inventory table. What I am trying to do is to check Orders for which we have enough stock available to dispatch. A few things I need to ...
0
votes
1answer
88 views

Query tables with 400 million records fast [closed]

Table: Customer Data Rows: 400 million Indexes: Non-clustered (as per queries) Issue: Index Scan happening with most queries, 400 million scan take too long to complete Need advise on how to address ...
0
votes
1answer
95 views

Simple query on sys.dm_db_partition_stats is running for 30 minutes

Simple query on sys.dm_db_partition_stats on user database is running for close to 30 mins where as it used to complete in less than 5 seconds earlier. Query is just to fetch the right partition for ...
1
vote
0answers
96 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 ...
0
votes
0answers
12 views

How to setup transaction in partitioned table with Always On?

I am currently creating a project that will use a table that will contain around 40 columns (including varchar (max)) and will be partitioned. About 200-250 records per second are to be inserted into ...
0
votes
2answers
172 views

SQL Server 2016 -> 2019 AlwaysOn Rolling Upgrade Migration

I have a migration concept with an AlwaysOn configuration from SQL Server 2016 (on windows server 2016) to SQL Server 2019 (on Windows Server 2019) which are in different sub-networks. So with ...
0
votes
0answers
36 views

Strict in OPENJSON not work well

I've a table for save sub tasks data. some time we change status of sub tasks, and i need both of them id and status in @JsonStatus parameter. I wrote a stored procedure like below : Declare @...
4
votes
1answer
211 views

Not able to restore a backup to SQL Server 2019

I am trying to a restore a SQL Server from a .bak file and always get the same error : The database was backed up on a server running version 15.00.2000. That version is incompatible with this server,...
6
votes
1answer
116 views

Incurs_seek_penalty column of sys.dm_os_volume_stats

A new column, incurs_seek_penalty, was introduced in the SQL Server 2019 sys.dm_os_volume_stats DMF. This tinyint column is not currently documented. Does anyone have information about the enumerated ...
1
vote
1answer
699 views

Cant install SQL Server 2019

I have a single node Windows 10 Pro machine, 64 bit, with 32GB Ram and an i7 processor. When I try to install the developer edition of Sql Server 2019, it fails installing the Database engine with a ...
1
vote
2answers
68 views

Provisioning SQL Server 2019

I'm provisioning a SQL Server 2019 build and have been allocated a virtual server running Windows Server 2016. It has 4 virtual processors and I have 410Gb of drive space to work with. My build has to ...
0
votes
1answer
18 views

How to connect to Microsoft Dynamics Rms 2.0 Running sql2008r2?

Would anyone know anything about Microsoft Dynamics Rms 2.0 (2012)? We are having trouble connecting our sql server to the client side cashier (windows 10 pro) this software sunset in 2016 I believe ...
0
votes
2answers
40 views

Windows Server 2019 and SQL Server Backups issue?

I have an issue with SQL Server that is really driving me nuts so I wanted to ask if anyone has any suggestions. I have a program that does a daily backup of the SQL Server database. This has worked ...
1
vote
1answer
28 views

SQL Server 2019 UPDATE Statement SET to function does not execute the function again for each row

Here is the scenario. I have a local SQL Server to which I have restored the live SQL Server databases. In order to be GDPR/CCPA compliant, I must anonymize the PII in the local server's databases. I ...
1
vote
2answers
167 views

SQL Server 2019 standard edition CDC with SSIS standard edition

The primary question is: Are there any methods for using SQL Server standard edition CDC for data synchronization to cloud based non SQL server databases that don't require use of SSIS Enterprise ...
0
votes
0answers
76 views

Power BI Report Server keeps asking for credentials locally

I've installed PBIRS on my laptop to do some development. Each time try to connect it prompts with dialog wanting me to login. If I try and connect from a remote PC using the same domain login, it ...
2
votes
1answer
33 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 ...
0
votes
1answer
39 views

VS 2019 to SQL 2019 deployment issue - SELECT permission was denied on the object 'server_principals' [duplicate]

Good day, We are currently facing an issue with SSIS deployment from Visual Studio 2019 to SQL Server 2019. Using the Visual Studio "Integration Services Deployment Wizard" to deploy even a ...
2
votes
2answers
19 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 (&...
0
votes
2answers
38 views

SQL Server Basic Availability Group: config-only replica?

In SQL Server Basic Availability Groups docs, I found this interesting line: Basic Availability Groups for SQL Server 2017 on Linux support an additional configuration only replica. What is a ...
1
vote
1answer
73 views

SQL Server - Why am I getting TCP connections without sessions?

I enabled SSL with Forced Encryption enabled, using a self-signed certificate. Then I've set up automated monitoring to detect not secured connections (seems redundant, I know, but bear with me here), ...
2
votes
0answers
93 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 ...
4
votes
0answers
141 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 ...
3
votes
1answer
47 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 ...
0
votes
1answer
20 views

What is causing BETWEEN to be replaced with <= and >=?

We have a Visual Studio solution for our SQL Server 2019 database, and it is stored in a git repo. One table has a couple of check constraints using the BETWEEN operator, like this. CONSTRAINT ...
0
votes
0answers
21 views

Primary Key and Unique Constraints in Partitioned Table

I am currently creating a project that applies to a table that contains about 40 columns (including varchar (max)) and will be partitioned. About 200-250 records per second will be inserted into the ...
0
votes
2answers
32 views

Default trace vs auditing in SQL Server

Currently I am working in an environment that has the default trace enabled and I would like to propose that we add SQL server auditing to our environment. I'm looking for some points to put forward ...
0
votes
2answers
88 views

I/O is frozen on database DBName. No user action is required

We are on Windows Server 2019 on Azure VM, with SQL Server 2019 Enterprise CU4 installed. SQL Server differential backup jobs are failing intermittently. I have looked into the SQL Server error log ...
3
votes
2answers
321 views

After upgrade to SQL Server 2019 some stored procedures fail

We just upgraded from SQL Server 2008 R2 to SQL Server 2019(Compability lvl 150). We have two different stored procedures that started failing after the upgrade, with error messages like this: Msg ...
4
votes
1answer
295 views

User-defined function returns first character only although RETURN VARCHAR(10) is defined?

Everyone, I cannot figure out what is missing in the below user-defined function: CREATE OR ALTER FUNCTION ufn_GetSalaryLevel(@Salary money) RETURNS VARCHAR(10) AS BEGIN IF (@Salary < 30000) ...
4
votes
0answers
116 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%,...
4
votes
1answer
74 views

How to control Segmentation min/max data_id on a non-clustered ColumnStore index

Given a simple row-based table without a PK but with a row-based clustered index like so: create clustered index [CX_PropertyValue] ON [dbo].[PropertyValue] ([PropertyId], [Value]) Then I wish to ...
0
votes
0answers
49 views

HA AlwaysOn and SMB File Share

I am planning on taking our current single 2014 SQL server and rebuilding it to a 2019 SQL Server HA AlwaysOn Cluster. The issue is, the listener IP and hostname has to stay the same and many years ...
0
votes
0answers
16 views

Windows Server 2019 non-owner node cannot see Cluster Name or IP

I am trying to build a SQL Server AlwaysOn Availability Group with 2 nodes in Azure. I have created 2 SQL Server VMs and Managed to create a Cluster with both Nodes as members of that cluster.  I ...
0
votes
2answers
254 views

SQL Server 2019 Always On using Distributed Network Name

We're running a couple SQL servers in Azure that are set up with an Always On availability group and Windows Failover Clustering. The servers are Windows 2019 and we're running SQL Server 2019. When ...
2
votes
1answer
32 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 ...
0
votes
1answer
33 views

Getting the exact and closest Search using CONTAINS/CONTAINSTABLE in SQL

I have a table that contains 30 000+ records named as ProductCodes that contains a column 'Value'. I have made a query that'll return the records matching and containing a passed String using ...