Questions tagged [sql-server-2017]
SQL Server 2017 (major build version 14.00.xxxx). Please also tag sql-server.
1,041 questions
0
votes
1
answer
198
views
MS Sql Server link a database level role to a server level role
I have created a server/instance level role called ReadOnly and assigned a user (windows based login) to it.
Now i'm trying to add a database level role with the same name where i can grant ...
- 101
0
votes
2
answers
2k
views
WHEN run query on SQL Server, it is very slow. How to solve execution plan issues?
I work with SQL Server and face an issue that a query is very slow.
INSERT INTO [ExtractReports].[dbo].[TradeCodesDelete] (PartID,
Code,
...
- 19
3
votes
2
answers
740
views
How can Temp Table creation time be reduced in terms of CPU time?
Inside a frequently executed stored procedure (sometimes up to 10-15 times per second), there are two particular statements that show up in Top 10 heaviest queries by cumulative CPU impact
These are ...
- 5,964
1
vote
0
answers
335
views
SQL Server 2017 - "SQL Server Assertion:" Error
I'm encountering a scenario very similar to this:
SQL 2016 SQL Server Assertion: File: <pageref.cpp>, line=951 Failed Assertion
With more or less the same error text:
SQL Server Assertion: File:...
-3
votes
1
answer
3k
views
Select records based on priority
I have data stored as:
TestId | Priority |
--------+-----+-----
test103 | 2 |
test104 | 2 |
test105 | 1 |
test106 | 0 |
test107 | 1 |
test108 | 2 |
...
user244947
1
vote
1
answer
763
views
AG failover successful, but old Primary node thinks its still primary after bringing back online
I am doing some testing on SQL 2017 Availability Groups and have the following setup:
1 SQL Clustered instance on a 2-node WSFC in DC1
1 standalone SQL node in DC2
There is 1 AG, containing 3 DBs, ...
- 13
3
votes
2
answers
725
views
CPU cores and sys.dm_os_workers
We recently failed over our AOAG cluster to a secondary region. In the first region, our boxes had 32 cores while in the secondary region, we have 64 cores. Traffic is similar, however on the larger ...
0
votes
1
answer
473
views
Insert Into Table performance questions
I am writing a stored procedure, main purpose of it will be the insertion into table
Procedure will be signed by certificate, and sending email notifications
Procedure will accept user-defined table-...
- 5,964
4
votes
2
answers
11k
views
SQL Server 2017; Memory troubleshooting; There is insufficient system memory in resource pool 'internal' to run this query
We are running a local SQL Server 2017 to support a datawarehouse database. The database is loaded on a schedule through SSIS largely through the use of staging tables and the MERGE function. Recently,...
- 158
1
vote
1
answer
74
views
Which areas need checking prior to decommissioning a windows account so that there are no issues?
I am looking at decommissioning a service account (windows account).
Before that I want to ensure that there are no dependencies on this account from any sql server areas. I have already checked under:...
- 3,499
-2
votes
2
answers
848
views
Why does log not get generated when the SQL maintenance plan job fails to execute (agent job history shows job failed)?
In maintenance plan, under the reporting and logging setting I have configured a text file report to be generated and email report. This gets generated and I receive an email every time the ...
- 3,499
0
votes
1
answer
771
views
Does the maintenance plan or the notify operator task support failure notification?
I want to notify an operator upon failure of the maintenance plan.
I know that I can configure the alerts via the agent job. But I am looking to configure the alert in the maintenance plan itself. I ...
- 3,499
2
votes
1
answer
4k
views
How to change the maintenance plan owner in SQL server?
I have configured a maintenance plan to run every day at 7am. This has automatically created the sql agent job and schedule.
I want to change the job owner to a different user. I cannot see any UI to ...
- 3,499
2
votes
0
answers
80
views
Using ADONET SqlClient library how to access the plain-text value of an encrypted column in SQL Server?
In a SQL Server 2017 database running on my development PC, a column in a particular table is encrypted with a column encryption key.
CREATE TABLE Test(
id int IDENTITY(1,1) NOT NULL PRIMARY ...
- 545
0
votes
2
answers
1k
views
AlwaysOn Availablity Groups and database count limits
Update:
Basic answer: I asked for something that isn't part of the design of AOAG.
Which in my eyes makes this a 'not a good question to ask'.
Unless this offends someone, I'll keep it here in the odd ...
- 103
1
vote
1
answer
275
views
Azure SQL Managed Backup
I have a SQL 2017 on-prem database server that the client wants to back up to Azure storage directly; instead of using local storage. The largest database backup is 4-5TB. I read the MS documentation ...
- 11
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 ...
- 53
0
votes
0
answers
2k
views
The INSERT statement conflicted with the FOREIGN KEY constraint - although value exists in referenced table
I am trying to insert a row into a table that has a foreign key constraint, with below query:
INSERT INTO Table1 (
ID,
tNum,
CompanyID, -- this column has foreign key on it
...
- 5,964
0
votes
3
answers
776
views
How Important is the buffer pool really?
I have been doing some testing of a new (virtual) server which is to replace an existing production server. We suspect the current production server is overspec'ed and therefore are tweaking the ...
- 2,083
0
votes
1
answer
440
views
SQL 2017 - Configuration MSC - greyed-out Remove for Startup Param (have dupes)
I'm a local Admin, so rights don't appear to be the issue.
Hopefully the image shows that somehow, some time, duplicate file paths were added to the default instance start-up parameters. I've tried ...
user15317
0
votes
0
answers
108
views
enable job results in a sp_send_dbmail error
On one of our production servers (SQL SERVER 2017 Enterprise) I get the following error whenever I right click and enable a Job in the SQL Server Agent:
Alter failed for Job 'My Job'. (Microsoft....
- 2,119
0
votes
0
answers
102
views
Procedures to safely perform SQL Server Always On database cluster failover during Updates & Patching?
I need some steps and guidance, to perform the Microsoft Cluster Failover (MSCS 2016) for my SQL Server 2017 Enterprise Edition – Always-On 2x nodes.
This is to perform the Windows Server updates that ...
5
votes
3
answers
1k
views
Query runs indefinitely - what is the root cause ? (not asking how to fix)
I have a simple query with a grouping, which worked fine, until I added one more join:
select
[ca].Value,
[c].cID,
[c].Name
from ReportingDB..Table1 [t1]
join MainDB..Companies [c] on
...
- 5,964
0
votes
1
answer
483
views
Create availability group between FCI and standalone node
first question here so please advise if I've missed anything.
I've read this question (AG of 2 replicas on 2 nodes in WSFC : is it possible to add 3rd replica to AG and NOT add 3rd machine WSFC?
) but ...
- 13
0
votes
1
answer
6k
views
Unable to install MS SQL Server due to missing DLL, how do I find and install this DLL?
I tried to install sql server 2017 on windows server 2019 but the following error appeared to me:
There is a Problem with this Windows Installer package. A DLL required for this installation to ...
tassadit
0
votes
0
answers
320
views
Calculate Start and End of Fiscal Period Weeks
I have a calendar table established (thank you, Aaron Bertrand):
SET NOCOUNT ON
IF OBJECT_ID (N'dbo.Dim_Calendar', N'U') IS NOT NULL
DROP TABLE dbo.Dim_Calendar
SET DATEFIRST 7, LANGUAGE ...
- 55
0
votes
0
answers
1k
views
SQL Error: Could not find stored procedure 'sp_cleanup_history_table'
Seeing errors in the SQL error log hourly on a recently patched SQL Express 2017 CU26 instance. Errors are below.
Feels like something system generated from the stored procedure name but very little ...
- 431
0
votes
1
answer
234
views
Server name contains port
One of our clusters returns the port number as part of the Server Name. See example below
SELECT SERVERPROPERTY ('ServerName')
-------------------------------------------------------------------------...
- 312
0
votes
1
answer
342
views
Table Lock Escalation - Performance Questions
I have few questions on table lock escalations, please confirm or disprove:
Index Scan operator in a query results in Table Lock Escalation ? always or not (small tables) ?
When Shared lock is ...
- 5,964
-1
votes
2
answers
1k
views
Linked server - When is it recommended to use SQL Server or the 4 Other data sources for SQL Server?
To configure linked server pointing to a SQL server, there are 4 possible options.
Server Type: SQL Server
Server Type: Other data source; Microsoft OLE DB Provider for SQL Server
Server Type: ...
- 3,499
-3
votes
2
answers
67
views
i need answer for this problem [closed]
I need to get How many days each customer place order?
Table name - T_Order
OrderId OrderDate MenuItemId Quantity CustomerId
1 2021-01-02 1 2 1
2 ...
6
votes
5
answers
2k
views
Is it possible to do a partial transaction log backup with SQL Server 2017? I don't have enough disk space to do a full backup
Inherited a database server to maintain and found that the transaction log is 92 GB. My plan is to do the backup of that and get it brought back down to a regular size, then fix it. The problem is I ...
- 169
-1
votes
1
answer
98
views
Why is the estimate (out of Stream Aggregate) so low?
I have a table like this:
create TABLE dbo.MyTable
(
TMyTableID bigint NOT NULL IDENTITY(1, 1),
LogArgumentID bigint NOT null,
LogID bigint NOT NULL,
LogTextID int NOT NULL,
FloatValue float NULL,
...
- 2,239
2
votes
1
answer
5k
views
How to create partition function on two columns like integer and datetime
Can a table be partitioned in two ways?
For example, both with date and with the unit
if unit=1 and date > 2021-09-01 save rows in file1.ndf
if unit=1 and date > 2021-10-01 save rows in file2....
-2
votes
1
answer
96
views
For how long will a query wait, and what will it do post the wait when offline index rebuild is running?
When an offline index rebuild is running, and a CRUD query is fired, assuming the rebuild takes couple of hours.
How long will the query wait for rebuild to complete?
What will it do once it ...
- 3,499
0
votes
1
answer
193
views
Why I can't take differential backup of primary replica?
I am trying to take a differential backup of my primary database by connecting to the primary replica, but I can't do that. We use SQL Server 2017 EE with latest CU. Database is in full recovery model ...
- 1,303
0
votes
1
answer
543
views
SQL Server 2017 freezing during bulk insert of large file into in-memory staging table
Running SQL Server 2017 CU20 Standard on a dedicated Dell PowerEdge with 28 cores and 192GB of RAM.
I have a stored procedure that processes large files (~7GB each) in a loop by bulk inserting them ...
- 123
0
votes
1
answer
99
views
how to get result value JSON and send result Format JSON
I have Query for result JSON but I need result like this:
result:[ "B":{1628424359816,35},
"A":{1628424359816,1},
"B":{1628424380816,63},
"...
0
votes
1
answer
195
views
Is there a way to customize SQL Server Performance Dashboard?
The report in SQL Server Management Studio presents System CPU Utilization broken out by a minute. Is there a way to specify the interval - perhaps by 10 seconds?
- 1,951
10
votes
1
answer
253
views
Could I have an undetected deadlock?
Upon running sp_whoisactive on a server for the first time, I was greeted by something unexpected:
Two sessions had been running for 13 days but both of them seemed to be blocking each other. Taking ...
- 32.6k
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 ...
- 9,030
8
votes
3
answers
678
views
SQL Server 2017 - CU25 - sp_pkeys - Wrong order
After our customers have installed CU25 there is a problem with the stored procedure sp_pkeys.
It may now return the wrong order if a primary key has multiple columns.
It can be tracked down in the ...
- 113
1
vote
1
answer
3k
views
Can I downgrade from SQL Server 2017 CU25 to CU20?
New instance of SQL Server 2017. I chose to install up to CU25 during the initial installation. Been using the database for a few months, but it seems to have a memory leak when using memory-optimized ...
- 123
0
votes
1
answer
3k
views
Select rows where result appears more than once
I have been looking all over StackExchange, testing various methods listed here, without any success.
I need to SELECT only users that have multiple sessions.
This is my beginning SELECT statement:
...
- 113
0
votes
2
answers
230
views
Restore fails in single mode because exclusive access cannot be obtained
We have a script that runs every night, this is the relevant part of the script:
---- Restore DB from file system
print ''
print '----------------------------------------------'
print 'Restoring ...
- 1,315
0
votes
0
answers
159
views
SSMS: Dragging vertical tab group divider and nothing happens
I've searched Google for "ssms vertical drag divider nothing happens" but can't find anything. Am I the only one experiencing this?
I'm using both SSMS 17 and 18, as some servers I work with ...
- 101
0
votes
1
answer
576
views
SQL Server high SQLCONNECTIONPOOL consumption
SQL Server Version : SQL Server 2017(RTM-CU21) on Linux
Runing environment : kernel 4.19,Docker 18.09
Docker Image : docker pull mcr.microsoft.com/mssql/server:2017-CU21-ubuntu-16.04
Problem ...
3
votes
1
answer
3k
views
How can I improve query performance with filter on datetime column using an indexed view?
I have this table:
CREATE TABLE transactions
(
id NUMERIC(20, 0) NOT NULL PRIMARY KEY,
amount NUMERIC(18, 2) ...
- 179
0
votes
0
answers
465
views
(Error : RESTORE HEADERONLY is terminating abnormally) when restoring SQL Backup from Standard edition to Developer Edition (Both 2017 Versions!)
The instances have the following versions
Source -->
Microsoft SQL Server 2017 (RTM-CU25) (KB5003830) - 14.0.3401.7 (X64)
Jun 25 2021 14:02:48 Copyright (C) 2017 Microsoft Corporation
Standard ...
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?
- 11