Questions tagged [sql-server-2016]

SQL Server 2016 (major build version 13.00.xxxx). Please also tag sql-server.

Filter by
Sorted by
Tagged with
2
votes
2answers
127 views

Columnstore: terrible execution plan - filter instead of seek

See the execution plan on https://www.brentozar.com/pastetheplan/?id=SyLQIPDtF (SQL 2016 Enterprise) I have a datawarehouse table peak_reporting_data which tracks the activity per day and hour and ...
1
vote
1answer
111 views

Query Store internal queries being executed thousands of times per minute

I have an SQL Server 2016 Standard production server with more 59 clients Databases. Microsoft SQL Server 2016 (SP2-CU17) (KB5001092) - 13.0.5888.11 (X64) Mar 19 2021 19:41:38 Copyright (c) ...
2
votes
1answer
61 views
+200

Powershell SMO changing database context from master when setting database to SINGLE_USER

I am trying to set a database to single user using SQL Server SMO and have the code below. This code is an abridged excerpt from a larger restore script $Credential = Get-Credential [System....
1
vote
1answer
44 views

Is there anyway in sql server transactional replication to not drop the target tables?

I have transactional replication setup between Server1 database and Server2 database on few tables. Now the problem is when I start the replication for the first time, it drops the table on target ...
0
votes
1answer
25 views

SQL Server 2016 - DSSNIClient initialization failed with error 0xffffffff, status code 0x80

After a windows server restart SQL instance does not start with following errors: 2021-11-19 05:43:39.72 spid10s Error: 17182, Severity: 16, State: 1. 2021-11-19 05:43:39.72 spid10s TDSSNIClient ...
0
votes
1answer
17 views

ALTER permission on database allows creation of security principals

It appears the ALTER permission granted at the database level allows the principal to which it is granted, to create security principals in the database. Consider the below example: /* setup database *...
1
vote
1answer
37 views

Referencing a system function from a DB with a lower compatibility level

I need to be able to parse a string array with STRING_SPLIT on a SQL 2016 instance. The vendor has required the one user DB we have access to be set with a compatibility level of 100 while the system ...
1
vote
2answers
27 views

Hourly Transaction Log job fails when DB is deleted or restored

When ever we delete a DB or backup and restore a DB, we get email alert that our hourly transaction log has failed (log is taken every 30 minutes). Every log after fails, to fix the issue we have to ...
-4
votes
1answer
50 views

MS SQL Server 2016 Standard Edition? [closed]

Is DB Online Index Rebuild supported by MS SQL Server 2016 Standard Edition?
-2
votes
0answers
35 views

Software Installing issues [closed]

I'm trying hard to install software that's need sql server 2016 to complete full installation it send me warnings ⚠️ messages . How can I fix it and how software can be connected automatically with ...
0
votes
1answer
21 views

Populate new rank field with custom order

I'm trying to populate a new Rank field in my table. Here is an example of how it should look like. +-----------+------------+------+ | CountryId | CreateDate | Rank | +-----------+------------+------+...
0
votes
0answers
14 views

SQL Server 2016 Service Pack 2 CU 17 Stuck on Install

I am updating an earlier CU with the new CU17 on one of my large SQL Servers. The issue is that when I started the upgrade process, I had a large database stuck in a Recovery state (forgot it's there) ...
0
votes
2answers
92 views

SQL index with parent child design

I am looking to understand a typical approach to indexes in a database parent-child design. This is a basic financial calculator for calculating complex commissions used monthly and driven by stored ...
14
votes
1answer
723 views

Incorrect comparison of datetime and datetime2

I know that it is not a good practice to have an implicit type convert. But this is really unexpected behavior when a lower value can suddenly become higher. declare @LastSelectedDate DATETIME = '2021-...
1
vote
1answer
84 views

log_reuse_wait_desc = AVAILABILITY_REPLICA, log is 100% full but AG is all fine

That's a weird scenario. The transaction log is full. It waits on AG. AG is all good. No latency, last commit/hardened time is almost real-time on all the nodes. The Estimated Recovery Time is zero. I ...
1
vote
1answer
50 views

Using Microsoft Data Migration Tool on the command line

I am using the Microsoft documentation from the link below https://docs.microsoft.com/en-us/sql/dma/dma-commandline?view=sql-server-ver15 The sample I am using from the documentation is this one ...
0
votes
1answer
32 views

Concatenate duplicates within table column [duplicate]

I have a query that is returning data similar to the following: po_line.line_no (PO Line #) oe_line_po.order_number (Sales order #) 1 A 1 B 1 C 2 D 3 E 3 F 4 null Some of our po_line.line_no ...
0
votes
1answer
22 views

Is there any option in change tracking to track changes of specific columns of table?

Do you know If is possible to track changes (CHANGE TRACKING or CDC) with 'Track columns updated' but only specific columns which we would like to track. The reason is that we have table with 300 ...
4
votes
1answer
144 views

Changing SET options within cursor - Different behavior based on compatibility level

I have found a difference in behavior on a piece of code between database compatibility levels and wondered what the cause of this is. Below is a simple example that iterates through a tally table and ...
5
votes
1answer
275 views

SQL Server service - Not Starting With Automatic Startup, "The system cannot find the file specified", but starts fine manually

This is a weird one, and I've run out of ideas for how to go about troubleshooting this. Here's the issue, best as I can sum it up. The SQL Server engine and Agent services are not started when the ...
0
votes
1answer
80 views

Speed up update in large table

I have a table with 128 million records. There are no foreign keys or indexes, just a heap table. I need to update 3 fields in all the records based on a logic that depends on other fields in the same ...
0
votes
0answers
18 views

Can't configure AOAG because the secondary node is not showing folders

I created a database on SERVER1. the folder structure is (Example): C:\CLUSTER\SQL\DATABASE1\DataFile.MDF But When I access the SERVER2 that is the secondary node ( to create the same folder ...
3
votes
1answer
161 views

SQL SERVER: Create table with named default constraint

I'd like to create a table with a default constraint for a field. I know I can add a constraint after the table is created but I need to create the table and the named default constraint in one single ...
1
vote
0answers
48 views

RESTORE HEADERONLY takes over 10 minutes to complete

I have a database backup that has been taken on another server (That I don't have access to) I want to restore the HEADERONLY and when I do so: RESTORE HEADERONLY FROM DISK = N'D:\SqlServer\backups\...
3
votes
1answer
141 views

How to reverse only English words?

I've an nvarchar column with English and Hebrew letters in some fields. In the fields where the English and Hebrew are together, the English letters are in reverse order (this is how the ERP program ...
1
vote
1answer
21 views

monitor the dataflow component performance of SSIS packages

I want to monitor the performance of each component of dataflow of SSIS packages is this possible and how to do it. I have used the SSIS pipeline counters in the monitor performance but it provides ...
3
votes
4answers
466 views

How to get historical queries in SQL Server, based on time

Please how do I get the query that was running at a specific time in SQL Server 2016? Something similar to AWR report in Oracle? We need to know what was happening at a specific time in the past ...
0
votes
2answers
34 views

What is the best way to measure INDEX impact on production?

We are in process of creating nonclustered indexes on tables on QA environment on SQL Server 2016. Tables are the same in terms of structure (columns, clustered indexes) but tables on production have ...
7
votes
3answers
2k views

Improper use of logical operators has led to poor query performance

I have a table with huge amount of data (Nearly 15 million) and below structure. create table test (a int,--> /* There is a normal index on this column */ b int, <other columns>) There is a ...
1
vote
1answer
55 views

