Skip to main content

Questions tagged [sql-server-2017]

SQL Server 2017 (major build version 14.00.xxxx). Please also tag sql-server.

Filter by
Sorted by
Tagged with
4 votes
4 answers
30k 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 ...
nicktheman's user avatar
8 votes
3 answers
6k 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 ...
James Jenkins's user avatar
17 votes
1 answer
11k 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) ...
Beginner's user avatar
  • 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 ...
Ken's user avatar
  • 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 ...
Erik Darling's user avatar
  • 41.9k
4 votes
1 answer
529 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 ...
Erik Darling's user avatar
  • 41.9k
3 votes
1 answer
889 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 ...
Erik Darling's user avatar
  • 41.9k
3 votes
1 answer
5k 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 ...
Martin Guth's user avatar
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 ...
Evan Carroll's user avatar
  • 63.7k
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 ...
Randi Vertongen's user avatar
14 votes
1 answer
21k 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....
serge's user avatar
  • 421
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 ...
warrior's user avatar
  • 265
10 votes
1 answer
3k 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 ...
jericzech's user avatar
  • 945
7 votes
2 answers
14k 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 ...
Evan Carroll's user avatar
  • 63.7k
7 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 ...
J.D.'s user avatar
  • 38.1k
6 votes
3 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 ...
CB_Ron's user avatar
  • 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 ...
Frederik Vanderhaegen's user avatar
3 votes
0 answers
696 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 ...
dev_etter's user avatar
  • 751
2 votes
1 answer
2k 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 ...
Ben Amada's user avatar
  • 283
2 votes
1 answer
467 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: ...
I dunno's user avatar
  • 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 ...
James Jenkins's user avatar
2 votes
1 answer
548 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 ...
Evan Carroll's user avatar
  • 63.7k
1 vote
1 answer
121 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 ...
Teja's user avatar
  • 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)...
HaBo's user avatar
  • 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 ...
Kulstad's user avatar
  • 55
0 votes
1 answer
105 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 ...
Quade's user avatar
  • 301
0 votes
1 answer
788 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: ...
Avi's user avatar
  • 265
172 votes
4 answers
178k 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 ...
Jonathan Allen's user avatar
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 ...
Daniel Hutmacher's user avatar
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 ...
Taryn's user avatar
  • 9,696
22 votes
4 answers
32k 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 ...
Aleksey Vitsko's user avatar
17 votes
1 answer
68k 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 ...
juFo's user avatar
  • 299
14 votes
1 answer
461 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 ...
Forrest's user avatar
  • 4,169
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 ...
Joe Obbish's user avatar
  • 32.5k
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?
Evan Carroll's user avatar
  • 63.7k
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, ...
Eric Cobb's user avatar
  • 561
13 votes
4 answers
3k 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 [...
Nick Whaley's user avatar
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 ...
Voysinmyhead's user avatar
11 votes
1 answer
589 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 ...
Randi Vertongen's user avatar
10 votes
1 answer
614 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 ...
McNets's user avatar
  • 23.8k
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. ...
Henrik Staun Poulsen's user avatar
9 votes
3 answers
9k 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 ...
Evan Carroll's user avatar
  • 63.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 ...
Martin Guth's user avatar
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 );...
Evan Carroll's user avatar
  • 63.7k
9 votes
1 answer
7k 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 (...
Steven Hibble's user avatar
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 ...
user avatar
8 votes
1 answer
187 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 ...
stefanhorne's user avatar
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 ...
Aleksey Vitsko's user avatar
6 votes
1 answer
964 views

What Other Information Is Stored in The Page Header

A SQL Server database page is defined as to be 8192 bytes in size. There is some header information which is said to be 96 bytes in size. And if you have ever tried to create a table with more than ...
John K. N.'s user avatar
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 ...
kirchner's user avatar
  • 318