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
3 votes
0 answers
47 views

request_status is Grant and Convert on the same resource_type at the same time

I have a typical blocking scenario due to lock contention. Lead blocker has these locks on an object. <Object name="Table1" schema_name="dbo"> <Locks> <Lock ...
SqlWorldWide's user avatar
  • 13.2k
0 votes
1 answer
46 views

Avoid logfile getting too large on SQL Server

I have a SQL Server stored procedure that essentially 'flattens' a complex database into a single, simplified, table in a separate database to allow users to query the data without needing to ...
Andy's user avatar
  • 3
0 votes
0 answers
40 views

Fast execution plan and search, slow to show results [SQL Server]

I have this query that shows the execution plan took about 2 seconds but for the results to load (which is one row) took 7 seconds. What would be causing this delay? I see it on the application side ...
Steven M's user avatar
0 votes
0 answers
18 views

Error after failing over always encrypted database

I started capturing the following error after performing failover into secondary replica for the database which was using the always encrypted feature of SQL Server 2017 Enterprise Edition: Some ...
Rauf Asadov's user avatar
  • 1,263
0 votes
1 answer
36 views

Upgrade SQL Server Server Express to Licensed Enterpise edition?

we have sql server express edition 2017 with default instanc ,now we want to upgrade it to Enterprise Licensed Edition 2017 with default instance in the same server At first I thought I had to ...
yamihero777's user avatar
0 votes
1 answer
79 views

Cannot do a backup of a read_only database

I'm facing a strange issue where my backup fail with the message : Msg 3906, Level 16, State 1, Line 5 Failed to update database "MyDBB" because the database is read-only. Msg 3013, Level ...
Dominique Boucher's user avatar
0 votes
1 answer
22 views

Is there a way to verify if a SSIS instance was configured as a cluster resource or not?

We had 2 servers in a cluster ( no aoag ) with SQL + SSIS. one of the cluster nodes died and we removed it. we are in the plan to create a new cluster node and configure SQL engine normally on it as a ...
Racer SQL's user avatar
  • 7,386
-1 votes
1 answer
78 views

Can Query Store Show Spills To Tempdb?

I know that Query Store can show tempdb usage as of SQL Server 2017, but can it show spills to tempdb? I'm highly confident that the plan cache can.
J. Mini's user avatar
  • 123
0 votes
1 answer
31 views

Changes to MSSQL System databases from Cumulative Updates

What sort of changes are made to System tables (master / model / msdb) by Cumulative Updates? For context, I have SQL servers that have AlwaysOn Groups, and am looking at a way to roll back updates if ...
Brett's user avatar
  • 101
0 votes
2 answers
58 views

Do I need the MDF and LDF files when I run a script to generate my schema? [closed]

I'm migrating a database from SQL Server 2019 to SQL Server 2017. To do so, I am using the "Generate scripts" feature in the source database to generate the schema and data separately. (I ...
arnoldbird's user avatar
1 vote
1 answer
40 views

Get All Windows between Start and End Dates

Here is the SQL Fiddle for my question: https://sqlfiddle.com/sql-server/online-compiler?id=ab1634d7-fec7-4918-ac1c-3f4fcac8dc92 I have the following sample data: DROP TABLE IF EXISTS #Price CREATE ...
Vaccano's user avatar
  • 2,496
0 votes
1 answer
28 views

Impact of timezone change during sql server migration

We are migrating from our legacy SQL Server 2017 on Windows 2016 host to SQL Server 2019 on Windows 2019. In above migration the bigger change has been the difference in timezone between legacy and ...
Newbie-DBA's user avatar
0 votes
0 answers
108 views

Multiple SQL Server versions and Integration Services evaluation period has expired

Initially, I have installed Enterprise Evaluation Edition and then we have installed licensed version Standard Edition 2017. All our apps using SQLSTANDARD instance and backup maintenance jobs were ...
Imran Qadir Baksh - Baloch's user avatar
1 vote
1 answer
46 views

