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
14 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
10 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
33 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
44 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
13 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
9 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
11 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
10 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
41 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
23 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 ...
0
votes
2answers
23 views

Default trace vs auditing in SQL Server

Currently I am working in an environment that has the default trace enabled and I would like to propose that we add SQL server auditing to our environment. I'm looking for some points to put forward ...
0
votes
0answers
6 views

Log Shipping Monitor Server Miss match

I'm receiving an LSAlert that my log ship database hasn't been updated. During digging I found that the log_shipping_primary_secondaries references the proper name of the secondary server, while ...
2
votes
1answer
104 views

Can adding a columnstore index to a table affect read performance of a query that uses a rowstore index on the same table?

I'm doing some testing of columnstore indexing on a single table that has about 500 million rows. The performance gains on aggregate queries have been awesome (a query that previously took about 2 ...
0
votes
0answers
26 views

Index Spool (Eager Spool), Difference in number of row before and after the operator

I was optimizing one of the Stored Procedure for which I went through the execution plan of it. In the execution plan I see the operator Index Spool (Eager Spool). There I see it is taking 1759 rows ...
0
votes
1answer
61 views

why are estimated rows exponentially increasing with each join?

summary We have an Object-Oriented database schema that we query with Hibernate with: 1 parent table (NAL_Actions) 7 child tables (NAL_*) If I want to query a couple of these "action"-...
0
votes
1answer
34 views

How often does SQL Server refresh group policy?

Does SQL Server check against the Windows GPO on the local server it is running on or with the domain controller? Does SQL Server update the GPO at its own interval or immediately when the server has ...
2
votes
1answer
66 views

How to implement High Availability for all nodes in a sharded database?

I'm doing some research into implementing sharding for one of my large (~2 TB) production databases. Currently the database has HADR via a multi-replica AlwaysOn Availability Group. Since sharding a ...
0
votes
0answers
9 views

How to optimize full-text search stored procedure

Our beta database (same server) data is 1 month behind live. However we did not much changes. What's wrong is query time of stored procedure. Beta has no issue and return results in 2 sec top. Live ...
0
votes
1answer
55 views

SQL Server 2016 - Availability Groups - How To Copy Permissions To Secondary Node [duplicate]

SQL Server 2016 SSMS v18.4 I want to create the same logins and give the same privileges on both node. In primary node I have no problem about this operation. But when I create the same login on ...
2
votes
1answer
29 views

Should we manually failover again if the node which was originally a primary role and went down recovered?

We have an SQL Server 2016 standard edition with Basic Availability Groups(BAG) setup between dbserver1 and dbserver2. This is our first time to implement the BAG and wanted to know what to do in a ...
0
votes
0answers
56 views

Stored procedure runs slow on SQL Server 2016 compared to SQL Server 2019

The stored procedure is running very slow on SQL Server 2016 as compared to 2019. It's doing some common validation from a temp table and inserting data into primary tables and mapping tables. The ...
0
votes
2answers
42 views

I can not navigate through the objects in sql server managemente studio - lock never goes away

Whenever I try to navigate through the objects in sql server management studio object explorer I get the error message Lock request time out period exceeded. (Microsoft SQL Server, error: 1222). It ...
0
votes
2answers
65 views

stored procedure case statement

I am building this where condition by concatenating the @whereClause variable in my stored procedure. At this point I am using the case to add additional values. if there a better way to do this? SET @...
0
votes
1answer
24 views

Bad estimates when sorting, good estimates without sort

I'm struggling with the tuning of one of the queries on our production SQL Server 2016 SP2. I needed to anonymize the query due to security policies. The query is like this: SELECT TOP 1000 Object1....
0
votes
0answers
26 views

Cannot not connect to Availability Group Listener

I have a HADR cluster running on Windows 2012 R2 and SQL Server 2016 with Availability Groups. The services were running under a high level domain account. Due to internal audits, I have been asked to ...
1
vote
0answers
28 views

Naming Convention for SQL AD Group Logins (Potential Bug)

My team is attempting to come up with a new naming standard for our Active Directory groups within SQL Server to help manage permissions. DOMAIN\ENVIRONMENT_DATABASE_FUNCTION EXAMPLES: TEST\...
0
votes
1answer
47 views

Backup database on linked server

I am trying to backup a db on ServerA via ServerB. I have: DECLARE @SQL NVARCHAR(100) SET @SQL = 'BACKUP DATABASE DBname to disk=''\\ServerB\E$\folder\DBname.bak''' EXECUTE ServerA.master.dbo....
0
votes
1answer
24 views

How can I compare row values as column name to the another table and Get data (Sql Server-2017)?

