Questions tagged [sql-server-2016]
SQL Server 2016 (major build version 13.00.xxxx). Please also tag sql-server.
2,628
questions
0
votes
1
answer
75
views
what databases are already set up in the log-shipping, and which ones are not?
I am setting up log-shipping from server1 to server2.
there are many databases on server1 and I want to automate the process.
I have already done a few databases manually, while I am working on the ...
- 14.8k
2
votes
1
answer
94
views
How to trim the log file that stores information about job history execution?
I was having a look at my sql server jobs and proxies on an specific server in the production environment.
I pretty much have an script to check the status of the jobs but in case I have to ...
- 14.8k
2
votes
2
answers
183
views
Is partial synchronisation possible between two databases on different servers?
Imagine a scenario where you have a primary instance of a database and a secondary instance that is automatically synchronised - pretty much as with Availability Groups.
My question is whether I could ...
- 1,425
0
votes
1
answer
228
views
Comma separated value string and compare against other values
Hey all I have been trying to wrap my head around how to go about doing this for a while now and I'm not really coming up with anything.
Here is my query:
DECLARE @ATID AS VARCHAR(3) = 12
DECLARE @...
- 105
0
votes
2
answers
33
views
does SQL automatic backup runs even the machine/server is offline
I am wondering if the SQL jobs like automating the database backup runs even the machine/server is offline?
- 13
1
vote
1
answer
35
views
Update same column, same DB with specific records
We are migrating a site from one Active Directory system to another but in the third-party app, we need to keep the old accounts, so we renamed the old accounts with "_Old" appended to the ...
- 13
4
votes
3
answers
254
views
Why do subsequent unmet conditions in my CASE statement affect my return data type?
I have the following variables and CASE statement:
DECLARE @DATE_VALUE VARCHAR(20) = '4/29/2021'
DECLARE @CONVERT_CODE VARCHAR(1) = 'I'
SELECT CASE @CONVERT_CODE WHEN 'I' THEN DATEDIFF(day, '04/21/...
- 135
1
vote
4
answers
923
views
Tsql help - doing a large update from CSV file to a table in ssms
I have been given a csv file with just ids in there and those ids are in a table in ssms with columns that require updating. Out of 90,000 ids in that table, only 83,000 of them require updating from ...
- 45
0
votes
0
answers
383
views
SQL Server Replication: How do you change the distributor?
I've been given the task to change the distributor to a new server for pre-existing replication (both snapshot and transaction).
Server A: Existing Replication (Publisher)
Server B: Existing ...
- 253
6
votes
5
answers
3k
views
Is OPTION (RECOMPILE) used in production environments?
Is OPTION (RECOMPILE) used in production?
This option seems to get a lot of bad press. Is it deserved?
I have a DBA who, so far, is not a fan of the idea of OPTION (RECOMPILE) within the meat of ...
- 453
1
vote
3
answers
326
views
How can parse column names using ordinal position (not name) from XML data in SQL Server?
I have several hundred tables in a database that have the same structure:
SomeId, Pos, Varying Number of Other Fields
So, for example, one table may look like this:
PersonId, Pos, Hobby, Degree
12345, ...
- 135
2
votes
1
answer
149
views
Use CTE to fetch most recent row for each ID
I just recently started learning about the wonders of CTEs and how they can be used, instead of WHILE loops, to optimize performance.
For context, I am automating the process of updating firmware on ...
4
votes
4
answers
1k
views
Why changing the column in the ORDER BY section of window function "MAX() OVER()" affects the final result?
I have a table with below structure and it's data :
create table test_table
(
Item_index int,
Item_name varchar(50)
)
insert into test_table (Item_index,Item_name) values (0,'A')
insert into ...
- 1,302
1
vote
1
answer
85
views
Does SQL Server upgrade to table locks at total locks or session locks?
I’ve a table that both has mass sequential inserts at the end of the CI and random (very distributed) reads+updates. Naturally, the mass inserts should not block the random access. RCSI is used, so ...
- 195
1
vote
1
answer
313
views
Invalid Column Name on CONCAT_WS
Sorry if this seems like rambling I'm not typically a DB person just trying to work through an issue.
I have two tables cases and legal_entity the data on the two tables looks like this
legal_entity
...
- 113
0
votes
2
answers
70
views
Online rebuild consuming lot of log space
why the online rebuild on a primary key clustered index in taking almost equal to the space of non clustered index size which is untouched.
Details:
We have a table with primary key clustered index (...
- 492
2
votes
2
answers
91
views
Key lock triggered by concurrent batch update
I'm doing a batch update operation,but it appears key lock I think I'm in trouble.
I don't understand why the deadlock is happening.
Environment: SQL Server 2016
Work mode：concurrent batch update
...
- 23
2
votes
0
answers
62
views
Researching Performance Degradation on AO Secondary using MultipleActiveResultSets (MARS)
Environment: SQL Server 2016 Enterprise Edition on Windows 6.3 9600 two node cluster with Always-On.
We have an application that executes SELECTs on the only secondary node of a SQL Server 2016 always-...
- 9,478
2
votes
3
answers
318
views
Table scan instead of index seeks happening when where clause filters across multiple tables in join using OR
We have an application generated query using a view that has two tables joined on a LEFT OUTER join. When filtering by fields from just one table (either table) an index seek happens and it's ...
- 21
0
votes
1
answer
50
views
alter compatibility level
I plan to change the compatibility level on the databases in an sql server 2016 sp1 instance from 100 to 130. When upgraded from sql 2008 to sql 2016 the compatibility level was changed from 80 to 100....
- 13
0
votes
0
answers
30
views
How To Grant A Specific Login Under Acitve Directory Group To View Server State In SQL Server [duplicate]
I want to grant only a login who is under an active directory user group to view server state in sql server. How can i do it?
- 1
0
votes
1
answer
36
views
Missing Target Local Server in Job
SQL Server 2016 Enterprise, CU15 + GDR.
Client recently started using OPCON in production and I noticed that when OPCON creates jobs, it doesn't add the Local Server as the target. This prevents the ...
- 9,478
1
vote
2
answers
646
views
Why is the query in ETL via SSIS slow but via a local stored procedure it is fast?
I'm seeing the classic 'runs fast in studio manager but slow in application' problem. Sounds like it might be parameter sniffing. However, my experience with ETL and SSIS is zero.
From the DBA I ...
- 453
0
votes
1
answer
45
views
sql server 2016 sp1 to sp2 with latest cumulative
I inherited an SQL Server 2016 SP1. I am not a DBA and work at a small company where we do not have a test environment. I know that I need to install the SP2 first. Do I then install the last ...
- 13
0
votes
1
answer
72
views
One-directional Transactional Replication is causing one table's Clustered Index to be copied over as a Nonclustered Index in the subscriber database?
This seems really odd to me, as I've used SQL Server's replication feature for a while now and have never seen this occur before.
I'm using One-Directional Transactional Replication and I have ServerA ...
- 24.7k
3
votes
1
answer
406
views
Can shrink database improve performance
Yesterday I've got a mail from one of our suppliers mentioning that there were performance issues with their application.
I don't know yet what the issues are but the solution they propose is to ...
- 1,982
0
votes
0
answers
18
views
Get real error from backup job failure instead of backup terminating abnormaly
I've had log backup jobs fail for a few different reasons lately. When I go to the job it tells me backup terminated abnormally. However, when I run the backup script in SSMS it will tell me the ...
- 2,069
0
votes
2
answers
312
views
Script execution order
I did Generate Script on a database and got over 1700 scripts. I've sorted them into folders below:
Each folder contains .sql files of that type, for example 06 UserDefinedFunction contains many ....
- 135
0
votes
3
answers
80
views
Index Maintenance on busy OLTP database
I have a database where constant read writes happening all the time. Searches are being done with wild card entries. The server is on SQL 2016 standard edition.
There is no budget for enterprise nor ...
- 1,003
1
vote
1
answer
137
views
Running Excel addin for MDS2019 vs MDS2016
We are upgrading master data services (MDS) of SQL2016 to SQL2019.Is there a way to run excel 2019 addin for connecting to SQL 2019 MDS along side(parallely) with excel 2016 addin for connecting to ...
- 73
0
votes
2
answers
34
views
Simple Backup Plan Question
I'm using Microsoft SQL Server 2016 Standard (64-bit). I have the following backup plan (9 steps long) ...
What I want is just one full (simple) backup everyday. Do I need to do step 8 (Backup ...
- 1
9
votes
5
answers
3k
views
Linked Server connection started failing with "Cannot generate SSPI context ... SQL Server Network Interfaces: The logon attempt failed" error?
The Issue
I have two SQL Server 2016 Standard Edition servers on the same domain. ServerA has two linked server connections setup to ServerB.
The first linked server connection is using a dedicated ...
- 24.7k
0
votes
1
answer
227
views
Is this an excessively large 'Lock' time and is it indicative of a problem?
Given:
Production environment
Application Servers using Hibernate
SQL Server Studio Manager v17.5
SQL Server 2016 in a clustered HAG setup
SQL Servers do NOT have the Query Store feature enabled
The ...
- 453
1
vote
1
answer
101
views
Stale Statistics and View performance on SQL Server 2016
I have a report query which behaves abnormally, but rebuilding the indexes/refreshing statistics seems to sort it momentarily.
So my doubt essentially is -
What could be the reason that even with ...
- 131
2
votes
1
answer
407
views
SELECT query in a SP in AlwaysOn Availability Group Read Only Routing
There is a stored procedure which has select statement as well as insert and other statements. What will be the behaviour of the stored procedure in a Read Only Routing configured in AlwaysOn ...
- 357
1
vote
0
answers
146
views
Is it possible to create a Linked Server from SQL Server 2000 to SQL Server 2016?
I am trying to create a Linked Server from SQL Server 2000 to SQL Server 2016 R2. However, while creating I am getting the below error when trying to create Linked server from SQL Server 2000.
TCP ...
1
vote
4
answers
72
views
How to enforce SQL Server policies in order to avoid users enabling bad practices
I have some rogue Windows Administrators who give themselves SA and then grant end-users SA.
These users then end up enabling auto-shrink, auto-growth percentage and all kinds of other poor practices -...
- 263
0
votes
2
answers
41
views
T SQL, Get new customer added or deleted from historical table
I have a Table containing historical data. Every day the same staff list will be added with the new dates (between 1200-12500 staff each day however only a few staff might be newly added todaysDate or ...
- 3
0
votes
1
answer
110
views
SQL Server License Migration [closed]
We need to move our SQL Server 2016 standard License. Our plan is to install SQL Server on the new box with the same license key , test the application / databases and then delete the old SQL server ...
- 1,003
0
votes
1
answer
74
views
Will an underpowered mirror host cause: The query processor could not start the necessary thread resources for parallel query execution [duplicate]
We recently moved our 2016 sql server mirror into Azure, but it's on an underpowered machine. Our main database server has 16 physical cores with hyper threading and 2 numa nodes. We have set the ...
- 101
1
vote
1
answer
41
views
Is it possible to "bind" sp_executesql to the calling procedure, as recorded in the Query Store?
Statements executed with sp_executesql appear to be generally "unbound" from the procedure in which they appear. By "bind", I simply mean "associated to the calling object&...
- 195
2
votes
3
answers
800
views
Slow Parallel SQL Server query, almost instant in serial
I have a SQL Server query as follows (obfuscated):
UPDATE [TABLE1]
SET [COLUMN1] = CAST('N' AS CHAR(1))
FROM [TABLE1]
WHERE (COLUMN1 = '2' AND COLUMN2 IN('VAL1', 'VAL2', 'VAL3')) OR
...
- 1,379
1
vote
0
answers
61
views
MS SQL Server Enterprise 2016 is truncating the audited select statements, how do we fix it?
TL;DR;
I am getting SELECT as the audit output instead of the actual (suspected) query SELECT DISTINCT CONVERT(varchar, DecryptByPassphrase(getkey(N'key1'), FIELD1)) as DECRYPTED_FIELD1 FROM view1 ...
- 113
0
votes
1
answer
2k
views
Recursive CTE to get Grandparent, parent, children [duplicate]
I have a data structure that relies on a parent/child model. The top level parent can have multiple children where they can also be parents.
Here's an example of how things can look in the tree:
...
- 309
0
votes
0
answers
94
views
Slow query SQL Server 2016
I have a query that is taking more than 7 hours and doesn’t finish, as it doesn’t finish I can’t generate the actual execution plan.
Here's brentozar's Paste The Plan
It has a temporary table, and ...
2
votes
3
answers
56
views
Preserving full refresh data source
I have a SQL Server database that gets updated every week with a completely new set of data.
I am currently using two staging databases. A series of view databases are connecting to these and one week ...
- 21
-1
votes
2
answers
31
views
Tool to Execute SQL Server Scripts and Automatically Recognize and Prompt for Scripting Variables [closed]
I have a folder of scripts that contain multiple objects and jobs that I roll out every time I deploy a new SQL Server Instance. The scripts utilize scripting variables, as an example, here is an ...
- 1,379
4
votes
2
answers
139
views
In-Memory OLTP SQL Server 2016 - insufficient system memory
I am trying to place an In-Memory OLTP table,
Here is the memory estimate you will need:
But when the migration starts, the following error message is displayed:
The size of my table is ...
- 41
0
votes
1
answer
129
views
High Latch Wait time with Logon trigger enabled while full backups are running
I have 14 production servers with SQL 2016 standard version, 128 gb of RAM, 16 CPU and SSD drives.
Microsoft SQL Server 2016 (SP2-CU15-GDR) (KB4583461) - 13.0.5865.1 (X64) Oct 31 2020 02:43:57 ...
- 1,949
1
vote
1
answer
727
views
In SQL Server, what's the best way to split multiple delimited strings into rows without a function?
I know there are numerous examples of how to split strings in SQL Server. However, they are often for a single delimited string. What's the best way to split multiple delimited strings without using a ...
- 135