Questions tagged [sql-server-2017]
SQL Server 2017 (major build version 14.00.xxxx). Please also tag sql-server.
1,041 questions
0
votes
0
answers
18
views
DB stuck on synchronized/recovery pending + ALT DB SET ONLINE
Im curious to understand this scenario if there was a better plan.
Index maintenance caused the log to grow, filling the disk and causing a db to go into SYNCHRONIZED/RECOVERY PENDING.
we ran ALTER ...
- 7,484
1
vote
1
answer
63
views
Interleaved Execution not happening in SQL 2017 DB
We have a SQL Server 2017 database that was imported from a 2008 instance long ago. The compatibility level is set to 140 (confirmed by the execution plans), but no multi-statement TVFs are ever ...
2
votes
1
answer
103
views
Is there any information in Query Store that can be used to find block leaders?
I am on a server with major blocking issues. Not deadlocks, just plain blocks. We know that it is the server's primary bottleneck. However, they do not know what the block leaders are, so I do not ...
- 811
1
vote
3
answers
110
views
IS NULL Filtered indexes and column choice
some filtered indexes have been created on one of our databases that filter on [EndDATE] IS NULL, so only records with a null enddate field appear in the index, which is good.
The enddate field is ...
- 33
0
votes
1
answer
81
views
If I am a sysadmin, does it matter if I have the MASTER KEY password or not?
So currently I've faced some issues restoring databases with Symmetrical keys, Master keys, SSISDB and etc and I got curious.
We didn't have the password for anything. All that was done in the past, ...
- 7,484
0
votes
1
answer
41
views
Proc having Key issues after moving DB from one server to another
After moving a database from one server to another, we started having an issue:
> Msg 15581, Level 16, State 7, Procedure xxxxx, Line 24 [Batch Start Line 2]
Please create a master key in the ...
- 7,484
0
votes
1
answer
43
views
"Avg Dist From EOL/LP Request" counter is very high
We have been facing a slowness issue on our PROD DB.
While checking a few counters on sys.dm_os_performance_counters DMV, I could see one counter ("Avg Dist From EOL/LP Request") value is ...
0
votes
0
answers
30
views
MSSQL on AWS: remote user unable to establish connection via SSMS
Newly created MSSQL 2017 SE instance, using SQL authentication. I am able to connect to it with no issues via my corporate VPN. Overseas user on VPN is unable to connect, getting this error from SSMS:
...
- 143
1
vote
1
answer
101
views
SQL Server Express mdf file is 50+gb
I have a SQL Server Express database running on a POS register where the mdf file is 50+gb. When I run select @@version I get:
Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:...
2
votes
1
answer
85
views
Deny access to Sql Server replica when it becomes primary
TL;DR
How to allow read-only access to some users on one replica (in an Sql Server availability group), only when the replica is secondary, and deny it when it becomes primary?
Explanation
I'm ...
- 143
0
votes
0
answers
49
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 ...
0
votes
0
answers
91
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 ...
0
votes
1
answer
84
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 ...
- 23
0
votes
1
answer
70
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 (...
- 11
0
votes
0
answers
251
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 ...
- 123
0
votes
0
answers
531
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 ...
- 1
0
votes
0
answers
24
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 ...
- 101
-1
votes
1
answer
207
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,...
- 1,550
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
1
vote
1
answer
82
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 ...
- 13
0
votes
0
answers
30
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 ...
- 1,303
0
votes
1
answer
52
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 ...
- 19
0
votes
1
answer
282
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 ...
- 3,257
0
votes
1
answer
43
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 ...
- 7,484
0
votes
1
answer
215
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.
- 811
0
votes
1
answer
65
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 ...
- 101
0
votes
2
answers
94
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 ...
- 101
1
vote
1
answer
63
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 ...
- 2,540
0
votes
1
answer
99
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 ...
- 794
0
votes
0
answers
223
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 ...
1
vote
1
answer
65
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 ...
- 25
0
votes
1
answer
54
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 ...
- 203
1
vote
2
answers
360
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 (...
- 5,964
0
votes
1
answer
90
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 ...
- 33
0
votes
2
answers
271
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 ...
- 33
2
votes
2
answers
743
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 ...
0
votes
1
answer
382
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 ...
- 53
1
vote
1
answer
625
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 ...
0
votes
0
answers
80
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.
- 11
0
votes
1
answer
270
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 "...
1
vote
0
answers
175
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. (...
- 111
-1
votes
1
answer
130
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 ...
- 794
0
votes
1
answer
60
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 ...
- 3
0
votes
2
answers
2k
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 ...
0
votes
0
answers
52
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 ...
- 5
0
votes
0
answers
79
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 ...
4
votes
1
answer
437
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.
...
- 263
0
votes
2
answers
182
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 ...
- 101
0
votes
1
answer
199
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 ...
0
votes
1
answer
199
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 ...
