Questions tagged [sql-server-2016]

SQL Server 2016 (major build version 13.00.xxxx). Please also tag sql-server.

Filter by
Sorted by
Tagged with
5
votes
3answers
4k views

In Memory Database Remove File and FileGroup

I have a SQL Server 2016 database and I configured in-memory table. I want to drop this configuration, then I drop that table. When I want to delete that file and filegroup, I got following error. ...
9
votes
6answers
5k views

How to prevent SQL files opening new instances of SSMS 17.9.1?

Synopsis This is not a new problem and there are several solutions floating around on Internet. Problem When you double click on a .sql file, a new instance of SSMS is opened. I did not have this ...
0
votes
1answer
1k views

Drop a replicated subscription table that has been orphaned after restore migration

We restored a database on a new server for a migration. When attempting to set a subscription to a transnational publication, we get the error: Can't drop the table because it is set for ...
0
votes
0answers
18 views

SQL Server performance issue (Log Shipping became slow)

I'm facing the problem that MS SQL Server 2016 suddenly become slow. For example, restore log process (log shipping). I can see LASTWAITTYPE is PAGEIOLATCH_EX in SYSPROCESSES table during the process. ...
1
vote
3answers
26 views

Tsql help - doing a large update from CSV file to a table in ssms

I have been given a csv file with just ids in there and those ids are in a table in ssms with columns that require updating. Out of 90,000 ids in that table, only 83,000 of them require updating from ...
3
votes
1answer
118 views

Understanding DBCC TRACEON and TRACEOFF Globally and Session-wise

For those of you that keep track of what I discuss in The Heap™ you might have been following my endeavours whilst migrating a database from SQL Server 2008 R2 to SQL Server 2016 and battling with ...
5
votes
1answer
8k views

Could not obtain exclusive lock on database 'model' - who is using it?

