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
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.
- 1,277
-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 ...
- 47.6k
0
votes
1
answer
271
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 "...
- 35.6k
3
votes
1
answer
1k
views
What's a pathological case where a bitmap filter would not allow the PROBE(Field, IN-ROW) semijoin reduction optimization?
I recently learned a performance optimization in SQL Server I did not know about ("Bitmap Filter On (Clustered) Non-Nullable Column Hash Semi-join Reduction via Scan + Probe IN-ROW" - a real mouthful),...
- 31
1
vote
0
answers
176
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
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 ...
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 ...
- 86.5k
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
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
4
votes
1
answer
438
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.
...
- 90.3k
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 ...
- 90.3k
0
votes
1
answer
202
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 ...
- 5,964
2
votes
1
answer
614
views
MS Access linked table displays short text instead of Decimal data type
I have linked tables in MS Access databases (various versions, 2007-2019) which are connected to SQL server 2017 via Sql server 2017 drivers (eg, msodbcsql_17.2.0.1_x64). Fields that are of data type ...
CommunityBot
- 1
14
votes
1
answer
476
views
Why does this RX-X lock not appear in Extended Events?
The Problem
I have a pair of queries that, under serializable isolation, cause an RX-X lock. However, when I use Extended Events to watch lock acquisition, the RX-X lock acquisition never appears, it ...
- 90.3k
3
votes
1
answer
998
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 ...
- 39.5k
0
votes
2
answers
186
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 ...
- 17.9k
4
votes
4
answers
2k
views
Query Store takes a very long time to load duration
I'm running Query Store to make a DB perform better.
It's been working fine until now..
When i try to load the Top durations of last day it took me 26 min to load the screen.
The SQL version im ...
- 765
0
votes
2
answers
180
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 ...
- 211
4
votes
1
answer
3k
views
The fulltext filter daemon host (FDHost) process stops abnormally on SQL Alwayson Cluster
We added a new SQL Always-On Cluster (FCI) 2017 and some of the databases needed full text filter service. So we also added that feature to the install. Ever since we did that we have been getting ...
CommunityBot
- 1
0
votes
1
answer
84
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, ...
- 16k
1
vote
1
answer
169
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 ...
- 13.6k
1
vote
1
answer
1k
views
Identify the line of a procedure of a spid/session
In SQL Server 2017, how can I identify or pinpoint at which line of a stored procedure a spid (session) is currently at and what the execution plan of the current statement looks like?
CommunityBot
- 1
0
votes
1
answer
189
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 ...
- 25.1k
-1
votes
1
answer
48
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 ...
- 35.6k
3
votes
1
answer
215
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 ...
- 86.5k
1
vote
1
answer
144
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 ...
- 90.3k
0
votes
1
answer
222
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 ...
- 11
2
votes
1
answer
4k
views
Error: CImageHelper::Init () Failed Load of symbol file with path
Using SQL server 2017 (14.0.3029.16) with Always On High Availability environment.
Getting error:
CImageHelper::Init () Failed load of symbol file with path = C:\Program Files\Microsoft SQL Server\...
CommunityBot
- 1
2
votes
1
answer
958
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
230
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
254
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 ...
- 90.3k
1
vote
3
answers
5k
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)
;...
- 35.6k
6
votes
3
answers
2k
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, ...
- 90.3k
2
votes
1
answer
822
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 ...
- 90.3k
0
votes
2
answers
1k
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 ...
- 16.3k
0
votes
0
answers
49
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 ...
- 39.5k
0
votes
1
answer
109
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 ...
- 35.6k
6
votes
2
answers
1k
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 @...
- 90.3k
11
votes
2
answers
46k
views
Getting "sqlcmd sqlcmd: command not found" in Linux?
Whenever I run sqlcmd I get "command not found"
sqlcmd -S localhost -U SA -P '<YourPassword>'
sqlcmd: command not found
How can I resolve that?
- 64.7k
9
votes
4
answers
11k
views
Adding a ROW_NUMBER() with no column to ORDER BY?
So I'm working on a code golf puzzle and need to add an INT "number" column n to a result while maintaining the current order.
Let's say my source data is:
SELECT value
FROM STRING_SPLIT('one,two,...
- 90.3k
14
votes
2
answers
11k
views
Will SSRS be extinct soon, and PowerBI the new model?
I read SQL Server 2017 will now include PowerBI Server. They also moved SSRS to a different installer, so it will not come packaged in the original SQL Server installation. Does this mean Microsoft ...
- 51
0
votes
1
answer
135
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 ...
5
votes
1
answer
321
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 ...
- 86.5k
11
votes
1
answer
2k
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 ...
- 5,512
1
vote
0
answers
484
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' ...
- 31
1
vote
1
answer
2k
views
How to troubleshoot SQLServerAgent startup
I wonder what can cause SQLServerAgent to stay in "starting" stage after restart. The service restarted successfully, but I can't get details of any job, or start/stop/delete with msdb ...
CommunityBot
- 1
0
votes
1
answer
156
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,964
0
votes
0
answers
404
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
332
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 ...
- 29.8k
0
votes
1
answer
192
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 ...
- 47.6k