Questions tagged [sql-server-2017]
SQL Server 2017 (major build version 14.00.xxxx). Please also tag sql-server.
1,041 questions
0
votes
2
answers
73
views
SQLCONNECTIONPOOL growing very huge
One of my db server SQLCONNECTIONPOOL growing very huge,it consumed 55GB , out of 65Gb (sql MAX) memory)
Environment: Microsoft SQL Server 2017 (RTM-CU25) Standard Edition
How can i reduce the ...
0
votes
1
answer
74
views
How to test whether current sql engine service account can decrypt the SMK?
Is it true that when the sql service account is changed via the config manager then the new account can decrypt the SMK.
However if the change was made via the services.msc, then the new account doesn'...
- 754
-2
votes
1
answer
3k
views
Row_number to not increment if values on three columns are the same from the row before
Suppose I have a dataset that looks like the screenshot below and I need to add a count column for the rows based off the orderid, DocumentNo_, and CurrPackageTrackingNo with the following logic:
...
- 548
-4
votes
1
answer
235
views
I need help with fixing this query please!
I need to figure out what is wrong with this query. I get this error when running the query: "Msg 8169, Level 16, State 2, Line 1
Conversion failed when converting from a character string to ...
- 9,396
1
vote
0
answers
155
views
Archiving Historical Data
Right now on Production, for deleting historical data, used a script which is manually run in SSMS.
Script deletes batches of 100 rows from Table1 where [Date] between [Start] and [End] in a looped ...
- 5,964
2
votes
1
answer
579
views
Avoiding Hash match to improve CPU usage for a specific query
We are trying to resolve or figure out change in plan for one of the top sql queries by CPU.
This query we have found (it's a vendor-based product) which unfortunately I do not have access to extract ...
- 39.5k
2
votes
2
answers
318
views
Issues with differential backup on random days
We are having a very weird issue with our backups related to differential backup for one of VLDB around 10 TB.
We run once a full backup every Monday and differential on remaining days. We do on ...
5
votes
2
answers
7k
views
log send queue size and redo queue size in AG
I am trying to figure out the way to monitor these 2 events
log send queue size - I can see this in perfmon
Redo queue size - I can see in dmv but not in perfmon counter
Is there any way of using ...
-3
votes
1
answer
90
views
How to write select statment that get data based on partid from last date to current date with filling gapes?
I work on sql server 2012 i face issue i can't make select statment get Partid from last month until current month march (3) .
based on last date exist per partid
and on same time if there are any ...
- 318
0
votes
1
answer
260
views
How do you manage your data retention Table cleanup routine
How do you manage table clean up when you have multiple tables that have different retention policies and FK Parent-Child relationships? Originally I had a table that had different delete statements (...
- 39.5k
4
votes
0
answers
129
views
Planning for application upgrade which connects to SQL server database
I am looking for some advise/guidance on how to achieve this with minimum downtime and possibly avoid any errors or failure during production change-
Background-
We have SQL database servers ...
- 794
-1
votes
1
answer
121
views
Should my company upgrade from Enterprise 2017 to sql server 2019? [closed]
Our production server is currently running SQL Enterprise 2017.
We're paying for software assurance which means we can upgrade at any time.
Should we be looking at SQL 2019?
- 1,694
0
votes
0
answers
139
views
Maintenance plan run date wrong
Win server 2019 & SQL Server 2017
On Jan 28th, 2022, I setup 3 maintenance plans to backup 3 databases from 3:00AM – 3:40AM. The last one finishes at 3:45AM. For a few weeks I confirmed that I am ...
- 3,426
1
vote
1
answer
2k
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: ...
CommunityBot
- 1
2
votes
2
answers
52
views
when make select to top 5 of rows from table it take too much time?
I work on sql server 2019 i face issue when make select top 5 rows it take too much time .
Number of Rows on Table Z2DataCore.parts.SourcingNotMappedParts 70 milion rows .
when run statment select ...
- 3,426
16
votes
5
answers
3k
views
Attempts to reclaim unused space causes the used space to increase significantly in SQL Server
I have a table in a production database that has a size of 525 GB, of which 383 GB is unused:
I'd like to reclaim some of this space, but, before messing with the production DB, I'm testing some ...
- 70.5k
0
votes
1
answer
259
views
High Availability with Transactional Replication
We are using SQL 2017 Enterprise and setting up a new DR site. One of our servers (Server B) is used for reporting and has many databases, one of which has tables populated through Transactional ...
CommunityBot
- 1
-2
votes
1
answer
821
views
when update large table it take too much time to update
I work on sql server 2019
when update table Z2DataCore.parts.SourcingNotMappedParts i found slow and long time when update
estimated execution plan
actual execution plan
Update statement takes 26.5 ...
- 147
3
votes
1
answer
158
views
Migrating a SQL Server 2014 AG to a new 2017 AG
Our current production server runs on SQL Server 2014 AlwaysOn AG. We are planning to upgrade it to SQL Server 2017 AlwaysOn.
Due to the configuration issues with the present cluster, we can not use ...
CommunityBot
- 1
11
votes
1
answer
4k
views
Non-yielding scheduler error in large memory server if the lock pages in memory option is disabled
We are seeing a lot of Non-yielding scheduler errors on a SQL Server VM. Reading this link on Microsoft Docs, it's recommended we enable Lock Pages in Memory.
I do not have much experience with Lock ...
- 3,426
-1
votes
1
answer
36
views
How to get partnumber that have at least have one source type Website and at least one stockid equal 1 Based on PartNumber?
I work on sql server 2017 i face issue i can't get partnumber
that have at least source type website and at least have stockid equal 1 per partnumber
so with another meaning i need to get part numers ...
- 4,463
1
vote
2
answers
2k
views
`CHANGE_TRACKING = AUTO` fails to populate full-text index
I have created full-text indexes on tables with the following options (simplified):
CREATE FULLTEXT INDEX ON [schema].[table]
(
ColumnA Language 'English',
ColumnB Language 'English'
)
...
- 136
0
votes
1
answer
290
views
Transactional replication upgrade from SQL Server 2017
We would like to inplace upgrade of transactional replication topology from SQL Server 2017 to SQL2019 with minimal downtime. The following is the setup. All the all current instances are on SQL ...
CommunityBot
- 1
0
votes
1
answer
167
views
SQL Server query consolidating each database file data in to one row
I have this Dataset base of this query below
SELECT DB_NAME() AS dbname , size/128 AS 'Total Size in MB',
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB', ...
- 3,426
2
votes
0
answers
278
views
Error communicating between BxlServer and client
have been trying to upgrade SQL server 2017 to run Python 3.7. We have installed CU28 and the product version is 14.0.3430.2 which includes the C:\Program Files\Microsoft SQL Server\MSSQL14....
- 101
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,426
7
votes
1
answer
1k
views
Why is statistics update slower on a clustered columnstore than on a rowstore?
I've moved several large tables (each with >10^9 rows and a couple of dozens columns) from clustered rowstore to clustered columnstore indexes on a SQL Server 2014 instance and noticed that ...
- 90.3k
0
votes
0
answers
70
views
Product Version for Analysis Services and Report Server using T SQL
How can I find the product version for both analysis services and report server using Transact-SQL?
I'm trying to put together a query that will get me the details of each SQL instance with their ...
- 90.3k
2
votes
0
answers
124
views
What would make an append-only table remain in the buffer cache in SQL Server
We have a table that is pretty much append-only. It's not overly huge (~4M rows, ~1.4GB data)
It's the largest object in the buffer cache, consuming ~1.4GB of data.
The SQL Server is busy, but appears ...
- 90.3k
2
votes
1
answer
872
views
Retention not working on temporal table or very slow?
I have a table with retention set to 25 months (from today is 2019-12-19) but when I query the validfrom/validTo dates directly in the history table, I see records with ValidTo dates before that. We ...
- 1,336
2
votes
0
answers
30
views
Controlling permissions for app team while assigning them ALTER ANY AG access
There is a need where we our DEV team needs to make index changes and run other index maintenance jobs on the primary replica. We have seen that while this happens, there is a blocking chain created ...
- 3,426
3
votes
1
answer
2k
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/...
CommunityBot
- 1
0
votes
0
answers
54
views
Why are both log file being written at the same time
I have a strange situation and I would like to get someone else taught.
I have a database that have 2 log files (I know, it should not and I plan of fixing it soon).
Now for what I know, log file are ...
- 3,257
8
votes
3
answers
679
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 ...
- 90.3k
0
votes
0
answers
101
views
Adding new column to clustered column store indexed table
I designing clustered column store indexed fact tables.
Later on many new columns might be added to these currently ~90 columns tables.
How adding new nullable column is affected by the size (number ...
- 265
0
votes
1
answer
417
views
Only a sysadmin can specify '@output_file_name'
I have developed an async batch system using the Agent Jobs in SQL Server 2017. It is working really well. I have one issue with setting up the operations staff to run this. We get the following error
...
CommunityBot
- 1
0
votes
1
answer
199
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 ...
- 39.5k
3
votes
2
answers
741
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
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,
...
1
vote
0
answers
338
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:...
- 14.5k
1
vote
1
answer
766
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, ...
- 35.6k
1
vote
1
answer
130
views
Using Policy-Based Management to check backup history for an Availability Group database
I like using Policy-Based Management to do some simple "everything's okay" sanity checks and email me if something goes out of spec. Typically, I validate the time since the last full backup, both to ...
22
votes
4
answers
33k
views
SQL Server has encountered occurences of I/O requests taking longer than 15 seconds
On Production SQL Server, we have following config:
3 Dell PowerEdge R630 servers, combined into Availability Group
All 3 are connected to a single Dell SAN storage unit which is a RAID array
From ...
- 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
-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 |
...
- 360
3
votes
2
answers
726
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 ...
- 35.6k
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,489
-2
votes
2
answers
850
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 ...
- 39.5k
0
votes
1
answer
773
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 ...
- 18k
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 ...
- 4,685