Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [sql-server-2016]

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

460 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
9 votes
0 answers
1k 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 ...
Riley Major's user avatar
  • 1,903
5 votes
1 answer
623 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 (...
user avatar
5 votes
0 answers
3k 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 ...
Mark Freeman's user avatar
  • 2,181
5 votes
0 answers
754 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) ...
Yishai's user avatar
  • 369
5 votes
1 answer
4k 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 ...
Guillaume Philipp's user avatar
4 votes
0 answers
149 views

Log Backup occasionally blocked by automated update

We have a canary table in each of our databases that our automation updates once a minute. The automation is a sql server agent job that utilizes a cursor to iterate over the databases and then ...
DForck42's user avatar
  • 3,048
4 votes
1 answer
2k 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. ...
that it guy's user avatar
4 votes
0 answers
3k 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 ...
GSerg's user avatar
  • 1,352
4 votes
0 answers
678 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=...
Some Guy's user avatar
4 votes
0 answers
942 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 ...
Jaron's user avatar
  • 41
4 votes
0 answers
2k 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, ...
Carl's user avatar
  • 141
4 votes
1 answer
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 ...
user avatar
3 votes
0 answers
179 views

Why am I getting RESOURCE_SEMAPHORE timeouts when there should be plenty of memory available for grants?

We occasionally have queries time out due to RESOURCE_SEMAPHORE waits but there appears to be plenty of available memory for memory grants. For example, I'm able to see timeouts by executing two ...
Joe Obbish's user avatar
  • 32.2k
3 votes
0 answers
182 views

Evaluating Success of CTFP Value Change

We have recently made a change to the CTFP setting on our test server with a view to rolling this out to live. Our current CTFP is 15 so we tried setting it to 50 which is often seen as a better value....
SE1986's user avatar
  • 1,980
3 votes
0 answers
1k views

Lined Server - Cannot create an instance of OLE DB provider "IBMDADB2.DB2COPY2" for linked server "MYDB2"

I would like to create Linked Server from SQL Server 2016 on Windows to Db2 v11.5.7 on Linux/x86. I created linked Server using Microsoft OLE DB Provider for DB2 and it works fine. Now I would like to ...
folow's user avatar
  • 339
3 votes
1 answer
488 views

TransactionLog not getting truncated in AlwaysOn Cluster

Folks, stuck with a wierd transaction log truncation(reuse) problem in an AlwaysOn cluster and running out of options, the transaction log is not getting truncated and keeps growing. Only one database ...
Amam's user avatar
  • 389
3 votes
1 answer
384 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). ...
Noah's user avatar
  • 73
3 votes
0 answers
66 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 ...
sachin-SQLServernewbiee's user avatar
3 votes
0 answers
2k 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:...
GMassDBA's user avatar
  • 335
3 votes
0 answers
1k 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 ...
datadawg2000's user avatar
3 votes
0 answers
773 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 ...
Chris Holt's user avatar
3 votes
2 answers
819 views

If the SIDs for ##certificate-based## SQL Server Logins don't match across replicas in an Availability Group, will this be a problem on failover?

