Questions tagged [sql-server-2017]
SQL Server 2017 (major build version 14.00.xxxx). Please also tag sql-server.
1,041 questions
172
votes
4
answers
179k
views
Help installing SQL Server 2017 - VS Shell installation has failed with exit code 1638
Any suggestions on how to deal with this error:
TITLE: Microsoft SQL Server 2017 Setup
------------------------------
The following error has occurred:
VS Shell installation has failed with exit ...
- 3,592
43
votes
2
answers
3k
views
Why does changing the declared join column order introduce a sort?
I have two tables with identically named, typed, and indexed key columns. One of the them has a unique clustered index, the other one has a non-unique.
The test setup
Setup script, including some ...
- 9,133
39
votes
1
answer
6k
views
How do you create a view with SNAPSHOT_MATERIALIZATION in SQL Server 2017?
SQL Server 2017 has a couple new stored procedures:
sp_refresh_single_snapshot_view – input param for @view_name nvarchar(261), @rgCode int
sp_refresh_snapshot_views – input param for @rgCode int
...
- 43.2k
31
votes
1
answer
1k
views
Self referencing scalar function nesting level exceeded when adding a select
Purpose
When trying to create a test example of a self referencing function, one version fails while another one succeeds.
The only difference being an added SELECT to the function body resulting in ...
- 16.5k
29
votes
2
answers
130k
views
SQL Server returns "Arithmetic overflow error converting expression to data type int."
When I run this command with SUM()
SELECT COUNT(*) AS [Records], SUM(t.Amount) AS [Total]
FROM dbo.t1 AS t
WHERE t.Id > 0
AND t.Id < 101;
I'm getting,
Arithmetic overflow error ...
- 64.6k
28
votes
6
answers
12k
views
Changing the use of GETDATE() in the entire database
I need to migrate an on-premises SQL Server 2017 database to an Azure SQL database, and I'm facing some challenges since there's quite a bit of limitations to go through.
In particular, since an ...
- 2,576
25
votes
1
answer
2k
views
SQL Server 2017 crashes when backing up because filepath is wrong
I was trying to restore my database and SQL Server kept crashing. I would get a message in SSMS that said there was a network transport error (the connection dropped bc the crash). I checked the logs ...
- 415
24
votes
4
answers
5k
views
If a database only ever has one insert, is it bad to index every possible column combination?
I am working on a reporting system that will require large select queries, but is based on a database that is only filled once. The database management system is Microsoft SQL Server 2017. There is ...
- 365
24
votes
3
answers
2k
views
Why would a table with a Clustered Columnstore Index have many open rowgroups?
I was experiencing some performance issues with a query yesterday and upon further investigation, I noticed what I believe is odd behavior with a clustered columnstore index that I'm trying to get to ...
- 9,716
22
votes
4
answers
33k
views
SQL Server has encountered occurences of I/O requests taking longer than 15 seconds
On Production SQL Server, we have following config:
3 Dell PowerEdge R630 servers, combined into Availability Group
All 3 are connected to a single Dell SAN storage unit which is a RAID array
From ...
- 5,964
22
votes
3
answers
10k
views
SQL Server Distributed Availability Group databases not syncing after a server reboot
We're getting ready to perform a large upgrade on our SQL Servers and are noticing some unusual behavior with Distributed Availability Groups that I'm trying to resolve before moving forward.
Last ...
- 9,716
19
votes
2
answers
4k
views
Which collation should I use for biblical Hebrew?
Which SQL Server collation should I use for biblical Hebrew? The database under consideration needs to accommodate diacritics (i.e., vowels, accents, trope, etc.).
- 191
18
votes
1
answer
75k
views
Messages about parallel redo
Parallel redo is shutdown for database '' with worker pool size [2].
Parallel redo is started for database '' with worker pool size [2].
Starting up database ''
I see this on a client PC a lot in ...
- 309
17
votes
1
answer
12k
views
Error starting SQL Server 2017 service. Error Code 3417
I have SQL Server 2017 installed on my computer. This is what SELECT @@VERSION returns:
Microsoft SQL Server 2017 (RTM-GDR) (KB4293803) - 14.0.2002.14 (X64) Jul 21 2018 07:47:45 Copyright (C) ...
- 273
16
votes
1
answer
9k
views
Cannot insert duplicate key row on a non-unique index?
We have encountered this strange error three times over the past few days, after being error free for 8 weeks, and I'm stumped.
This is the error message:
Executing the query "EXEC dbo....
- 1,336
16
votes
5
answers
3k
views
Attempts to reclaim unused space causes the used space to increase significantly in SQL Server
I have a table in a production database that has a size of 525 GB, of which 383 GB is unused:
I'd like to reclaim some of this space, but, before messing with the production DB, I'm testing some ...
- 193
15
votes
3
answers
3k
views
Increase speed altering column on large table to NON NULL
I recently added a NULL-able bit column to a table which has close to 500 million rows. There's not a default on the column, however all inserts are specifying a value of 0 or 1, and I ran a one-time ...
- 293
15
votes
1
answer
22k
views
Clone a database from SQL Server 2019 to 2017 [duplicate]
I try to clone a database from an Sql Server on my local machine v15 (2019) to a server machine v14(2017). I did a .bak file and when I tried to import on the server side it says:
System.Data....
- 451
15
votes
1
answer
1k
views
Why is this stream aggregate necessary?
Check out this query. It's pretty simple (see the end of the post for table and index definitions, and a repro script):
SELECT MAX(Revision)
FROM dbo.TheOneders
WHERE Id = 1 AND 1 = (SELECT 1);
...
- 29.8k
15
votes
1
answer
601
views
Cardinality estimate outside the histogram
Setup
I'm having some trouble understanding a cardinality estimate. Here's my test setup:
the 2010 version of the Stack Overflow database
SQL Server 2017 CU15+GDR (KB4505225) - 14.0.3192.2
the new ...
- 29.8k
15
votes
2
answers
2k
views
SQL Server 2017 with 500 databases - Frequent AG disconnects since CU9
Hi everyone and thanks in advance for your help.
We are experiencing challenges with SQL Server 2017 Availability Groups.
Background
Company is a retail B2B back-end software. About 500 single ...
- 4,098
14
votes
4
answers
4k
views
Fast alter column NVARCHAR(4000) to NVARCHAR(260)
I have a performance problem with very large memory grants handling this table with a couple of NVARCHAR(4000) columns. Thing is these columns are never larger than NVARCHAR(260).
Using
ALTER TABLE [...
- 141
14
votes
5
answers
515
views
What is the fastest way to convert many nullable ints 1:1 to a binary string?
Part of my workload uses a CLR function that implements the spooky hash algorithm to compare rows to see if any column values have changed. The CLR function takes a binary string as an input, so I ...
- 32.6k
14
votes
1
answer
18k
views
How do I totally remove SQL Server 2017 on Ubuntu?
The docs has instructions about how to remove SQL Server. However, this leaves certain packages behind. How can I remove all traces and uninstall SQL Server 2017?
- 64.6k
14
votes
2
answers
11k
views
Will SSRS be extinct soon, and PowerBI the new model?
I read SQL Server 2017 will now include PowerBI Server. They also moved SSRS to a different installer, so it will not come packaged in the original SQL Server installation. Does this mean Microsoft ...
user129291
14
votes
2
answers
2k
views
Why is a temp table a more efficient solution to the Halloween Problem than an eager spool?
Consider the following query that inserts rows from a source table only if they aren't already in the target table:
INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
SELECT ...
- 32.6k
14
votes
1
answer
476
views
Why does this RX-X lock not appear in Extended Events?
The Problem
I have a pair of queries that, under serializable isolation, cause an RX-X lock. However, when I use Extended Events to watch lock acquisition, the RX-X lock acquisition never appears, it ...
- 4,179
14
votes
2
answers
14k
views
Error updating temporal tables
I have implemented the System Versioned Temporal Tables on the table "Orders". The application is using different access patterns to modify the data in this table. There are direct statements coming ...
- 1,428
13
votes
1
answer
2k
views
Temporarily moving a SQL Server 2016 database to SQL Server 2017 and then moving back. Is it possible?
If I take a backup of a database from a SQL Server 2016 instance and then restore it to a 2017 instance to do some work on it.
Can I then turn around and back up that database from the 2017 instance ...
- 241
13
votes
2
answers
4k
views
Is there an efficient way to see the cause for "String or binary data would be truncated"?
This is a follow-up on this question. It is also related to this feature request from Microsoft.
However, many years have passed and several major releases reached the market since it was reported.
...
- 1,171
13
votes
3
answers
2k
views
Queries and updates extremely slow after IndexOptimize
Database SQL Server 2017 Enterprise CU16 14.0.3076.1
We recently tried switching from the default Index Rebuild maintenance jobs to the Ola Hallengren IndexOptimize. The default Index Rebuild jobs ...
- 133
13
votes
1
answer
833
views
Physical only checkdb is failing, but full one is completed successfully
I am executing checkdb with physical_only option and it fails with multiple errors like below:
Msg 8965, Level 16, State 1, Line 1 Table error: Object ID 1557580587,
index ID 1, partition ID ...
- 313
13
votes
1
answer
5k
views
Solving intra parallel query deadlocks
We are experiencing deadlocks in our production environment. Seems to be the victim and winner are the same spid's. We rebuild/reorganize indexes daily and we dont find any missing indexes.
Have you ...
- 265
13
votes
2
answers
24k
views
Unable to start SQL Server on Ubuntu 16.04
I had SQL Server v.Next public preview on my Ubuntu 16.04 machine installed and working yesterday. But it's not working today.
Getting below error:
root@OraServer:/var/opt/mssql/log# systemctl ...
- 417
13
votes
1
answer
1k
views
Backup Compression Causing Corruption In SQL 2017 TDE Database
On SQL Server 2017 (CU3), whenever I enable backup compression on one of my TDE databases, the backup process always corrupts a specific page in the database. If I run the backup without compression, ...
- 561
12
votes
2
answers
3k
views
Why does a DELETE query run in one format much longer than in another?
I have specific cleanup code that tries to remove some duplicates.
This runs perfectly on many customer sites. The logs tell me that at least 1 sec up to 45 sec is consumed by this query:
DELETE ...
- 230
12
votes
2
answers
2k
views
Prevent SSMS from seeing the server's file system
I have several users who share an MS SQL server 2017 under my administration. They should not see (or even be aware) of the other users and their data on that server. Each user has his own database. ...
- 335
11
votes
1
answer
4k
views
Non-yielding scheduler error in large memory server if the lock pages in memory option is disabled
We are seeing a lot of Non-yielding scheduler errors on a SQL Server VM. Reading this link on Microsoft Docs, it's recommended we enable Lock Pages in Memory.
I do not have much experience with Lock ...
- 3,426
11
votes
1
answer
2k
views
If you disable a non-clustered index, are the statistics still used?
tl;dr - As the title states - If I disable a non-clustered index on a table, are the linked statistics for that index still used?
I'm aware there are many questions about "dropping vs disabling ...
- 213
11
votes
2
answers
46k
views
Getting "sqlcmd sqlcmd: command not found" in Linux?
Whenever I run sqlcmd I get "command not found"
sqlcmd -S localhost -U SA -P '<YourPassword>'
sqlcmd: command not found
How can I resolve that?
- 64.6k
11
votes
2
answers
1k
views
Why am I getting an implicit conversion of Int / Smallint to Varchar, and is it really impacting Cardinality Estimates?
I'm trying to trouble shoot a slow performing query using Show Plan Analysis (SSMS) on the actual execution plan. The Analysis tool points out that estimates for number of rows are off from returned ...
- 257
11
votes
2
answers
944
views
Why/when does SQL Server evaluate the probe side of an inner hash join when the build side was empty?
Setup
DROP TABLE IF EXISTS #EmptyTable, #BigTable
CREATE TABLE #EmptyTable(A int);
CREATE TABLE #BigTable(A int);
INSERT INTO #BigTable
SELECT TOP 10000000 CRYPT_GEN_RANDOM(3)
FROM sys....
- 86.5k
11
votes
3
answers
5k
views
Distributed Availability Group with Manual Seeding
I am looking for a step by step walk through of how to setup a distributed availability group with manual seeding. I can get the automatic seeding working but when I try to manually seed I cannot get ...
- 971
11
votes
1
answer
761
views
Strange query plan when using OR in JOIN clause - Constant scan for every row in table
I'm trying to produce a sample query plan to show why UNIONing two result-sets can be better than using OR in a JOIN clause. A query plan I've written has me stumped. I'm using the StackOverflow ...
- 113
11
votes
1
answer
627
views
Sparse columns, cpu time & filtered indexes
Sparsing
When doing some tests on sparse columns, as you do, there was a performance setback that I would like to know the direct cause of.
DDL
I created two identical tables, one with 4 sparse ...
- 16.5k
11
votes
1
answer
3k
views
bcp command Incorrect syntax near '�'. Character is actually: "ä"
I have mssql-server and mssql-tools installed on Ubuntu (Linux). When I try to export data with the bcp command using the following command-line:
bcp DBname.dbo.Täble_Name out Täble_Name -c -k -...
- 113
10
votes
4
answers
7k
views
Increased memory to server, but do I need to change page-file size?
I'm running SQL Server 2017 on Windows Server 2012 R2 and have increased the memory from 256 GB to 512 GB.
I've noticed the following configuration for Virtual Memory:
C: [OS] = 2 GB
G: [DATA] = ...
- 131
10
votes
3
answers
3k
views
Long Running Query on Read-Only Replica that takes moments on the Primary
I've got a 4 node AG setup as follows:
VM Hardware Configuration of all nodes:
Microsoft SQL Server 2017 Enterprise Edition (RTM-CU14) (KB4484710)
16 vCPUs
356 GB RAM (long story to this one...)
max ...
- 9,487
10
votes
1
answer
8k
views
"Warnings: Operation caused residual I/O" versus key lookups
I've seen this warning in SQL Server 2017 execution plans:
Warnings: Operation caused residual IO [sic]. The actual number of rows read was (3,321,318), but the number of rows returned was 40.
...
- 2,239
10
votes
3
answers
2k
views
DB backups have suddenly gotten smaller
About 8 GB has been trimmed off of the size of our main database. This is reflected in backups and when we look at free vs available space.
Everything seems to be working and we can't find any sign ...
