Questions tagged [sql-server-2017]
SQL Server 2017 (major build version 14.00.xxxx). Please also tag sql-server.
1,010
questions
0
votes
0
answers
28
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
68
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
19
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
28
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
55
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 ...
- 762
0
votes
1
answer
44
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
155
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
29
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
39
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
252
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
70
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
35
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
45
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
152
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 ...
- 171
0
votes
2
answers
46
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
97
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
61
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
28
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
88
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
50
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
55
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
30
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,344
3
votes
1
answer
159
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.6k
1
vote
1
answer
57
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
215
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
91
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
294
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
137
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
189
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
981
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
553
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
36
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
604
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
77
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
952
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
83
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
277
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
170
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
82
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
76
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,302
0
votes
0
answers
254
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
131
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
117
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
4
votes
1
answer
2k
views
Assigning values to multiple @variables using SELECT
I've encountered some T-SQL code which seems to rely on behaviour I found unexpected.
When assigning values to multiple @variables in one SELECT statement, when one @variable is dependent on another, ...
- 736
0
votes
0
answers
71
views
Computed column marked as PERSISTED still calculates every time [duplicate]
I have the following table:
CREATE TABLE dbo.Cases (
CaseCaption nvarchar(255),
NickNameComputed AS (dbo.fn_ParseNickName(CaseCaption)) NOT NULL,
NickNamePersisted AS (dbo.fn_ParseNickName(...
- 1,929
3
votes
1
answer
379
views
Performance problems in SQL Server. Parameter Sniffing or not in my scenario?
Here I am facing a performance problem that occurs with particular statements in a stored procedure, that has many statements, on occasion
SP is executed once a second, normally completes less than 50 ...
- 5,302
0
votes
0
answers
291
views
SQL server DB connectivity issue for application server restart
I have configured 2 applications Database in single SQL Server 2017 .Whenever restart one application server the other application server connectivity also break down from DB and causes application ...
- 1
-1
votes
1
answer
64
views
Performance Metrics are missing for a problem timeframe [closed]
There was a problem time frame of 30 mins (22:00 to 22:30) during which queries had performance issues - bad execution plans, which led to high CPU.
That's what monitoring tool shows:
CPU:
Checking ...
- 5,302