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
29 views

Diagnose the exact cause of WAIT_ON_SYNC_STATISTICS_REFRESH

We are currently facing query timeout issues with our online API . The related request is an ad hoc select query referencing two tables. In Redgate, we see that each time the request is blocked, it's ...
user avatar
0 votes
0 answers
29 views

The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction

Since last week, one of our jobs started failing with this error: The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction It's a stored procedure. It ...
user avatar
  • 7,104
0 votes
2 answers
74 views

I can't create a Database after I uninstalled SQL Server and then reinstalled SQL Server

I uninstalled SQL Server and then reinstalled SQL Server on my laptop and now when I try to create an old database from my first installation called 'SSIS' and I get the following error: Msg 5170, ...
user avatar
1 vote
1 answer
103 views

Find and show special character codes from nvarchar column

I want to know which special characters (line breaks, tabs, etc) are in my column of type nvarchar. I know how I can select which rows contain a specific character. e.g. SELECT * FROM your_table WHERE ...
user avatar
  • 229
0 votes
1 answer
46 views

REPLACE() function accepts and returns non-character data types

The Microsoft documentation indicates that the REPLACE() function accepts and returns only character data types. However, these work without error: SELECT REPLACE(123456, 'X', 'Y') ; SELECT ...
user avatar
0 votes
0 answers
19 views

Restoring 2008 R2 db backup to 2019 causes invalid allocated space/free space

3 occasions so far I've been working with legacy 2008 R2 databases that need to be restored to SQL 2019 instances. Each db is at compatibility level 100 on the old instance. Compressed backups being ...
user avatar
0 votes
1 answer
67 views

SQL Server can't create sysname type colunmn

I'm trying to create a new table based on an existing table. In the existing one, there are two columns that are sysname data type. But when I'm trying to create the new one with sysname, SSMS shows ...
user avatar
0 votes
1 answer
47 views

MSSQL Listener issues

I have a problem with my SQL production cluster that I am really struggling to figure out. Recently, we had to add a secondary NIC to our production cluster for SQL because in AWS land, most instance ...
user avatar
0 votes
0 answers
16 views

SSIS suddenly stopped logging package execution details in SSISDB

Brief Summary: A few days ago in production, SSIS suddenly stopped logging in the internal.operation_messages_scaleout, internal.event_messages_scaleout, internal.event_message_context_scaleout, and ...
user avatar
0 votes
1 answer
39 views

New DB: Best settings for a BI staging area

I would like to know if there are specific recommended settings when creating a new DB (in SQL Server) which will serve as a staging area DB for BI data flow. In terms of DB use behavior this means: ...
user avatar
  • 9
0 votes
0 answers
25 views

insert missing records into table has sequence id

I am using SQL server 2019, enterprise-production- Before the deployment, I took backup for the DB Let's call it BK1 After the deployment, we had to rollback Before rollback, I took another backup ...
user avatar
  • 31
0 votes
1 answer
31 views

SQL Server Upgrade + BPE

One of the SQL instance has BPE( buffer pool extension) enabled. Would this have an impact ( or any issues ) if instance is upgraded from SQL 2014 to SQL 2019. I went through the MS docs and does not ...
user avatar
4 votes
1 answer
411 views

Storing special characters (German, French, Spanish) in SQL Server result in weird strings

I'm trying to store the German ß in SQL Server, but it results in ß. For example "Gleimstraße" is stored as "Gleimstraße" But this also happens for other characters: "König-...
user avatar
  • 229
0 votes
0 answers
26 views

Can't start SQL Server Agent in SQL Server 2019 in Docker Linux

I installed SQL Server 2019 from Docker Hub in Docker Desktop on my Intel Mac using docker pull mcr.microsoft.com/mssql/server:2019-latest. In SQL Server Management Studio (running on Windows 10), SQL ...
user avatar
0 votes
1 answer
30 views

SSIS Package is writing corrupt data, but only when run as a Job

Our environment is the following: SQL Server 2019 (15.0.4153.1) + SQL Integration Services (15.0.2000) We have a DTSX Package that uses a OLE connection manager to connect to an SQL Server to read ...
user avatar
  • 126
7 votes
2 answers
645 views

ReadOnly queries on secondary server do not return correct values

I have two servers A and B. Both are in an Always On Group. The B server is configured for ReadOnly requests. This works fine so far, but recently there is a problem for queries for a service. The ...
user avatar
-1 votes
2 answers
108 views

Does ASYNC_NETWORK_IO wait types result in thread locking until entire result is consumed by client application?

ASYNC_NETWORK_IO wait types are mostly caused because the client application cannot process the data that arrives from SQL Server fast enough. As the query executes, and as the results start getting ...
user avatar
  • 2,375
-2 votes
0 answers
71 views

How to determine whether there is a need to increase the number of CPU's?

