Questions tagged [sql-server-2017]
SQL Server 2017 (major build version 14.00.xxxx). Please also tag sql-server.
1,013
questions
0
votes
1
answer
58
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 ...
- 11
0
votes
2
answers
74
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 ...
- 11
2
votes
2
answers
128
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
78
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
87
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
38
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
96
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 "...
0
votes
0
answers
30
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)
...
- 1
1
vote
0
answers
36
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
62
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 ...
- 772
0
votes
1
answer
46
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
540
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
39
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
48
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
293
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
74
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
46
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
0
answers
73
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 ...
3
votes
1
answer
250
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 ...
- 181
0
votes
2
answers
68
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 ...
0
votes
1
answer
124
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 ...
- 293
0
votes
2
answers
70
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 ...
- 155
0
votes
1
answer
36
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, ...
- 101
1
vote
1
answer
101
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 ...
- 13k
0
votes
0
answers
77
views
SSIS Database Always on AG and Patching
I am after clarification, when we patch a SQL Server Always on AG environment that has the SSIS database in the AG we remove the SSIS database, patch failover, patch and add the SSIS DB back into the ...
- 427
0
votes
1
answer
70
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 ...
- 5
-1
votes
1
answer
32
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 ...
- 7,374
3
votes
1
answer
163
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 ...
- 24.7k
1
vote
1
answer
70
views
Transferring ownership of table to a schema using sp_changeobjectowner
I have a number of tables in the database that belong to schema FOO, at least I think they do; they appear in the tables listing in SSMS as FOO.tablename, and when I right click on one of them and ...
- 435
2
votes
2
answers
290
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/...
0
votes
1
answer
106
views
Can not Truncate the database because “secondaries has no log was added”
I am a newbie to SQL Server. I got an error when trying to truncate the database:
Database can not shrink until all secondaries have moved past the point where the log was added.
When I check the ...
2
votes
1
answer
417
views
SQL server wont start automatically after OS upgrade
I did an in place upgrade for host OS from Windows server 2012 R2 to windows server 2022. After this move, I noticed the SQL server 2017 enterprise edition instance therein does not start ...
2
votes
1
answer
151
views
Significance of Historical waits report in SQL server Performance dashboard
I have the following historical wait report for my SQL server.
I have noticed that Wait Category Tran Log IO is consuming 27.09% of percentage time followed by Query Store which takes 24.34%. I want ...
4
votes
1
answer
203
views
Unable to restore database with datetime2(0) in Partition Function
I have a SQL-Server database with large table that partition by a datetime2(2) column.
Some (old) filegroups marked as READ_ONLY.
Periodically I make the backup with READ_WRITE_FILEGROUPS option.
I ...
- 43
6
votes
3
answers
1k
views
Regretting an identity: Is there a way to force inserts to specify the identity column?
To prevent an X-Y problem here's the actual problem we're trying to solve:
The Problem:
We have a bunch of lookup tables that were unfortunately created with an identity column on the Primary Key, ...
- 245
2
votes
1
answer
604
views
Stats update during Index rebuild
I have a task in hand to move rebuild a big table to move LOB pages to a different filegroup on SQL Server 2017 Enterprise Edition.
I was testing the scripts in a proof-of-concept environment, and I ...
0
votes
3
answers
2k
views
CTE with UNION ALL not working as expected
The query below seems simple and straightforward, yet it produces unexpected results.
CREATE TABLE #NUMBERS
(
N BIGINT
);
INSERT INTO #NUMBERS VALUES
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9)
;...
- 141
0
votes
0
answers
39
views
Backward compatibility for filestream databases
I have a database on a filestream enabled instance and with file system tables on SQL Server 2019. I am able to generate scripts for schema and data permitting restore on earlier SQL Server versions ...
0
votes
2
answers
747
views
AG secondary is out of sync for hours after index maintenance
We have a 3TB OLTP database running Siemens Opcenter MES software at one of our manufacturing plants in China. This is setup in an Always-On Availability group with one secondary node. As this is a ...
0
votes
1
answer
85
views
Unable to force a query store sql plan in Principal DB
I have a SQL 2017 mirroring setup and found a query has 2 plans in query store and I wanted to force the good plan which runs much faster. But when i force the query plan I get below error. Same error ...
6
votes
2
answers
996
views
Why is the length of a value sometimes the width of the column instead of the length of the string?
Consider the following SQL
DECLARE @JSON VARCHAR(max);
DECLARE @t AS TABLE(
field char(32),
len1 int,
nfield nchar(32),
nlen1 int,
vfield varchar(32),
vlen1 int
);
SET @...
- 421
0
votes
1
answer
89
views
SQL Plan Guides Use Same Plan Handle
Have a table that is written to a lot but the data is then removed by the app side and stays relatively constant. It is hit by multiple queries that are the same except for the IN clause, there can ...
- 427
5
votes
1
answer
283
views
SQL server Cardinality Estimation for LIKE query
I have this statistics histogram vector for my non-clustered index made on LastName column of a table named AspNetUsers.
If I run a query as SELECT * FROM dbo.AspNetUsers WHERE LastName = 'Baker' it ...
11
votes
1
answer
1k
views
If you disable a non-clustered index, are the statistics still used?
tl;dr - As the title states - If I disable a non-clustered index on a table, are the linked statistics for that index still used?
I'm aware there are many questions about "dropping vs disabling ...
- 213
1
vote
0
answers
232
views
The fulltext filter daemon host (FDHost) process has stopped abnormally Event 30089
ENV: SQL 2017std CU25 8 CPU 48GB mem
Vendor Software Full Text Catalog setup.
Found in FT crawler log:
A string of these 0x80004005 errors:
2023-01-27 07:49:02.34 spid19s Error '0x80004005' ...
- 21
0
votes
0
answers
104
views
How to grant impersonate in SQL Server on AWS RDS - For Profiler Replay
I'm trying to use SQL Server Profiler to Replay a Trace.
But when it tried to impersonate the users/logins, it gives this error:
Failed to set proper user name ('application_login') for the ...
- 41
0
votes
1
answer
87
views
Extended Events for Enable Disable Trigger
We have a table that has a trigger defined on it
Sometimes, some application runs disable trigger command, then does something, then runs enable trigger to enable it back
Scanning the GIT, developers ...
- 5,472
0
votes
0
answers
313
views
Inner Join Same Table
I have data below
What I need is something like this
Is this possible to get data like this?
I do some research and getting insight using lead / lag function, but the result was not expected, heres ...
- 1
1
vote
1
answer
160
views
Query Store switches to ERROR, logs a message suggesting inconsistency
Today I have found 3 production databases writing this message to SQL Server logs :
The Query Store in database XXX has an invalid structure for internal table plan_persist_plan, possibly due to ...
- 107
0
votes
1
answer
128
views
Will update statistics fix wait type IO_COMPLETION issue in Microsoft SQL Server?
I am using Microsoft SQL Server 2017 and recently we have come up with a situation where we see there is Wait Type IO_COMPLETION is contributing 60%. After performing update statistics this issue got ...
- 251