Questions tagged [sql-server-2017]

SQL Server 2017 (major build version 14.00.xxxx). Please also tag sql-server.

Filter by
Sorted by
Tagged with
0 votes
0 answers
19 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 ...
user avatar
  • 2,050
0 votes
1 answer
20 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'...
user avatar
  • 2,050
0 votes
0 answers
21 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 ...
user avatar
  • 2,050
0 votes
0 answers
20 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....
user avatar
  • 2,050
-1 votes
0 answers
33 views

After a server OS drive crash, how to restore user dbs after restoring the system dbs? [duplicate]

Assuming sql server OS drive crashes and data/log files are safe in their own drives. I installed sql server with same version on a new machine. Then I restore the system databases (master, msdb, ...
user avatar
  • 2,050
-2 votes
1 answer
39 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: ...
user avatar
0 votes
0 answers
35 views

Excel header columns changes when export data from sql server to Excel using pandas?

I work on sql server 2017 I run script depend on python language . I run script on sql server 2017 to export data from sql server 2017 to Excel file already exist. header of excel file before export ...
user avatar
0 votes
1 answer
28 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 ...
user avatar
-2 votes
2 answers
84 views

SQL Server 2017 transaction log does not shrink logfile after rebuild index [closed]

I have 10GB database and i have configured transaction log shipping. My rebuild indexes job executed indexes fillfactor changed from 100 to 70 and transaction logfile has growth 60GB. I have tried so ...
user avatar
-4 votes
1 answer
45 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 ...
user avatar
  • 1
0 votes
1 answer
32 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 ...
user avatar
2 votes
1 answer
59 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 ...
user avatar
2 votes
2 answers
62 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 ...
user avatar
0 votes
1 answer
27 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 (...
user avatar
0 votes
1 answer
77 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 ...
user avatar
  • 45
4 votes
0 answers
56 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 ...
user avatar
-1 votes
1 answer
45 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?
user avatar
  • 637
0 votes
0 answers
46 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 ...
user avatar
-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 ...
user avatar
0 votes
1 answer
40 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 ...
user avatar
-2 votes
1 answer
51 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 ...
user avatar
2 votes
2 answers
43 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 ...
user avatar
-1 votes
1 answer
29 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 ...
user avatar
1 vote
0 answers
86 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 ...
user avatar
2 votes
0 answers
43 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....
user avatar
0 votes
1 answer
48 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', ...
user avatar
6 votes
1 answer
260 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 ...
user avatar
2 votes
0 answers
87 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 ...
user avatar
  • 21
0 votes
0 answers
35 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 ...
user avatar
  • 121
2 votes
0 answers
58 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 ...
user avatar
0 votes
0 answers
46 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 ...
user avatar
2 votes
0 answers
26 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 ...
user avatar
0 votes
1 answer
62 views

Is it possible to add a column to a partition in a partitioned table?

I have a large partitioned table of 16 billion plus records. I am currently updating one of the columns to NULL using the partition number. The process is cumbersome in that it takes a lot of time to ...
user avatar
  • 31
10 votes
1 answer
564 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 ...
user avatar
0 votes
0 answers
24 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 ...
user avatar
  • 266
2 votes
1 answer
85 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 ...
user avatar
  • 1,222
0 votes
1 answer
37 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 ...
user avatar
  • 101
-1 votes
2 answers
190 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, ...
user avatar
3 votes
2 answers
284 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 ...
user avatar
1 vote
0 answers
110 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:...
user avatar
-3 votes
1 answer
60 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 | ...
user avatar
1 vote
1 answer
36 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, ...
user avatar
  • 13
3 votes
2 answers
324 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 ...
user avatar
0 votes
1 answer
109 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-...
user avatar
3 votes
2 answers
517 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,...
user avatar
  • 126
1 vote
1 answer
34 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:...
user avatar
  • 2,050
-2 votes
2 answers
77 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 ...
user avatar
  • 2,050
0 votes
1 answer
72 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 ...
user avatar
  • 2,050
2 votes
1 answer
353 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 ...
user avatar
  • 2,050
2 votes
0 answers
16 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 ...
user avatar
  • 369

1
2 3 4 5
19