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
3
votes
4answers
10k 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 ...
15
votes
5answers
1k 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 ...
6
votes
2answers
3k 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 ...
17
votes
1answer
7k 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) ...
3
votes
2answers
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 ...
5
votes
1answer
1k 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
1answer
427 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
1answer
2k 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 ...
3
votes
1answer
767 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 ...
31
votes
1answer
856 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 ...
10
votes
1answer
2k 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 ...
5
votes
1answer
434 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
votes
1answer
595 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
votes
2answers
7k 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 ...
2
votes
1answer
489 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 ...
1
vote
2answers
141 views

Multiple disks vs single disk on virtualized environment

We're planning to install a SQL Server on this physical architecture: A hyper-v cluster based on windows server 2019 A cluster shared volume where all VMs live A full-flash SAN that contains the CSV ...
7
votes
2answers
8k 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 ...
3
votes
0answers
199 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 ...
2
votes
1answer
269 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 ...
1
vote
3answers
1k 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 ...
1
vote
3answers
9k 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)...
0
votes
1answer
35 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 ...
168
votes
4answers
172k 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 ...
42
votes
2answers
2k 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 ...
20
votes
4answers
18k 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 single Dell SAN storage unit which is a RAID array From ...
13
votes
1answer
826 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, ...
14
votes
1answer
17k 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?
6
votes
1answer
9k 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 ...
11
votes
1answer
285 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 ...
8
votes
1answer
175 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 ...
5
votes
1answer
332 views

On SQL Server 2017 (Linux), where is the Resource Database persisted (located on disk)?

In my default install of SQL Server 2017 on Linux, under /var/opt/mssql/data I see the following databases, master.mdf - master Database - Records all the system-level information for an instance of ...
5
votes
1answer
413 views

Is it possible to create encrypted backups using encrypted by password certificate?

I have encrypted by password certificate, which I want to use for backup encryption. The issue is I am not able to use the certificate (in T-SQL statement or maintenance plan) because I am not able to ...
5
votes
3answers
6k 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 ...
4
votes
2answers
4k views

Will matching SQL Server network packet size to mtu increase performance

We have recently upgraded our production facility to sql server 2017, and moved to clusterless availability groups. There is a primary, a secondary on site, and a remote secondary. We were ...
4
votes
0answers
414 views

Why is my SSIS package not getting updated?

I have a Integration Services project within SSDT that has multiple packages. When I right click a single package and choose deploy, SSDT says the operation completed successfully. However, when I ...
14
votes
2answers
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 ...
10
votes
1answer
315 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 ...
9
votes
3answers
920 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 );...
8
votes
1answer
2k 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 ...
7
votes
2answers
1k 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://docs.microsoft.com/en-us/sql/linux/sql-server-linux-availability-group-configure-ha. This ...
4
votes
2answers
344 views

What is 'Log Memory' in Query Store 2017

In SQL 2017 there is a new execution metric, 'Log memory' other than that it was added in 2017 I am not finding anything about it. Execution metric: (SQL 2017) CPU time, Duration, Execution Count, ...
1
vote
2answers
1k views

Required free Disk Space for Index rebuild offline Operations

I have SQL Server 2017 Standard Editon with two Databases. The current total index size of Database1 is 111 GB and for Database2 is 122 GB. Since I'm on Standard Edition I can only rebuild the ...
11
votes
2answers
569 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 ...
11
votes
1answer
25k 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 ...
9
votes
2answers
5k 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 ...
9
votes
3answers
4k 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 ...
9
votes
1answer
4k 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. ...
6
votes
1answer
1k 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 ...
5
votes
1answer
450 views

What does sp_restore_filelistonly do?

In SQL Server 2017, after applying Cumulative Update 7, there's a new system stored procedure in master called sp_restore_filelistonly. What's it for?
4
votes
2answers
78 views

Display local variable in while loop

I have a while loop that is running for days (bydesign) see related Is there a limit to how many backups can be appended to a single file? I have a local variable that displays the loop count, when I ...