How to track or understand if a "timeout" was caused by SQL Server issues or network, server and etc?

This is a question that haunts me for ages. every job I got I see plenty of people complaining for the database administrators about "timeout" errors: System.Data.SqlClient.SqlException: ...
0
votes
1answer
67 views

Tracking incrementing id's for hundreds of thousands of objects

We have an ever growing list of guid's that we need to be able to track an ever incrementing list of integer id's for. Our current implementation creates a sequence with no cache for each guid. ...
2
votes
1answer
98 views

SQL Server log_reuse_wait_desc is ACTIVE_TRANSACTION but there are no active transactions

Just had an issue with a SQL Server 2016 database where this query returned ACTIVE_TRANSACTION for log_reuse_wait_desc: select name,log_reuse_wait_desc from sys.databases where name='database name' ...
0
votes
0answers
62 views

SQL Server linked server query issue

I am using SQL Server 2016 and recently one of the stored procedures I have been using is throwing error. I use a select statement with joins on many tables to the remote server using a linked server ...
-2
votes
1answer
68 views

What is the best way to order Partitioned table?

What is the most efficient way to get ordered data from a partitioned table? I have this query: SELECT TOP (4000) * FROM dbo.MS ORDER BY MANDT , MBLNR , MJAHR , ZEILE Table is partitioned by MBLNR. ...
0
votes
3answers
42 views

