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
2answers
25 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 ...
5
votes
1answer
124 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
27 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
2answers
72 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
35 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
72 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
61 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
31 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 ...
1
vote
2answers
53 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
73 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
25 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
31 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
31 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
48 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
27 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
27 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
60 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
163 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
79 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
72 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
68 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
367 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
43 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
75 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
155 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
32 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
29 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
10 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
56 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
26 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
89 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
60 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
30 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
39 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
vote
1answer
44 views

SQL Server: calculating memory requirements for Full db backups

In 2018, we inherited a production SQL Server 2012 FCI running on Windows 2012 with 32 GB RAM. SQL Server max server memory was set at 23.6 GB, and things were running fine. However, in 2019, we ...
0
votes
2answers
26 views

How can I track down an issue with a locked table, endless query?

I have a table that is periodically getting locked in a manner that I don't quite understand. I cannot do the following: select * from thetable select count(*) from thetable There are roughly 2,000 ...
0
votes
1answer
22 views

Cost of indexed view versus filtered index?

I have met the following table design for recording references in non-normalized way: RecordGUID uniqueidentifier DestinationEntity uniqueidentifier DestinationRecordGUID ...
0
votes
2answers
19 views

How to check if staff member has end date with a Null value

I have a database that records employees tasks throughout the organisation and when they finish etc., I need to identify if each employee, has at least one row with a null value in either finish. ...
1
vote
2answers
41 views

What are the benefits of using Manual Failover Mode in an AlwaysOn Availability Group instance as opposed to Automatic Failover Mode?

Why wouldn't I want to use Automatic Failover as the Failover Mode for an AlwaysOn Availabiltiy Group instance? Are there pros for Manual Failover Mode that don't exist for Automatic Failover Mode?
0
votes
1answer
33 views

Backup up database to compressed local folder - what will be the performance implication?

I have sql server 2016 web edition on Windows 2016 server VM. I want to backup the database, to a folder on a local drive (SSD) - please don't ask why, long story. Due to the size of the DBs, and ...
0
votes
2answers
78 views

T-SQL Query or DB Engine Optimization? [closed]

This query takes 100 ms on avarage but when you have hundreds or thousends of calls of them every second this would cost a lot. The dataset can not be cached as it may be changed at any second. Any ...
1
vote
1answer
29 views

How do you minimize chance of collision between system-named constraints on temp tables?

We have occasionally hit the error “There is already an object named 'PK__#TempTab__0796211ACE71813B' in the database” when some of our procedures attempt to create temp tables with a system-named PK ...
0
votes
1answer
28 views

SQL Server AlwaysOn Availability Group - Creating an Index Online in SSMS

I am running a 2-node SQL Server 2016 AlwaysOn Availability Group. I would like to create a non-clustered index online in SSMS for one of the availability databases in the AlwaysOn Availability Group....
10
votes
2answers
586 views

Break the link between a Database User and SQL Server Login

When restoring database from once instance to another one sometimes has to relink SQL Server Logins to the Database User. This is normally achieved with one of the following procedures. Deprecated ...
0
votes
1answer
54 views

Determine the source of user permissions in a database

I have a developer that just accidentally ran an UPDATE and ALTER TABLE script he was working on in a production database instead of the development environment. How can I track down what security ...
6
votes
4answers
570 views

How can I configure Ola Hallengren IndexOptimize script to run against a subset of tables

I'm using Ola Hallengren IndexOptimize script against a SQL 2016 database that is 7 TB in size, with over 300,000 tables. I only have a 6 hour window each night to manage indexes. I'm using the ...

1
2
3 4 5
46