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
9
votes
2answers
510 views

Why/when does SQL Server evaluate the probe side of an inner hash join when the build side was empty?

Setup DROP TABLE IF EXISTS #EmptyTable, #BigTable CREATE TABLE #EmptyTable(A int); CREATE TABLE #BigTable(A int); INSERT INTO #BigTable SELECT TOP 10000000 CRYPT_GEN_RANDOM(3) FROM sys....
1
vote
0answers
9 views

Is there a way to provide a client without administrator access additional information when they receive Error 18456?

When a user fails to log in to SQL Server, they receive this error: Login failed for user 'foo'. (Microsoft SQL Server, Error: 18456) On the client side, this error is always State 1. This is ...
1
vote
1answer
47 views

Temporal Tables Granularity

I've start using temporal tables on SQL Server 2017 and I've noticed that rapid updates aren't recorded in the history table. Does anyone know if this is configurable or do I just have to live with ...
0
votes
3answers
55 views

How do you generate a build script for all views [duplicate]

I would like to create a build script that can run through and recreate all views (and stored procedures too, but one step at a time). I am trying to create a baseline of all of our user databases (...
-2
votes
1answer
52 views

Optimize query with nested inner query and case statements [closed]

I have below query written by someone targeting Microsoft SQL server 2017. I do not have access to database to create indexes etc., So only way I can do is optimize below query as efficient as ...
1
vote
1answer
165 views
+50

Operator used tempdb to spill data . The estimated row are less than real rows

I have a query that takes around 32 seconds, I tried many ways to fix the performance problem. The first thing I tried is adding a new column that contain date and time as an integer. After I added ...
1
vote
0answers
27 views

database mirror - A connection attempt failed because the connected party did not properly respond after a period of time

I have database mirror set up in one of my servers and all seems to be fine, however, from time to time I get this error message in the logs: --Execute it on Primary/Secondary server EXEC ...
0
votes
1answer
35 views

Can't open Microsoft Access Database inside a Microsoft SQL Server Filetable Folder

We recently started to migrate the Data on our Fileserver to SQL Server (Version 14) Filetables and I encountered a problem with MS Access Databases (.accdb, Access 2007). When the .accdb file is ...
0
votes
4answers
88 views

Windows Logs Application is full of “Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: ****]”

I have Windows Server 2016, with SQL Server 2017, and found Windows Log Application is full of Login failed messages (as follows) : Login failed for user 'sa'. Reason: Password did not match that ...
0
votes
1answer
13 views

Limit TLS and Cipher Suites on SQL Server 2016-2019

I'm wanting to configure some SQL 2016+ servers to use only TLS 1.2+ and a subset of Cipher suite options (no ciphers considered weak or compromized). I'm having trouble tracking down how to ...
0
votes
1answer
75 views

Is filtering on int better than filtering on date [closed]

I have a system where most of our table has the current datetime as primary key and we filter most of the data based on the datetime. The question, if I move this datetime to another new table and ...
0
votes
1answer
29 views

Windows Server and SQL Server updates and patching - and how to rollback (revert) if necessary

Say we have a server as SQL Server 2017 on Windows Server 2016, both have some pending updates recently Below few questions: What's the correct order of installing updates - first Windows, and then ...
5
votes
2answers
96 views

Query Execution oddities between SQL Server 2008r2, 2017 and 2019

I have a very basic query that was running completely fine with index seeks across the board on SQL Server 2008r2. When we migrated to SQL Server 2017, it started performing worse, and now does a ...
0
votes
0answers
21 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 ...
0
votes
2answers
34 views

Configure Always On to use secondary site as DR

I have two SQL Server 2017 Standard Edition clusters, each in a different datacenter, only a pair of servers each. I was able to configure and test the Windows Cluster, Quorum for it, AG, including ...
4
votes
1answer
94 views

Distributed AG on SQL Server 2017 Standard Edition?

Can I configure Distributed AG with SQL Server 2017 Standard Edition?
2
votes
1answer
39 views

Gaps and islands with GPS breadcrumb data

