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

What is purpose of cluster and sql server ips in FCI

I have installed 2 node FCI and 1 standalone instance, added all 3 nodes to cluster, along with file share witness, and via SSMS configured AG between the FCI and standalone instance for 1 db. For ...
  • 2,874
0 votes
1 answer
27 views

How to configure the listener in a read scale SQL AG post failover?

I have configured SQL read scale AG (without the WSFC) and listener. When creating the listener, I have given it a name SQLAGListernerTest2019 and entered the IP address of the primary SQL server. ...
  • 2,874
0 votes
1 answer
21 views

Does the DEA A/B testing (using trace replay) require the target database migrated/restored and does it update the target database data?

I am exploring this link: https://learn.microsoft.com/en-us/sql/sql-server/migrate/guides/sql-server-to-sql-server-upgrade-guide?view=sql-server-ver15#optional-ab-testing The goal is to migrate sql ...
  • 2,874
-3 votes
1 answer
23 views

Where to download a backup of the stack overflow to use for SQL Server 2019

Please can someone help me with a link to where to download a .bak copy of the Stack Overflow database that I can restore for training on my newly installed SQL Server 2019 instance? Thank you
  • 233
0 votes
0 answers
11 views

SSIS Package with "Analysis Services Processing Task" become Corrupted in MSDB

Working on upgrading from SQL Server 2017 to SQL Server 2019. When using a desktop or another server running SQL Server 2017, SSMS v18.12.1 is used to import SSIS packages into MSDB on a remote server ...
  • 1
0 votes
1 answer
21 views

Is it not possible to add instance and shared features to a pre-existing FCI?

I'm looking at a sql server fci that has the database engine installed. My task is to add the full text search service and integration services. I'm exploring how to do this when I came across this ...
  • 2,874
1 vote
0 answers
24 views

Long temp table TRUNCATE

On Microsoft SQL Server 2019, there is a lot of long temp tables TRUNCATE operations with time over 5..10 seconds. Easy query like "TRUNCATE TABLE #tt22" can take over 15 seconds. It's an ...
  • 11
-3 votes
0 answers
27 views

Assuming I am on the GDR train, then what would be the use case of applying CU16-GDR or CU16? [duplicate]