I am running a SQL server with 32 logical cpus (4 NUMA, 8 processors on each node). There are queries with complex calculations. During their execution the cpu usage %processors time ranges from 80% ...
user avatar
  • 2,375
0 votes
1 answer
39 views

What is the relation between renaming the computer and sp_dropserver/sp_addserver?

I have default SQL server instance (not a named instance). have changed the SQL server computer name, and not run the following commands: sp_dropserver 'oldname'; sp_addserver 'newname', 'local'; ...
user avatar
  • 2,375
-3 votes
0 answers
26 views

Sign all Stored Procedures with a Certificate. Advisable?

As our company is going about increasing our security on all our databases. The question proposed to me was to sign all our Stored Procedures with a certificate. Anyone have any thoughts on this? I ...
user avatar
-1 votes
1 answer
59 views

Understanding read uncommitted isolation level's relation with clustered index and page splits

Missing one or more rows that were not the target of update When you are using READ UNCOMMITTED, if your query reads rows using an allocation order scan (using IAM pages), you might miss rows if ...
user avatar
  • 2,375
0 votes
0 answers
26 views

Setting up an HTAP Environment

I've had success in the past with setting up a CIX/PK on an identity column and then setting up supporting NCIX and NCCsIX on tables to help support an HTAP environment. I'm curious, going forward ...
user avatar
0 votes
1 answer
27 views

Issue with idle connections/open transactions from Python to SQL Server

I am having issue with Python code leaving idle connections to SQL Server while having open transactions in them. Background Recently, we had some major blockings on the SQL Server database due to ...
user avatar
  • 1
6 votes
2 answers
559 views

Which execution plan is stored in the plan cache?

When query is executed, SQL server will produce a list of query plans and heuristically choose the plan with lower cost. The chosen plan will be stored in the plan cache for subsequent use when it ...
user avatar
  • 2,375
-3 votes
1 answer
57 views

How to get `dbcc checkdb` report when run via agent job?

When I run dbcc checkdb via management studio, then I get the full report as output. However when I run dbcc checkdb via sql agent job, then I think that the job status will either show as success if ...
user avatar
  • 2,375
2 votes
2 answers
77 views

How to get the specific queries that are contributing to high lock wait times?

I am looking at SQL lock wait time perf mon counter and it is indicating high value ranging from 5 to 20 seconds during certain times of the day. I have setup extended events which gives me the T-SQL ...
user avatar
  • 2,375
-1 votes
2 answers
46 views

How to estimate cpu based on peak rate of query requests?

By default, SQL Server allows a maximum of 32767 concurrent connections which is the maximum number of users that can simultaneously log in to the SQL server instance. I am developing 10 web apis ...
user avatar
  • 2,375
-2 votes
1 answer
334 views

Is there a deterministic relation between the page life expectancy and disk IO throughput?

Start with Page Life Expectancy, which should be well above the 300. This tells you how long pages are staying in the buffer pool, and a value of 300 equates to 5 minutes. If you have 120GB of buffer ...
user avatar
  • 2,375
-3 votes
1 answer
58 views

Does max SQL memory include SQL AG memory requirements?

I have sql server 2019 enterprise on windows server 2019, with 30 databases totalling 500GB size. RAM is 100GB. Of this 94GB is allocated as Max sql server memeory. So 6GB remains for OS. There is ...
user avatar
  • 2,375
-5 votes
1 answer
49 views

Does enabling readable secondary in the availability group put any load on the primary server's tempdb?

Enabling readable secondary will result in addition of 14byte extra information to each row in the primary as well as secondary. This will also automatically enable the snapshot isolation level on the ...
user avatar
  • 2,375
-1 votes
2 answers
39 views

How does the read committed snapshot isolation level relate (link) the row in tempdb to the original row?

I understand that the read committed snapshot isolation level holds the row versions in the tempdb. I enabled this feature on a test database and I was expecting to see a new column (guid) get ...
user avatar
  • 2,375
4 votes
3 answers
893 views

How to recover from a SQL server log file drive crash?

Assuming data files are on D: and Log files are on E: Say the E drive crashes and the log files (.ldf) are lost. I attach a new empty disk to the E: and start the sql server. Upon startup, SQL Server ...
user avatar
  • 2,375
-1 votes
1 answer
47 views

What is the reasoning behind allocating the OS 1GB for each 4GB of RAM? [duplicate]

Link: https://codingsight.com/understanding-the-importance-of-memory-setting-in-sql-server/ Let us now briefly look at the techniques to size the memory. 1 GB of memory reserved for Operating System ...
user avatar
  • 2,375
0 votes
0 answers
40 views

xp_sqlagent_enum_jobs - Unable to enumerate Jobs due to failure to obtain serialization object

I a procedure whose job is to ensure that certain SQL agent jobs do not overlap with other jobs, the logic is as follows(Simplified): CREATE TABLE #Job ( job_id UNIQUEIDENTIFIER NOT NULL ,...
user avatar
-6 votes
1 answer
57 views