I've got a gaps and islands (GAI) problem that I can't seem to crack to save my life. Most of the GAI problems I've seen involve data in the form of: [Id] [StartDateTime] [EndDateTime] But mine in ...
3
votes
2answers
65 views

SQL Server 2017 Distributed Availability Groups

My Environment is SQL Server 2017 Distributed Availability Groups, details as follows: Configured SQL Distributed AG from DC1 to DC2. DC1 (Production): TEMPDB1 AND TEMPDB2 (HOSTED ON WSFC1) DC2 (DR)...
-1
votes
0answers
16 views

SqlMemory Future Prediction for new Server Provisioning [duplicate]

we have a current DB server and having 8GB of RAM and SQL is utilized by 6GB as assigned. We are going to migrate to new DB server and would like predict how much RAM we can ask depend upon the usage ...
0
votes
0answers
25 views

SQL Server 2017 Machine Learning repair fails

CU17 update failed on an Enterprise 2017 box, both R and Python (we use both). I removed them both. Trying to Add Feature, and R still fails - Python is good. Removed R again, after stopping ...
2
votes
0answers
67 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 ...
0
votes
1answer
83 views

Installation of Database Engine Services failed - “SQL Server Browser service group does not exist”

I tried to install a new instance of SQL Server 2017 on a Windows Server 2016, but received an error: “The following error has occurred. SQL Server Browser service group does not exist. Check for ...
2
votes
1answer
48 views

Variable to select database in SQL query

If I want use a variable in the FROM clause of a SQL query I need to use dynamic SQL. Which might be a pain if the query is long and complex. There are few different ways of running a query from a ...
4
votes
3answers
504 views

SQL Server - Snapshot is useful in production environments?

I'm reading about SQL Server high availability solutions and disaster recovery and among the available resources the SQL Server have snapshot feature. In theory all seems like beautiful. I also ...
1
vote
1answer
39 views

fn_trace_gettable() reports an error

The following code returns some rows with [Error] = 2 SELECT v1.[name] as EventName, tr.TextData, tr.HostName, tr.ApplicationName, tr.LoginName, tr.SPID, tr.Duration, tr.StartTime, tr.EndTime, tr....
3
votes
1answer
31 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 ...
1
vote
0answers
16 views

SQL Replication sysarticles Status 57

I am looking at the data kept in the various SQL Replication tables. I am trying to understand what information is available and what it means. In this example I have push replication on the ...
1
vote
1answer
42 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 ...
2
votes
0answers
22 views

SQL Server update from GDR to CU-17 [duplicate]

I'm using SQL SERVER 2017 GDR version 14.0.2002.14. I want to upgrade to the latest possible version of SQL Server 2017. My question is can I download CU-17 and directly run the latest cumulative ...
0
votes
0answers
48 views

Parallelism not engaging on specific database

I have a few databases with identical schema, lets call: DB_A DB_B I have a query that when run takes 11 seconds and returns 11K (11000) records on DB_A but when the exact same query is run on the ...
0
votes
3answers
49 views

Database Mirror - Host can not be reached or does not exist

I tried mirroring database with GUI and T-SQL, but I had problems in both cases. Here is some information about infrastructure and SSMS: INFRASTRUCTURE Principal server with SQL Server 2017 ...
0
votes
1answer
32 views

SQL Server 2017 Installation Errors

Installing SQL Server 2017 Standard on Windows 2016 and hitting errors when entering the new service accounts for SQL agent, service, SSIS. 'The RPC Service is unavailable, Click retry the failed ...
0
votes
1answer
71 views

SQL Server Stolen Server Memory - what it consists of?

Below what my server's performance counters show: Questions: 1) What "Stolen Server Memory" consists of ? I can assume "Plan Cache", "Lock Memory", "SQL Cache", "Connection", "Optimizer" are ...
2
votes
1answer
81 views

Missing master.dacpac publishing dacpac Azure Devops

The problem I have been trying to get a build pipeline to work in Azure DevOps to demo how we could build and test our databases. I've come to the point where I want to publish my DACPAC but it ...
9
votes
1answer
382 views

