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
1
vote
1answer
26 views

Performance tuning of Purging table Data in SQL Server 2016

I have to Automate the Purging huge volume of data from main table to Archive. Steps I am following : I am inserting the data from main table to archive table. After cross verifying the count in ...
0
votes
0answers
9 views

SQL Failover and Failback Script for SRM Recovery Plan(preferably power shell or CMD or Batch script)

OS: Windows Server 2016 Standard SQL: SQL Server 2016 Enterprise SQL Setup: Two Nodes(Primary and Secondary Node) in PDC(Primary Data Center) having Always On Availability Group with Synchronous Mode ...
1
vote
0answers
23 views

SQL Server 2016, history temporal table not compressed by default in Standard Edition

I have a BI database running on SQL Server 2016 Standard SP2 CU14 (13.0.5830.85) in which there are multiple temporal tables defined with default history tables. According to Microsoft, such history ...
0
votes
1answer
64 views

How can you split multiple delimited strings of varying sizes into rows?

I have a situation where for each ID in my table there are multiple fields that each contain a comma-delimited string. The strings don't necessarily have the same number of elements as one another. ...
0
votes
0answers
10 views

Upgraded DB Driver SRVSQL High Waits PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICY

Upgraded our PHP SQL driver and now the Top 3 waits are below? Wait_Type Wait_Time_Seconds Waiting_Tasks_Count Percentage_WaitTime PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICY 5.551000 53732 21....
1
vote
0answers
50 views

service broker queue index growing beyond control

We have a queue that was set up about ten years ago in 2008. We have been running it on an Azure VM Standard edition sql 2016 for about 3 years no with no problems. In the last 3 months, with no ...
2
votes
1answer
28 views

Select query slow in the big table because of expired stats in Microsoft SQL Server

I have an application to select/search records from the database (application and database in separate servers). In the database, one table only is fast growing table (more than 2 Millions per day). ...
0
votes
1answer
33 views

Creating an Index based on Execution Plan

I've been rewriting a view that was performing poorly - it had 28 LEFT JOINs to another view and I have managed to modify this to a single LEFT JOIN and we are seeing a pretty good improvement in the ...
6
votes
1answer
176 views

Is there any side effect if select statements are wrapped in BEGIN TRAN … COMMIT?

I am working on a rather old .NET project and have introduced some new features (on top) that have generated the following side-effect: all generated SELECTs (or groups of) are wrapped in BEGIN TRAN .....
0
votes
1answer
23 views

MS SQL Server 2016 - Database Security - Login Permissions

I have an SQL Server 2016 database with Always On structure. I have a login that I called x. My x login's properties are: Default database: abc Server roles: public (ticked) User mapping: Map (ticked)...
1
vote
0answers
25 views

sp_prepare vs sp_execeute PHP MSSQL

