Questions tagged [sql-server-2016]
SQL Server 2016 (major build version 13.00.xxxx). Please also tag sql-server.
2,732
questions
0
votes
0
answers
10
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
39
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
67
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
27
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
49
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
233
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 ...
- 283
0
votes
0
answers
13
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
33
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 ...
- 111
4
votes
1
answer
510
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
48
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,033
2
votes
2
answers
103
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
80
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
29
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
19
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
33
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
12
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
43
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,021
0
votes
2
answers
86
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
66
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
39
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 (...
- 402
0
votes
0
answers
34
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
44
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
578
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
18
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
104
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
91
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
74
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
536
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
113
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
99
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
194
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
473
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
109
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
0
votes
1
answer
44
views
Overkill or good application of normalization to create parent table just to associate a collection of children
I have a situation where I need to keep a group of detail rows together but where the "parent" has no additional attributes and the "children" have no purpose on their own. I ...
- 103
1
vote
1
answer
107
views
Slow Cross Apply Query Needs Optimization
I have a stored procedure which takes about 4 min to complete. I added some logging and discovered that the reason it is slow is the Cross Apply queries, of which there are 5. Each one takes about 40 ...
- 111
0
votes
0
answers
18
views
Replication Distribution issue after installed SP3
I have a system with replication distribution.
We have been asked to upgrade SQL Server 2016 to SP3. Now I saw issues surfaced out.
There are 2 servers in the sys.server table. This is what we have ...
Betty
2
votes
2
answers
62
views
Complicated relationship Q
I'm trying to do a query to find products that aren't on the customer's authorized products list, and it's breaking my brain.
Each customer has an authorized product group list, and within a product ...
- 63
0
votes
1
answer
43
views
Why do consecutive .bak full backup files increase in size?
First, this is NOT a duplicate of this question. In that question the person was over-writing the file each time and the solution is to use INIT or NOINIT.
In my case we are generating a unique ...
- 1,575
1
vote
1
answer
97
views
Ola Hallengren's Maintenance Solution script doesn't dump transactional log file to Destination
At one of our client sites, I implemented a high availability (HA) solution. However, during the backup process, I encountered an issue related to backup environment configuration. We had configured ...
- 23
0
votes
1
answer
36
views
Remote DB-replication with time-lapse sync
I have a central DB and three remote client DB, say RDB-A, RDB-B and RDB-C. Main DB is implemented on a SQL Server Standard instance and all the RDB on different SQL Express instances. The RDB-X are ...
- 13
4
votes
1
answer
205
views
How can I trace unknown high CPU usage on SQL Server?
I have a troublesome SQL Server instance that is causing up to 95% CPU usage on a 5-6 minute cycle. Stays at this level for about a minute or so then drops again only to start the cycle again. I am ...
- 41
1
vote
1
answer
239
views
Can I revoke database permissions on "NT AUTHORITY\SYSTEM" but still let it have the "sysadmin" role?
I need to implement this: https://www.stigviewer.com/stig/ms_sql_server_2016_instance/2020-12-16/finding/V-213934
In our instance IsClustered and IsHadrEnabled are both 0.
Using an automated tool it ...
- 111
1
vote
0
answers
49
views
Read a text file using SQLNCLI [closed]
I want to read a text (in this case, csv) from a file. I'm not trying to bulk-load for now, simply read.
As far as I can tell, only the SQLNCLI provider is available.
Ad Hoc Distributed Queries are ...
- 111
0
votes
1
answer
27
views
Why my rows getting duplicated and showing summing incorrectly?
I have these tables:
Payments
vendor | VendorLocation | VendorName | CheckDate | Amount | TransactionID | InvoiceID
---------------------------------------------------------------------
777 |...
- 125
0
votes
0
answers
106
views
MS Docs "Partitioned table parallelism" vs "Parallel query processing on partitioned tables and indexes"
The Microsoft documentation speaks of 2 features for SQL Server 2019 regarding parallelism that look very similar, but they are most likely very different. These are "Partitioned table ...
- 517
3
votes
2
answers
277
views
From last id, recursively find parent id and fill column
CREATE TABLE t1
(
COL1 nvarchar(36),
COL2 nvarchar(36),
ExpectedResult nvarchar(36)
)
INSERT INTO t1 (COL1, COL2, ExpectedResult)
VALUES
('CA4462D3-BD2C-4C80-A50E-586DA28A877A','CA4462D3-BD2C-4C80-...
- 33
0
votes
1
answer
71
views
Who was using a spid 2 hours earlier in SQL Server?
I have a specific spid from 2 hours ago, and would like to know who was using it.
Is this possible? Is this stored in the cache, some DMV, Ringbuffer? SQL Server 2019.
Thank you in advance.
- 13
0
votes
1
answer
173
views
This backup set cannot be applied because it is on a recovery path that is inconsistent with the database
I am trying to insert hourly log files after a database has been changed from:
"Standby / Read-Only" mode to
regular (online) mode to
"Read-Only" mode to
"Restoring" ...
- 213
4
votes
4
answers
425
views
Is there a way to disable logging / backups for view indexes?
We have a SQL Server database that uses indexed views to allow for efficient querying of some data that changes regularly (yes I know there's a cost to maintaining the index when the data changes but ...
- 143
3
votes
1
answer
595
views
Debugging slow SQL Server performance with SQL Profiler
I have been debugging an issue in our production environment(on-premises) where queries perform slowly even the columns are indexed. The table has roughly 200 million rows, is not partitioned and is ...
- 33