Questions tagged [sql-server-2016]

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

441 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
7
votes
0answers
190 views

CMEMTHREAD waits during Index Optimisation in Availability Group

In a 3 node Availability group, a secondary replica will often become subject to redo lag due to the causes covered in Microsoft's documentation: Troubleshoot: Changes on the Primary Replica are not ...
7
votes
0answers
275 views

SQL Server 2016 & 2017 Force Plan failing with reason 8695

I'm trying to force a plan using query store but it keeps failing with reason 8695 according to data in SYS.QUERY_STORE_PLAN the last_force_failure_reason_desc is 'General_Failure'. Does anyone know ...
5
votes
1answer
418 views

Reinitialize Table Values in SQL SSDT Unit Testing

I am creating SQL Server Unit Tests. We are testing various stored procedures. In Unit testing principles, it is good practice to setup a small database table, populate values, and tear down (...
5
votes
0answers
2k views

SQL Server 2016 InitializeExternalUserGroupSid failed message on startup

I have a SQL Server 2016 installation that I inherited. It does not use or have R or Machine Learning configured. When it starts, I see these messages in the log: InitializeExternalUserGroupSid ...
5
votes
1answer
3k views

SQL SERVER 2016 high CPU

I have a brand new server with SQL Server 2016. It has 24 CPU, about 80go of RAM. The thing is, sometimes, the CPU gets pretty high (> 70%) with no specific reason at all. If I look at the 'execute ...
4
votes
0answers
294 views

Moving to version 2016 from 2008 and Breaking Change Alert from Upgrade Advisor

DBMS: SQL Server Upgrading from version 2008 to 2016. I have this part of the code that is being flagged by the Upgrade adviser as Breaking Change. Error: uses a constant expression which are ...
4
votes
0answers
279 views

Online index rebuild higher fragmentation on intermediate level

I need to rebuild some big indexes and I'm doing some tests with the various options (sort_in_tempdb, maxdop, online) of the ALTER INDEX statement on an test index with 4 levels and 800000 pages on ...
4
votes
0answers
2k views

A row read and locked with UPDLOCK, ROWLOCK, READPAST is not subsequently available

For many years we had a simple queue system, where a regularly executed loop would select and lock a next available row and call a stored procedure to actually process it, which, in turn would also ...
4
votes
0answers
616 views

SQL 2016 Clustered Install - SlipStream - Updates not Shown

Attempting to install SQL 2016 with the latest SP and CU. Running the following (from the directory with the Install exe): setup.exe /Action= InstallFailoverCluster /UpdateEnabled=TRUE /UpdateSource=...
4
votes
0answers
843 views

Always On DTC Support on new database?

Is it possible in SQL Server 2016 Enterprise to add a new database to an Always On availability group and have it enabled for DTC support without recreating the AG? Our Availability Group is already ...
4
votes
0answers
1k views

SQL Server 2016 Database Full Text Catalog not being updated

I have a database with a Full Text Catalog enabled, however the Catalog hasn't updated since about 4 weeks ago. There are no errors in the event logs, the SQL Server logs or the last SQLFT log file, ...
4
votes
0answers
754 views

Extended Events - sqlserver.process_login_finish Not Firing?

We're trying to see whether a client is able to reach a database server, as it's receiving error: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. The error ...
4
votes
0answers
348 views

Should Full Text Filter Daemon Service be a cluster resource?

Our newly installed and configures Sql Server Always On Availability Failover Cluster with Sql 2016 Enterprise Edition with four instances and six nodes. It includes the DB Engine Service and the ...
4
votes
1answer
952 views

SSMS Cannot show the editor for this task (Notify Operator Task)

I'm getting an error message whenever I double click on a Notify Operator Task in our Maintenance Plans in SSMS, which says "Cannot show the editor for this task." Property IsMailHost is not ...
3
votes
0answers
54 views

Why recovery took longer time even though analysis phase is not that long

We are doing Volume Snapshot restoration from Production to Warm Stand-By Server on daily basis. Daily it takes around 10 mins for DB to recover. But yesterday it took 44 mins to recover. I am ...
3
votes
1answer
175 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 ...
3
votes
2answers
114 views

SQL Server 2017 Distributed Availability Groups

My Environment is SQL Server 2017 Distributed Availability Groups, details as follows: Configured SQL Distributed AG from DC1 to DC2. DC1 (Production): TEMPDB1 AND TEMPDB2 (HOSTED ON WSFC1) DC2 (DR)...
3
votes
1answer
130 views

SQL query with Like operator with Leading Wildcards

I have a query as follows: select top (30000) * from table1 where licenseplate like '%as43bv%' I have an index on licenseplate but the query will not hit the index due to leading wildcard. Since ...
3
votes
0answers
233 views

Brand new SQL Server 2016 (SP2/CU1), has high CPU utilization with no mounted databases when the box experiences memory pressure

Brand new SQL Server 2016 (SP2/CU1), has high CPU utilization with no mounted databases when the box experiences memory pressure. I have a test box for nightly builds with a brand new SQL Server 2016 ...
3
votes
0answers
60 views

Moving from dynamic disk allocation to direct space disk via storage pool in SQL server

Pardon me if I am not clear in asking the question since I have very less understanding from storage terms used or how it's used with Sql server. Currently our storage team is testing to move disk ...
3
votes
0answers
399 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. ...
3
votes
0answers
230 views

Best Way to Index Multiple Effective Date Columns

I've got a table that is primarily to represent relationships between entities (i.e. mostly composed of foreign keys). These relationships change over time, and so the table has a StartDate and an ...
3
votes
3answers
290 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....
3
votes
1answer
2k views

SQL Server 2016 Linked Server OPENQUERY Hangs

I've been trying to figure out this linked server problem for about a week now with no luck. The set up I have is a linked server from an instance of SQL SERVER 2016 to an instance of SQL SERVER 2016 ...
3
votes
0answers
423 views

Query store is slow/unresponsive

When opening the query store for the first time on any give day, it will take a VERY long time to output data. I took a SQL script from this redgate blog article : Top 10 most expensive queries ...
3
votes
0answers
132 views

User defined stored procedure - where's the execution plan?

SQL Server 2016, Enterprise Edition in Windows Is there anyway to display the execution plan of a stored procedure created in C# with Visual Studio Data Tools? This is a question that someone with ...
3
votes
0answers
1k views

There is insufficient system memory in resource pool 'default'

I'm running SQL Server 2016 and I'm getting this error: There is insufficient system memory in resource pool 'default' ...when running batch queries with millions of rows. I decreased the batch ...
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 ...
3
votes
0answers
113 views

SQL Server 2016 install configuration domain account not added to SeManageVolumePrivilege

I've installed several SQL Server 2016 SP1 servers with the use of a configuration file but I noticed that the service account is not added to 'Perform Volume Maintenace Tasks'. Instead the user 'NT ...
3
votes
1answer
624 views

SQL Server 2016 very slow snapshot generation

We've faced such a problem after migrating from SQL Server 2008 to SQL Server 2016: there's a merge replication implemented and it takes up to 2 hours (!) to generate a snapshot, instead of 5 minutes ...
3
votes
0answers
77 views

Workout change operation with temporal table records

I have just started using using temporal tables/system versioning, its very easy to setup and get going in terms of querying the data. The one thing missing for me is storing the change operation (...
3
votes
1answer
223 views

Upgrade and migrate SQL Server 2014 AlwaysOn AGs to SQL Server 2016, using the existing WSFC name

Here are the components: WSFC Name: SQLPROD1 Existing Nodes in this "cluster": SQL2014OnSite and SQL2014OffSite New Nodes to be added (?) to SQLPROD1: SQL2016OnSite and SQL2016OffSite All 4 servers ...
3
votes
0answers
411 views

An Indexed View slows updates to a crawl. Is there a hint to work around it?

I experimented with creating an Indexed View. I finally had a case which (almost) met the criteria for one. All inner joins, very few non-deterministic functions (which were moved to an outer view) ...
3
votes
1answer
91 views

Determine minimal permissions required for a given SQL script or session

For security reasons I need to determine minimal permissions required for a given script or session without having to manually audit activity then work it out by hand. I'm assuming this can be done ...
3
votes
1answer
2k views

Is it possible to use cross database queries in both Azure and SQL Server 2016?

I'm working on a project that is designed to run on Microsoft's Azure platform. At present we have a few developers working locally on their own machines with the intent of being able to publish the ...
3
votes
2answers
335 views

Sql Server 2016 Counter Reporting Zero

I utilize the resource governor on my 2016 Sql Server. In prior 2016 sql server versions, this query: select * from master.sys.dm_os_performance_counters opc where opc.object_name = 'SQLServer:...
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). ...
2
votes
2answers
63 views

Storing data files in separate physical drives

Does storing sql server data files for 1 database in different drives gain performance? Is it recommended to do so? Any benefits gained from splitting the data files to multiple physical drives? There ...
2
votes
0answers
40 views

Why is it faster to SELECT my logical CASE field into a new temp table as opposed to UPDATing the original temp table to SET the field?

I have a pretty large temp table (about 4.5 million rows) with no indexes, so it's stored in a heap. Let's call it #TempTable1. #TempTable1 has a BIT field called ToInclude which is set in a ...
2
votes
0answers
315 views

Missing log block for availability database in SQL Server 2016

I've recently begun receiving alerts like this one: Always On Availability Groups transport has detected a missing log block for availability database "myDb". LSN of last applied log block is (xxxx:...
2
votes
0answers
226 views

Will “Stack Signature for the dump…” appear in the SQL Server error log every time there is a memory dump?

I am currently monitoring sql server memory dumps as they appear in the error log. All I really need to know is if this occurred, I can find out the details another way. Right now my logic is that if ...
2
votes
0answers
171 views

SQL Server 2016 SP2 CU9 - WFC Failover failing

I have updated my SQL Server 2016 on one of my Windows Failover Cluster nodes successfully. When I attempted to failover one of my SQL instances onto the newly patched node, SQL Server service fails ...
2
votes
0answers
35 views

How can I quickly update cache expiration schedules for 100s of datasets?

I have over 100 different datasets on an ssrs-2016 site. Each one of those datasets needs a new caching schedule. I have created a shared schedule to apply to each dataset, but I am wondering if there ...
2
votes
1answer
212 views

Secondary Database Replica in Reverting/InRecovery (Long time)

Reposted from: https://www.sqlservercentral.com/forums/topic/secondary-database-replica-in-reverting-inrecovery-long-time Hi, One of my AG secondary replica has been in reverting/in recovery for ...
2
votes
0answers
93 views

BulkAdmin Role Doesn't Work

There is a query that our BI team use. They can not execute the query because of the error below. Ad hoc access to ole db provider 'MSDASQL' has been denied. You must access this provider ...
2
votes
1answer
102 views

What is the difference between sys.dm_hadr_ tables and sys.availability_groups / sys.availability_replicas?

Browsing around some sys tables, I see: sys.dm_hadr_availability_replica_cluster_nodes.group_name = sys.availability_groups.name and sys.dm_hadr_availability_replica_cluster_nodes....
2
votes
0answers
105 views

Where can I find the reason why TDE encryption Scan aborted?

When encrypting large database > 10TB . I had to issue --alter database set encryption on several times , but the encryption starts and fails with the same message in the error log. where can i find ...
2
votes
0answers
87 views

View with where clause slower than query with same where clause

This query with periods (PeriodStart,PeriodEnd,ticker,FundamentalId,dimension) as (select b.reportdate PeriodStart, MIN(a.reportdate) PeriodEnd,b.ticker, b.FundamentalId,b.dimension from Base a ...
2
votes
0answers
64 views

Paralleled SSIS package flatlining at 20% CPU

I'm a DBA and my developers are wanting to aggregate data as quickly as possible. They have run the 3 main executions within SSIS to run in parallel but rather than make the CPU hurt for a shorter ...
2
votes
1answer
128 views

SSIS Merge Join: Delete does not work

I've set up a Data Flow Task to sync a flat file with its destination table using a Merge Join with a Left Outer Join as described here (and elsewhere). Merge Join: Inserting and Updating works fine,...

1
2 3 4 5
9