This question is regarding the PDO_SQLSVR driver for PHP and Microsoft SQL Server and how it interacts with the database, whether via sp_prepare (prepare statement execution) or sp_execute (direct ...
-5
votes
0answers
47 views

How to determine what is the potential max worker thread count on a server where the server property is set to 0? [duplicate]

I have a SQL Server instance where the Max Worker Threads server property is set to the default of 0. Periodically I'm receiving the the following errors (in the SQL Error Log): Error: 17189, ...
0
votes
0answers
44 views

Large number of connections to an SQL server results in read write bottleneck on hard drive?

I have the following problem which I can't find a solution for. We have a learning School System for more than 10000 students that upload photos and PDF of Homeworks as binary to an SQL server. During ...
0
votes
1answer
36 views

SQL Server 2016: Table can't grow while 313 MB is still available in the database

Yesterday, the below error was reported: Could not allocated space for object 'dbo.X'.'Y' in database 'Z' because the 'PRIMARY' filegroup is full After deletion of some records from the table by an ...
0
votes
1answer
20 views

MS SQL Server 2016 - Always On Node-2 Stuck In Resolving State

For testing the Always On structure; we suddenly shut down the machine on which Node-1 was running. So we wanted to see that Node-2 turns primary. However, Node-2 was stuck in resolving mode. 3 ...
0
votes
1answer
19 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 ...
0
votes
1answer
33 views

SQL database mail configuration to use a domain account that is not the SQL Service account

We have been asked to use a domain service account for everything releated to email (smtp). I looked at the SQL Database Mail and the only option that looked like Windows Auth. is the first one: The ...
2
votes
3answers
49 views

Query to find sql server services currently running [duplicate]

Is there any query to find all SQL services currently running on the server including SSRS, SSIS, SSAS?
0
votes
2answers
52 views

SQL Server 2016 -> 2019 AlwaysOn Rolling Upgrade Migration

I have a migration concept with an AlwaysOn configuration from SQL Server 2016 (on windows server 2016) to SQL Server 2019 (on Windows Server 2019) which are in different sub-networks. So with ...
0
votes
1answer
23 views

Install old Cumulative Update SQL Server 2016 Standard

We were running on SQL Server 2016 Standard Edition 2016 SP2-CU4 on our ALWAYS-ON HA cluster. Due to some recent issues one of our nodes got corrupted and we had to replace it with a new node. Since ...
0
votes
2answers
29 views

Grant an AD Group permission to edit all MSSQL Agent jobs

We have a team of people, we all log in MSSQL Server from SSMS using our Active Directory accounts, by Windows Authentication. Our AD accounts are added to an AD group, which is used to grant all ...
13
votes
2answers
733 views

Unclear update conflict

I have two questions: 1. Why do I get update conflict in this situation instead of just blocking: -- prepare drop database if exists [TestSI]; go create database [TestSI]; go alter database [TestSI] ...
1
vote
2answers
61 views

MSSQL TempDb full

I have a problem with my MSSQL cluster. When switching to SQL Server 2016 (from 2014) an error occurs with the tempdb, which is full after a certain time for no apparent reason. Is there a proven way ...
1
vote
1answer
38 views

sp_send_dbmail: Failing When Adding Attachment

I have a simple script that should send an attachment using send_dbmail. It works when I exclude the attachment or send a very small file, e.g. 5KB. The prod file I want to send is 280KB. Exec ...
-2
votes
1answer
34 views

Is Buffer Pool Extension (BPE) needed if my database is already on SSD?

Here is my simplified scenario, let's say I have one (and only one) database of 500GB on SQL Server 2016 EE, this database is on SSD drives already (both data and log files), and my server has 64GB ...
1
vote
1answer
24 views

Why did full backup file size grow after data movement for Availability Group replica was suspended?

Found an error log informing that data movement to a secondary availability replica was suspended. I'm not sure whether the cause of the suspension is relevant to answering my question, but the cause ...
0
votes
1answer
27 views

Receiving “Query processor could not produce a query plan because of the hints defined in this query” when using FORCESEEK but can use FORCESCAN?

I have a fairly large table (500 million rows, 30 columns wide, about 130 GB of data total). One of the columns in that table is a DateTime data type, and I need to repeatedly select records from that ...
0
votes
0answers
32 views

Why is there no implicit conversion when using FORMAT vs CAST/Convert in CONCAT [duplicate]

When we have the following table: CREATE TABLE Test1 ( Name NVARCHAR(50) NOT NULL , Code SMALLINT NOT NULL ) With only 1 row for demo purpose: INSERT INTO Test1(Name, Code) VALUES ( 'ABC', 1)...
0
votes
1answer
31 views

Updating SQL Server 2016SP2 to latest CU in Cluster…what happens? [duplicate]

Updating to latest CU in a cluster with a bunch of databases. Can I do this on the passive node without SQL Server kicking on? Do I need to worry about that? I think I can run it on the passive, ...
-1
votes
1answer
28 views

Power shell script for failover from primary to DR

SQL server version is SQL Version 2016 Current setup is two nodes in primary having always on availability group. Adding a 3rd node which is DR(Disaster and Recover). Does anyone knows a power shell ...
0
votes
0answers
28 views

Combine multiple alerts in to single alert

I'm trying to find a way to generate a single alert by combining multiple alerts for AlwaysOn setup (for example error codes- 1480 , 19406) which gets logged in SQLERRORLOG file during planned or ...
0
votes
1answer
23 views

After Insert Trigger WIth MS SQL Server

I think I have all my bases covered but wanted to ask someone who knows better than I. This is my syntax. UPDATE SI SET autoInsert = 0 FROM inserted WHERE SI.autoInsert = SI.autoInsert OR SI....
0
votes
2answers
43 views

How Are SQL Server Clients Connecting?

I am asking two questions in this post. If I see activity in netstat with port 1433 for an unnamed instance, whose tcp addresses are not enabled, what does that mean? What is the preferred way to ...
-2
votes
1answer
46 views

Multiple condition case when query

Edit: I figured out the code. I decided to go a totaly different way with it. IF the software is comptible I make the background green, if not compatible I made the background red. Here is a sample ...
2
votes
1answer
42 views

fulltext_stoplists to database_principals issue

When I run a DBCC check against the database, it informs me that there is an issue with the sys.fulltext_stoplists object. It appears that the principal_id in stoplists does not have a relationship ...
2
votes
1answer
29 views

Is it possible to expose the max depth of the B-Tree behind a rowstore index or a way to see how many nodes were traversed for a particular query?

I'm personally interested in data structures and with knowing that rowstore indexes are stored with a B-Tree behind them, I'm always curious in trying to visualize what those B-Trees look like for a ...
-1
votes
1answer
45 views

How do you convert a cursor to CTE? T-SQL

In an effort to clarify I have added more information and removed the "simplified" code. I have table with datetime punch information. CREATE TABLE [dbo].[AT_Punches]( [PunchID] [int] ...
1
vote
1answer
25 views

I am looking for a query to determine when the snapshot has been finished applying on the subscriber

Is there any way to determine with a query when the replication has finished applying the snapshot on the subscriber? I have a transnational pull replication set up for reporting purposes and it also ...
4
votes
1answer
267 views

SQL rolling back all iterations of a while loop when each iteration has its own explicit transaction

For the past few days I've been trying to write a query for an automated process that will delete records from a log table within a specified date range (a month) to another table, so they can then be ...
0
votes
1answer
16 views

Find Unique Index that does not have Unique Constraint

I need to find list of unique indices that does not have unique constraint on that column. How to achieve this? I know, following query will give me unique constraints. SELECT * FROM ...
0
votes
1answer
13 views

SQL Sever Transactional Replication on DB with deadlocks and blocking issues

Is it feasible to try and start Transactional Replication on a SQL Server DB with deadlocks and blocking issues. Are there any other options for a close to hot backup?
1
vote
0answers
39 views

Badly-formed histogram causes bad estimates on Nested Loop

We have a query on a SQL Server 2016 Enterprise where the Query Optimizer estimates that only 1 row would come out of the Nested Loop operator, in reality 104989 rows came back. This caused that the ...
1
vote
1answer
47 views

High CPU Usage on certain query when changing DB Compatibility Level

We migrated from SQL 2008R2 to SQL 2016 a few years ago and have been running on Compatibility Level 100 since that point. We are looking to make use of memory optimization introduced in SQL 2016 and ...
1
vote
1answer
21 views

Add Total Row To PIVOT Query

I want to add a TOTAL monthly row to my query, below is my DDL, how can I have an additional row, under the last employeename that is labeled TOTAL and it shows the SUM() of the sales for ALL ...
0
votes
0answers
11 views

SQL Server Audit Records Non-Committed Deletes

I have a table with a trigger as follows: CREATE TABLE AuditTable ( RecordTime DATETIME, RecordDetail NVARCHAR(100) ); -- trigger to block deletes and updates CREATE TRIGGER ...
-1
votes
3answers
30 views

Get values from table if this or that applies, but not when both apply

I'm currently struggling with a seemingly simple task. The goal is to get the corresponding prices according to the customer number - OR - the customer price group if the customer number does not ...
0
votes
0answers
15 views

Does SQL Server supports both encrypted and unencrypted connections at the same time?

Is it possible to have encrypted and unencrypted connections on the same port (1433)? If so, how?
0
votes
1answer
11 views

Can't load reportserver in browser

We are running SQL Server 2016 and cannot view the list of reports in the browser when we go to http://servername/reportserver. When we go to that link in the browser, it asks for credentials. When I'...
1
vote
0answers
42 views

SQL Query fails when content of a single parameter change

Im very puzzled about this issue.. I have a SQL Server database with many records and it is used by my website. When the website user searches for a specific record, the SQL command gets a timeout. ...
0
votes
1answer
26 views

SQL Server Slowest Query is NULL

I am looking at both the SQL Server expensive queries report and the query below, but both are showing this mysterious NULL query as the slowest query on my server. Is there any way I can find out ...

1
2 3 4 5
46