Questions tagged [sql-server-2017]
SQL Server 2017 (major build version 14.00.xxxx). Please also tag sql-server.
1,041 questions
2
votes
0
answers
198
views
CREATE or ALTER when using replication
I am using CREATE OR ALTER in the scripts for all of my views and stored procedures, as they are pushed frequently from a CD pipeline. I am also using replication to push certain tables, views, and ...
- 1
7
votes
1
answer
2k
views
Does lightweight pooling disable built-in CLR facilities?
Running SQL Server in fibre mode (lightweight pooling) disables the use of SQL CLR:
Common language runtime (CLR) execution is not supported under lightweight pooling. Disable one of two options: "...
- 90.3k
6
votes
3
answers
15k
views
Login lacks connect endpoint permission in SQL Server - Error 18456, State: 149
I have a fresh database I've setup, which I can connect to and perform operations like normal via the SQL Server Management Studio. I have two accounts I created for my application to use, and both ...
0
votes
1
answer
873
views
Update staging table records in SQL Server 2017 with INSERT trigger and table-valued function, using INSERTED records
I am loading hundreds of order files (positional flat files), via SSIS, into staging tables, and ultimately into production tables. As part of the staging table population process, I would like to ...
CommunityBot
- 1
2
votes
2
answers
254
views
Log error restoring old database to SQL Server 2017
I am trying to migrate a database from an old SQL Server 2008 R2 to SQL Server 2017. The database was originally 2000 and thus has a compatibility level of 80. Initially I took a full database backup ...
CommunityBot
- 1
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, ...
- 39.5k
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
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
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 ...
- 19.1k
-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 ...
- 39.5k
2
votes
2
answers
532
views
Optionally exclude certain partitions using OLA's scripts
I have been using OLA's scripts for a long time and love them.
I have a very large database - 20+ TB with hundreds of partitioned tables.
I purge old data by dropping (merging the old partitions)...
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] ...
- 90.3k
0
votes
1
answer
523
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 ...
- 5,926
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
0
votes
1
answer
629
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:...
CommunityBot
- 1
2
votes
1
answer
972
views
Unable to Install SQL 2017 CU 28
I am in the process of upgrading our SQL 2017 servers to the current CU. I have ran across two systems that won't allow me to apply the update. In the SQL installer, I see below text when I click on ...
- 21
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 ...
- 16.6k
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;
...
- 69.5k
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 ...
- 70.5k
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 ...
- 35.6k
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 ...
- 5,964
-1
votes
1
answer
181
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 ...
- 35.6k
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 ...
- 10.4k
1
vote
4
answers
9k
views
Sql Max value from multiple columns in Computed Column
In this article solution 1, it talks about finding the maximum value from many columns. I would like to conduct this in a computed/persisted column. How would I do this?
https://www.mssqltips.com/...
- 603
0
votes
0
answers
595
views
SSRS Delete Users Via TSQL
We want to remove SSRS users via TSQL rather than manually deleting their permissons via the reporting services admin page.
Testing on SQL 2017 dev edition
We have a script that does run and does ...
- 431
5
votes
2
answers
413
views
Random Error 824 - All Environments
We have been experiencing seemingly random error 824 on our DEV/QA/PROD SQL 2017 Enterprise servers. The servers run nearly identical code ingesting identical daily files via an ETL process into our ...
- 153
3
votes
2
answers
2k
views
Logical reads and LOB logical reads
I have a query app scanning a whole table with a text field.
The query is doing this many reads:
Scan count 1, logical reads 170586, physical reads 3, read-ahead reads
174716, lob logical reads ...
- 602
1
vote
1
answer
6k
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 ...
- 70.5k
0
votes
0
answers
2k
views
An error occurred during service master key initialization (sql error log)
I have migrated sql server 2017 to new hardware. The sql error log shows following error log whenever the sql server service is restarted.
An error occurred during service master key initialization
I ...
- 3,489
0
votes
1
answer
332
views
SQL Agent Job fails after upgrade to SQL 2017
I have upgraded a database to SQL 2017. A SQL Agent Job that previously ran now fails with this error:
Unable to start execution of step 1
(reason: JobOwner DOMAIN\USERNAME doesn't have permissions ...
- 111
18
votes
1
answer
75k
views
Messages about parallel redo
Parallel redo is shutdown for database '' with worker pool size [2].
Parallel redo is started for database '' with worker pool size [2].
Starting up database ''
I see this on a client PC a lot in ...
- 90.3k
7
votes
1
answer
2k
views
SSIS Package Not Being Deployed
I have an issue with an SSIS package, that when deployed via VS2017 to the integration services catalog is not actually being deployed. This is an individual package deployment, and not a project ...
- 5,926
1
vote
1
answer
848
views
Multi-instance 2-node SQL Cluster - do instances need their own disks?
I currently have a single default instance of SQL2017 Standard on a 2-node cluster.
Against my preference, I need to setup a second instance on the cluster (the new database requires some users to ...
CommunityBot
- 1
1
vote
1
answer
1k
views
Cannot connect to linked server using name in hosts file
I am trying to set up a linked server in SQL Server 2017 using the local Windows hosts file on the server where the linked server is being set up. The reason for this is to allow me to hard-code the ...
- 47.6k
8
votes
3
answers
3k
views
What is causing this error: None of the specified replicas for availability group ag1 maps to the instance of SQL Server to which you are connected
I am configuring Always-on for SQL Server 2017 RC1 (14.0.80.90, dated 2017-7-18) for Linux as per https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-availability-group-configure-ha. This ...
- 1
2
votes
2
answers
2k
views
SQL Server Logon Auditing and Reporting the data from a DB Table
Is it possible to configure a SQL Server database to write audit information for logons (success and failure) to a database table that can be queried?
The requirement is to be able to generate a ...
CommunityBot
- 1
0
votes
1
answer
1k
views
Filtering a recursive CTE as view
i'm using MSSQL Server 2017 and have a CTE used in a view to get the root element of a hirarchical tree linked to one specific element.
If the CTE is used directly and the base elements are allready ...
- 14.5k
1
vote
0
answers
1k
views
A transport-level error has occurred when receiving results from the server. An existing connection was forcibly closed by the remote host
We have an application in Azure which connects to SQL Server on-premise over hybrid connection
Every 4-5 requests from 1000 would fail with error 10054:
A transport-level error has occurred when ...
- 5,964
8
votes
1
answer
661
views
What can I add to an Extended Event to return correct row_counts?
I'm running an Extended Event session in SQL Server 2017 that's only recording module_end events for a specific database. The reason for this trace is two-fold:
I'm wanting to see the frequency (or ...
- 181
3
votes
2
answers
408
views
Different application databases in the same instance of SQL Server
I have 3 servers each with a SQL Server 2017 instance, and 3 different applications that connect to each sql server instance, basically:
Application 1 -> Instance 1 (SQL Server 2017)
Application 2 -...
- 25.1k
2
votes
0
answers
168
views
Sys.dm_os_performance_counters DMV row counts
We have noticed an odd issue on two servers in a 3 node SQL Server 2017 cluster running Always On Availability Groups.
When checking the DMV sys.dm_os_performance_counters the row counts are totally ...
- 90.3k
3
votes
1
answer
246
views
Curbing Wasteful Memory Grants - MSSQL 2017
I'm running an on-premise SQL Server 2017 Enterprise at the latest patch/CU level supporting vendor software, which will become important later on.
Server Stats:
Virtual Machine (Memory is Pinned)
...
- 14.5k
2
votes
0
answers
381
views
Service Broker performance issues after upgrading from 2012 to 2017 - tons of LCK_M_IX
Last night we upgraded a SQL Server 2012 server to SQL Server 2017 + CU 29. This is one of a matched set - we send the same message to both and each processes it. No, this is not AlwaysOn; we're ...
- 433
0
votes
1
answer
208
views
Finding and locally deleting deleted records in a million-record table
I'm trying to incrementally load data from a remote server to a local one (using SSIS and linked server). Remote table has 1.7 million of records, increasing every hour. So far, I have been able to ...
- 4,685
0
votes
1
answer
80
views
Replace %S with Feature Value on Feature String Based on Feature Name and Partid
I need to write a select statement that will replace a %S string in the FeatureString column with the actual values stored in the same table for a given PartID.
With the following example data:
+------...
- 90.3k
7
votes
1
answer
3k
views
Memory Requirements for SQL Server
We have a machine with 256 GB RAM.
Max server memory for the SQL Server is set to 180 GB.
Out of 180 GB, SQL Server typically uses:
Database Cache memory - ~ 140-150 GB
Plan Cache - ~ 10 GB
Stolen ...
- 90.3k
0
votes
1
answer
219
views
SQL join unexpected result
I have the following table:
item
running
resourceid
017510
C1
43
338877
C4
44
451233
C1
45
771225
C4
41
011212
C4
47
313366
C3
34
771226
C4
48
990000
C4
46
for each "running" I ...
- 548
1
vote
1
answer
1k
views
Check auto-numa is enabled
I run query
select cpu_count, hyperthread_ratio, softnuma_configuration, softnuma_configuration_desc
from sys.dm_os_sys_info
and got results, where said, that numa is off:
cpu_count ...
- 90.3k
8
votes
3
answers
7k
views
How can I export Query Store data?
I have Query Store (QS) running on a SQL 2017 instance. Currently at RTM, with RTM CU13 currently in test, to be applied on prod in next month's patch window.
While most queries and reports return ...
- 1