when trying to run the following query in order to create a new database: CREATE DATABASE [Lunch] CONTAINMENT = NONE ON PRIMARY ( NAME = N'Lunch', FILENAME = N'E:\Data Files\...
0
votes
1answer
2k views

Error 20598 - The row was not found at the Subscriber when applying the replicated command

I am facing the error 20598 with Transactional Replication: The row was not found at the Subscriber when applying the replicated command. Normally this error occurs when an UPDATE or DELETE ...
0
votes
1answer
97 views

High Latch Wait time with Logon trigger enabled while full backups are running

I have 14 production servers with SQL 2016 standard version, 128 gb of RAM, 16 CPU and SSD drives. Microsoft SQL Server 2016 (SP2-CU15-GDR) (KB4583461) - 13.0.5865.1 (X64) Oct 31 2020 02:43:57 ...
6
votes
3answers
3k views

How to retrieve a large text from inside a table?

for my own records I have a table in one of my servers where I like to save my activities and scripts the table definition is: IF OBJECT_ID('[dbo].[activity]') IS NOT NULL DROP TABLE [dbo].[...
0
votes
0answers
19 views

SQL Server Slow After VM and SQL Restore [closed]

SQL Server 2016 SP2 Standard Edition 4 sockets with 6 cores per socket totaling 24 cores 128 GB RAM Windows Server 2012 R2 Data Center So, we had an incident about a month or so ago that required us ...
1
vote
1answer
418 views

View with OPENQUERY won't build in SSDT project

I have an existing database that I'm trying to set up as a database project. I'm not able to get the following statement to build: CREATE view [dbo].[VW_SOMEVIEW] as SELECT * FROM OPENQUERY ([...
3
votes
2answers
1k views

Migrate replication from SQL Server 2008 R2 to SQL Server 2016 - Old to new hardware

I will be working on a project shortly that would involve moving the replicated databases off of old to the new SQL Server instance. I haven't done this in the past so I don't have much knowledge ...
0
votes
2answers
28 views

does SQL automatic backup runs even the machine/server is offline

I am wondering if the SQL jobs like automating the database backup runs even the machine/server is offline?
0
votes
2answers
546 views

Data Warehouse: Auto Generate Slowly Changing Dimension Code For Any Table with Dynamic Sql

Can someone code review to take OLTP/Stage data and transform it into Data Warehouse Kimball Slowly Changing Dimension Type 2? This code should be dynamic, and should be able to utilize any table as ...
1
vote
1answer
55 views

Stale Statistics and View performance on SQL Server 2016

I have a report query which behaves abnormally, but rebuilding the indexes/refreshing statistics seems to sort it momentarily. So my doubt essentially is - What could be the reason that even with ...
1
vote
1answer
33 views

Update same column, same DB with specific records

We are migrating a site from one Active Directory system to another but in the third-party app, we need to keep the old accounts, so we renamed the old accounts with "_Old" appended to the ...
0
votes
2answers
9k views

SQL Server Connection property applicationIntent

I have a doubt in ApplicationIntent connection property of SQL Server. The doc say it has two values ReadWrite and ReadOnly. If I set the value as ReadOnly, will it allow only read queries or will it ...
0
votes
2answers
920 views

SQL Server ReadOnly Routing Connection String

Can we route read-only connection to readable secondary replica without setting connection string (ApplicationIntent = ReadOnly)?
1
vote
2answers
3k views

Load balancing reads SQL Server 2016 AG

I'm trying to balance read traffic between two SQL Server 2016 Enterprise Edition instances (Primary and Secondary) using availability groups and the read only routing. Here is my current setup: ...
3
votes
3answers
220 views

Why do subsequent unmet conditions in my CASE statement affect my return data type?

I have the following variables and CASE statement: DECLARE @DATE_VALUE VARCHAR(20) = '4/29/2021' DECLARE @CONVERT_CODE VARCHAR(1) = 'I' SELECT CASE @CONVERT_CODE WHEN 'I' THEN DATEDIFF(day, '04/21/...
0
votes
1answer
114 views

Logon failed for login due to trigger execution / LOGON TRIGGER

I have this situation. I created a logon script to block any session for specific logins. These logins are in a table. So the behaviour of my script is simple. Get the login from the table, compare it ...
4
votes
1answer
643 views

22046 “Impersonation error” running SQL Server Agent job

(New to SSIS. None of the similar questions mention this particular impersonation error.) I'm trying to run an SSIS package as an Agent job via a proxy, and I'm getting a non-helpful error message. ...
1
vote
1answer
36 views

Invalid Column Name on CONCAT_WS

Sorry if this seems like rambling I'm not typically a DB person just trying to work through an issue. I have two tables cases and legal_entity the data on the two tables looks like this legal_entity ...
0
votes
0answers
24 views

SQL Server Replication: How do you change the distributor?

I've been given the task to change the distributor to a new server for pre-existing replication (both snapshot and transaction). Server A: Existing Replication (Publisher) Server B: Existing ...
1
vote
3answers
100 views

How can parse column names using ordinal position (not name) from XML data in SQL Server?

I have several hundred tables in a database that have the same structure: SomeId, Pos, Varying Number of Other Fields So, for example, one table may look like this: PersonId, Pos, Hobby, Degree 12345, ...
1
vote
1answer
327 views

How does one export/import registered servers with passwords?

I have a pretty extensive collection of registered servers on SQL Server Management Studio 2016 on my box. However, I got a new box and want to move the entire collection there. So I right-clicked ...
6
votes
5answers
2k views

Is OPTION (RECOMPILE) used in production environments?

Is OPTION (RECOMPILE) used in production? This option seems to get a lot of bad press. Is it deserved? I have a DBA who, so far, is not a fan of the idea of OPTION (RECOMPILE) within the meat of ...
2
votes
1answer
114 views

How to check if backups are reliable?

I generally have to restore databases to different servers after important data imports or other important and delicate database operations. I need to be sure that my backups up to that point in time ...
0
votes
1answer
146 views

Merge replication - Could not remove directory

We are facing an issue with our replication server. We are currently setting up a new environment to replace the old replication server and we keep getting the following error. Server XXX\YYY, Level ...
1
vote
2answers
175 views

Mandatory to use “Three Parts Name” in T-SQL statements of SP

Using three parts name in SQL statements is a good practice it helps in object identification and performance improvement as well. It can also help for many other purposes like if you want to move SPs,...
2
votes
1answer
42 views

Use CTE to fetch most recent row for each ID

I just recently started learning about the wonders of CTEs and how they can be used, instead of WHILE loops, to optimize performance. For context, I am automating the process of updating firmware on ...
4
votes
4answers
621 views

Why changing the column in the ORDER BY section of window function “MAX() OVER()” affects the final result?

I have a table with below structure and it's data : create table test_table ( Item_index int, Item_name varchar(50) ) insert into test_table (Item_index,Item_name) values (0,'A') insert into ...
1
vote
1answer
28 views

Does SQL Server upgrade to table locks at total locks or session locks?

I’ve a table that both has mass sequential inserts at the end of the CI and random (very distributed) reads+updates. Naturally, the mass inserts should not block the random access. RCSI is used, so ...
7
votes
2answers
1k views

What is the optimized way to pass multiple parameter from a single table to a function?

I have a function [dbo].[FN_CalcAerialDistance] which accepts 4 parameters and returns its result. Now that 4 parameters come from the same table, I do not want to hit Database multiple times to bring ...
2
votes
1answer
47 views

Key lock triggered by batch update

I'm doing a batch update operation,but it appears key lock I think I'm in trouble. I don't understand why the deadlock is happening. Environment: SQL Server 2016 deadlock graph detailed: <deadlock-...
0
votes
2answers
40 views

Online rebuild consuming lot of log space

why the online rebuild on a primary key clustered index in taking almost equal to the space of non clustered index size which is untouched. Details: We have a table with primary key clustered index (...
3
votes
3answers
381 views

Permission error when using sys.dm_exec_input_buffer with @@SPID

When I run this blob of SQL on SQL Server 2016 or an Azure SQL DB: --hello world SELECT event_info FROM sys.dm_exec_requests req CROSS APPLY sys.dm_exec_input_buffer(req.session_id, req....
0
votes
1answer
89 views

Does Data Migration Advisor migrate system databases?

I would like to know if Data Migration Assistant copies system databases (master, model, msdb) into the new server.
2
votes
3answers
97 views

Table scan instead of index seeks happening when where clause filters across multiple tables in join using OR

We have an application generated query using a view that has two tables joined on a LEFT OUTER join. When filtering by fields from just one table (either table) an index seek happens and it's ...
2
votes
0answers
34 views

Researching Performance Degradation on AO Secondary using MultipleActiveResultSets (MARS)

Environment: SQL Server 2016 Enterprise Edition on Windows 6.3 9600 two node cluster with Always-On. We have an application that executes SELECTs on the only secondary node of a SQL Server 2016 always-...
2
votes
1answer
68 views

SELECT query in a SP in AlwaysOn Availability Group Read Only Routing

There is a stored procedure which has select statement as well as insert and other statements. What will be the behaviour of the stored procedure in a Read Only Routing configured in AlwaysOn ...
0
votes
1answer
673 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 ...
0
votes
1answer
652 views

SSPI Handshake failed

I am facing a strange situation... On a SQL2016 on Windows 2019, I have an application running on windows2019 that connect to the SQL server using a domain service account (same domain as the SQL ...
3
votes
1answer
388 views

How to decrypt dtsx package?

I've got some SSIS packages stored as varbinary, and I can load them into a temp table like so: Use SSISDB if object_id('tempdb..#package') is not null begin drop table #package end ...
0
votes
1answer
46 views

sp_Blitz - RegOpenKeyEx() returned error 2

I'm running the latest sp_Blitz script on a SQL 2016 Std server on a Windows Server 2016 physical box. I'm getting the following error. What could be causing this and how do I troubleshoot it? ...
0
votes
1answer
43 views

alter compatibility level

I plan to change the compatibility level on the databases in an sql server 2016 sp1 instance from 100 to 130. When upgraded from sql 2008 to sql 2016 the compatibility level was changed from 80 to 100....
0
votes
1answer
21 views

Missing Target Local Server in Job

SQL Server 2016 Enterprise, CU15 + GDR. Client recently started using OPCON in production and I noticed that when OPCON creates jobs, it doesn't add the Local Server as the target. This prevents the ...
1
vote
1answer
193 views

Why is the query in ETL via SSIS slow but via a local stored procedure it is fast?

I'm seeing the classic 'runs fast in studio manager but slow in application' problem. Sounds like it might be parameter sniffing. However, my experience with ETL and SSIS is zero. From the DBA I ...
1
vote
1answer
843 views

How to check who has permission to execute a stored procedure?

I have two environments/servers. Each has two databases, say DATABASE_A and DATABASE_B. There is a stored procedure in DATABASE_A (in both environments) that queries DATABASE_B and also calls multiple ...

1
2 3 4 5
50