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
-3 votes
1 answer
23 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,243
2 votes
2 answers
57 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,243
-2 votes
0 answers
31 views

Does PLE have any relation to the backups performed? [duplicate]

Page life expectancy tells how many seconds a page remains in memory. Backup uses backup buffers which are separate to the sql server buffer pool memory. So this means that when a backup is performed, ...
user avatar
  • 2,243
-1 votes
2 answers
40 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,243
-3 votes
0 answers
46 views

SQL Server differential backups too much slow and big

I configured a backup plan on my SQL Server 2019 Web Edition with this scheduling: a full backup every day at 00:00 a differential backup every 15 minutes What I was imaging is that every ...
user avatar
  • 127
-2 votes
1 answer
286 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,243
-3 votes
1 answer
51 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,243
-5 votes
1 answer
39 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,243
-1 votes
2 answers
37 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,243
4 votes
3 answers
860 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,243
-1 votes
1 answer
40 views

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

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,243
0 votes
0 answers
25 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
54 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,243
-4 votes
1 answer
32 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,243
-1 votes
0 answers
35 views

Does restart in FCI/AG mean node restart or sql server cluster aware services restart?

There are failure condition levels for FCI and AG. I read the keyword restart. Just want to confirm whether restart means node restart or sql server cluster aware services restart?
user avatar
  • 2,243
1 vote
1 answer
44 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,243
-2 votes
1 answer
43 views

What is recommended approach for fixing corruption related errors?

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,243
-1 votes
1 answer
32 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,243
0 votes
1 answer
23 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,243
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,243
1 vote
1 answer
26 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,243
0 votes
1 answer
25 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,243
0 votes
0 answers
11 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?

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,243
-1 votes
0 answers
14 views

When the connection is lost, then does the Resource DLL restart the timeout ignoring the previous calls?

The following items describe how this property affects timeout and repeat interval settings: The resource DLL calls the sp_server_diagnostics stored procedure and sets the repeat interval to one-third ...
user avatar
  • 2,243
0 votes
0 answers
14 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,243
0 votes
0 answers
12 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,243
0 votes
0 answers
13 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,243
1 vote
0 answers
12 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,243
-2 votes
1 answer
19 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,243
-1 votes
1 answer
24 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,243
0 votes
1 answer
24 views

How to filter extended event session by sp execution time (seconds)?

Trying to view all executions of a named stored procedure where execution time exceeds a given number of seconds. How do you filter on execution time (seconds)? This is what I have cobbled together ...
user avatar
0 votes
0 answers
25 views

Is there a query or report to see how often log shipping is falling behind?

Is there a query or report to see how often log shipping fell behind for a given month?
user avatar
0 votes
3 answers
45 views

How does FCI guarantee data consistency and integrity?

FCI technology uses windows clustering to start-up the sql server service on the secondary node and provides the secondary node with the data and log disks. Assuming the OS on node 1 crashed, and the ...
user avatar
  • 2,243
-4 votes
1 answer
27 views

Is the effect of statistics update immediate or does sql server need to be restarted?

Index statistics are utilized by sql server to derive the best plan for query execution. Suppose I update the index statistics then can existing queries use the effect of updated statistics or does ...
user avatar
  • 2,243
-5 votes
1 answer
30 views

Does the shrink data and log file command need to be done during maintenance hours or less busy hours?

Our data and log files have increased drastically due to a recent large INSERT query and DELETE query. The database in simple recovery mode. Does the shrink data and log file command take the database ...
user avatar
  • 2,243
0 votes
0 answers
9 views

Root CA for Hyper-V virtual TPM Endorsement Key required for setting up Always Encrypted

I'm trying to setup Always Encrypted with Secure Enclaves in a lab environment using Hyper-V. HostKey attestation is straightforward, but I'm struggling with TPM mode. My setup is a Hyper-V host VM ...
user avatar
-1 votes
2 answers
51 views

Why does MAXDOP = 0 result in high CXPACKET wait time?

