Questions tagged [sql-server-2017]
SQL Server 2017 (major build version 14.00.xxxx). Please also tag sql-server.
1,012
questions
5
votes
3
answers
794
views
SQL Server Logon Trigger Implications - are there any side effects possible in my scenario?
Some of developers on my team know passwords from SQL accounts that have extended permissions
We would like to track and be alerted whenever any of developers are using any of those SQL accounts to ...
- 5,472
3
votes
1
answer
1k
views
Stuck REDO Thread of an availability group
We are facing issues with REDO queue fluctuating as we have readable secondary configuration.
Based on my understanding in newer versions of SQL Server, redo threads have been made parallel. Current ...
- 774
2
votes
0
answers
211
views
Unhandled Deadlocks with SQL Server 2017 [closed]
We’re running into issues with unresolved deadlocks on the most recent version of SQL-Server 2017, has anybody seen a problem like this before?
Detailed explanation: We migrated our database from ...
- 21
0
votes
1
answer
101
views
SQL Always On automatic failover incase of multiple secondary
I have three node setup for SQL Always on in SQL 2017 enterprise edition. All three are in same subnet and in synchronous commit mode. Now when there is a failover how do I make sure it should always ...
- 101
1
vote
1
answer
407
views
DBCC Check DB fails due to operating system error 665
We have a SQL Server 2017 Standard version where recently the CHECKDB is failing with the operating system error 665.
The fragmentation level of the disk drive is 0% as it is automatically ...
- 1,039
0
votes
1
answer
68
views
Is there a way to tell optimizer not to use index for reads?
Of course I can just drop and recreate or disable and rebuild it.
But for large tables it can take time, and for critical tables it can cause issues.
Is there any "more sophisticated" way to ...
- 742
1
vote
3
answers
4k
views
Are there any things to consider before granting VIEW SERVER STATE and VIEW DEFINITION to Developers?
I would like to grant below to permissions to Developers group, on Production SQL Server:
VIEW SERVER STATE
VIEW DEFINITION (server level)
This is done to make them able to query some of dynamic ...
- 5,472
3
votes
1
answer
331
views
What's the difference between equality (=) and inequality (<>) on BIT field in SQL Server?
Is there difference between WHERE [BitField] = 1 and WHERE [BitField] <> 0?
They seem logically equivalent.
SQL Server, however, disagrees and creates slightly different execution plans. (Which ...
- 103
0
votes
1
answer
1k
views
How to test user permissions
I have two server users, lets say admin and notadmin. I need to test notadmin's permissions. I need set and test VIEW SERVER STATE permission on notadmin, but usually I have access only to database ...
- 103
1
vote
1
answer
671
views
Reduce transaction log file utilisation
I have a SQL Server 2017 database that currently has an 80GB data file and a 90GB log file. I want to get the log file down to a more manageable size, because I am about to run some big scripts which ...
- 171
0
votes
1
answer
134
views
Upgrading Operating System only. OS 2016 - 2019
Currently we have a number of Servers that are using Windows 2016 with SQL Server 2017. The support team want to do an in-place upgrade of the OS to 2019 due to 2016 Mainstream support ending in Jan ...
- 63
0
votes
1
answer
103
views
How do you handle maintenance window for DDL commands on Primary replicating to readable secondary AGs
We are currently running into this issue for one of our availability group design:-
On Primary :- Nightly Table switch/Purges are happening (DDL) for approx. 2-3 hours
On Secondary which is configured ...
- 774
0
votes
0
answers
165
views
Readers blocking reader on readable secondaries
We recently migrated to AG setup for one of our VLDB's around 20+TB.
Current setup is Multi subnet AG with primary accepting mix workload while heavy reporting offloaded to readable secondary replicas....
- 774
3
votes
2
answers
1k
views
Blocking on readable secondary replica
We recently migrated from LogShipping standby/read-only setup to Multi Subnet AG setup with readable secondaries.
Generally on old setup we have select queries running for longer duration as the ...
- 774
14
votes
1
answer
19k
views
Clone a database from SQL Server 2019 to 2017 [duplicate]
I try to clone a database from an Sql Server on my local machine v15 (2019) to a server machine v14(2017). I did a .bak file and when I tried to import on the server side it says:
System.Data....
- 421
3
votes
3
answers
4k
views
to change collation for system database SQL Server 2017
We are using fresh SQL Server 2017 CU23 Standard Edition database with "SQL_Latin1_General_CP1_CI_AS". Now, I want to change all system databases (master , model , temp, msdb) to another ...
- 1,078
0
votes
0
answers
32
views
How To Grant A Specific Login Under Acitve Directory Group To View Server State In SQL Server [duplicate]
I want to grant only a login who is under an active directory user group to view server state in sql server. How can i do it?
- 1
3
votes
1
answer
737
views
SQL Server High CPU usage and RESOURCE_SEMAPHORE waits
Last week had an issue on one of SQL Servers, CPU started burning over 80 % (normal is 10-30 %)
This lasted for about 2 hours until I manually failed over to secondary replica in AG (and this has ...
- 5,472
1
vote
0
answers
102
views
Does dm_os_memory_clerks show all SQL Server memory?
We are running SQL Server 2017 on a machine with 256GB Ram. Noticed a dramatic shift in some performance counters (e.g. page life expectancy usually very high, but had started fluctuating a lot the ...
- 113
2
votes
0
answers
318
views
Telemetry_xevents session and auto_stats
I've noticed on a number of instances that the telemetry_xevents session is full of auto_stats events.
The statistic is not being updated. I've even disabled auto update statistics and auto update ...
- 180
0
votes
0
answers
68
views
SQL Cluster (Always On) - Database table replication
I have the following scenario at this moment.
Two old sql servers - they have setup some kind of publisher subscriber replication from one database to another but only some of the tables at a 12 hour ...
0
votes
0
answers
26
views
Aborting long running query under AG configuration [duplicate]
Currently we have a process where any query running over 2 mins (only select) in primary database participating in db mirroring is killed, running every 5 mins.
As we have moved to Availability groups ...
- 2,169
1
vote
1
answer
345
views
Set the Deadlock Priority to Lowest possible value, but my script is still NOT always the victim in deadlock situations
I have below two queries on Production:
Query # 1: Script that deletes older historical data from core table "Table1" based on "Date" column
There are ~20 tables that point to &...
- 5,472
2
votes
1
answer
381
views
Why does a newly-inserted row show up in two filegroups on a partitioned table in SQL Server?
I partitioned an existing table by year. After inserting a new record for a date in 2020, the new record shows as part of the 2020 partition and in the primary filegroup. I'm having a hard time ...
- 153
1
vote
1
answer
5k
views
Error 41145 after joining database to availability group
I keep getting from my SQL monitoring service the following alert:
Error: 41145, Severity: 16, State: 1. Cannot join database to
availability group. The database has already joined the
availability ...
- 602
5
votes
2
answers
628
views
Why does SQL Server not perform constant (UNION ALL) branch elimination with OPTION(RECOMPILE) when selecting the result into a scalar variable?
We use some 'aggregate' views to select from multiple tables using a discriminator (note: these views are not partitioned views, because the discriminator is not in the base tables). This normally ...
- 195
1
vote
3
answers
5k
views
Backup sqlserver 2019 and restore to Sqlserver 2017
I have only 1 table in SqlServer 2019 that I want to backup and restore into SqlServer 2017 in different server.
To SqlServer 2019 I have access to server and with SSMS but to SqlServer2017 I have ...
1
vote
0
answers
454
views
Unable to connect excel to sql server. Getting an error:
sql server microsoft sql: a connection was successfully established with the server, but then an error occurred during the login process. (provider: ssl provider, error: 0 - the certificate chain was ...
- 11
1
vote
3
answers
270
views
Shrinking data file to save disk space before migration
I know this is most discussed answered on various forums: I completely understand answer is no in most of the time or cases.
However want to know if it can be good approach under any scenario:
Lets ...
- 774
2
votes
1
answer
359
views
Check Free Space in SQL Server (License Limit)
I have a Microsoft SQL Server 2017 Express Edition those have a size limit of 10240MB (10Gigs) and I am not sure how I can check how much space I have left in this license. I have tried the following:
...
- 23
0
votes
1
answer
46
views
Monitoring Query which executes thousand of run/minute and is generally fast
I am looking for some advise here on one of our SQL database server with below behavior-
Queries running for this databases are generally considered good with avg run time of 20 ms.
Suddenly on some ...
- 774
1
vote
2
answers
613
views
CDC on Sql Server 2017 with HA cluster stopped working [closed]
I am currently using Sql Server 2017 (14.0.3294.2) running on Windows Server 2016 as an HA cluster, one primary with two secondaries.
A few days ago, the primary was rebooted and I think the person ...
- 427
3
votes
3
answers
527
views
Error 297, Severity 19 trying to create new table as sysadmin
I am completely scratching my head right now. I have two database instances that I am trying to add a table to, and both give me the same error:
The user does not have permission to perform this ...
- 93
0
votes
1
answer
1k
views
Using gMSA account for SQL server reporting services
I am trying to configure SSRS on my VM:s via gMSA account which does not seem to work and unable to access the reporting url ( Web Portal URL) link to check configuration.
The requirement is to have ...
- 774
2
votes
0
answers
383
views
Some SQL Server memory consumers keep stealing memory from "Total Server Memory" ? It keep decreasing without any external memory pressure
Problem Description : SQL Server Memory Usage keep increasing while Total Server Memory keep decreasing till ERROR: fail_page_allocation accoured and instance crash
Version : SQL Server 2017(RTM-CU21) ...
-1
votes
1
answer
66
views
RAID 0 for development [closed]
in your experiences does of real world testing of RAID5 come "close" or "far" behind RAID0 in terms of performance as the underlying storage for a heavy write OLTP workload? Please ...
0
votes
1
answer
91
views
Setting Up Multi-Server Administration---SQL Server Account
I am trying to set up Multi Server Administration so that I can easily update multiple instances of the same database across our network with ease. Where I am getting lost is how to set up the SQL ...
- 93
0
votes
0
answers
141
views
Linked Server Using a DNS Alias Returning Bad Data
Somehow on the same DEV server we have two Linked Servers to the same PROD server. While that, in and of itself, is not an issue; we have noted something we can't explain and we wonder of anyone else ...
- 135
0
votes
1
answer
497
views
Ola Hallengren DataBaseBackup to URL results in un-equal file sizes causing IO Device Error
we have been using Ola's Maintenance Solution and Database backup direct to URL since we migrated our system to Azure IAAS. We have multi terabyte DB's (approx 40% Filestream data) and therefore ...
- 3
0
votes
0
answers
156
views
SQL YTD calculations adding periods without postings
I have a query which give me the YTD figures per company (client), account, IC and period for the periods in which financial postings have been performed. The table where I get this from is AAGCONSO ...
- 1
1
vote
1
answer
401
views
SQL Full-text Filter Daemon Launcher with Startup set to Manual is Running after Bootstrap
I have on my Windows 10 PC an instance of SQL Server 2017 Developer edition
All the services Startups are set to Manual
Today I started the PC and found in the SQL Server Configuration Manager that ...
- 159
0
votes
1
answer
1k
views
MSSQL Logon Error in /var/opt/mssql/log/errorlog
Hello I checked in /var/opt/mssql/log/errorlog. then I get an error message like this:
The login packet used to open the connection is structurally invalid; the connection has bee
2021-01-22 16: 47: ...
arif
1
vote
0
answers
326
views
Linked Server to Hana DB
I am trying to create a linked server connection from sql-server to Hana DB. I have downloaded the Hana client and all the drivers along with it.
I have tried creating a system DSN and then adding the ...
- 131
4
votes
3
answers
795
views
General Question on sp_recompile
For couple of scenarios, not many a times but few we have seen doing sp_recompile on a stored proc improving the performance.
Being a DBA i understand few of caveats involved when doing sp_recompile ...
- 2,169
2
votes
3
answers
808
views
Will SQL Server go offline if it loses network connectivity to SAN where master and msdb system databases reside?
I have a setup where 3 servers combined into Availability Group
All 3 servers have local directly attached SSD drives, and user database files are hosted on these drives
But the system databases (...
- 5,472
1
vote
2
answers
1k
views
Multiple disks vs single disk on virtualized environment
We're planning to install a SQL Server on this physical architecture:
A hyper-v cluster based on windows server 2019
A cluster shared volume where all VMs live
A full-flash SAN that contains the CSV
...
- 602
3
votes
1
answer
1k
views
How to collapse rows with individual, contiguous dates into single rows with date range start and end
Given a data set like this:
Id
Date
Value
1
01/01/2021
100
1
01/02/2021
100
1
01/03/2021
100
...
... (rows omitted for brevity)
...
1
12/29/2021
100
1
12/30/2021
100
1
12/31/2021
100
2
01/01/...
- 31
0
votes
1
answer
505
views
High CPU for SQL server running on VmWare
I am trying to understand the below behavior for one of our SQL server running on VM.
SQL Server version 2017 has 64vCores and Host has 96 cores with base speed 2.1 GHZ
Based on the article from https:...
- 2,169
6
votes
1
answer
2k
views
Are the results of the STRING_SPLIT() function returned in a deterministic order?
I need to split a comma delimited string, manipulate it, and then concatenate it back into a single string retaining the original order of the data (if possible).
For example, take a column definition ...
- 35.2k
0
votes
1
answer
64
views
Understanding the load running on my SQL server
We have a project this year to move lot of physical SQL Servers, starting with medium critical servers onto Virtual machines.
Generally on physical boxes we have a large count of logical processors, ...
- 774