I understand that: GDR can be chosen if I am on RTM or on the GDR train. CU16-GDR can be chosen if I am on RTM, CU train, or GDR train. Currently I am on the GDR train, and apply the latest GDR (...
  • 2,874
0 votes
1 answer
22 views

Whether the latest GDR from Microsoft includes all prior GDRs?

I am looking at patching SQL server using the latest GDR (KB 5014356) from here: https://learn.microsoft.com/en-us/sql/database-engine/install-windows/latest-updates-for-microsoft-sql-server?view=sql-...
  • 2,874
2 votes
2 answers
156 views

Does large tempdb file size have impact on sql server startup time?

Sql server tempdb data and log initial file size is configurable. Assuming I set it to large values (say 10GB) then will sql server take more time to start compared to if the data/log initial file ...
  • 2,874
0 votes
1 answer
19 views

What is the use case difference between forced quorum and force cluster to start without quorum?

I'm learning about handling fci issues and stuck at 2 concepts to recover from cluster failure: Forced quorum Force start without quorum What is the factor to decide whether to use which of the ...
  • 2,874
-4 votes
0 answers
32 views

How does 5 node cluster handle split scenario when half nodes are unreachable?

Scenario: Assuming I have a 5 node WSFC, and 1 file share. Total 6 votes. Say there is a situation due to which nodes 1, 2, 3 can communicate with each other, but not with node 4, 5. Everyone can ...
  • 2,874
-2 votes
0 answers
21 views

What are administrative shares in a FCI? And should sql server service account also be a cluster administrator?

Link: https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/failover-cluster-instance-administration-and-maintenance?view=sql-server-ver15#changing-service-accounts If the ...
  • 2,874
-1 votes
1 answer
29 views

How to work around sp_blitzwho lock timeout?

Running sp_blitzwho @GetLiveQueryPlan = 1 on SQL2019 to monitor a big index rebuild operation but running into the following error: Lock request time out period exceeded. Anything I can do to avoid ...
-2 votes
0 answers
15 views

What is an example of system, resource and query processing errors in FCI?

I'm learning about the FailureConditionLevel setting. Link: https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/failover-policy-for-failover-cluster-instances?view=sql-server-...
  • 2,874
-1 votes
1 answer
31 views

What does `same WSFC cluster can run multiple FCIs (multiple resource groups)` mean?

Link: https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/always-on-failover-cluster-instances-sql-server?view=sql-server-ver15#FCIelements the same WSFC cluster can run ...
  • 2,874
1 vote
0 answers
17 views

Does sql config manager changes (example: service account) on 1 FCI node auto update it onto the other node?

Link: https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/always-on-failover-cluster-instances-sql-server?view=sql-server-ver15#FCIelements Each node in the resource group ...
  • 2,874
1 vote
0 answers
16 views

What is the approach to handle failover and compatibility issues when patching FCI nodes?

I have FCI with 2 nodes. Following is steps to patch the nodes: Remove node from Possible owners Patch the inactive node Add the node to the Possible owners Failover to inactive node Leave it running ...
  • 2,874
1 vote
1 answer
20 views

What is the extended events equivalent of the default sys trace security audit events?

I am exploring the default sys trace and it contains various records for the security changes on the SQL server (event record examples are shown in screenshot below). Since the trace feature can be ...
  • 2,874
0 votes
1 answer
21 views

Why does fn_trace_gettable with the DEFAULT parameter not roll over all trace log files?

These are my SQL default trace files: To review the trace logs I run the following query: SELECT min(tf.StartTime) MIN_STARTTIME, max(tf.StartTime) MAX_STARTTIME FROM ::fn_trace_gettable(CONVERT(...
  • 2,874
0 votes
1 answer
14 views

How to view the trace_id of the default trace files?

Sql server automatically creates the default trace into multiple files. These can be seen in the LOGS directory of sql server installation path. The sys.fn_trace_getinfo has a trace_id parameter which ...
  • 2,874
-1 votes
0 answers
58 views

SQL Server Stored Procedure performance slows from 30min to 22 hours overnight

We are running SQL Server 2019 web edition on AWS RDS (t3.xlarge which is 4vcpu x 16gb). We have been running a SQL Job Agent scheduled job that executes approx 50 Stored Procedures for flattening ...
3 votes
2 answers
91 views

Unique Constraint on 2 columns or single column and null exists

Within a multi-tenant database we have a table that contains some system values and some tenant values defined as follows. CREATE TABLE [dbo].[ItemTypes] ( [Id] INT IDENTITY ...
  • 135
-1 votes
0 answers
30 views

What is the way to handle corruption in db having simple recovery model?

A database in simple recovery model cannot have log backups. In this situation what is the way to handle corruptions reported by dbcc checkdb without data loss? The documentation says: Use the REPAIR ...
  • 2,874
-1 votes
1 answer
30 views

What is the purpose of indirect checkpoint when the default checkpoint time (when recovery time is 0) does a 1 minute checkpoint?

Indirect checkpoint is performed when the target recovery time is configured. When the value is 0, then it will do checkpoints such that default recovery time is 1 minute. Where as if the value is ...
  • 2,874
-1 votes
1 answer
29 views

Can SQL AG be used across 2 sites over the internet to create replica readonly db on remote server?

I have SQLSVR1 having multiple dbs located in Scotland. One of these dbs is used in an ETL process running on SQLSVR2 located in a remote data center located in London via linked server. The ETL ...
  • 2,874
-1 votes
1 answer
33 views

What permissions are needed to create User Defined Database role

Question: If you are not a db_owner or a sysadmin, what minimal permissions are needed to perform the following tasks: Create user defined database role, say, MyDbRole List item all users inside ...
  • 135
1 vote
1 answer
31 views

Databases in Recovery Pending or Suspect Mode after SQL Server service restart

This issue applies for SQL Server 2019 Always on environment with the latest CU applied. When SQL service needs a restart or the entire OS needs a reboot, some of the DBs come back in recovery pending/...
0 votes
1 answer
51 views

Does shrink (of data/log file) causes blocking?

https://learn.microsoft.com/en-us/sql/relational-databases/databases/shrink-a-file?view=sql-server-ver15#remarks Shrink operations in progress can block other queries on the database, and can be ...
  • 2,874
0 votes
0 answers
11 views

Does polybase support having a table to represent a REST API?

I have customer information in a SQL server table, and the billing information is exposed via REST web api (with customer_id as parameter). I am exploring using polybase to create the polybase table ...
  • 2,874
1 vote
1 answer
32 views

Converting rows to desired JSON format in TSQL

Trying to convert the data in required JSON but wasn't successful, any help or suggestions would be appreciated. SQL Server version: 2019 Test script: drop table if exists t1 go create table t1(id int,...
0 votes
1 answer
65 views

How does SQL Server's Performance Dashboard categorize a query as "ad-hoc" or otherwise?

I'm looking at the CPU usage in sql server's server performance dashboard report. I have 10 dbs on the server. So the pie chart shows me 85% for adhoc queries. Whereas only 15% is split amongst all ...
  • 2,874
0 votes
1 answer
22 views

Does polybase bring entire external table data onto sql server?

I'm exploring sql polybase using Azure blob storage and other data sources. For blob storage sql server brings the entire blob onto the server and then performs where/join. What is the behaviour when ...
  • 2,874
0 votes
0 answers
11 views

How does polybase handle SELECT query on an excel file that is being actively used?

I am using ssis to read data from an excel file (located on shared network folder) and insert into sql server table. When the file is being used by an user then I get an error when package runs ...
  • 2,874
0 votes
0 answers
18 views

I need help with Migrating a 2008 R2 SSRS to a new 2019 SSRS

We are migrating a 2008 r2 SSRS to a 2019 SSRS, do i need to upgrade on the current before the migration, I need advice please what can i do? How can i make the migration successful. Please take note ...
  • 13
10 votes
3 answers
3k views

Does killing an application kill the SQL connection?

Suppose an application has established two connections to SQL Server. A session is actively running a query A session that is sleeping, waiting for the next instruction Does killing this application ...
  • 2,874
5 votes
0 answers
83 views

Find a previous statement or held locks in a blocking situation

I'm using XE blocked_process_report to detect and analyse the blocking. But since it's a point-in-time situation, I only see the blocking leader's statement currently being run and the incompatible ...
  • 2,315
-3 votes
1 answer
39 views

Why is there discrepancy between number of connections in sys.sysprocesses and sys.dm_exec_connections?

The number of active connections reported by sql server is as follows: sys.sysprocesses 154 sys.dm_exec_connections 125 Why is there discrepancy between number of connections in sys.sysprocesses and ...
  • 2,874
2 votes
1 answer
60 views

Microsoft SQL Server 2019 Developer Edition suddenly throwing Internal Connection Fatal Errors

I have Microsoft SQL Server 2019 Developer edition installed on my computer for local development work with .NET. Everything was working well as of two months ago, when I was last working on a ...
  • 23
0 votes
2 answers
24 views

Does tail log backup option during restore (UI) truncate the tlog? And is there any way (UI option) to prevent truncation?

When restoring the db via SSMS UI, there is an option (checkmark) to take the tail log backup and optionally to place the db in NO RECOVERY mode. Does such a tail log backup truncate the tlog? And is ...
  • 2,874
0 votes
0 answers
30 views

How to analyze causes of the fluctuation in memory buffer?

I'm exploring the following query to understand database-wise memory usage patterns on a sql server: SELECT databases.name AS database_name, COUNT(*) * 8 / 1024 AS mb_used FROM sys....
  • 2,874
1 vote
2 answers
45 views

What is the difference between latency calculation using sys.dm_os_buffer_descriptors.read_microsec vs sys.dm_io_virtual_file_stats?

I'm trying to calculate the disk io latency (time required to fetch the page from disk into memory) for a SQL database. Following are 2 approaches I am exploring: Approach 1 - Using sys....
  • 2,874
0 votes
1 answer
48 views

REVOKE CREATE TABLE is not removing the permission

I gave the following permission to user1 so user1 can create tables on database1: GRANT CREATE TABLE TO user1 WITH GRANT OPTION I verified that user1 can create table. Then I ran the following ...
  • 135
0 votes
0 answers
53 views

How to partition a 16TB table with 3 billion rows within a maintenance window?

I've inherited a large table (16TB, 3 billion rows, lot of ntext data), that's never been partitioned. I want to split it over set of filegroups. Current estimate based on limited test is about 70 ...
2 votes
3 answers
295 views

What is the meaning of scan count in SET STATISTICS IO ON?

I'm looking at the output of a query after running the following command: SET STATISTICS IO ON Table1 shows scan count as 5, logical reads 12197 Table2 shows scan count as 0, logical reads 80 The ...
  • 2,874
2 votes
2 answers
74 views

Permission to grant SELECT, UPDATE, DELETE, ALTER on all tables

In SQL Server 2019, I have created a user defined database role MyDbRole. Now I want to grant following permissions to MyDbRole for all tables in the database. CREATE, SELECT, UPDATE, DELETE, ALTER ...
  • 135
-1 votes
0 answers
17 views

SQL Server 2019 Standard Edition User Rights Assignment

I am currently investigating the requirement for User Rights Assignment on a SQL 2019 Standard Edition instance joined to a Windows 2019 AD domain. The instance has been installed using the default ...
  • 1
-1 votes
0 answers
15 views

Convert Excel formulas to SQL language automatically

Is it possible convert excel formula to sql? I mean like this site for convert VB.NET to C#. I have to write lots of complicated excel function in format of SQL function like this: =VLOOKUP(D4,Table25,...
3 votes
1 answer
67 views

How to find all entities owned by a login?

I have to produce a report that lists all the SQL entities owned by a particular login. Is there any way I can audit/get list of all the entities owned by a login using SQL audit/dmv? For example: ...
  • 2,874
1 vote
1 answer
81 views

Given SQL Server's ability to adjust I/O block sizes based on operation, how does formatting the disk with 64K allocation unit help a read query?

Following is a SQL Server I/O block size reference table. Given its ability to adjust the block size based on operation, how does formatting disk using 64KB vs 4KB allocation units help a read query? ...
  • 2,874

1
2 3 4 5
14