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

Recovery attempt for database corruption of catalog, corruption of internal tables

The issue was noticed when we ran dbbc checkdb. EDIT: I looked up altering system tables and was successful in deleting the internal table from sys.sysschobjs$ from its id. https://www.sqlskills.com/...
3 votes
1 answer
12k 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 ...
1 vote
1 answer
466 views

Filestream directory exists for a partition but the corresponding partition does not exist in the database

I am investigating a high memory usage issue of tempdb on a production SQL Server 2017 Enterprise (64-bit) deployment. This is similar to an issue discussed here. The server has two production DBs ...
0 votes
1 answer
342 views

SQL Server user running SSIS package getting EXECUTE permissions error when they have that permission

We have a user running an SSIS package from Visual Studio. The step that is failing is it is trying to run a stored proc but the error within Visual Studio is "The EXECUTE permission was denied ...
1 vote
1 answer
731 views

Azure AG Listener not accessible from secondary Nodes

We are building PoC SQL 2017 Availability Group up in Azure. This is part of a longer-term plan to both provide resiliency & a path to upgrade in the Spring I have been able to stand up the AG &...
3 votes
0 answers
47 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 ...
0 votes
1 answer
46 views

Avoid logfile getting too large on SQL Server

I have a SQL Server stored procedure that essentially 'flattens' a complex database into a single, simplified, table in a separate database to allow users to query the data without needing to ...
3 votes
1 answer
722 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
1 answer
2k 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 ...
0 votes
0 answers
40 views

Fast execution plan and search, slow to show results [SQL Server]

I have this query that shows the execution plan took about 2 seconds but for the results to load (which is one row) took 7 seconds. What would be causing this delay? I see it on the application side ...
0 votes
0 answers
18 views

Error after failing over always encrypted database

I started capturing the following error after performing failover into secondary replica for the database which was using the always encrypted feature of SQL Server 2017 Enterprise Edition: Some ...
2 votes
1 answer
960 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 ...
0 votes
1 answer
36 views

Upgrade SQL Server Server Express to Licensed Enterpise edition?

we have sql server express edition 2017 with default instanc ,now we want to upgrade it to Enterprise Licensed Edition 2017 with default instance in the same server At first I thought I had to ...
0 votes
1 answer
79 views

Cannot do a backup of a read_only database

I'm facing a strange issue where my backup fail with the message : Msg 3906, Level 16, State 1, Line 5 Failed to update database "MyDBB" because the database is read-only. Msg 3013, Level ...
1 vote
1 answer
474 views

SQLServer Linux: Error restoring backup of DB from Windows w/ full-text data file

I'm trying to move a Windows SQL Server database from Windows 10 to Linux. For this, I'm following the instructions in https://learn.microsoft.com/es-es/sql/linux/sql-server-linux-migrate-restore-...
9 votes
1 answer
329 views

Statistics disappearing/empty randomly throughout the day

I've got a SQL Server 2017 (CU9) database that is exhibiting some performance related issues that I believe have to do with the index statistics. While troubleshooting I discovered that the ...
0 votes
1 answer
2k views

Using gMSA account for SQL server reporting services

I am trying to configure SSRS on my VM:s via gMSA account which does not seem to work and unable to access the reporting url ( Web Portal URL) link to check configuration. The requirement is to have ...
1 vote
1 answer
4k views

"New request is not allowed to start because it should come with valid transaction descriptor" using linked server

I have a simple query in a stored procedure that LEFT JOINs a local temp table with several tables across a linked server (the same linked server). This query is resulting in the following error: ...
6 votes
2 answers
5k views

How to create a synonym for a column in SQL Server?

The following is not working. I want to create a synonym on a column in SQL Server 2017. How would I go about doing this? CREATE SYNONYM dbo.[ProductTest] for [dbo].[Product].[ProductId] GO select ...
3 votes
1 answer
879 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
1 answer
22 views

Is there a way to verify if a SSIS instance was configured as a cluster resource or not?

We had 2 servers in a cluster ( no aoag ) with SQL + SSIS. one of the cluster nodes died and we removed it. we are in the plan to create a new cluster node and configure SQL engine normally on it as a ...
0 votes
1 answer
31 views

Changes to MSSQL System databases from Cumulative Updates

What sort of changes are made to System tables (master / model / msdb) by Cumulative Updates? For context, I have SQL servers that have AlwaysOn Groups, and am looking at a way to roll back updates if ...
2 votes
1 answer
525 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 votes
1 answer
78 views

Can Query Store Show Spills To Tempdb?

I know that Query Store can show tempdb usage as of SQL Server 2017, but can it show spills to tempdb? I'm highly confident that the plan cache can.
3 votes
1 answer
356 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. ...
0 votes
1 answer
376 views

TempDB file overflow

This is my experience with TempDB. The Algorithm works better when the TempDB File sizes are the same size. We were seeing heavy TempDB contention on a TempDB file that was larger than the others. We ...
0 votes
2 answers
58 views

Do I need the MDF and LDF files when I run a script to generate my schema? [closed]

I'm migrating a database from SQL Server 2019 to SQL Server 2017. To do so, I am using the "Generate scripts" feature in the source database to generate the schema and data separately. (I ...
1 vote
1 answer
40 views

Get All Windows between Start and End Dates

Here is the SQL Fiddle for my question: https://sqlfiddle.com/sql-server/online-compiler?id=ab1634d7-fec7-4918-ac1c-3f4fcac8dc92 I have the following sample data: DROP TABLE IF EXISTS #Price CREATE ...
0 votes
1 answer
28 views

