Questions tagged [sql-server-2017]
SQL Server 2017 (major build version 14.00.xxxx). Please also tag sql-server.
1,041 questions
3
votes
1
answer
994
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 ...
- 301
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 ...
0
votes
2
answers
527
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 ...
- 303
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 ...
- 155
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, ...
- 101
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
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 ...
- 5
-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 ...
- 7,484
3
votes
1
answer
213
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 ...
- 25.1k
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 ...
- 545
2
votes
2
answers
752
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
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 ...
2
votes
1
answer
957
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 ...
- 105
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 ...
- 43
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, ...
- 245
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 ...
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)
;...
- 151
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 ...
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 ...
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 ...
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 @...
- 439
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 ...
- 431
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 ...
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 ...
- 213
1
vote
0
answers
483
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
0
votes
1
answer
155
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
330
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
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 ...
- 251
4
votes
1
answer
4k
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, ...
- 856
3
votes
1
answer
483
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,964
0
votes
0
answers
365
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
71
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,964
1
vote
2
answers
4k
views
In-Place upgrade from SQL Sever 2014 to 2017 finds nothing to upgrade
I'm attempting an in place upgrade from SQL 2014 to SQL 2017, Standard Edition on Windows 2012 R2 (Yes, I know...) The issue is that the installer never presents me with a screen to select the ...
- 166
0
votes
1
answer
520
views
Using Ola Hallengren's succeeds to take full backups but fails to take a differential
I take weekly Full backups (all my databases are in Full recovery model) on Sundays and then schedule the Diffs to run nightly (except on Sundays).
Looking through my logs I see this:
Cannot perform ...
0
votes
1
answer
507
views
Aggregate each row and create new monthly column for the weeks to a month
I appreciate all your help. Below is my query and request.
Here is my staging table:
CREATE TABLE MyTable (
[WC] nvarchar(255),
[Requestor] nvarchar(255),
[MTR#] nvarchar (255),
[Date Added] ...
- 3
1
vote
0
answers
116
views
MSSQL Server Database Trigger Permissions - issue after migrating from 2008 to 2017 Server
I have some triggers set up on a database (Dynamics NAV) across various tables for Insert, Update, Delete. The triggers write to a separate database to log the DB, Table, RowId and date/time. The ...
- 11
5
votes
1
answer
13k
views
How to register CLR assembly as trusted in SSDT deployment
I have CLR assembly in SSDT and to deploy that it has to be trusted. What I understand there are 4 options how to do that
First option, use TRUSTWORTHY
EXEC sp_configure 'clr enabled', 1;
...
- 1,109
0
votes
2
answers
1k
views
Cannot find role db_datareader
I am logged in as sa (double checked it in my session with select suser_name()).
I have created a login cards_login_generator, and the same-named user on the database [cards]. I want to permit this ...
- 447
1
vote
1
answer
1k
views
Azure AG Listener not accessible from secondary Nodes
We are building PoC SQL 2017 Availability Group up in Azure. This is part of a longer-term plan to both provide resiliency & a path to upgrade in the Spring
I have been able to stand up the AG &...
- 11
0
votes
3
answers
1k
views
MS SQL Server query that gives me all records LIKE each keyword from a list
I'm hoping someone can help me with a MS SQL Server query. I have SQL 2017. I have a comma delimitated string of keywords. The string of keywords can be up to 50 keywords. I know I could write a ...
- 11
-1
votes
2
answers
235
views
Whether SQL standard edition has NUMA support?
Link: https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2016?view=sql-server-2017#RDBMSSP
For NUMA Aware Large Page Memory and Buffer Array Allocation the table ...
- 3,499
1
vote
1
answer
704
views
SQL Server Standard installation error: Login failed. The Login is from an untrusted domain and cannot be used with Integrated authentication
I have been getting this error when trying to install SQL server 2017 standard. I am installing this on a newly formatted system too. The network is set to private, I'm not sure if that matters.
I ...
- 131
3
votes
1
answer
2k
views
How much resources does a sleeping connection require in SQL Server?
One of our DBAs complained to our team that he noticed about a dozen sleeping connections are virtually permanent. Each of them indicates a very short query (a single record from a single record was ...
- 1,171
9
votes
2
answers
1k
views
MS SQL Server DB Transaction Log Growth Rate
I have a busy database, it has been busy for years and I've seen a transaction log size of approx a few GB a day.
In the last few weeks, that transaction log size has increased to 40-50 GB every 6 ...
- 193
0
votes
1
answer
444
views
SQL Server user running SSIS package getting EXECUTE permissions error when they have that permission
We have a user running an SSIS package from Visual Studio.
The step that is failing is it is trying to run a stored proc but the error within Visual Studio is "The EXECUTE permission was denied ...
- 101
0
votes
1
answer
1k
views
Join other tables from comma separated value
Although many questions on the same topic I haven't been able to find the right solution.
I'm using a piece of software that aggregates IDs to another table as a comma separated value instead of a ...
-1
votes
1
answer
180
views
Do I need to stop Log backups when I stop data sync on AOAG?
I was curious about this specific topic.
If I stop data sync to alter primary without affecting secondaries, do I need to stop log backup jobs? does a log backup during the stop data sync would mess ...
- 7,484
-3
votes
1
answer
3k
views
What is the difference between CU-GDR and GDR?
There are 2 paths to patching SQL server. Either the SQL CU (cumulative) upgrade or GDR (critical) upgrade.
CU includes functionality fixes and security fixes (https://support.microsoft.com/en-us/...
- 1