Questions tagged [sql-server-2016]
SQL Server 2016 (major build version 13.00.xxxx). Please also tag sql-server.
2,746
questions
0
votes
0
answers
12
views
SSRS 2016 running slow with blocking
SSRS 2016 - Standard Default Settings.
Work Horse for Reporting. Some large reports can run 5 hours+.
Most reports cannot be optimized/tuned - 3rd party vendor.
On occasion noticed procs "...
- 21
-1
votes
1
answer
47
views
Automatically fetch USD Exchange Rate from API and Update it in Microsoft SQL Database
I want to update a table in a database but I can't even fetch the rate from the API.
I get this error:
Msg 39007, Level 16, State 2, Procedure sp_execute_external_script, Line 1 [Batch Start Line 0]
...
0
votes
0
answers
35
views
SSIS Error: "Metadata could not be determined because procedure uses temp table"
We have recently restored SSIS Packages and I'm facing this error whenever I try to execute a SSIS package:
Unable to determine metadata because « INSERT INTO #File_Results EXEC Master.dbo....
3
votes
0
answers
92
views
Why am I getting RESOURCE_SEMAPHORE timeouts when there should be plenty of memory available for grants?
We occasionally have queries time out due to RESOURCE_SEMAPHORE waits but there appears to be plenty of available memory for memory grants. For example, I'm able to see timeouts by executing two ...
- 32k
1
vote
1
answer
47
views
Why does a SAVE TRAN outside of a CURSOR loop not work, but inside does?
Why, when my SAVE TRAN SavePoint is at location (A) do I get these errors on the third instance of falling into the CATCH and beyond...
"Msg 6401, Level 16, State 1 ... Cannot roll back ...
0
votes
1
answer
40
views
Database Mirroring - Failed Logins on primary after failover
We have a Database Mirroring setup between PROD and SECONDARY and we have failed over to the secondary replica (SECONDARY) . We are now getting a number of almost constant failed logins showing in the ...
- 1,928
0
votes
1
answer
46
views
Batch update in a large dataset
We have 3 tables:
Product (product)
Stock (product_Stock)
Price (product_price)
In each table, we have more than 290 bilions of rows. These tables have a compound primary key: product_fk and ...
- 191
0
votes
1
answer
47
views
Creating AGL for Named Instance on SQL Server 2016 [duplicate]
I have installed a Named Instance on an existing server so the set up is like the following:
Server 1:
QA01 - Default Instance
QA01\BU0 - Named Instance
Server 2:
QA02 - Default Instance
QA02\BU0 - ...
- 573
3
votes
1
answer
278
views
Are there any possibilties to mitigate data loss risk on delayed durability
Due to poor disk performance I need to evaluate using the delayed durability setting in SQL Server 2016.
Microsoft states clearly, that this comes with a data loss risk because transactions are ...
- 171
0
votes
0
answers
37
views
SQL Server 2016/2019 mixed AG causing constant DB transaction log growth
We have a SQL Server 2016 Always On Availability Group (AG) setup with 3 nodes and recently added 3 new nodes with SQL Server 2019.
After the addition of the new nodes, we noticed that the database ...
Jay
-3
votes
1
answer
343
views
System dbs have different collation
i am new on this forum.i have installed an instance of sql srv 2016 sp2 called DNF containing 2 dbs and also the system dbs.after providing the instance to the dev team the realise that they need to ...
- 1
0
votes
3
answers
136
views
Partitioning an existing table yearly in SQL Server with huge data
I have a logs table for my application which is almost 3TB data. I want to partition this table yearly.
Below is the scripts for Table, View, and indexes script:
GO
CREATE TABLE [dbo].[AuditLogs](
...
0
votes
1
answer
50
views
Adding custom table to local third-party ERP database
We have a locally hosted third-party SQL Server ERP database I've been working with for a while.
For one of our KPI reports we need historical data. This data is not generated by the ERP database ...
0
votes
0
answers
168
views
Report Server WMI Provider Error: Invalid namespace when connecting to Report Server Instance on Reporting Services Config Manager
I have two SQL Server 2016 instances (Instance A and Instance B) on the same server. Both instances have SSRS. At one point I upgraded Instance A to SQL Server 2019 but then due to the changes made to ...
- 1
0
votes
1
answer
73
views
Failover in AG without listener
We have SQL Servers with versions SQL Server 2016 and SQL Server 2019. We already check that we can do failover on SQL Server 2019 without listener but I can not test it on SQL Server 2016. Do you ...
- 295
-1
votes
1
answer
117
views
SP_SERVER_DIAGNOSTICS_SLEEP wait type
I have one problem. SP_SERVER_DIAGNOSTICS is in suspended state and starttime is not refreshed. I execute procedure SP_SERVER_DIAGNOSTICS manually and it is working normally. Some forums say its ...
1
vote
2
answers
546
views
DBeaver does not recognize declared variable when executing SQL script on SQL Server
We are using DBeaver Community Edition (v23.2.0.202309041200) as a client connecting to Microsoft SQL Server 2016 (v13.0.5101.9).
The issue happens when executing the below SQL statements in DBeaver ...
- 139
0
votes
2
answers
61
views
Compare two tables for discrepencies
I am trying to compare two tables in two separate databases (same instance)
for a new data extract
The tables have around 4 millions rows and due to how they've been extracted are not in the same ...
0
votes
1
answer
71
views
SQL Server Service Broker on a High Availability Group
I'm using a instance with Microsoft SQL Server 2016 || Enterprise Edition and I need to enable Service Broker for my database with this sentence:
ALTER DATABASE DB SET ENABLE_BROKER;
The size of the ...
- 37
0
votes
1
answer
170
views
Msg 9002 - The transaction log for database [dbName] is full due to 'CHECKPOINT'
The database in question is set to SIMPLE recovery mode. The disk is not out of space. The database is essentially read only. I can't change any of its properties or run a checkpoint. The transaction ...
0
votes
1
answer
51
views
Sql server migration side by side ( 2016 to 2019)
What is the best (i.e. fastest) way (backup and restore) for migrating a 6 TB database in SQL Server 2016 (Windows 2012r2) to another server, SQL Server 2019 (Windows 2019)?
Can we, for example, ...
- 5
0
votes
1
answer
55
views
Why does data file growth fail even with plenty of disk space?
We have a SQL Server 2016 (v13 SP3) Enterprise Edition server hosted in a Windows failover cluster / SQL Availability Group with two nodes (primary & secondary). The two nodes are running inside ...
- 740
3
votes
1
answer
364
views
What is "execution count" in Query Store?
We are using SQL Server 2016. We have some problems in terms of queries duration on 2023-09-11. Would like to check it on QueryStore and I saw on query store executed on 2023-09-11 05:00 PM:
That we ...
- 295
0
votes
0
answers
33
views
SQL Server CEIP login account
One of my servers have login of "DOMAIN\MACHINENAME$" in both login_name and original_login_name for SQLServerCEIP when checked using sp_who2.
Under services, SQL Server CEIP settings shows ...
- 167
0
votes
1
answer
57
views
Got an error when trying to get ER-diagram on SQL Server
We are using Microsoft SQL Server 2016 Management Studio (version 13.0.16106.4) connecting to an instance of SQL Server 2016 running on Windows 2016. The authentication is "SQL Server ...
- 139
4
votes
1
answer
534
views
Compilations/sec high when load testing a simple procedure
I am trying to load test a simple insert stored procedure:
CREATE TABLE _test(ID BIGINT)
GO
CREATE OR ALTER PROCEDURE dbo.test_sp
AS
BEGIN
SET NOCOUNT ON;
BEGIN
INSERT INTO _test
...
- 1,451
0
votes
1
answer
62
views
Text Search on Multiple Columns without enabling FULL TEXT Search
I have a scenario where we need to enable a multi-keyword search within a text field. This search should involve scanning the keywords across several columns (approximately 4-6) in multiple tables (...
- 4,053
2
votes
2
answers
125
views
Group by running totals with reset
Sample Data
Expected Data
Need to append trades under an order in a record, was able to do that using string_agg, now I need to split the orders with a maximum value of 10. That is, the quantity for ...
- 23
0
votes
0
answers
194
views
Remote SQL Connection Failing due to MS SQL Server running under a domain account
I'm getting a strange issue with one Microsoft SQL Server 2016. I get the following error if the MS SQL Server runs under a domain account. Just from one Linux client, all other clients work as ...
0
votes
0
answers
39
views
SSRS 2016 compatibility with SQL 2017 after SQL2016 upgrade
We have an SQL Server database engine on the 2016 version. We also have SSRS in the 2016 version. I plan to upgrade the SQL database engine from the 2016 version to the 2017 for it to participate in ...
- 5
-1
votes
1
answer
22
views
dm_exec_query_stats returning all NULLs for Worker and Elapsed time
I am executing the following statement on a customer's SQL Server. The version is Microsoft SQL Server 2016 (SP3-GDR) (KB5021129) .
declare @Milliseconds int
SELECT top 200 *
--sum([Total Worker ...
- 373
0
votes
0
answers
61
views
Unable to insert data into specific table partition, it is showing identity error
Unable to insert data into specific table partition it is showing identity error.
Msg 8101, Level 16, State 1, Line 172
An explicit value for the
identity column in table 'dbo.orders' can only be ...
0
votes
0
answers
19
views
Changing Encyption on Sql Server 2016 Column from Triple Des to AES256
In the process of changing encryption from Triple Des to AES 256. Have been advised to have keys for both available for clients on server until migration is complete. First question is can I have a ...
0
votes
0
answers
80
views
SQL Default trace not deleting files above the max no of trace files
We have a Sql server 2016 Sp2. Noticed the trace file path D:\Data\MSSQL13.MSSQLSERVER\MSSQL\Log\log_298.trc is getting filled with trace files and the files are more than 2 months old. To my ...
- 1,039
0
votes
2
answers
169
views
How to Import 21k Comma Delimited Text Files having a few columns with quote delimited comma separated values into SQL Server
I inherited a broken process, need to fix it and I'm hoping you, my fellow DBAs, may have a few good solutions.
There's a Windows server folder containing 21,663 CSV files that need to be imported ...
- 1
0
votes
0
answers
150
views
In Snowflake, and in SQL Server, how do I unpivot 83 columns into 83 rows?
I need to compare the SUMs of 83 columns in my source table (in SQL Server) to the SUMs of 83 columns in my target table (in Snowflake) to see if the SUMs of the source and target table columns match. ...
- 65
0
votes
0
answers
45
views
SQL Server Replication between two AlwaysOn HADR Clusters
We got a requirement to replicate data between two different HA Clusters.
---Current Prod Environment-------
SQL Server 2016 Servers
HADR Cluster 1 (HA1) - Primary Replica (PR1), Secondary Replica (...
- 412
0
votes
0
answers
49
views
UAC/elevation of proxy accounts in SQL Server 2016/2019
I have a puzzle:
We have 3rd party tool for backups that we trigger via cmd commands executed in SQL Agent jobs. The tool requires that the user initiating backups should be both local admin on OS and ...
- 23
0
votes
1
answer
122
views
MSSQL - Insert Into Select number from global count table and update the global count for each row then inserted
Not sure I've articulated that well in the title, so here goes...
I have three tables
Table1
ID
NewNames
10
some row data
11
some more row data
Table2
ID
OldNames
1
my old data
2
and more data
...
- 1
1
vote
1
answer
621
views
Do statistics have an impact on delete performance?
Background:
I am a developer working on an application that has a relatively large table (in my experience at least) of 1.7 billion records.
The table is a history table, and the application imports ...
- 113
0
votes
1
answer
21
views
Activity Monitor duplicates in recent expensive query
I have this strange issue on one of my DB (SQL Server 2016) in which the activity monitor display duplicates entries in recent expensive query.
In the picture above you can see a snapshot (only two ...
- 358
3
votes
2
answers
109
views
Performance problems on SQL Server 2016 that goes away after running two updates flipping a null column value
This is an abstract question, at least to begin with. But I am looking for theories on why I am experiencing what I am experiencing.
I have a table with a number of columns, one of which is a Int that ...
- 133
0
votes
0
answers
156
views
High volume of LCK_M_SCH_M waits on METADATA: database_id = 1 SECURITY_CACHE
We are suffering very bad performance after migrating 340 databases and 74000 logins from a SQL Server 2012 Standard Edition to a SQL Server 2016 standard edition (13.0.6404.1) in a new and more ...
1
vote
1
answer
120
views
CDC Error - NULL Source Schema and Source Table - Error msg on both enable and disable table
Today I re-enabled CDC on the primary node of an availability group. When the jobs were created the cleanup job failed with the following error:
Could not delete change table entries made obsolete ...
- 11
0
votes
1
answer
882
views
SQL to check if a certain time duration occurs within a particular period of the day
I'm working with data that has a start datetime and end datetime column for each event which can be a short period of time
(Few hours) up to a number of days in duration.
I'm trying to establish a way ...
- 13
0
votes
0
answers
136
views
Improving Query Performance on Non-Indexed Single HEAP File DB
My teammates and I often have to write queries that revolve around a database that is not partitioned (a single heap file) and has no index. I was told by the DBA that this won't change. Are there ...
- 101
1
vote
1
answer
119
views
SET SINGLE_USER stuck on wait type PRINT_ROLLBACK_PROGRESS
I want to drop a database on one of our systems (SQL Server 2016 SP2 CU17), but the process to set the database in single user mode is stuck on the wait type PRINT_ROLLBACK_PROGRESS. I use the ...
- 517
3
votes
1
answer
199
views
Simplified view for double-entry inventory transfers
I'm trying to get a clean view of a table showing product transfers between warehouses. There are 2 records for each transfer: a "FROM" record debiting the sending warehouse and a "TO&...
- 63
0
votes
0
answers
1k
views
How to fix error "Line 25: The statement terminated. The maximum recursion 100 has been exhausted before statement completion."
I added OPTION (MAXRECURSION 1000) at the very bottom but it was not enough. My query:
Use DatabaseName;
DECLARE @SOURCE_TABLE_NAME NVARCHAR(200) = 'WorkOrderVersion'
DECLARE @SOURCE_SCHEMA_NAME ...
- 1
0
votes
1
answer
129
views
Installed SQL Server 2016 latest GDR patch KB5021129 and my nightly integrity scans doubled in execution time (5 mins to 20-ish)
I was running SQL Server 2016 SP3 with 2022/06/13 GDR. The new GDR on 2023/02/14 came out - I applied it on May 4th. My integrity scan SQL Agent Job went from 5 mins to 20+ overnight. Nothing ...
- 81