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
0
votes
1answer
19 views

SQL job sending email even if not configured to send notifications

I am using this version of SQL Server. Microsoft SQL Server 2016 (SP2-CU12) (KB4536648) - 13.0.5698.0 (X64) Feb 15 2020 01:47:30 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on ...
7
votes
0answers
46 views

Running a .vbs Script Locally Results in Intermittent Connection Issues

I have this Windows Server with two SQL Server 2016 instances and each SQL Server instance has two databases. Then we have the monitoring solution checkmk (Checkmk Homepage) that connects locally via ...
0
votes
0answers
12 views

SQL Mirroring Replace Certificates

I have inherited two SQL 2016 servers with two mirrored databases. They have been built with certificates as they are in a workgroup, the certs expire in six months. I am trying to understand the ...
2
votes
1answer
56 views

When to use BEGIN DISTRIBUTED TRANSACTION instead of BEGIN TRANSACTION in SQL Server?

For this question lets assume there are two servers, ServerA and ServerB, and there is a linked server setup on ServerA to ServerB. Let's also assume I have REMOTE_PROC_TRANSACTIONS set to ON for both ...
0
votes
2answers
58 views

Solution for syncing a database between two server and secondary can be used for all DML operation

I need a solution to synchronize a database (SQL Server 2016 Enterprise) between two servers, and I would be able to connect my secondary application to the secondary database which is getting ...
1
vote
2answers
32 views

Number of connections differ between performance monitor and SQL Server Profiler

WHAT I'M TRYING TO FIND OUT How many logins/s and logouts/s I'm having on my SQL Server 2016 Standard. WHAT I DID SQL SERVER PROFILER In SQL Server Profiler, I made a trace selecting only 'Audit Login'...
0
votes
0answers
23 views

Bad estimates for 'dbcc checkdb with estimateonly'

I'm trying to compute the estimated tempdb space needed to run CHECKDB on one of our largest databases of size 7TB using the command DBCC CHECKDB WITH ESTIMATEONLY. The estimates came out to be 1.7 TB,...
0
votes
1answer
55 views

String aggregation using STUFF

Let's say I have table #DOC like this: polNum boxNo batchNum 111111 null qwe1234 111111 ff/55 rto1235 111111 ee/59 yhn1238 111111 ww/55 rto1235 222222 dd/58 jkl1234 222222 null fgh1234 333333 ...
0
votes
0answers
16 views

External verification of `SIGNBYCERT()` signatures from SQL Server 2016

I want to sign plaintext using a SQL Server certificate and then verify that signature using the certificate's public key on an external system. Here's what I've tried. -- Create a self-signed ...
0
votes
1answer
24 views

XE process_login_finish

Can anybody explain the purpose for process_login_finish extended event? I trying to catch some events for XE session with this event, but no one messages arrives to the session. However event "...
0
votes
1answer
16 views

Adding new user info to current old user accounts

Hey all so I have a database that has been up and running for a few months now. I had a request to add the users First and Last name as well as their organization. This was easy enough to do for NEW ...
0
votes
0answers
23 views

TSQL - Take a single row query and transpose based on field value

I'm trying to create a fact table with data I was given. We have a monthly goal number, but we want to be able to figure out how many weeks are in that month and divide the goal number by the weeks in ...
-1
votes
1answer
17 views

Why does query rowcount change when I enable “include actual execution plan” and “include live query statistics” together?

I have a moderately complex SELECT query that utilises inline views and joins. I am intending to see if I can rewrite portions of the query to improve performance. I pasted the query into two SQL ...
0
votes
0answers
10 views

SSRS High IOPS and Slowdowns

Please let me preface by saying that I understand poorly formed reports can cause poor SSRS performance and I have people looking at that issue separately. I have an SSRS server that is seeing ...
0
votes
1answer
56 views

What would cause this SQL Server deadlock?

I have a node.js script that is attempting to geocode records in a table. The script selects 100 records at a time that do not have lat/log data, geocodes them using a third party api and writes the ...
0
votes
1answer
15 views

Query store data maintenance background tasks

Query store has been running for years on the server without any noticeable disk IO spikes on log backups. We run on SQL Server 2016 EE version 13.0.5622.0 Recently we experienced a sudden surge in ...
-1
votes
1answer
33 views