Impact of timezone change during sql server migration

We are migrating from our legacy SQL Server 2017 on Windows 2016 host to SQL Server 2019 on Windows 2019. In above migration the bigger change has been the difference in timezone between legacy and ...
2 votes
1 answer
429 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 ...
11 votes
3 answers
5k views

Distributed Availability Group with Manual Seeding

I am looking for a step by step walk through of how to setup a distributed availability group with manual seeding. I can get the automatic seeding working but when I try to manually seed I cannot get ...
3 votes
1 answer
3k 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 ...
0 votes
1 answer
231 views

Database Tuning Advisor CLI "dta" missing

My Windows workstation can launch the GUI for the Database Engine Tuning Advisor, but the CLI called "dta" is missing. Why? Background The Database Tuning Advisor is a SQL Server tool for ...
0 votes
0 answers
108 views

Multiple SQL Server versions and Integration Services evaluation period has expired

Initially, I have installed Enterprise Evaluation Edition and then we have installed licensed version Standard Edition 2017. All our apps using SQLSTANDARD instance and backup maintenance jobs were ...
0 votes
1 answer
670 views

Is it possible to add a column to a partition in a partitioned table?

I have a large partitioned table of 16 billion plus records. I am currently updating one of the columns to NULL using the partition number. The process is cumbersome in that it takes a lot of time to ...
1 vote
1 answer
46 views

Can a server outside the Windows domain connect to a 2nd SQL instance via a linked server?

I have the infrastructure shown in the diagram below. Web Server 1 is outside the Windows Domain. Web Server 2 is inside the Windows Domain. SQL Server 1 and 2 are both inside the domain. There is a ...
0 votes
1 answer
422 views

Insert Into Table performance questions

I am writing a stored procedure, main purpose of it will be the insertion into table Procedure will be signed by certificate, and sending email notifications Procedure will accept user-defined table-...
0 votes
1 answer
446 views

SQL Server high SQLCONNECTIONPOOL consumption

SQL Server Version : SQL Server 2017(RTM-CU21) on Linux Runing environment : kernel 4.19,Docker 18.09 Docker Image : docker pull mcr.microsoft.com/mssql/server:2017-CU21-ubuntu-16.04 Problem ...
1 vote
1 answer
4k views

The OLE DB provider "MSDASQL" for linked server "(null)" reported an error

I created a linked server to MySQL database server on my SQL Server database server. I tested the connection. It succeeded successfully for MySQL connection. I can see the databases on MySQL instance ...
3 votes
1 answer
454 views

Database-wide trigger

I'm new to DBA and I'm attempting to create a trigger that will update the a specified column in one table whenever an insert is made into every table across the database. I understand it is possible ...
0 votes
1 answer
414 views

OPENROWSET Bulk insert Text File NULL Columns

I need to automate a weekly import of .txt log files into a SQL database. I have already created my table, format file and can get some of the data to work using OPENROWSET BULK, but the data from the ...
-1 votes
2 answers
191 views

Whether SQL standard edition has NUMA support?

Link: https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2016?view=sql-server-2017#RDBMSSP For NUMA Aware Large Page Memory and Buffer Array Allocation the table ...
0 votes
1 answer
47 views

Create user that can have access all tables in Schema1 but just have insert to specific table in Schema2

Please consider this scenario: I have 2 Schemas: SCHEMA1, SCHEMA2 In each schema I have some tables. I have two logins for each schemas. I want to create a user that can access (CRUD operation) on all ...
0 votes
1 answer
948 views

Grant permissions only if an object exists, but without mentioning the object twice

We have a number of database build scripts that grant permissions to specific objects (sprocs, functions, etc) only to specific roles. We have recently moved to an automated deployment model that has ...
1 vote
1 answer
505 views

DBCC Check DB fails due to operating system error 665

We have a SQL Server 2017 Standard version where recently the CHECKDB is failing with the operating system error 665. The fragmentation level of the disk drive is 0% as it is automatically ...
1 vote
1 answer
583 views

SQL Server Always ON - LOGPOOL_CONSUMER wait and replication issue

I'm facing a strange issue in one of my customers AG. There're 4 instances under this AG. Staring a few days ago, my customer complains about some query (SELECT) timeouts issues. When I faced this ...
1 vote
1 answer
1k views

Linked Server Configuration using remote user logins

I'm trying to configure a Linked Server using remote logins reducing who has access to the linked server by taking advantage of the not be made option. For test purposes alone, I have used SA, ...
1 vote
2 answers
288 views

Query to polybase external table giving error "Access to the remote server is denied because no login-mapping exists."

we have successfully created all pre-requisite of creating external tables using SQL Server 2017 Standard Edition CU22 Polybase. The issue we have is the non-sysadmin login can't query those external ...
1 vote
2 answers
2k views

How to create and fill multiple session temp tables with dynamic SQL?

I have a column that holds JSON data. The table can be filtered for sets of similar JSON data that will have mostly identical JSON keys in the JSON column. I can dynamically collect the set of JSON ...
1 vote
1 answer
462 views

Machine login for Microsoft SQL Server for Linux

-- old question -- I'm building a multi-tenant SQL Server for Linux solution for hosting companies. Basically end-users will get access to SQL server with their own user account. Now, I want to be ...

1
2 3 4 5
21