During a parallel query execution (whether or not to go parallel is decided by the cost threshold for parallelism) MAXDOP limits the number of tasks per request. Assuming cost threshold for ...
user avatar
  • 2,243
2 votes
1 answer
230 views

SQL Server: Is it worth checking for change in a field before the update?

I'm investigating this statement performance on SQL Server 2019 (I've extracted the important part): UPDATE m SET A1 = src.A1, A2 = src.A2, A3 = src.A3, ...
user avatar
  • 185
0 votes
1 answer
19 views

How to check whether or not dynamic quorum and dynamic witness is enabled on WSFC?

Dynamic quorum and dynamic witness are abilities of the WSFC to dynamically adjust the vote of the nodes and witness to prevent unnecessary failovers. Where can I check whether the WSFC configuration ...
user avatar
  • 2,243
1 vote
0 answers
34 views

Throubleshoot high average latency

I am using the following query: SELECT DB_NAME(vfs.database_id) AS database_name ,physical_name AS [Physical Name], size_on_disk_bytes / 1024 / 1024. AS [Size of Disk] , CAST(...
user avatar
0 votes
1 answer
39 views

Do linked servers stop working after migrating the system dbs to a new sever?

Link: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-service-master-key-transact-sql?view=sql-server-ver15 The service master key is automatically generated the first time it is needed ...
user avatar
  • 2,243
-1 votes
0 answers
21 views

Is there a possibility to restore a ssis db on new server with the mdf/ldf attach/replace method?

I want to migrate sql server 2019 to another server (same version). I understand one option is backup/restore. Followed by using the master key.. Is there a possibility to restore a ssis db on new ...
user avatar
  • 2,243
-1 votes
0 answers
29 views

Is it possible to capture the connection string used to connect to SQL Server

I have recently taken over a number of SQL Server 2008 databases that are configured with mirroring. I understand there are some applications that connect using the "Failover Partner" option ...
user avatar
0 votes
1 answer
78 views

What is the num of NUMA nodes for MAXDOP calculation when there is discrepancy between various ways to count the NUMA nodes?

Querying the dm_os_performance_counters shows that I have 2 NUMA nodes: select * from sys.dm_os_performance_counters where object_name = 'SQLServer:Buffer Node' and counter_name = 'Page life ...
user avatar
  • 2,243
0 votes
0 answers
25 views

Why are SQL SSIS counters zero even though there are packages running on the server?

Why are SSIS performance counters: Buffer memory, Buffers in use, Buffers spooled returning 0 even though there are SSIS packages on the sql server; agent jobs configured to run these SSIS packages. I ...
user avatar
  • 2,243
0 votes
1 answer
31 views

How to execute SSIS packages on another SQL server's hardware?

I have 20 ssis packages deployed on SQLSvr1 in the SSIS catalog. They are invoked via the SQL agent jobs on this server. Suppose I re-create these agent jobs on another SQL Server (SQLSvr2), then will ...
user avatar
  • 2,243
0 votes
0 answers
29 views

How to migrate the ssisdb into a new server using backup when source server is inaccessible?

After the source server crashed, and assuming that I have the ssisdb full backup and password, what are the steps to configure and restore the ssisdb/packages/permissions onto a new server? Here is ...
user avatar
  • 2,243
0 votes
1 answer
28 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 ...
user avatar
  • 2,243
0 votes
1 answer
37 views

Does cluster go down if one of the node is restarted manually and the file share witness is unstable?

I am exploring the 2 node cluster with file share. The example is as shown here: https://docs.microsoft.com/en-us/azure-stack/hci/concepts/quorum#two-nodes-with-a-witness Majority is 2/3. So when ...
user avatar
  • 2,243
0 votes
2 answers
37 views

Migrating SQL server to new hardware - restore msdb vs scripts job?

I am planning for sql server 2019 migration to new hardware. For the server level logins, roles I can either restore the master db or script these out. Similarly, for sql agent jobs I can either ...
user avatar
  • 2,243

1
2 3 4 5
12