I am reading mixed messages about server level object syncing between replicas in an Availability Group. As I understand it, server-level objects must be kept in sync (aside: then why isn't it part ...
youcantryreachingme's user avatar
3 votes
0 answers
157 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 ...
BeginnerDBA's user avatar
  • 2,170
3 votes
0 answers
558 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 ...
yegnasew's user avatar
  • 487
3 votes
1 answer
5k 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 ...
maitland's user avatar
3 votes
0 answers
2k views

INSERT-SELECT Statement occasionally fails silently and works upon re-run

We have a batch import process that BULK INSERTs raw data into staging tables, does some data mapping, and then pushes it to another database for validation. The procedure where my issue occurs is ...
Haris Khan's user avatar
3 votes
0 answers
251 views

Query store works in dev, but not production in Amazon RDS

I'm having an issue getting query store to collect and record data on our production workload, however, it works as expected on our non-production instances (development and staging). The only ...
Julie S's user avatar
  • 31
3 votes
0 answers
1k views

Specified initialization vector (IV) does not match the block size for this algorithm

I built an SSIS project on "test" server, and deployed the project across to "live" server. The project has property ProtectionLevel set to EncryptSensitiveWithPassword. Everything deployed ...
DimUser's user avatar
  • 382
3 votes
0 answers
150 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 ...
Craig Efrein's user avatar
  • 9,640
3 votes
0 answers
377 views

What are the intended use cases for Remote Blob Storage (RBS)?

Microsoft's two main sources on RBS do not mention SharePoint explicitly: https://technet.microsoft.com/en-us/library/gg316768(v=sql.105).aspx https://learn.microsoft.com/en-us/sql/relational-...
mathewb's user avatar
  • 1,144
3 votes
0 answers
588 views

SSRS 2016 Generates SQL Server log entry daily

First, a bit about our environment. We run two instances of SQL Server 2016 Enterprise (13.0.4435.0) on Windows 2016 Servers. To save money, we run SSRS 2016 alongside SQL Server on both servers We ...
ChrisK's user avatar
  • 43
3 votes
0 answers
3k 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 ...
DarthVegan's user avatar
3 votes
0 answers
152 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 ...
Frederik Vanderhaegen's user avatar
3 votes
0 answers
94 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 (...
gdp's user avatar
  • 131
3 votes
1 answer
268 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 ...
SQL_Hacker's user avatar
3 votes
1 answer
216 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 ...
Schneider's user avatar
  • 131
2 votes
1 answer
88 views

SQL Server 2016 Deadlock

I want to validate my understanding of the sqlserver deadlock report: context: parent_table(id); child_table(parent_id, col1, col2, ...); constraint primary_key(id) on parent_table; constraint ...
brownsugarpearl's user avatar
2 votes
0 answers
55 views

Views that were created in SQL Server 2008 R2 are encountering compatibility issues when re-created in SQL Server 2016 with identical definitions

We've created a view in 2008 R2. Provided below are the definitions for both the base table and the view. Base table: CREATE TABLE dbo.test( fcrecid char(25) NOT NULL, fctype char(1) NOT NULL, ...
Yashas's user avatar
  • 21
2 votes
0 answers
305 views

What to do when can't find Microsoft SQL Server error explanation

Following this doc View or Change the Default Locations for Data and Log Files for SQL Server 2016. I am attempting a simple change of location for data/log/backup files in MSSM. Connect in Object ...
Dave's user avatar
  • 121
2 votes
0 answers
80 views

How to enable Database Auditing on SQL server

I want to audit SQL database activities, but when I am trying to enable Database Level Adut specification, it is capturing all activities that is done from application side and also from queries that ...
Gizaw Simeon's user avatar
2 votes
1 answer
223 views

Isolation Level Conflict

Hi i was asked to look at a third party app recently. There were a few queries doing giant reads and that was easy enough to fix. However, while i was working on the queries i kept seeing different ...
Simon Bradley's user avatar
2 votes
0 answers
202 views

How to determine the Tipping Point of which Physical Join Operator gets used to process a query?

I've been noticing some strange occurrences lately on my development SQL Server instance where the Nested Loops Physical Join Operator is used when a Hash Join would be much more efficient, within a ...
J.D.'s user avatar
  • 37.9k
2 votes
0 answers
156 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-...
Craig Efrein's user avatar
  • 9,640
2 votes
0 answers
289 views

High CPU on Unloaded Server -- Performance Monitor

We recently upgraded a very lightly used SQL server (running < 5% CPU 99% of the time) from 2012 to 2016. In conjunction, we moved the server from an AWS t2 to a t3 instance and installed the new ...
KirkMan's user avatar
  • 21
2 votes
1 answer
198 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 ...
Andrzej Błoch's user avatar
2 votes
0 answers
50 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 ...
J.D.'s user avatar
  • 37.9k
2 votes
0 answers
346 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 ...
dajonx's user avatar
  • 91
2 votes
0 answers
46 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 ...
Sethen's user avatar
  • 121
2 votes
1 answer
1k 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 ...
dajonx's user avatar
  • 91
2 votes
0 answers
307 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 ...
Furkan's user avatar
  • 37

1
2 3 4 5
10