Questions tagged [sql-server-2017]
SQL Server 2017 (major build version 14.00.xxxx). Please also tag sql-server.
959
questions
8
votes
2
answers
439
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 ...
- 183
-2
votes
0
answers
14
views
Getting an Cannot generate SSPI context when trying to connect [closed]
Trying to log onto a SQL Server and getting the error message "Cannot generate SSPI context" error. There are two other instances of SQL server running with no issue.
- 1
0
votes
1
answer
20
views
SQL Server user running SSIS package getting EXECUTE permissions error when they have that permission
We have a user running an SSIS package from Visual Studio.
The step that is failing is it is trying to run a stored proc but the error within Visual Studio is "The EXECUTE permission was denied ...
- 101
0
votes
1
answer
64
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 ...
-1
votes
1
answer
29
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 ...
- 7,156
0
votes
1
answer
58
views
What is the difference between CU-GDR and GDR?
There are 2 paths to patching SQL server. Either the SQL CU (cumulative) upgrade or GDR (critical) upgrade.
CU includes functionality fixes and security fixes (https://support.microsoft.com/en-us/...
- 1
0
votes
0
answers
16
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 ...
- 425
0
votes
0
answers
13
views
On a fresh install (post OS drive crash), does the SSISDB database master key or initial password matter when same master db/sql svc account is used?
My sql server OS drive has crashed so I'm rebuilding sql server on a fresh install. The crashed machine had ssis and I don't have a backup of the SSISDB database master key or the initial password ...
- 2,800
5
votes
2
answers
212
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
0
votes
1
answer
59
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
3
votes
2
answers
195
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
41
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 ...
- 13
0
votes
0
answers
71
views
Grant permissions only if an object exists, but without mentioning the object twice
We have a number of database build scripts that grant permissions to specific objects (sprocs, functions, etc) only to specific roles. We have recently moved to an automated deployment model that has ...
- 101
0
votes
1
answer
98
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 ...
- 3
8
votes
1
answer
139
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
59
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 -...
- 29
1
vote
0
answers
196
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 ...
- 4,900
2
votes
0
answers
105
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 ...
- 425
3
votes
1
answer
66
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)
...
- 9,142
2
votes
0
answers
79
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
26
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 ...
- 1
0
votes
1
answer
57
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:
+------...
- 103
7
votes
1
answer
1k
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 ...
- 4,900
0
votes
1
answer
54
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 ...
- 11
1
vote
1
answer
267
views
Storage space used for NULL values
According to below links, NULL values take some storage space:
https://stackoverflow.com/questions/3731172/how-much-size-null-value-takes-in-sql-server
https://www.sqlservercentral.com/forums/topic/...
- 4,900
1
vote
1
answer
113
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 ...
- 147
0
votes
0
answers
196
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 ...
- 2,800
0
votes
1
answer
32
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'...
- 2,800
0
votes
0
answers
41
views
How to deal with encryption keys during a migration?
I am migrating sql server 2017 to another hardware. I have backed up the system dbs, user dbs and restored it on the destination.
Now I am reading about the concept of service master key which is ...
- 2,800
0
votes
0
answers
106
views
Error when trying to setup the ssisdb on new server (An error occurred during service master key decryption)
I have an SQL server 2017 ssisdb backup and password that was used when creating the catalog.
I have restored db on a new server (same sql version) by following the steps given here: https://docs....
- 2,800
-2
votes
1
answer
466
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:
...
- 617
0
votes
2
answers
38
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 ...
-4
votes
1
answer
53
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 ...
- 1
0
votes
1
answer
435
views
error [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified when import excel data to sql server
I work on SQL server 2017 I need to import data from excel 2016 to sql server 2017
I using python script to do that
I create odbc and success test with name Testserver
path G:\ImportExportExcel have ...
2
votes
1
answer
93
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 ...
- 765
2
votes
2
answers
81
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 ...
- 765
0
votes
1
answer
39
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 (...
- 365
1
vote
1
answer
259
views
SQL Server-- Calculating Optimal Number of CPU Cores
We're soon going to rebuild the SQL Server running our production ERP. Our SAN Admin issued me the following challenge:
Assume I could give you as many Intel Xeon Gold 6240 CPU @ 2.6 GHz cores as you ...
- 55
4
votes
0
answers
63
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 ...
- 765
-1
votes
1
answer
56
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?
- 637
0
votes
0
answers
51
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 ...
- 1
-3
votes
1
answer
41
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 ...
- 155
0
votes
1
answer
216
views
Help syntax for Stored Procedure to INsert/Update records from 1 table into another [duplicate]
I am trying to create a stored procedure to insert or update records from 1 table into another table and I am having several syntax issues.
it does not like the @MergeLog declaration
it errors on ...
-2
votes
1
answer
67
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 ...
- 155
2
votes
2
answers
45
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 ...
- 155
-1
votes
1
answer
33
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 ...
- 155
1
vote
0
answers
90
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 ...
- 4,900
2
votes
0
answers
84
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....
- 61
0
votes
1
answer
59
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', ...
- 617
7
votes
1
answer
390
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 ...