Does offline index rebuild of clustered or non-clustered index block user's SELECT/UPDATE/INSERT/DELETE queries?

I understand that index rebuild requires schema stability (Sch-S) lock which means it will block any query that tries to do the schema modification (Sch-M). Does an offline index rebuild of clustered ...
user avatar
  • 2,375
-4 votes
1 answer
35 views

Why is SQL AG sync commit mode classified as no data-loss HA solution even when the session timeout can make the mode async?

Assuming the SQL AG is in sync commit mode. Suppose the secondary becomes unresponsive or has secondary has network issues, then upon the session timeout expiry the AG will automatically get converted ...
user avatar
  • 2,375
1 vote
1 answer
48 views

Unlike FCI, doesn't AG make use of heartbeat to check for server down?

SQL AG in WSFC checks whether server is down via the lease timeout Where as SQL FCI checks whether server down via heartbeat Does the SQL AG not make use of heartbeat at all? Below is a sample log ...
user avatar
  • 2,375
-2 votes
1 answer
44 views

What is recommended approach for fixing corruption related errors? [closed]

When there are errors of code 823 in the log or dbcc has detected corruption, then should the 1st choice to solve this be: dbcc checkdb with no data loss or restore from full backup+tlog backups?
user avatar
  • 2,375
-1 votes
1 answer
39 views

Is disk witness still the recommended best option for 2 node cluster (versus file share) in SQL server 2019?

Link: https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2008-r2-and-2008/cc770830(v=ws.10)#local-two-node-cluster It says that the disk witness is the recommended best ...
user avatar
  • 2,375
0 votes
1 answer
25 views

Does dynamic quorum having any solution for partition in time?

Assume 2 node 1 file share cluster (Node 1 is active), where say the file share is down for some reason. The dynamic quorum will allow cluster to continue working and assign the voting capability ...
user avatar
  • 2,375
0 votes
0 answers
10 views

In WSFC, can the child volume be shared among instances? and if so, then what is the purpose of this (sharing)?

Link: https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/install/before-installing-failover-clustering?view=sql-server-ver15#Hardware The base drive, the one with the drive letter, ...
user avatar
  • 2,375
1 vote
1 answer
28 views

Does SQL server provide automatic page repair in the async commit mode AG?

I understand that SQL AG supports the automatic page repair when the primary is synchronized with the secondary. Suppose the AG is configured in an async commit mode, then does also mean that the ...
user avatar
  • 2,375
0 votes
1 answer
29 views

How does SQL server handle the password change of the service account?

When installing the SQL server, when asked for the sql service account, I have entered a domain user and its password. In future, when the password of the domain account will change, then will I have ...
user avatar
  • 2,375
0 votes
0 answers
12 views

During scheduled maintenance of secondary node, should I set the non-primary node weights to 0 or should I set the FailoverConditionLevel to 0? [closed]

During scheduled maintenance of the secondary nodes in an FCI, should I set the non-primary node weights to 0 or should I set the FailoverConditionLevel to 0 to prevent the failover/restart?
user avatar
  • 2,375
0 votes
0 answers
16 views

What are the specific changes that need to be made in client code to handle multi-subnet FCI?

The client application then retrieves all registered IP addresses from the DNS server and attempts to connect to the addresses either in order or in parallel. This means that client recovery time in ...
user avatar
  • 2,375
0 votes
0 answers
15 views

Why does WSFC recommendation include configuring odd number of votes when this is automatically handled by dynamic quorum and dynamic witness?

Link: https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/wsfc-quorum-modes-and-voting-configuration-sql-server?view=sql-server-ver15#RecommendedAdjustmentstoQuorumVoting Odd ...
user avatar
  • 2,375
0 votes
0 answers
19 views

How to prevent split brain scenario in multi subnet cluster during network failure between the subnets?

Suppose I configure a multi subnet cluster say with 3 nodes in subnet1 and 3 nodes in subnet2. Since the total count is 3+3=6, which is an even number, one of the nodes will be auto assigned 0 weight (...
user avatar
  • 2,375
1 vote
0 answers
15 views

What is the voting process in WSFC?

Node and File Share Majority. Similar to Node Majority quorum mode, except that a remote file share is also configured as a voting witness, and connectivity from any node to that share is also counted ...
user avatar
  • 2,375
-2 votes
1 answer
27 views

Does tempdb recommended count depend on the total processors on the computer or on the processors in a single NUMA node?

The number of secondary data files depends on the number of (logical) processors on the machine. As a general rule, if the number of logical processors is less than or equal to eight, use the same ...
user avatar
  • 2,375
-1 votes
1 answer
26 views

What is the concept of service master key in FCI?

In a single sql server instances, sensitive data like passwords in linked server and credentials are encrypted using the server master key. The key or objects can be decrypted only by the sql service ...
user avatar
  • 2,375

1
2 3 4 5
12