How does enabling instant file initialization lead to information disclosure (when SQL server encrypts the data at rest)?

How does this lead to information disclosure when SQL server encrypts the data at rest?
0
votes
0answers
47 views

Created an index for a table, adding just one more column, and SQL still suggests a new index

I got a recommendation for SQL Server for this query: SELECT field1 FROM table WHERE ( field2 < getdate() - 2557) Obviously the recomendation was CREATE INDEX idx_name on TABLE (field2) INCLUDE (...
1
vote
1answer
47 views

SQL Server 2016 Standard Edition TDE

I have Standard Edition of SQL Server 2016. Is it possible to enable TDE? I understand that it was an enterprise feature but it is now a feature for SQL 2019 Standard and therefore I wondered if there ...
2
votes
1answer
80 views

Load assembly from a certificate in SQL Server

We received an assembly from external vendor as CREATE ASSEMBLY MYCALC_DLL AUTHORIZATION dbo FROM 0x42A728....<300,000 binary values> WITH PERMISSION_SET = UNSAFE GO And this DLL is been ...
2
votes
3answers
151 views

Large database partitioning and backups of only updated data (partial backups)

I'm setting up a large SQL Server database (2-4tb), but only a small percentage of it will be updated. I'd like to partition by year so that my backups can be small. HOWEVER, some of the old data may ...
0
votes
0answers
41 views

SQL Server 2016 - There was a failure to calculate the applicability of setting INSTALLSHAREDDIR

I am trying to install new instance on the server with already installed instance but short after running the setup.exe I am getting the error: SQL Server Setup has encountered the following error: ...
0
votes
5answers
98 views

Identify how many CPUs our database server is using

Our server where the Microsoft SQL Server 2016 Web Edition server is installed is running very slow. We are thinking of increasing the number of cores licensed on it. However, we need to check how ...
1
vote
1answer
24 views

How to rebuild Full Text indexes after MSSQL upgrade from 2014 to 2017

When I updated from 2014 to 2017 I chose the option to migrate Full-Text Indexes instead of rebuild them during the update. The update went well, and I'd like to repopulate those indexes now, but I'm ...
2
votes
1answer
231 views

SQL Server using index scan instead of seek, all of a sudden

We have a stored procedure that runs daily. In one of the sub queries (query 20, in the execution plan), SQL Server was using an index seek till 2021/09/24. But all of a sudden when the query ran on ...
5
votes
3answers
188 views

Alternate Solution For REPLACE() In ntext Column

Overview I have a picture database that catalogue information of pictures taken with cameras. The database contains a table pictures which has 256 columns which contain information about the picture ...
2
votes
1answer
106 views

Why does adding a WHERE clause break my working query, with error "Query processor could not produce a query plan because of the hints defined..."?

I have the following (stupidly-simplified) query that leverages two CTEs that refer to the same table and joins them to each other: WITH CTE1 AS ( SELECT dbo.RemoveNonNumericCharacters(PhoneNumber)...
0
votes
0answers
61 views

Multiple SQL instances on the same server and how to manage TempDB

Probably a bizarre approach but I don't really have much say in the setup. We have a large server deployed in Azure and the plan is to get 16 separate instances on this machine. We plan on using the ...
0
votes
0answers
36 views

Does collation effect SQL Server replication

I've been getting a replication error for some time now between my Publisher and Subscriber databases (SQL 2016). The error is the classic: "42000 The row was not found at the Subscriber when ...
2
votes
0answers
128 views

How to determine the Tipping Point of which Physical Join Operator gets used to process a query?

I've been noticing some strange occurrences lately on my development SQL Server instance where the Nested Loops Physical Join Operator is used when a Hash Join would be much more efficient, within a ...
0
votes
2answers
70 views

What are the more important columns to include in NONCLUSTERED INDEX - from Group by or Where?

I am thinking about adding a NONCLUSTERED INDEX to a table in SQL Server 2016. I found that most queries refers to WHERE clause like: WHERE varcharColumn <> '' and varcharColumn2 = '2' and ...
0
votes
0answers
48 views

Why is the FILESTREAM data not accessible, after AG listener is created?

I'm having a hard time figuring out why my app can't access filestream data, after we enable AG listener. Background info: Initial environment: APP Server Web Server IIS Windows Server 2012 R2 DB ...

1
2 3 4 5
51