Search string data with special characters removed

We have a table with ~6 million of products: ID Name Code 1 One 123.456.789-M 2 Two 852.789456 3 Three 1-123654.P What is a good way (with regards to performance) to filter products by code ...
0
votes
1answer
24 views

what databases are already set up in the log-shipping, and which ones are not?

I am setting up log-shipping from server1 to server2. there are many databases on server1 and I want to automate the process. I have already done a few databases manually, while I am working on the ...
2
votes
1answer
37 views

How to trim the log file that stores information about job history execution?

I was having a look at my sql server jobs and proxies on an specific server in the production environment. I pretty much have an script to check the status of the jobs but in case I have to ...
2
votes
2answers
159 views

Is partial synchronisation possible between two databases on different servers?

Imagine a scenario where you have a primary instance of a database and a secondary instance that is automatically synchronised - pretty much as with Availability Groups. My question is whether I could ...
0
votes
0answers
41 views

Comma separated value string and compare against other values

Hey all I have been trying to wrap my head around how to go about doing this for a while now and I'm not really coming up with anything. Here is my query: DECLARE @ATID AS VARCHAR(3) = 12 DECLARE @...
0
votes
0answers
25 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. ...
0
votes
2answers
30 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?
1
vote
1answer
34 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 ...
4
votes
3answers
223 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/...
1
vote
3answers
32 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 ...
0
votes
0answers
37 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 ...
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 ...
1
vote
3answers
113 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, ...
2
votes
1answer
44 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
644 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
29 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 ...
1
vote
1answer
53 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
2answers
41 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 (...
2
votes
2answers
68 views

Key lock triggered by concurrent 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 Work mode:concurrent batch update ...
2
votes
0answers
37 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
3answers
100 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 ...
0
votes
1answer
57 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
0answers
21 views

How To Grant A Specific Login Under Acitve Directory Group To View Server State In SQL Server [duplicate]

I want to grant only a login who is under an active directory user group to view server state in sql server. How can i do it?
0
votes
1answer
23 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
210 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 ...
0
votes
1answer
36 views

sql server 2016 sp1 to sp2 with latest cumulative

I inherited an SQL Server 2016 SP1. I am not a DBA and work at a small company where we do not have a test environment. I know that I need to install the SP2 first. Do I then install the last ...
0
votes
1answer
32 views

One-directional Transactional Replication is causing one table's Clustered Index to be copied over as a Nonclustered Index in the subscriber database?

This seems really odd to me, as I've used SQL Server's replication feature for a while now and have never seen this occur before. I'm using One-Directional Transactional Replication and I have ServerA ...
3
votes
1answer
335 views

Can shrink database improve performance

Yesterday I've got a mail from one of our suppliers mentioning that there were performance issues with their application. I don't know yet what the issues are but the solution they propose is to ...
0
votes
0answers
28 views

There are currently no logon servers available to service the logon request

I am facing this particular issue in one of my server (SQL 2016 std on Win 2016 std) , where SQL server does not come online post OS reboot. Event viewer has below error. After I login to server and ...
0
votes
0answers
17 views

Get real error from backup job failure instead of backup terminating abnormaly

I've had log backup jobs fail for a few different reasons lately. When I go to the job it tells me backup terminated abnormally. However, when I run the backup script in SSMS it will tell me the ...
0
votes
2answers
49 views

Script execution order

I did Generate Script on a database and got over 1700 scripts. I've sorted them into folders below: Each folder contains .sql files of that type, for example 06 UserDefinedFunction contains many ....
0
votes
3answers
52 views

Index Maintenance on busy OLTP database

I have a database where constant read writes happening all the time. Searches are being done with wild card entries. The server is on SQL 2016 standard edition. There is no budget for enterprise nor ...
1
vote
1answer
30 views

Running Excel addin for MDS2019 vs MDS2016

We are upgrading master data services (MDS) of SQL2016 to SQL2019.Is there a way to run excel 2019 addin for connecting to SQL 2019 MDS along side(parallely) with excel 2016 addin for connecting to ...

1
2 3 4 5
50