Questions tagged [sql-server-2017]
SQL Server 2017 (major build version 14.00.xxxx). Please also tag sql-server.
1,014
questions
1
vote
1
answer
416
views
Filestream directory exists for a partition but the corresponding partition does not exist in the database
I am investigating a high memory usage issue of tempdb on a production SQL Server 2017 Enterprise (64-bit) deployment. This is similar to an issue discussed here. The server has two production DBs ...
CommunityBot
- 1
1
vote
2
answers
7k
views
Error while trying to create a database for SSRS
I have installed SQL Server Reporting Services 2017, and my database engine is developer edition 2017. Now, while trying to configure SSRS from its Configuration Manager I get this error
I have found ...
- 1
3
votes
1
answer
634
views
Does ordinal position of columns matter for SQL Server replication
I am setting up replication on SQL Server 2017. Transaction replication with push subscriptions such that many smaller database are replicated into a single larger database for reporting.
The ...
CommunityBot
- 1
2
votes
1
answer
2k
views
SSIS - Setup OLEDB Connection to SQL Server Always On Listener
I am trying to connect to SQL Server 2017 Always On from an SSIS package (built in SSDT 2017).
I am using an OLEDB Connection Manager. 'Application Intent' is set to READWRITE. However, where do I ...
CommunityBot
- 1
0
votes
2
answers
74
views
Masking data for SA users
I'd like to able to mask data for all users including SA's. I'm not trying to guard against malicious intent, but rather, stop people unintentionally seeing data they shouldn't.
Dynamic Data Masking ...
- 45.8k
0
votes
1
answer
56
views
Managing very large VLF
I'm investigating a database with a large transaction log. Looking at DBCC Loginfo I can see the database has 1200 VLF's, which is on the high side but not terribly concerning. What does worry me ...
- 38.7k
2
votes
1
answer
934
views
DPM transaction log backups of SQL Server partially contained databases
I am trying to use Microsoft System Center Data Protection Manager (DPM) 2016 (5.0.342.0) to protect SQL Server 2017 Standard CU8 (14.0.3029.16) on Windows Server Std 2016.
I have a named instance ...
CommunityBot
- 1
1
vote
1
answer
471
views
SQLServer Linux: Error restoring backup of DB from Windows w/ full-text data file
I'm trying to move a Windows SQL Server database from Windows 10 to Linux.
For this, I'm following the instructions in
https://learn.microsoft.com/es-es/sql/linux/sql-server-linux-migrate-restore-...
CommunityBot
- 1
0
votes
1
answer
1k
views
Using gMSA account for SQL server reporting services
I am trying to configure SSRS on my VM:s via gMSA account which does not seem to work and unable to access the reporting url ( Web Portal URL) link to check configuration.
The requirement is to have ...
CommunityBot
- 1
-2
votes
1
answer
1k
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
2
votes
2
answers
125
views
What happens if you stop the IndexOptimize job, because it is running long and causing blocking?
When the dbo.IndexOptimize job is running for long time and causing blocking, if we stop the job, does it cause Rollback of everything that was reorged or rebuilt? Or it just stop where it is and ...
- 17.3k
2
votes
1
answer
474
views
Handling new fields when merging in data using hashbytes?
We load in data from stage into our ODS and check for differences using hashbytes. We calculate hashbytes from the stage tables and insert/update into the destination table and also store the ...
CommunityBot
- 1
3
votes
1
answer
851
views
How to install Distributed Replay (dreplay.exe) for SQL Server 2017
In SQL Server 2014, dreplay.exe was available under "Management Tools – Basic" feature. However, in SQL Server 2017 that feature no longer exists (Enterprise edition) and it doesn't come with SSMS ...
CommunityBot
- 1
5
votes
2
answers
2k
views
"the maximum number of '1' dedicated administrator connections already exists" message in error log when connecting to DAC from Powershell
I'm connecting to SQL Server (2016 and 2017 latest builds) via the Dedicated Administrator Connection (DAC) from PowerShell.
The following error message is recorded in the SQL Server Error Log:
Date ...
- 69.7k
0
votes
1
answer
339
views
TempDB file overflow
This is my experience with TempDB. The Algorithm works better when
the TempDB File sizes are the same size.
We were seeing heavy TempDB contention on a TempDB file that was larger than the others. We ...
CommunityBot
- 1
3
votes
1
answer
344
views
How to lock a MSSQL (2017 Linux) account after N unsuccessful login attempts
I have to configure account lock after N unsuccessful login attempts on MSSQL 2017 Linux. That is standalone server and is not in AD.
I couln'd find any valuable information so far unfortunately. ...
CommunityBot
- 1
0
votes
1
answer
1k
views
SQL Server - Add signature to stored procedure by certificate does not work
I have a stored procedure in Database 1 that updates couple of tables in Database 1 and Database 2
To avoid granting calling login/user permissions to do table updates, I would like to use code ...
- 5,452
2
votes
2
answers
1k
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/...
- 392
0
votes
1
answer
77
views
Wait time on server replies is sometimes very long when running the query over and over
As seen in this screen shot, I'm running the same query over and over again, comes back with 0 rows (as expected). For some reason I'm seeing very large wait times on server replies while running this ...
- 25.1k
7
votes
2
answers
8k
views
Persistent Severity 016 alerts: "Change Tracking autocleanup is blocked on side table"
We had a large database running on a SQL Server 2014 Enterprise instance (evaluation license). Due to cost constraints, we had to move it to a new SQL Server 2017 Standard instance (per-core license; ...
1
vote
1
answer
86
views
CDC is not working after restoring the databse
I have just restored my database using the below script,
USE [master]
GO
RESTORE DATABASE [MyDB]
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLSTANDARD\MSSQL\Backup\mydb.bak' WITH ...
- 5,452
0
votes
0
answers
38
views
Cannot perform this operation while SqlServerAgent is starting problem
I use SQL Server 2017, this mornnig when i check jobs i got this error;
I searched a lot but couldn't find a solution.
Does anyone know the solution to the problem?
thanks.
2
votes
1
answer
383
views
SQL Agent stopped recording history (SQL Server 2017)
I have a Dev server that has been running without issue for at least 2 years. This dev machine was a VM Clone of the Production server.
On 2021-06-05 something happened on this server that altered ...
CommunityBot
- 1
3
votes
1
answer
2k
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 ...
CommunityBot
- 1
1
vote
1
answer
3k
views
"New request is not allowed to start because it should come with valid transaction descriptor" using linked server
I have a simple query in a stored procedure that LEFT JOINs a local temp table with several tables across a linked server (the same linked server). This query is resulting in the following error:
...
CommunityBot
- 1
0
votes
1
answer
539
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 ...
CommunityBot
- 1
-1
votes
1
answer
57
views
Alternatives to reclaim free space after Shrink activity
For couple of our SQL server databases on version SQL2017 APP team did purge to change retention on these partitioned tables which has released quite good amount of space.
Some background-
Before ...
- 45.8k
0
votes
1
answer
95
views
SQL Server 2017 Availability Group now thinks it is in a cluster - it isn't and never was!
We have a read-scale availability group on SQL 2017 that for years has worked without fault. Now when we tried to add a new replica (with CLUSTER_TYPE=NONE), it fails because the primary now "...
- 29.9k
0
votes
1
answer
385
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-...
CommunityBot
- 1
0
votes
0
answers
29
views
SQL Server 2017 peer-to-peer Replication problem after adding new column
Originally I have a DB config p2p replication data between 2 SQL Server 2017
Sp_MSupd_XXX has a parameter
@bitmap binary(21)
System table Conflict_XXX column
__$update_bitmap varbinary(21)
...
- 1
3
votes
1
answer
1k
views
What's a pathological case where a bitmap filter would not allow the PROBE(Field, IN-ROW) semijoin reduction optimization?
I recently learned a performance optimization in SQL Server I did not know about ("Bitmap Filter On (Clustered) Non-Nullable Column Hash Semi-join Reduction via Scan + Probe IN-ROW" - a real mouthful),...
- 31
1
vote
0
answers
36
views
Altering the column without sysadmin or db_owner roles in published tables
I am using replication between two MS SQL Servers (2017 14.0.3421.10).
Unfortunately, when replication publishing is switched on, my user for database schema changes is not able to ALTER any columns. (...
- 111
0
votes
1
answer
347
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 ...
CommunityBot
- 1
1
vote
1
answer
4k
views
The OLE DB provider "MSDASQL" for linked server "(null)" reported an error
I created a linked server to MySQL database server on my SQL Server database server. I tested the connection. It succeeded successfully for MySQL connection. I can see the databases on MySQL instance ...
CommunityBot
- 1
0
votes
1
answer
389
views
OPENROWSET Bulk insert Text File NULL Columns
I need to automate a weekly import of .txt log files into a SQL database. I have already created my table, format file and can get some of the data to work using OPENROWSET BULK, but the data from the ...
CommunityBot
- 1
0
votes
1
answer
45
views
Storing segmented time series - performance issue caused by triangle relationship / maximum search
I am trying hard to create a database design from scratch, that allows to store multiple different time series, where each one may be segmented into individual epochs. Inserting new data points for a ...
1
vote
1
answer
400
views
DBCC Check DB fails due to operating system error 665
We have a SQL Server 2017 Standard version where recently the CHECKDB is failing with the operating system error 665.
The fragmentation level of the disk drive is 0% as it is automatically ...
CommunityBot
- 1
0
votes
1
answer
697
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 ...
CommunityBot
- 1
1
vote
1
answer
921
views
Linked Server Configuration using remote user logins
I'm trying to configure a Linked Server using remote logins reducing who has access to the linked server by taking advantage of the not be made option. For test purposes alone, I have used SA, ...
CommunityBot
- 1
1
vote
1
answer
499
views
SQL Server Always ON - LOGPOOL_CONSUMER wait and replication issue
I'm facing a strange issue in one of my customers AG. There're 4 instances under this AG.
Staring a few days ago, my customer complains about some query (SELECT) timeouts issues. When I faced this ...
CommunityBot
- 1
0
votes
2
answers
515
views
Converting multiple comma-separated columns into rows
I have an SQL Server database table that holds comma separated values in many columns. For example:
id
Column B
column c
1
a,b,c,
1,2,3,
2
d, ,f,
4,5,6,
3
g,h,i,j,
7, ,9,8,
I want to split all ...
- 83.3k
1
vote
2
answers
2k
views
How to create and fill multiple session temp tables with dynamic SQL?
I have a column that holds JSON data. The table can be filtered for sets of similar JSON data that will have mostly identical JSON keys in the JSON column. I can dynamically collect the set of JSON ...
CommunityBot
- 1
1
vote
1
answer
431
views
Machine login for Microsoft SQL Server for Linux
-- old question --
I'm building a multi-tenant SQL Server for Linux solution for hosting companies. Basically end-users will get access to SQL server with their own user account.
Now, I want to be ...
CommunityBot
- 1
1
vote
2
answers
279
views
Query to polybase external table giving error "Access to the remote server is denied because no login-mapping exists."
we have successfully created all pre-requisite of creating external tables using SQL Server 2017 Standard Edition CU22 Polybase.
The issue we have is the non-sysadmin login can't query those external ...
CommunityBot
- 1
2
votes
1
answer
1k
views
DBCC ShrinkFile EmptyFile fails due to system table SYSFILES1
In following a recommendation from our new storage vendor, I'm trying to split up some large & busy databases into multiple data files, each to live on a separate VDisk that has a separate ...
CommunityBot
- 1
2
votes
1
answer
327
views
sp_addUser through sp_execute (SQL) in master db
The task is to automate restoring a group of databases, fixing orphaned users, and granting SELECT/INSERT/UPDATE/DELETE permissions.
use master
go
ALTER DATABASE [myDatabase] SET SINGLE_USER WITH ...
CommunityBot
- 1
0
votes
0
answers
38
views
SSRS 2016 compatibility with SQL 2017 after SQL2016 upgrade
We have an SQL Server database engine on the 2016 version. We also have SSRS in the 2016 version. I plan to upgrade the SQL database engine from the 2016 version to the 2017 for it to participate in ...
- 5
0
votes
2
answers
74
views
On update: Login failed for SQL Server authenticated sysadm
I run into a weird bug?
User complained to me that he cannot anymore update certain tables in database. He had owner rights in database and the login was window login (ad-login). I tried elevating him ...
- 101
4
votes
1
answer
292
views
Selecting from sys.dm_tran_locks takes 100+ ms
Our application uses SQL Server application locks and we have cases where we need to see whether a given lock exists or not. To do this, we retrieve and filter information from sys.dm_tran_locks.
...
- 82.9k
0
votes
0
answers
47
views
How to check Default Isolation Level at server/instance level
We have installed SQL Server 2017 in 2 servers. Then created 2 databases same way in both servers. In one server I see my database snapshot isolation level is ON and another server its OFF.
So, how ...
- 82.9k