Questions tagged [sql-server-2017]
SQL Server 2017 (major build version 14.00.xxxx). Please also tag sql-server.
836
questions
2
votes
1answer
170 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 ...
0
votes
1answer
21 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 ...
1
vote
1answer
24 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 ...
0
votes
1answer
18 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 ...
0
votes
1answer
42 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 ...
0
votes
0answers
42 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....
3
votes
2answers
87 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 ...
1
vote
1answer
148 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....
3
votes
3answers
153 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 ...
0
votes
0answers
21 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?
3
votes
1answer
125 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 ...
0
votes
0answers
22 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 ...
0
votes
0answers
12 views
TDE State Changes in Error Logs
I am experiencing some messages in our Error logs on a Development database relating to TDE. This is running on SQL Server 2017 CU21.
[TDE] SetDbeState on DEK 0x0000000000000000: Old State: None (0), ...
0
votes
0answers
12 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 ...
0
votes
0answers
26 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
0answers
23 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 ...
0
votes
0answers
16 views
Partition function with all future ranges
I am planning partitioning fact tables nowadays (in 2021 March).
They will be split up by month.
Currently they are empty but will get billions of rows.
Until now, I just entered the month starts for ...
1
vote
1answer
46 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 &...
2
votes
1answer
94 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 ...
0
votes
1answer
107 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 ...
0
votes
0answers
16 views
Does pausing an online resumable rebuild on an otherwise inactive partition affect the entire index that spans across all partitions?
Using SQL Server Enterprise 2017 edition.
I just had an interesting issue with a partitioned clustered index that i was rebuilding just a partition of.
The table and all but one indexes are on a ...
5
votes
2answers
104 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 ...
1
vote
0answers
32 views
DBCC MEMORYSTATUS, IO error - how to troubleshoot
SQL Server 2017 CU 20.
DBCC MEMORYSTATUS information:
Process/System Counts Value
---------------------------------------- --------------------
Available Physical Memory ...
0
votes
3answers
89 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
0answers
32 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 ...
1
vote
3answers
63 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 ...
0
votes
0answers
16 views
Linked server - cannot create an instance of OLE DB provider “SQLNCLI11”
I have created a linked server and I am receiving the following error:
The test connection to the linked server failed.
Additional information:
An exception occurred while executing a Transact-SQL ...
1
vote
1answer
29 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:
...
0
votes
1answer
32 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 ...
1
vote
2answers
47 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 ...
3
votes
3answers
157 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 ...
0
votes
0answers
43 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 ...
2
votes
0answers
70 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) ...
0
votes
0answers
33 views
Manual SCSU 'compression' in SQL Server 2017 for NVARCHAR(MAX)
SQL Server has the wonderful SCSU "compression" gem that works on NVARCHAR(1-4000) types. The usage of SCSU effectively means that the physical storage space used is approximately that of ...
-1
votes
1answer
55 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
1answer
22 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 ...
0
votes
0answers
22 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 ...
0
votes
1answer
65 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 ...
0
votes
0answers
29 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
vote
1answer
29 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 ...
1
vote
1answer
39 views
Uninstalling SQL Server Cumulative Update Issue
When I am trying to uninstall a CU on my SQL Server 2017 server getting below issue. Has anyone faced this problem and please guide me on how can I proceed. I don't have access the SQL 2017 image ...
0
votes
1answer
62 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: ...
1
vote
0answers
46 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 ...
4
votes
3answers
310 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
votes
3answers
272 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 (...
0
votes
2answers
30 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
...
0
votes
0answers
49 views
Always Encrypted on SQL Server LINUX
We can achieve Always Encrypted on Windows Server Machine using SSMS on my Application Server and if Database Server too want to view the encrypted column, we can always transfer the certificate from ...
3
votes
1answer
56 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/...
0
votes
1answer
46 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:...
5
votes
1answer
160 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 ...