Can a server outside the Windows domain connect to a 2nd SQL instance via a linked server?

I have the infrastructure shown in the diagram below. Web Server 1 is outside the Windows Domain. Web Server 2 is inside the Windows Domain. SQL Server 1 and 2 are both inside the domain. There is a ...
ktakmn's user avatar
  • 25
0 votes
1 answer
47 views

Create user that can have access all tables in Schema1 but just have insert to specific table in Schema2

Please consider this scenario: I have 2 Schemas: SCHEMA1, SCHEMA2 In each schema I have some tables. I have two logins for each schemas. I want to create a user that can access (CRUD operation) on all ...
DooDoo's user avatar
  • 203
1 vote
2 answers
150 views

Track CPU time used by a Stored Procedure

I need to track CPU time (not duration!) used by a stored procedure. Procedure has many SQL statements inside it. I've tried module_end event in Extended Events, but it does not have cpu_time field (...
Aleksey Vitsko's user avatar
0 votes
1 answer
71 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 ...
AlexP012's user avatar
0 votes
2 answers
109 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 ...
AlexP012's user avatar
2 votes
2 answers
285 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 ...
veena ranganath's user avatar
0 votes
1 answer
160 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 ...
Steven M's user avatar
1 vote
1 answer
189 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 ...
Imran Qadir Baksh - Baloch's user avatar
0 votes
0 answers
50 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.
shnsrl's user avatar
  • 11
0 votes
1 answer
158 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 "...
Stephen H's user avatar
0 votes
0 answers
51 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) ...
Hallbeer's user avatar
1 vote
0 answers
75 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. (...
Dex's user avatar
  • 111
-1 votes
1 answer
74 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 ...
Newbie-DBA's user avatar
0 votes
1 answer
52 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 ...
rellachs's user avatar
0 votes
2 answers
1k 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 ...
jawad riaz's user avatar
0 votes
0 answers
47 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 ...
mk SQL's user avatar
  • 1
0 votes
0 answers
55 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 ...
Imran Qadir Baksh - Baloch's user avatar
4 votes
1 answer
350 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. ...
mwigdahl's user avatar
  • 263
0 votes
2 answers
97 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 ...
user1054844's user avatar
0 votes
1 answer
88 views

EF Queries Using Read Commited Though snapshot_isolation_state and is_read_committed_snapshot_on are ON

I am running this query, SELECT name , snapshot_isolation_state , is_read_committed_snapshot_on FROM sys.databases s name snapshot_isolation_state is_read_committed_snapshot_on master ...
Imran Qadir Baksh - Baloch's user avatar
0 votes
0 answers
116 views

SQL Server Always On Availability Groups are green, but manual failover attempts consistently failing ("duplicate IP address", etc. in cluster logs)

We're running SQL Server 2017 Enterprise Edition on a pair of EC2 instances (we'll call them "P1" and "P3" here) in AWS. Each EC2 instance is in its own subnet, and runs two ...
Skip Daly's user avatar
3 votes
1 answer
499 views

How to create aligned index (PK) on partitioned table and delete the non-aligned index?

I have a large partitioned table by month (70 Billion rows) It has 2 indexes Clustered index on deviceId, logDateTime, eventId, id (unique) Non Clustered index on id (unique) - Primary Key the first ...
Quade's user avatar
  • 301
0 votes
2 answers
116 views

What are the Minimum Permissions to Create an MSSQL Database and Take Ownership of it?

I would like a less privileged user (KINGDOM\joker) to be able to create, manage, and drop databases on an MSSQL 2017 server [14.0.2027.2 (X64)]. KINGDOM\joker should only be able to affect the ...
Anthony Ciani's user avatar
0 votes
1 answer
200 views

Transparent Data Encryption doesn't appear to start encrypting the DB

I have enabled TDE on a big DB of mine (mdf file:9.6GB, log file:12.6GB). I have followed the steps from this video (amongst other sources): https://www.youtube.com/watch?v=6EhCeLbAS_o It has been ...
user2173353's user avatar
0 votes
2 answers
88 views

