Skip to main content

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
0 votes
0 answers
29 views

Accessing this server via SQL Management Objects (SMO) or Distributed Management Objects (DMO) is currently not permitted

When I run this, EXEC SP_CONFIGURE 'show advanced options', 1 EXEC SP_CONFIGURE 'SMO and DMO XPs', 0 SQL Server management studio unable to connect with error, Accessing this server via SQL ...
Imran Qadir Baksh - Baloch's user avatar
0 votes
0 answers
73 views

SQL Server 2016 upgrade

We need to upgrade our SQL Server 2016 to SQL Server 2019. We are also using replication for our test and production environments. What my question is: what are the downfalls of upgrading from SQL ...
Andrea Jared's user avatar
0 votes
1 answer
40 views

SQL Server 2017 not recognizing Oracle driver for Linked server

I would like to connect from my SQL Server 2017 to an Oracle database via Linked server. I installed the Oracle driver from Oracle-Client-for-Microsoft-Tools-64-bit.exe, but the driver does not appear ...
Balazs Kiss's user avatar
0 votes
1 answer
42 views

SQL 2017 log reuse issue

I have a SQL 2017 Enterprise server where more and more VLFs are being consumed and none are being released. Log backups are running every 15 minutes. It is in an AG but this is working correctly (...
Bruce Benson's user avatar
0 votes
0 answers
61 views

High number of Latch Waits, is it a problem, and how to investigate?

We have reports of performance issues on one of our applications. I tried to have a look for anything obvious, but I'm an Oracle DBA, and my SQL Server knowledge is pretty limited - we don't have any ...
Carlovski's user avatar
  • 113
0 votes
0 answers
107 views

Maintenance Plans Failing Invalid Class String

This is on a SQL Server 2017. We are required to use maintenance plans for full/log backups and DBCC Checks. They have been working fine, but I received an email that they have failed yesterday. I ...
Lisa's user avatar
  • 1
0 votes
0 answers
20 views

SQL Database Mirroring Timeout Update Questions

When performing SQL Database Mirror Timeout value updates: Does both Principal & Mirror need to be in 'Synchronized' state? Does the SQL Server instance need to be restarted for the changes to ...
thebernardlim's user avatar
-1 votes
1 answer
98 views

Identify which index's rebuild operation is causing the log file to grow

We have a database, in full recovery model, which goes under index rebuild maintenance once a week. There is an index which is causing the log file to grow. I have log file growth event alerts enabled,...
Stackoverflowuser's user avatar
3 votes
0 answers
54 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 ...
SqlWorldWide's user avatar
  • 13.4k
1 vote
1 answer
70 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 ...
Andy's user avatar
  • 13
0 votes
0 answers
19 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 ...
Rauf Asadov's user avatar
  • 1,273
0 votes
1 answer
37 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 ...
yamihero777's user avatar
0 votes
1 answer
96 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 ...
Dominique Boucher's user avatar
0 votes
1 answer
23 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 ...
Racer SQL's user avatar
  • 7,386
0 votes
1 answer
138 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.
J. Mini's user avatar
  • 501
0 votes
1 answer
36 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 ...
Brett's user avatar
  • 101
0 votes
2 answers
64 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 ...
arnoldbird's user avatar
1 vote
1 answer
42 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 ...
Vaccano's user avatar
  • 2,496
0 votes
1 answer
50 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 ...
Newbie-DBA's user avatar
0 votes
0 answers
139 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 ...
Imran Qadir Baksh - Baloch's user avatar
1 vote
1 answer
53 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 ...
ktakmn's user avatar
  • 25
0 votes
1 answer
48 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 ...
DooDoo's user avatar
  • 203
1 vote
2 answers
206 views

Track CPU time used by a Stored Procedure

I need to track CPU time (not duration!) used by a stored procedure. Procedure has many SQL statements inside it. I've tried module_end event in Extended Events, but it does not have cpu_time field (...
Aleksey Vitsko's user avatar
0 votes
1 answer
75 views

Managing very large VLF

I'm investigating a database with a large transaction log. Looking at DBCC Loginfo I can see the database has 1200 VLF's, which is on the high side but not terribly concerning. What does worry me ...
AlexP012's user avatar
0 votes
2 answers
160 views

Masking data for SA users

I'd like to able to mask data for all users including SA's. I'm not trying to guard against malicious intent, but rather, stop people unintentionally seeing data they shouldn't. Dynamic Data Masking ...
AlexP012's user avatar
2 votes
2 answers
416 views

What happens if you stop the IndexOptimize job, because it is running long and causing blocking?

When the dbo.IndexOptimize job is running for long time and causing blocking, if we stop the job, does it cause Rollback of everything that was reorged or rebuilt? Or it just stop where it is and ...
veena ranganath's user avatar
0 votes
1 answer
212 views

Wait time on server replies is sometimes very long when running the query over and over

As seen in this screen shot, I'm running the same query over and over again, comes back with 0 rows (as expected). For some reason I'm seeing very large wait times on server replies while running this ...
Steven M's user avatar
1 vote
1 answer
316 views

CDC is not working after restoring the databse

I have just restored my database using the below script, USE [master] GO RESTORE DATABASE [MyDB] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLSTANDARD\MSSQL\Backup\mydb.bak' WITH ...
Imran Qadir Baksh - Baloch's user avatar
0 votes
0 answers
52 views

Cannot perform this operation while SqlServerAgent is starting problem

I use SQL Server 2017, this mornnig when i check jobs i got this error; I searched a lot but couldn't find a solution. Does anyone know the solution to the problem? thanks.
shnsrl's user avatar
  • 11
0 votes
1 answer
192 views

SQL Server 2017 Availability Group now thinks it is in a cluster - it isn't and never was!

We have a read-scale availability group on SQL 2017 that for years has worked without fault. Now when we tried to add a new replica (with CLUSTER_TYPE=NONE), it fails because the primary now "...
Stephen H's user avatar
0 votes
0 answers
55 views

SQL Server 2017 peer-to-peer Replication problem after adding new column

Originally I have a DB config p2p replication data between 2 SQL Server 2017 Sp_MSupd_XXX has a parameter @bitmap binary(21) System table Conflict_XXX column __$update_bitmap varbinary(21) ...
Hallbeer's user avatar
1 vote
0 answers
86 views

Altering the column without sysadmin or db_owner roles in published tables

I am using replication between two MS SQL Servers (2017 14.0.3421.10). Unfortunately, when replication publishing is switched on, my user for database schema changes is not able to ALTER any columns. (...
Dex's user avatar
  • 111
-1 votes
1 answer
88 views

Alternatives to reclaim free space after Shrink activity

For couple of our SQL server databases on version SQL2017 APP team did purge to change retention on these partitioned tables which has released quite good amount of space. Some background- Before ...
Newbie-DBA's user avatar
0 votes
1 answer
53 views

Storing segmented time series - performance issue caused by triangle relationship / maximum search

I am trying hard to create a database design from scratch, that allows to store multiple different time series, where each one may be segmented into individual epochs. Inserting new data points for a ...
rellachs's user avatar
0 votes
2 answers
1k views

Converting multiple comma-separated columns into rows

I have an SQL Server database table that holds comma separated values in many columns. For example: id Column B column c 1 a,b,c, 1,2,3, 2 d, ,f, 4,5,6, 3 g,h,i,j, 7, ,9,8, I want to split all ...
jawad riaz's user avatar
0 votes
0 answers
47 views

SSRS 2016 compatibility with SQL 2017 after SQL2016 upgrade

We have an SQL Server database engine on the 2016 version. We also have SSRS in the 2016 version. I plan to upgrade the SQL database engine from the 2016 version to the 2017 for it to participate in ...
mk SQL's user avatar
  • 5
0 votes
0 answers
57 views

How to check Default Isolation Level at server/instance level

We have installed SQL Server 2017 in 2 servers. Then created 2 databases same way in both servers. In one server I see my database snapshot isolation level is ON and another server its OFF. So, how ...
Imran Qadir Baksh - Baloch's user avatar
4 votes
1 answer
369 views

Selecting from sys.dm_tran_locks takes 100+ ms

Our application uses SQL Server application locks and we have cases where we need to see whether a given lock exists or not. To do this, we retrieve and filter information from sys.dm_tran_locks. ...
mwigdahl's user avatar
  • 263
0 votes
2 answers
112 views

On update: Login failed for SQL Server authenticated sysadm

I run into a weird bug? User complained to me that he cannot anymore update certain tables in database. He had owner rights in database and the login was window login (ad-login). I tried elevating him ...
user1054844's user avatar
0 votes
1 answer
111 views

EF Queries Using Read Commited Though snapshot_isolation_state and is_read_committed_snapshot_on are ON

I am running this query, SELECT name , snapshot_isolation_state , is_read_committed_snapshot_on FROM sys.databases s name snapshot_isolation_state is_read_committed_snapshot_on master ...
Imran Qadir Baksh - Baloch's user avatar
0 votes
1 answer
139 views

SQL Server Always On Availability Groups are green, but manual failover attempts consistently failing ("duplicate IP address", etc. in cluster logs)

We're running SQL Server 2017 Enterprise Edition on a pair of EC2 instances (we'll call them "P1" and "P3" here) in AWS. Each EC2 instance is in its own subnet, and runs two ...
Skip Daly's user avatar
3 votes
1 answer
626 views

How to create aligned index (PK) on partitioned table and delete the non-aligned index?

I have a large partitioned table by month (70 Billion rows) It has 2 indexes Clustered index on deviceId, logDateTime, eventId, id (unique) Non Clustered index on id (unique) - Primary Key the first ...
Quade's user avatar
  • 301
0 votes
2 answers
124 views

What are the Minimum Permissions to Create an MSSQL Database and Take Ownership of it?

I would like a less privileged user (KINGDOM\joker) to be able to create, manage, and drop databases on an MSSQL 2017 server [14.0.2027.2 (X64)]. KINGDOM\joker should only be able to affect the ...
Anthony Ciani's user avatar
0 votes
2 answers
286 views

Transparent Data Encryption doesn't appear to start encrypting the DB

I have enabled TDE on a big DB of mine (mdf file:9.6GB, log file:12.6GB). I have followed the steps from this video (amongst other sources): https://www.youtube.com/watch?v=6EhCeLbAS_o It has been ...
user2173353's user avatar
0 votes
2 answers
112 views

How to benchmark SQL Server performance after hardware replacement?

We are going to upgrade server with new hardware. And i'm kinda curious about how is SQL Server performance will improve. I'm not a DBA and know just a little about performance monitoring. My only ...
Kliver Max's user avatar
0 votes
1 answer
62 views

SSISDB rename for maintenance

We have an SSISDB database that has become too large and we wanted to do some maintenance on it. I've read that you shouldn't rename the SSISDB database as it would break links to existing packages, ...
Nolan St. Martin's user avatar
1 vote
1 answer
134 views

Which Intelligent query processing features are available in the Enterprise Edition only?

Many features have been released starting from SQL Server 2017 to SQL Server 2022 as part of the 'Intelligent query processing' family. Which ones are Enterprise only, and which are available in the ...
SqlWorldWide's user avatar
  • 13.4k
0 votes
1 answer
118 views

Asynchronous synchronization failed on secondary node

Any idea why synchronization failed to asynchronous node. The database got corrupted and a new database had to be created and synchronization reinitialized. Asynchronous synchronization failed with ...
mk SQL's user avatar
  • 5
-1 votes
1 answer
39 views

AOAG not failover when server is unresponsive

We usually have some issues with one of our servers. We notice it was related to RAM and we are already dealing with it. In the meantime, when the server becomes unresponsive, it will just lock all ...
Racer SQL's user avatar
  • 7,386
3 votes
1 answer
186 views

How is a statistics collection different to normal table stats?

I have a small table with few rows create table dbo.p(i int); insert dbo.p(i) values (1), (2), (3), (4), (4); I created statistics; no index, just the stats. create statistics p_c on dbo.p(i) with ...
Michael Green's user avatar

1
2 3 4 5
21