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
6
votes
1
answer
1k
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
129
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 ...
- 794
4
votes
0
answers
986
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 ...
- 13.2k
4
votes
1
answer
14k
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 ...
- 16.6k
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
4k
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 ...
- 64.6k
3
votes
0
answers
68
views
request_status is Grant and Convert on the same resource_type at the same time
I have a typical blocking scenario due to lock contention.
Lead blocker has these locks on an object.
<Object name="Table1" schema_name="dbo">
<Locks>
<Lock ...
- 13.6k
3
votes
1
answer
4k
views
SQL Server-- Calculating Optimal Number of CPU Cores
We're soon going to rebuild the SQL Server running our production ERP. Our SAN Admin issued me the following challenge:
Assume I could give you as many Intel Xeon Gold 6240 CPU @ 2.6 GHz cores as you ...
- 75
3
votes
0
answers
749
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
3
votes
0
answers
545
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....
- 427
3
votes
0
answers
128
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 ...
- 9,487
3
votes
1
answer
920
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
832
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 ...
- 145
3
votes
1
answer
495
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
759
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,133
3
votes
1
answer
373
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. ...
- 31
3
votes
1
answer
158
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 ...
- 673
3
votes
0
answers
303
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 ...
- 141
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),...
- 465
2
votes
0
answers
168
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 ...
- 431
2
votes
0
answers
381
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
278
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....
- 101
2
votes
0
answers
124
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 ...
- 201
2
votes
0
answers
30
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 ...
- 794
2
votes
0
answers
80
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 ...
- 545
2
votes
0
answers
576
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 ...
- 180
2
votes
0
answers
470
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
304
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
198
views
CREATE or ALTER when using replication
I am using CREATE OR ALTER in the scripts for all of my views and stored procedures, as they are pushed frequently from a CD pipeline. I am also using replication to push certain tables, views, and ...
- 121
2
votes
0
answers
239
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
0
answers
10k
views
Issue: Data synchronization state of availability database is not healthy
I have a sqlserver 2017 cluster (sql server 14.0.3026.27) with two secondary nodes set with Availability Mode = Synchronous commit. The server has 8 databases that are replicated on the secondary ...
- 427
2
votes
1
answer
3k
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 ...
- 533
2
votes
1
answer
2k
views
DBCC ShrinkFile EmptyFile fails due to system table SYSFILES1
In following a recommendation from our new storage vendor, I'm trying to split up some large & busy databases into multiple data files, each to live on a separate VDisk that has a separate ...
- 804
2
votes
2
answers
254
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
1k
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 ...
- 625
2
votes
0
answers
834
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 ...
- 335
2
votes
1
answer
374
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
387
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 ...
- 545
2
votes
0
answers
535
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 ...
- 971
2
votes
0
answers
368
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 ...
- 165
2
votes
1
answer
624
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,336
2
votes
0
answers
68
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 ...
- 21
2
votes
0
answers
335
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://learn.microsoft.com/en-us/sql/linux/sql-server-linux-shared-disk-cluster-...
- 397
2
votes
1
answer
1k
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 ...
- 203
2
votes
0
answers
2k
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 ...
- 229
2
votes
0
answers
183
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 ...
- 31
2
votes
0
answers
762
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
577
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 ...
- 794