How to benchmark SQL Server performance after hardware replacement?

We are going to upgrade server with new hardware. And i'm kinda curious about how is SQL Server performance will improve. I'm not a DBA and know just a little about performance monitoring. My only ...
Kliver Max's user avatar
0 votes
1 answer
52 views

SSISDB rename for maintenance

We have an SSISDB database that has become too large and we wanted to do some maintenance on it. I've read that you shouldn't rename the SSISDB database as it would break links to existing packages, ...
Nolan St. Martin's user avatar
1 vote
1 answer
124 views

Which Intelligent query processing features are available in the Enterprise Edition only?

Many features have been released starting from SQL Server 2017 to SQL Server 2022 as part of the 'Intelligent query processing' family. Which ones are Enterprise only, and which are available in the ...
SqlWorldWide's user avatar
  • 13.2k
0 votes
0 answers
103 views

SSIS Database Always on AG and Patching

I am after clarification, when we patch a SQL Server Always on AG environment that has the SSIS database in the AG we remove the SSIS database, patch failover, patch and add the SSIS DB back into the ...
Stockburn's user avatar
  • 419
0 votes
1 answer
102 views

Asynchronous synchronization failed on secondary node

Any idea why synchronization failed to asynchronous node. The database got corrupted and a new database had to be created and synchronization reinitialized. Asynchronous synchronization failed with ...
mk SQL's user avatar
  • 1
-1 votes
1 answer
33 views

AOAG not failover when server is unresponsive

We usually have some issues with one of our servers. We notice it was related to RAM and we are already dealing with it. In the meantime, when the server becomes unresponsive, it will just lock all ...
Racer SQL's user avatar
  • 7,386
3 votes
1 answer
173 views

How is a statistics collection different to normal table stats?

I have a small table with few rows create table dbo.p(i int); insert dbo.p(i) values (1), (2), (3), (4), (4); I created statistics; no index, just the stats. create statistics p_c on dbo.p(i) with ...
Michael Green's user avatar
1 vote
1 answer
99 views

Transferring ownership of table to a schema using sp_changeobjectowner

I have a number of tables in the database that belong to schema FOO, at least I think they do; they appear in the tables listing in SSMS as FOO.tablename, and when I right click on one of them and ...
Tim's user avatar
  • 545
2 votes
2 answers
461 views

Recovery attempt for database corruption of catalog, corruption of internal tables

The issue was noticed when we ran dbbc checkdb. EDIT: I looked up altering system tables and was successful in deleting the internal table from sys.sysschobjs$ from its id. https://www.sqlskills.com/...
Rajin Tamrakar's user avatar
0 votes
1 answer
127 views

Can not Truncate the database because “secondaries has no log was added”

I am a newbie to SQL Server. I got an error when trying to truncate the database: Database can not shrink until all secondaries have moved past the point where the log was added. When I check the ...
Anh Nguyen's user avatar
2 votes
1 answer
631 views

SQL server wont start automatically after OS upgrade

I did an in place upgrade for host OS from Windows server 2012 R2 to windows server 2022. After this move, I noticed the SQL server 2017 enterprise edition instance therein does not start ...
franklwambo19's user avatar
2 votes
1 answer
206 views

Significance of Historical waits report in SQL server Performance dashboard

I have the following historical wait report for my SQL server. I have noticed that Wait Category Tran Log IO is consuming 27.09% of percentage time followed by Query Store which takes 24.34%. I want ...
IT Researcher143's user avatar
4 votes
1 answer
221 views

Unable to restore database with datetime2(0) in Partition Function

I have a SQL-Server database with large table that partition by a datetime2(2) column. Some (old) filegroups marked as READ_ONLY. Periodically I make the backup with READ_WRITE_FILEGROUPS option. I ...
Oleg's user avatar
  • 43

1
2 3 4 5
21