CPU Usage Incorrect in sys.dm_os_ring_buffers

I'm running a cloud VPS with a SQL Server instance. Because it's for personal use, I'm using express edition (I can't use developer edition because I technically have production apps running on it and ...
1
vote
2answers
37 views

How to query SQL Server Memory Manager performance counters in DMV?

Is there any system catalog views or DMVs that can be used to query (in SSMS for example) below performance counters from SQL Server:Memory Manager ? Free Memory (KB) Target Server Memory Total ...
6
votes
2answers
2k views

How SQL Server caches data in memory

I have couple of questions on how caching of data works Imagine we have below situation: Server restarted or we just ran DBCC DROPCLEANBUFFERS We have a Table1 that is 50 GB and has columns A, B, C, ...
0
votes
1answer
32 views

Adding ReadOnly Domain-less replica to existing AG with two replicas on two nodes WSFC

I have AG setup as follows: Multi sub-net WSFC (Windows Server 2016) - All nodes part of same domain AG with two replicas (SQL 2017) - Each node (mentioned above) contains one standalone instance. ...
2
votes
1answer
46 views

Why does sp_replmonitorhelpsubscription not work without parameters?

I am just starting to play around with all the cool replication tools to Programmatically Monitor Replication One of the early finds is sp_replmonitorhelpsubscription When I run it as ...
3
votes
0answers
73 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 ...
2
votes
2answers
39 views

SQL Server Table Reports Disk Usage 0 Rows but reserved 46 GBs

I've executed report on database "Disk Usage by Table" And I have couple of weird tables, where there are no rows, but disk used couple dozens of gigabytes. What caused such behavior? What to do with ...
0
votes
1answer
41 views

Backup SQL Server 2017 to remote directory

There is SQL Server instance with 4 DBs. There is remote directory '\u12160.your-backup.com\backup\SQL' which can be accessed ONLY with User/Password (no user auth or anything other). I want to ...
2
votes
1answer
70 views

How to properly configure SQL Database Mail to send through an Office 365 SMTP server without using an on-prem SMTP server? [closed]

We used to use SQL Database Mail through an on-prem SMTP server, but have since transitioned to Office 365. I created a new profile for O365 and tried configuring a new account in that profile like ...
4
votes
2answers
361 views

SQL Server trusted connection additional security

Using Windows authentication, is there a way to prohibit users from connecting via ODBC to the database?
8
votes
1answer
158 views

Internals of backup compression with TDE (SQL Server)

Does anyone have any (inside?) knowledge of how backup compression works with TDE on SQL Server (version 2016+). In general I would expect compression of encrypted data to have minimal benefit, ...
1
vote
0answers
90 views

Connecting to SQL Server 2017 from Oracle SQL Developer

Pretty much as the title says, has anyone tried connecting to SQL Server 2017 via Oracle SQL Developer? I downloaded the latest jTDS (jtds-1.3.1-dist) but this only seems to support SQL Server 2012 (...
0
votes
2answers
48 views

Create table permissions issues with SQL Server

I have a database _Repository and a Login COMPANY\ANALYST_USERS which contains a handful of employees, and I am using Microsoft SQL Server 2017. The database has some core tables in it that I want ...
0
votes
0answers
33 views

Rebuild clustered index online on specific partition

Can I rebuild clustered index ONLINE (MS SQL 2017 Developer Edition) for specific partition which is on partitioned table with different filegroups? Selected partition is on read_write filegroup, but ...
4
votes
1answer
47 views

SQL Server System Versioned Table Execution Timing

I am trying to learn more about system versioned tables in SQL Server so I am running a script to perform simple DML statements on a system versioned table and capturing different times stamps that ...
0
votes
1answer
79 views

Sql Server - Multiple errors regarding to memory in error log

MSSQL-Server 2017 Express Edition runs on my Linux server. The server has 4GB RAM. The total size of DB is 300MB. My website and the sqlserver are on the same server, it's a small website with max of ...