Questions tagged [sql-server-2017]
SQL Server 2017 (major build version 14.00.xxxx). Please also tag sql-server.
97 questions
5
votes
4
answers
37k
views
SQL database stuck in recovery
I have SQL server 2017. I have one 3 TB size database there. Somehow due to long running transaction the database got stuck 'IN Recovery' mode after SQL server Restarted. When I checked the sql error ...
- 161
8
votes
3
answers
7k
views
How can I export Query Store data?
I have Query Store (QS) running on a SQL 2017 instance. Currently at RTM, with RTM CU13 currently in test, to be applied on prod in next month's patch window.
While most queries and reports return ...
- 6,318
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
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
5
votes
1
answer
2k
views
SQL Server 2017: How do Batch Mode Adaptive Joins work?
Batch Mode Adaptive Joins are part of a family of features in the 2017 query processor that consists of:
Batch Mode Adaptive Joins
Interleaved Execution
Batch Mode Memory Grant Feedback
So how do ...
4
votes
1
answer
546
views
SQL Server 2017: How does Interleaved Execution work?
Interleaved Execution is part of a family of features in the 2017 query processor that consists of:
Batch Mode Adaptive Joins
Interleaved Execution
Batch Mode Memory Grant Feedback
So how does ...
3
votes
2
answers
2k
views
Can I disable the spyware in SQL Server?
There are two titles for the spyware in SQL Server,
Customer Experience Improvement Program (CEIP)
Telemetry
I'm wondering how I disable them. You can see them referenced here,
My SQL Server says ...
- 64.7k
3
votes
1
answer
896
views
SQL Server 2017: How does Batch Mode Memory Grant Feedback Work?
Batch Mode Memory Grant Feedback is part of a family of features in the 2017 query processor that consists of:
Batch Mode Adaptive Joins
Interleaved Execution
Batch Mode Memory Grant Feedback
So ...
3
votes
1
answer
6k
views
SQL Server sys.dm_tran_active_transactions long running transaction worktable
today I used the system object sys.dm_tran_active_transactions (to be honest for the first time). I had an issue with distributed transactions (Microsoft DTC) and went digging there after I had no ...
- 705
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
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
10
votes
1
answer
4k
views
Unbelievably slow and unusable Query Store
I'm beginning with Query Store and it's with problems :-( No report of Top Resource Consuming Queries is ever generated. It just keeps saying "Waiting" for hours and hours. Query on the ...
- 945
8
votes
2
answers
10k
views
MS SQL Server: Do multiple queries in a batch ever execute in parallel and if so what happens when the second query is dependent on the first?
If I have a batch of two different SELECT statements, is it possible for them to be executed in parallel (if the SQL Optimizer deems it to be the most efficient execution method)?
If the first SELECT ...
- 39.5k
7
votes
2
answers
16k
views
How do you directly modify the system catalogs in SQL Server 2017?
NOTE: I understand the risks involved, and the possibility of destroying a production system by doing this. I'm interested in doing it anyway.
Whenever I try to play with system catalogs, I gets ...
- 64.7k
6
votes
4
answers
10k
views
Backup a database from Azure SQL Managed Instance and restore to on premise SQL Server
I have three databases that I am migrating to Azure SQL Managed Instance. One is a Utility database, just a small collection of generic functions and procedures. One is the OLTP database for a web ...
- 291
5
votes
1
answer
1k
views
Performance gain with MultiSubnetFailover for single subnet
On BOL I read following about MultiSubnetFailover=True:
The MultiSubnetFailover connection option should be set to True even if the availability group only spans a single subnet. This allows you to ...
- 2,124
3
votes
0
answers
750
views
RDS MSSQL in-place upgrade how to update master db compatibility level when rdsa account is db owner
Background
My workplace infrastructure team ran a typical in-place upgrade of an RDS MSSQL instance to update the version from 2012 to 2017.
After the upgrade, while logged in as the "master ...
- 751
2
votes
1
answer
564
views
What differences are there with SQL Server 2017's packaging of `Binn` on Linux and Windows?
Knowing that the Binn is packaged differently on Windows and Linux, I'm wondering what differences there are between the two? Do they come with different utilities? On Linux Binn is distributed in a ...
- 64.7k
2
votes
1
answer
3k
views
Unexplained sustained space used in the TempDB log file
A few days ago the amount of used space in both the tempdb data and log files started growing rapidly. After 30 minutes, the tempdb log remained around 99% space used for about 7 hours. Towards the ...
- 293
2
votes
1
answer
525
views
Check Free Space in SQL Server (License Limit)
I have a Microsoft SQL Server 2017 Express Edition those have a size limit of 10240MB (10Gigs) and I am not sure how I can check how much space I have left in this license. I have tried the following:
...
- 23
2
votes
1
answer
2k
views
How can you have a Buffer cache hit ratio of 100% with PLE of 103? [duplicate]
I found What does a "Buffer cache hit ratio" of 9990 mean? (I was getting result of 9000+ from a blog that was wrong) and used the solution by LowlyDBA/Denis Gobo combined with the PLE ...
- 6,318
1
vote
1
answer
126
views
Compatibility level of databases based on sql server version
We need to check for databases that are not in the correct compatibility level on a particular instance. For example, on a SQL Server 2016 instance, if there are databases that are not in ...
- 11
1
vote
3
answers
13k
views
SQL Server 2017 Installation issues
When installing SQL Server 2017 on my computer/server I am receiving an installation error.
Error Log
Overall summary:
Final result: Failed: see details below
Exit code (Decimal)...
- 191
1
vote
3
answers
2k
views
SQL Server 2017 login language display is wrong
I have just done a clean install of SQL Server 2017 (CU15) on two of my servers, and have noticed that the displayed language for all my logins shows ARABIC instead of '' or 'English'.
Here is the ...
- 55
0
votes
1
answer
820
views
Piecemeal restore only one filegroup without primary filegroup restore
I have a db in SIMPLE recovery model, and regularly take FULL,DIFF backups.
This db also have filegroups for each month.
Each filegroup has exactly one NDF file.
Like this:
FileGroup: PRIMARY
File: ...
- 265
0
votes
1
answer
125
views
Replication reverted partitioned table to primary filegroup, can I re-link to the partitions?
We have 2 servers:
Server A. production (no partitions)
Server B. New Server (partitions and compression)
We restored a backup to the new server, partitioned a large table by datetime and turned on ...
- 301
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
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
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
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
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
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
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.7k
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
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
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
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
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
1
answer
632
views
Why shouldn't INFORMATION_SCHEMA views be used to determine the schema of an object?
According to MS-DOCS about System information schema views, the schema columns definition have a warning note that says:
** Important ** Do not use INFORMATION_SCHEMA views to determine the schema ...
- 23.9k
9
votes
3
answers
2k
views
Does SQL Server allow (make visible) DDL inside a transaction to the transaction prior to commit?
In PostgreSQL I can create a table with some test data, and then in a transaction migrate it to a new column of a different type resulting in one table-rewrite upon COMMIT,
CREATE TABLE foo ( a int );...
- 64.7k
9
votes
3
answers
10k
views
SQL Server Error, "Invalid usage of the option FIRST in the FETCH statement."
From 2012 onward the SQL Server docs show they support OFFSET..FETCH which I'm trying to use instead of a LIMIT.
The following works fine in PostgreSQL to sample a result set,
SELECT *
FROM ( VALUES ...
- 64.7k
9
votes
2
answers
9k
views
Cannot remove filegroup with no files associated
I am experiencing some odd error messages on SQL Server 2017 CU3. I am migrating databases and reorganising filegroups. By "reorganising" I mean that I use a stored procedure which creates a partition ...
- 705
9
votes
1
answer
8k
views
Can STRING_AGG be used multiple times with different delimiters?
I was doing some string aggregation and noticed what looked like a bug to me. STRING_AGG was used twice in the same query with different separator arguments. However, both produced the same results (...
- 760
8
votes
3
answers
3k
views
What is causing this error: None of the specified replicas for availability group ag1 maps to the instance of SQL Server to which you are connected
I am configuring Always-on for SQL Server 2017 RC1 (14.0.80.90, dated 2017-7-18) for Linux as per https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-availability-group-configure-ha. This ...
Erik Brandsberg
8
votes
1
answer
190
views
Data inherently ordered as if it were a clustered index
I have the following table with 7.5 million records:
CREATE TABLE [dbo].[TestTable](
[Id] [int] IDENTITY(1,1) NOT NULL,
[TestCol] [nvarchar](50) NOT NULL,
[TestCol2] [nvarchar](50) NOT ...
- 215
7
votes
1
answer
2k
views
How to avoid Table Lock Escalation?
I've got a task to update 5 million rows in a production table, without locking down the whole table for extended time
So, I used approach that helped me before many times - updating top (N) rows at a ...
- 5,964
6
votes
1
answer
13k
views
Is Locked Pages In Memory Still Recommend in SQL Server 2017?
I have done some research about lock pages in memory, the question that still bothers me is (I know it really depends on your specifications but) is it still recommended to lock pages in SQL Server ...
- 299
6
votes
1
answer
2k
views
Clustered columnstore index space usage
I have a simple table with a clustered columnstore index:
ID INT NOT NULL,
Hash BINARY(20) NOT NULL
This table has some billion rows and according to sp_spaceused, sys.allocation_units and SSMS ...
- 318