Questions tagged [sql-server-2017]

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

212 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
6 votes
1 answer
836 views

Memory-optimized table index size 10X bigger than data

On one of our production servers, I found that index size is 10X times bigger than data size. These servers and schemas are identical with a similar workload. CREATE TABLE [dbo].[...
  • 193
4 votes
0 answers
63 views

Planning for application upgrade which connects to SQL server database

I am looking for some advise/guidance on how to achieve this with minimum downtime and possibly avoid any errors or failure during production change- Background- We have SQL database servers ...
4 votes
0 answers
534 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 ...
4 votes
0 answers
4k views

Safely drop master key

I've restored a database in my Dev environment (SQL version 14.0.1000.169) and I'm using the wizard to create a "Availability Groups", when I try to select the database, I get a pop up "This database ...
  • 115
4 votes
0 answers
3k views

Getting: Possible index corruption detected. Run DBCC CHECKDB

When I connect to the database and run a command on some catalogs, I get Msg 9100, Level 23 1> SELECT * FROM sys.databases; 2> GO Msg 9100, Level 23, State 2, Server x230, Line 1 Possible index ...
  • 57.3k
3 votes
0 answers
409 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 ...
  • 702
3 votes
0 answers
258 views

Why does sql server consider a function that extracts a value from an xml parameter non-deterministic?

I want to add a computed field that extracts a value from another xml column. The function looks like this: create or alter function extractValue(@val xml) returns varchar(14) as begin return @val....
  • 407
3 votes
0 answers
68 views

A curious discrepancy between DBCC PAGE and %%physloc%% for System Base Tables

During an attempt to dig into some odd system behavior I've come up against a scenario where I'm using the undocumented routine DBCC PAGE to find some information within a page, but I'm not getting ...
3 votes
1 answer
702 views

How to install Distributed Replay (dreplay.exe) for SQL Server 2017

In SQL Server 2014, dreplay.exe was available under "Management Tools – Basic" feature. However, in SQL Server 2017 that feature no longer exists (Enterprise edition) and it doesn't come with SSMS ...
  • 131
3 votes
1 answer
280 views

Does ordinal position of columns matter for SQL Server replication

I am setting up replication on SQL Server 2017. Transaction replication with push subscriptions such that many smaller database are replicated into a single larger database for reporting. The ...
  • 135
3 votes
1 answer
274 views

Solution for the deletion of old Database Backups for Linux SQL Server 2017 after Backups created with Ola Hallengren scripts?

I have a problem when I want to delete older backups, I've created with Ola Halengreen scripts. USE Maintenance EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory ='/mssql/backup/'...
  • 31
3 votes
0 answers
605 views

Import Oracle unicode data with OLE DB Source and destination in SSIS 2015

My issue is identical to this post on mssqltips.com, however my issue is importing from Oracle, not Excel. I get all the way to the bottom where I need to map the output columns from my data ...
  • 1,091
3 votes
1 answer
8k views

DATABASE lacks a quorum of nodes for high availability

Just after setting up successfully alwayson - availability group, testing the failover, I get this error message: TITLE: Microsoft SQL Server Management Studio Failed to resume data movement in ...
3 votes
1 answer
296 views

How to lock a MSSQL (2017 Linux) account after N unsuccessful login attempts

I have to configure account lock after N unsuccessful login attempts on MSSQL 2017 Linux. That is standalone server and is not in AD. I couln'd find any valuable information so far unfortunately. ...
3 votes
1 answer
130 views

Migrating a SQL Server 2014 AG to a new 2017 AG

Our current production server runs on SQL Server 2014 AlwaysOn AG. We are planning to upgrade it to SQL Server 2017 AlwaysOn. Due to the configuration issues with the present cluster, we can not use ...
3 votes
0 answers
1k views

Snapshot isolation transaction failed in database 'tempdb'

Upgraded from SQL Server 2008 R2 to SQL Server 2017. In SS 2008 R2 the application would occasionally get this error: “Snapshot isolation transaction failed in database 'tempdb' because the ...
3 votes
0 answers
223 views

FILESTREAM database options cannot be set on system databases such as 'model'

Summary of Question My question is for SQL Server 2017 is there a way you can re-attach the model database (without rebuilding master or restoring master) if it has been dropped given that both of ...
3 votes
0 answers
1k views

Developer Edition install error code -2068119551

As stated above I am attempting to install SQL Server 2017 Developer Edition and coming up with the following error: Exit Code (Decimal): -2068119551 Error Description: Attempted to perform an ...
3 votes
1 answer
1k views

What's a pathological case where a bitmap filter would not allow the PROBE(Field, IN-ROW) semijoin reduction optimization?

I recently learned a performance optimization in SQL Server I did not know about ("Bitmap Filter On (Clustered) Non-Nullable Column Hash Semi-join Reduction via Scan + Probe IN-ROW" - a real mouthful),...
2 votes
0 answers
105 views

Sys.dm_os_performance_counters DMV row counts

We have noticed an odd issue on two servers in a 3 node SQL Server 2017 cluster running Always On Availability Groups. When checking the DMV sys.dm_os_performance_counters the row counts are totally ...
  • 425
2 votes
0 answers
79 views

Service Broker performance issues after upgrading from 2012 to 2017 - tons of LCK_M_IX

Last night we upgraded a SQL Server 2012 server to SQL Server 2017 + CU 29. This is one of a matched set - we send the same message to both and each processes it. No, this is not AlwaysOn; we're ...
  • 433
2 votes
0 answers
83 views

Error communicating between BxlServer and client

have been trying to upgrade SQL server 2017 to run Python 3.7. We have installed CU28 and the product version is 14.0.3430.2 which includes the C:\Program Files\Microsoft SQL Server\MSSQL14....
2 votes
0 answers
210 views

Unable to Install SQL 2017 CU 28

I am in the process of upgrading our SQL 2017 servers to the current CU. I have ran across two systems that won't allow me to apply the update. In the SQL installer, I see below text when I click on ...
  • 21
2 votes
0 answers
65 views

What would make an append-only table remain in the buffer cache in SQL Server

We have a table that is pretty much append-only. It's not overly huge (~4M rows, ~1.4GB data) It's the largest object in the buffer cache, consuming ~1.4GB of data. The SQL Server is busy, but appears ...
2 votes
0 answers
29 views

Controlling permissions for app team while assigning them ALTER ANY AG access

There is a need where we our DEV team needs to make index changes and run other index maintenance jobs on the primary replica. We have seen that while this happens, there is a blocking chain created ...
2 votes
0 answers
45 views

Using ADONET SqlClient library how to access the plain-text value of an encrypted column in SQL Server?

In a SQL Server 2017 database running on my development PC, a column in a particular table is encrypted with a column encryption key. CREATE TABLE Test(         id int IDENTITY(1,1) NOT NULL PRIMARY ...
  • 369
2 votes
1 answer
205 views

SQL Agent stopped recording history (SQL Server 2017)

I have a Dev server that has been running without issue for at least 2 years. This dev machine was a VM Clone of the Production server. On 2021-06-05 something happened on this server that altered ...
2 votes
0 answers
116 views

Telemetry_xevents session and auto_stats

I've noticed on a number of instances that the telemetry_xevents session is full of auto_stats events. The statistic is not being updated. I've even disabled auto update statistics and auto update ...
2 votes
0 answers
219 views

Some SQL Server memory consumers keep stealing memory from "Total Server Memory" ? It keep decreasing without any external memory pressure

Problem Description : SQL Server Memory Usage keep increasing while Total Server Memory keep decreasing till ERROR: fail_page_allocation accoured and instance crash Version : SQL Server 2017(RTM-CU21) ...
2 votes
0 answers
157 views

Problems with Distributed Availability Group( DAG)

I have 2 servers in AWS Central joined with AWSCentralAG and I have 2 servers in AWS West joined with AWSWestAG. Obviously, I have the listener name and listener IP address and I able to connect to ...
  • 175
2 votes
0 answers
94 views

Problem with decimal point setting in Windows

I am using bcp.exe from a SQL Server 2017 installation on Windows 10 (build 19041). The OS language is English (US) but I use scandinavian settings for date, time, currency and decimal delimiter - the ...
2 votes
1 answer
1k views

SSIS - Setup OLEDB Connection to SQL Server Always On Listener

I am trying to connect to SQL Server 2017 Always On from an SSIS package (built in SSDT 2017). I am using an OLEDB Connection Manager. 'Application Intent' is set to READWRITE. However, where do I ...
  • 503
2 votes
2 answers
178 views

Log error restoring old database to SQL Server 2017

I am trying to migrate a database from an old SQL Server 2008 R2 to SQL Server 2017. The database was originally 2000 and thus has a compatibility level of 80. Initially I took a full database backup ...
  • 21
2 votes
0 answers
493 views

REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT for Readscale AG failover

I have 2 node Readscale AG setup where each node is on a different data center. I want to know if SET (REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 1); needs to be run even after setting both the ...
2 votes
0 answers
701 views

SQL Server Restore Failing with Error Read on "M:\MSSQL\My_FULL_Backup_20190924_010558.bak" failed: 1101(A tape access reached a filemark

I have been trying to restore one of our db for the past few days without any luck. I keep getting the below error. I tried the restore during peak hours, off peak hours both by accessing the backup ...
  • 195
2 votes
1 answer
251 views

sp_addUser through sp_execute (SQL) in master db

The task is to automate restoring a group of databases, fixing orphaned users, and granting SELECT/INSERT/UPDATE/DELETE permissions. use master go ALTER DATABASE [myDatabase] SET SINGLE_USER WITH ...
2 votes
0 answers
352 views

All but one of my SQL Server 2017 databases are GONE after Windows 10 nightly update failure

EDIT: Restored to a restore point from three days ago, prior to a "Critical Update", and the databases are back. This is, I believe, the culprit update: September 10, 2019-KB4514601 Cumulative Update ...
  • 369
2 votes
1 answer
2k views

The fulltext filter daemon host (FDHost) process stops abnormally on SQL Alwayson Cluster

We added a new SQL Always-On Cluster (FCI) 2017 and some of the databases needed full text filter service. So we also added that feature to the install. Ever since we did that we have been getting ...
  • 352
2 votes
0 answers
390 views

Distributed Availability Group Endpoint_URL Setup Question

I have two sites: SiteA and SiteB. Both sites are running SQL Server 2017 Enterprise v14.0.3048.4 on Windows 2012. Both sites are linked with a dedicated VPN connection. We have a series of ...
  • 941
2 votes
0 answers
286 views

Aggressive Tempdb spill process

We are having an issue with aggressive tempdb spills that significantly effect overall server performance and, in rare cases, cause AlwaysOn failures. Tested on SQL2016 and SQL2017 Enterprise (recent ...
2 votes
1 answer
262 views

Handling new fields when merging in data using hashbytes?

We load in data from stage into our ODS and check for differences using hashbytes. We calculate hashbytes from the stage tables and insert/update into the destination table and also store the ...
  • 1,244
2 votes
0 answers
59 views

Is this "idiosyncratic" method to manage constraints declarations beneficial?

I just wanted to get feedback from actual DBAs, based on their experience and knlowledge of database theory, in regards to this. At my company, we have a large .sql file that holds all of our schema ...
2 votes
1 answer
3k views

Error: CImageHelper::Init () Failed Load of symbol file with path

Using SQL server 2017 (14.0.3029.16) with Always On High Availability environment. Getting error: CImageHelper::Init () Failed load of symbol file with path = C:\Program Files\Microsoft SQL Server\...
  • 186
2 votes
0 answers
295 views

SQL Server 2017 Failover Cluster on RHEL Linux

I am trying to create Failover Cluster on SQL Server 2017 on RHEL 7.3 Linux, following this document from Microsoft: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-shared-disk-cluster-...
  • 357
2 votes
1 answer
831 views

DPM transaction log backups of SQL Server partially contained databases

I am trying to use Microsoft System Center Data Protection Manager (DPM) 2016 (5.0.342.0) to protect SQL Server 2017 Standard CU8 (14.0.3029.16) on Windows Server Std 2016. I have a named instance ...
2 votes
0 answers
1k views

Reinstalling SQL Server 2017

I am installing SQL Server 2017 on my laptop I already have SQL Server 2008 Express I downloaded and installed SQL Server 2017 Developer but when i tried to connect to it the instance i created did ...
  • 189
2 votes
0 answers
153 views

Enable compression for SQL Server Managed Backup to Microsoft Azure

I am using SQL Server Managed Backup to Microsoft Azure for multiple databases on my SQL Server 2017. I am trying to figure out how to enable compression on some of the databases. It seems from this ...
2 votes
0 answers
718 views

Can't install SQL SERVER 2017 on Centos 7.4 - mssql-conf setup error

I'm trying to install the lastest SQL Server 2017 on a Centos 7.4 VM (Vmware). I get the following error when I try running: sudo /opt/mssql/bin/mssql-conf setup using Bash shell. The error: ...
2 votes
0 answers
6k views

SQL Server 2017 stand-alone stuck

My standalone install of SQL Server 2017 is stuck on the Feature Selection page. Below is what I see in the install logs, does anyone have any ideas how to get past this? (08) 2017-10-24 11:15:20 ...
2 votes
1 answer
92 views

Avoiding Hash match to improve CPU usage for a specific query

We are trying to resolve or figure out change in plan for one of the top sql queries by CPU. This query we have found (it's a vendor-based product) which unfortunately I do not have access to extract ...

1
2 3 4 5