SQL Server 2017 (major build version 14.00.xxxx). Please also tag sql-server.
4
votes
0answers
31 views
Managed backup large database to Azure
Got a bit of an issue here that I need some help to solve.
Hosting a database, that is about 400Gigs. This is giving us a little headache getting it to work with Managed Backup to Azure Storage ...
1
vote
1answer
44 views
MS SQL 2017 Batch insert performance degradation
This one's a bit strange so bear with me:
I'm seeing extreme insert performance degradation after an insert was interrupted in a dirty fashion (the transaction was not committed and the connection ...
1
vote
1answer
24 views
Msg 8985, Level 16, State 1,Could not locate file 'MyTal_Log'
Query:
DBCC SQLPERF (Logspace)
USE master;
GO
ALTER DATABASE Tal
SET RECOVERY FULL;
GO
BACKUP DATABASE Tal
TO DISK = 'D:\SQLServerBackups\MyTalLog.bak'
WITH FORMAT;
GO
-- Truncate the log by ...
3
votes
1answer
46 views
Unable to use plan guide with sp_execute
I have an application that's querying a 10 million rows table.
The query is filtering by an integer column which is indexed. This column contains only NULLs and the query always looks for non-NULL ...
0
votes
0answers
20 views
Executing stored proc in a SQL Agent Job fails on secondaries within a High Availability group with a sys.fn_hadr_is_primary_replica guard
I have a High Availability cluster of two servers. I have the same job setup on both servers within the cluster. The job has a single TSQL step, which is executed against master. The TSQL is as the ...
10
votes
1answer
156 views
SQL Server 2017 with 500 databases - Frequent AG disconnects since CU9
Hi everyone and thanks in advance for your help.
We are experiencing challenges with SQL Server 2017 Availability Groups.
Background
Company is a retail B2B back-end software. About 500 single ...
0
votes
0answers
17 views
Sharding at SQL Server level [duplicate]
I would like to get some advice about how to implement sharding at SQL Server Level (on-premise), basically, the architecture is something as shown below:
Stand Alone.
Writer: SSIS, Linked Servers
...
0
votes
0answers
31 views
SSMS not showing database compatibility mode [closed]
when I run the following query to identify databases with cross database ownership on:
select name,is_Db_chaining_on,suser_sname(owner_sid) as [owner], compatibility_level
from sys.databases where ...
0
votes
1answer
39 views
when to use a TCP dynamic port and when TCP Port?
I have a new server that is configured using a dynamic port as you can see below:
The application that connects to this server uses the following connection string, but I am getting the following ...
0
votes
0answers
29 views
is it best practices leaving the sql server browser account as nt authority\local service?
The SQL Server Browser needs the following privileges:
Account Privileges:
SQL Server Browser listens on a UDP port and accepts unauthenticated requests by using SQL Server Resolution Protocol (SSRP)...
0
votes
1answer
67 views
Behavior Differences Running SQL 2017 in 130 Compat Mode vs. Running SQL 2016 in 130 Compat Mode?
We have a new application which includes ETL scripts, R, and .NET code all actively being developed on SQL 2016 architecture.
I just recently received approval to start setting up new environments ...
3
votes
2answers
134 views
New SQL Server with slow Index Spool (Eager Spool) and old server used index
So yesterday I posted this question regarding that we have upgraded from SQL Server 2008 to SQL Server 2017 and that we excperience some performance problems.
I got the answer to try updating ...
1
vote
0answers
38 views
Error: CImageHelper::Init () Failed Load of symbol file with path
Using SQL server 2017 (14.0.3029.16) with Always On High Availability environment.
Getting error:
CImageHelper::Init () Failed load of symbol file with path = C:\Program Files\Microsoft SQL ...
2
votes
1answer
48 views
Upgrade to SQL Server 2017 Fails
Windows Server 2016 - Fully Patched
Sql Server 2016 SP2 CU3 -> Upgrade to 2017
In place upgrade fails with error message:
Wait on the Database Engine recovery handle failed. Check the SQL server ...
0
votes
1answer
28 views
Update Sql server, not user databases
I have one machine we've been fighting to do an in place upgrade of sql server from 2016 sp2 cu3 to 2017. It keeps failing, but thats another topic. My question is, is there a way I can run the ...
0
votes
1answer
51 views
Transfer SQL Server 2017 Standard license to a new server [closed]
We recently purchased a license for SQL Server 2017 Standard (16 cores) in behalf of a client from an online reseller (not directly from Microsoft), and installed it without problem: we downloaded and ...
2
votes
1answer
105 views
Is Locked Pages In Memory Still Recommend in SQL Server 2017?
I have done some research about lock pages in memory, the question that still bothers me is (I know it really depends on your specifications but) is it still recommended to lock pages in SQL Server ...
1
vote
0answers
31 views
How to import .dbf files (with more than 256 columns) to SQL Server?
I would need once a month to import 12 .dbf files to SQL Server 2017. Files have dBase III version. They have a similar structure. They have no PK and no unique index and no combination of columns can ...
1
vote
1answer
89 views
Why do LocalTemp table showing with lengthy underscore under Temporary Tables of tempdb in SSMS 2017 Express Edition? [duplicate]
In my Microsoft SQL Server 2017 Express Edition, I have created a local temp table using below create statement,
create table #localtemptable
(tempid int,tempname varchar(20) )
After creating #table,...
1
vote
1answer
19 views
Updating column in table a from top 1 in column b with foreign key relationship
I've got two tables:
Table A:
PlayerProfileId (PK),
Email,
HighScore,
Name,
UserName,
Date
1 g@s.com 37 Gustav MegaBoi 2018-09-14 09:44:07.4505232
Table B:
ScoreId (PK),
Points,
Date,
PlayerId (...
0
votes
0answers
10 views
SqlDependency isn't firing [migrated]
SqlDependency isn't firing. This is something that worked in the past so I don't know what went wrong.
private static void Main(string[] args)
{
SqlDependency dependency;
var ...
2
votes
3answers
191 views
Display one row over another if there are two
Out of about 6300 employees and 53 sites there are about around 200 that have addresses in multiple locations.
If I sort the list by ID and Site.ID the first matching result is the value we want ...
1
vote
2answers
42 views
Running remote stored procedure with DDL locally
Sorry if my question is redundant, I searched Stack Exchange and the internet for hours but do not have a definitive answer to my question.
Database: SQL Server 2017 + Azure
The goal is to build a ...
1
vote
0answers
14 views
Export Data Tier Application Logging
Is there a way to monitor or track when a Database is being exported using the Export Data Tier Application?
The reason is our users require DBO permissions when working with a Database but we just ...
2
votes
1answer
116 views
How to set up SQL Server 2017 as a service
I have a question about what would be the best way to set up SQL Server 2017 as a service.
I have clients that buy a SQL Server database as a service, as if it were an Azure or AWS instance, where ...
-1
votes
1answer
24 views
Restoring AdventureWorks2014 Database to SQL Server 2017
I restored AdventureWorks2014 database from SQL Server 2014 to SQL Server 2017.
(I restored it twice, I changed the name on the 2nd restore, of course)
It works fine.
Now I tried to change the ...
1
vote
3answers
55 views
List all users on SQL Server for auditing
I'm using SQL Server Management Studio 17 and I'm trying to list all the users for auditing in an organized way with mentioning their database name.
I tried the query below but it's showing the ...
4
votes
1answer
171 views
Error starting SQL Server 2017 service. Error Code 3417
I have SQL Server 2017 installed on my computer. This is what SELECT @@VERSION returns:
Microsoft SQL Server 2017 (RTM-GDR) (KB4293803) - 14.0.2002.14 (X64) Jul 21 2018 07:47:45 Copyright (C) ...
2
votes
0answers
31 views
I've got 60 million sales of 800 different liquid products whose price change daily
..and I'm having a bit of a battle getting to customer stats like who's bought the most, and when was their last purchase.
I've no problem writing the query, it's just really slow
Sales
CustID, ...
3
votes
1answer
87 views
Mixing SQL Server 2016 and 2017 in a distributed availability group
I have an old Availability Group made up of 2 SQLServer 2016 instances (2 Windows 2016 servers in WSFC)
I also have 2 new SQLServer 2017 instances (2 Windows Server 2016) that I originally wanted to ...
3
votes
0answers
46 views
Stored Procedure triggered by Service Broker can't access another database in SQLServer 2017
I make an extensive use of SQL Server Service Broker to trigger/parallelize data science tasks (data imports with SSIS or direct queries to another database on the same server, then data management ...
1
vote
2answers
69 views
How do I group these results?
I have this query to calculate the Idle Time of a vehicle.
The Business Rule for Idle Time is - Ignition = 1 AND Speed = 0
So I have came up with the following Query.
DECLARE
@startDate ...
0
votes
2answers
56 views
Error 0x80131904 failed to load assembly id 65536 / HRESULT: 0x8013150A for microsoft.sqlserver.integrationservices.server
I am running SSIS Packages through an Agent job and getting error 0x80131904:
Message:
Executed as user: NT Service\SQLSERVERAGENT.
Microsoft (R) SQL Server Execute Package Utility
Version ...
2
votes
1answer
47 views
Optimizer failing to load statistic when creating plan in SQL Server 2017 “Calculator failed. Replanning.”
This morning, the statistics on one of our filtered indexes updated, and when a plan that uses that statistics recompiled it stopped using that index (and started scanning the entire clustered index).
...
2
votes
1answer
83 views
SQL Server 2017 log file growth with memory optimized table
I'm start to using the memory optimized table, the problem that I have is the log file growth also with the database put in simple mode.
On my maintenance schedule I checkpoint and shrink, but still ...
6
votes
1answer
103 views
SQL Server chooses Nested Loop join with dimensional table and make seek for each row
I face an issue where SQL Server generates a un-optimal execution plan: a
Nested Loop join and seek to the dimensional table and executes 2M reads on it.
The Sort operation Estimation is 100 rows ...
5
votes
1answer
249 views
Actual rows read in a table scan is multiplied by the number of threads used for the scan
I'm running into quite a strange problem. I'm running the same script to generate data and do some matching later on, on an older 2008R2 instance. The last query (an UPDATE) does a single table scan ...
4
votes
1answer
95 views
How to drop an in-memory temporal columnstore
Consider the following:
CREATE DATABASE [Foo]
ALTER DATABASE [Foo] ADD FILEGROUP XTP CONTAINS MEMORY_OPTIMIZED_DATA
ALTER DATABASE [Foo] ADD FILE (NAME=XTP,FILENAME='C:\Program Files\Microsoft SQL ...
2
votes
0answers
90 views
Snapshot isolation transaction failed in database 'tempdb'
Upgraded from SQL Server 2008 R2 to SQL Server 2017. In SS 2008 R2 the application would occasionally get this error:
“Snapshot isolation transaction failed in database 'tempdb' because
the ...
5
votes
1answer
180 views
Does SQL Server 2017 CU10 include CVE-2018-8273 hotfix?
Microsoft released SQL Server 2017 CU10 KB4342123 (14.0.3037.1) yesterday. I tried looking through the Hotfixes included list, but didn't see any reference to the recently released security update for ...
0
votes
0answers
56 views
Unable to remove replica from read-scale availability group in SQL Server 2017
I'm trying to remove a replica from a cluster-less (CLUSTER NONE) availability group in SQL Server 2017, but the operation is failing with this error message:
====================================
...
3
votes
1answer
95 views
Solving intra parallel query deadlocks
We are experiencing deadlocks in our production environment. Seems to be the victim and winner are the same spid's. We rebuild/reorganize indexes daily and we dont find any missing indexes.
Have you ...
0
votes
0answers
21 views
Decrypted data shows null when replicating from SQL server 2017 to SQL server 2008R2
My SQL Server setup looks like the following
Publisher : SQL Server 2017 - 14.0.3026.27
Subscribers : SQL Server 2008 R2 - 10.50.6220.0 & two more SQL Server
2017 servers
Replication Type : Merge
...
-3
votes
1answer
57 views
calculate count by month and year
I am trying to calculate how many people are on a waiting list or still on the waiting list at the end of the month. Ex. If 5 people are waiting in Jan and then in Feb 3 people get added to the list ...
4
votes
1answer
73 views
Turn off telemetry in SQL Server install config file
I'm creating a SQL Server 2017 Image Completion config file, and the automatically generated one has a bunch of stuff about "telemetry". I clicked no to the prompts, but it still put several user ...
3
votes
1answer
688 views
MS SQL Server: Do multiple queries in a batch ever execute in parallel and if so what happens when the second query is dependent on the first?
If I have a batch of two different SELECT statements, is it possible for them to be executed in parallel (if the SQL Optimizer deems it to be the most efficient execution method)?
If the first SELECT ...
0
votes
1answer
63 views
SSMS 17.8.1 Error: Accessing SQL Server logs
I am experiencing an error when I am trying to access the SQL Server Logs through SSMS 17.8.1 for SQL 2017.
I click to expand the SQL Server Log folder in SSMS and I get the following error.
How can ...
2
votes
1answer
40 views
T-SQL DATEDIFF_BIG - 469ms diff with currentmills and documented start-end constraints incorrect?
(NB I don't count as a db admin... I'm just using one, so please assume zero other knowledge)
My reference date is '02/04/2017 18:00:00.124', and for no particular reason, whilst I need the time ...
4
votes
1answer
418 views
Cannot Start SQL Server 2017?
I have been trying to start SQL Server 2017 and been getting
'Not enough storage is available to complete this operation. '
Full Error:
TDSSNIClient initialization failed with error 0xe, status ...
2
votes
0answers
38 views
SQL Server 2017 Failover Cluster on RHEL Linux
I am trying to create Failover Cluster on SQL Server 2017 on RHEL 7.3 Linux, following this document from Microsoft:
https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-shared-disk-cluster-...
