Questions tagged [sql-server-2019]
SQL Server 2019 (major build version 15.00.xxxx). Please also tag sql-server.
584
questions
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 ...
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 ...
- 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, ...
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 ...
- 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 ...
- 379
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 ...
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
...
- 383
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 ...
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 ...
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:
...
- 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 ...
- 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 ...
- 1
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-...
- 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 ...
- 101
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 ...
- 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 ...
- 71
-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 ...
- 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% ...
- 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'; ...
- 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 ...
- 1
-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
...
- 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 ...
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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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
,...
- 151
-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 ...
- 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 ...
- 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 ...
- 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?
- 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 ...
- 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 ...
- 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, ...
- 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 ...
- 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 ...
- 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?
- 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
...
- 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 ...
- 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 (...
- 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
...
- 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 ...
- 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 ...
- 2,375