CREATE TABLE Table1( ID int NOT NULL, PTA_1 int NOT NULL, PTA_2 int NOT NULL, PTA_3 int NOT NULL, PTA_4 int NOT NULL, PTA_5 int NOT NULL, DTA_1 int NOT NULL, DTA_2 int NOT NULL, DTA_3 int NOT NULL, ...
0
votes
0answers
24 views

SQL Server 2016 Replication Error while Configuring Diistributor

I am trying to configure Distributor for transactional replication set up on SQL Server 2016 Enterprise Edition. But I get the following error: SQL Server could not configure 'Server01' as a ...
1
vote
2answers
57 views

Why a backup created on SQL Server 2016 is significantly larger than one created on SQL Server 2012?

I've backed up a database on SQL Server 2012 that resulted in a file of 33MB in size. Then, I restored that backup on SQL Server 2016 and created another backup, but this time on SQL Server 2016. The ...
4
votes
1answer
154 views

What steps can I take to determine if my server is under-provisioned for memory?

My server is running SQL Server 2016. The environment is fairly high workload with a lot of write transactions and reading of the data throughout the day. I have a hunch the server isn't provisioned ...
1
vote
0answers
75 views

Database restore time increased after we changed from SQL Server 2014 to SQL Server 2016

I have a 2 TB database (200 GB backup) that I am restoring every night on a secondary server of a AlwaysOn group, to erase confidential information and then backup the clean database and send it to ...
0
votes
2answers
69 views

DBCC Shrinkfile not working on SQL 2016

Executed the DBCC Shrinkfile('file1',emptyfile) successfully on first file in the filegroup and removed the file that I shrank. Executed the DBCC ShrinkFile('file2',emptyfile) unsuccessfully on file ...
1
vote
1answer
31 views

Will this trigger always complete before returning results of Insert to client?

I have the following trigger for Auditing. CREATE OR ALTER TRIGGER dbo.TR_MyTrigger ON dbo.Cust AFTER INSERT, UPDATE, DELETE AS BEGIN SET NOCOUNT ON; DECLARE @EntityState AS SMALLINT; ...
1
vote
1answer
56 views

If a rowstore index is stored in a b-tree data structure, what kind of data structure is used for a columnstore index? [duplicate]

My understanding of how regular rowstore indexes are stored is in a type of b-tree data structure, but I was wondering because of the different nature of columnstore indexes, what type of underlying ...
0
votes
1answer
52 views

SQL Server Standard 2016 Service Pack and Cumulative Update [duplicate]

We are currently using SQL Server 2016 Standard Edition with ALWAYS-ON High Availability. We have two different clusters of SQL servers. We need to update it to the latest version of SQL (As per my ...
9
votes
1answer
355 views

Simplifying Function with Recursive CTE and/or Window Function

I'm trying to come up with a Recursive CTE and/or Window Function to create a function. After days, I've boiled the function down to (pseudocode) where I have N and B, and need to generate E: En =...
0
votes
0answers
42 views

To automatically stop/disable transactional replication whenever table get altered

I have set up a transactional replication from multiple Databases (6 Different Server) to 1 consolidated database. The schema is identical. Segregation is done by the column SiteName . My problem is :...
0
votes
2answers
73 views

Vastly different query run-times on different servers [duplicate]

I've got two databases setup on two different SQL Servers. One is 2012 and one is 2016, but aside from that, they have the same CPUs, same core count, and same RAM. They're both VMs in the same ...
2
votes
1answer
136 views

SQL Server - Prevent Clustered Index Scan on a specific table

My database contains a specific table that is quite large (250+M rows, 100+GB data space). Generating a clustered index scan on this table is always a bad idea. This table has multiple indexes and ...
0
votes
1answer
31 views

Specific format/formula when changing column datatype [duplicate]

Can one modify a column's datatype using a very specific conversion format? For example, when converting from datetime to varchar I'd like to use the yyyy-MM-dd hh:mm:ss format, not the system ...
0
votes
0answers
28 views

SQL Server: Cannot connect remotely after changing server name successfully

My local computer's operating system is Windows 10 Professional. We have a remote server named XXXXAD01. Its operating system is Windows Server 2016 Standard. It is part of a Domain ISYYYY.ZZZZZ. ...
0
votes
0answers
9 views

In SQL Server, data returned by sys.dm_os_performance_counters for “SQLServer:Broker Statistics” is not changing over time

In SQL Server [Microsoft SQL Server 2016 (SP2-CU10) (KB4524334) - 13.0.5492.2 (X64)], I'm trying to determine just how much activity our Service Broker queues are processing. One tactic is to use sys....
0
votes
1answer
44 views

Trouble with differential backup - full backup not being recognized

I have recently implemented backups using https://ola.hallengren.com/. Backups EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES, -%Archive', @Directory = N'H:\sqlbackup', @BackupType = '...
1
vote
0answers
25 views

DBCC CHECKTABLE - 2570 error

Running DBCC Checktable on one of my tables causes several errors: Msg 2570, Level 16, State 3, Line 1 errors, Page (1:442), slot 1 in object ID 565577053, index ID 0, partition ID ...
0
votes
1answer
45 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 ...
1
vote
1answer
58 views

Need advice on how to migrate from existing database mirroring to availability groups

Need some practical/general guidance on moving from database mirroring to AOAG Current setup - 2 standalone servers with database mirroring configured as async. Server A: Windows 2016 running SQL ...
1
vote
1answer
29 views

Where Database Alerts & Operators Exist In SQL Server?

My SQL Server database server has crashed. I have system databases backup. I will reinstall the sql server but i don't know how to find my operators, alerts and config settings. Is there a way to find ...
2
votes
1answer
35 views

How to get certificate expiration date for certificate in mirroring endpoint in SQL Server 2016 Always On Availability Group?

I created a domain-independent 2-node SQL Server 2016 Always On Availability Group using the Microsoft instructions: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/...

1
2 3 4 5
46