Questions tagged [sql-server-2016]

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

429 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
7
votes
0answers
854 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 ...
5
votes
1answer
513 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
1answer
359 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 ...
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
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 ...
4
votes
1answer
833 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. ...
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
637 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
885 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
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
0answers
55 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
0answers
550 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 ...
3
votes
0answers
542 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
86 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
1answer
3k 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
898 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 ...
3
votes
0answers
144 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
316 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://docs.microsoft.com/en-us/sql/relational-...
3
votes
0answers
2k 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
0answers
128 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
0answers
80 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
238 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
526 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
104 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
114 views

How to reverse only English words?

I've an nvarchar column with English and Hebrew letters in some fields. In the fields where the English and Hebrew are together, the English letters are in reverse order (this is how the ERP program ...
2
votes
0answers
49 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-...
2
votes
1answer
49 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 ...
2
votes
2answers
176 views

SQL server AlwaysOn alternate option for readintent=true

I know applications need certain(newest) version of ODBC drivers to connect an AlwaysOn SQL instance replica and to use the option "applicationIntent= readonly" Now my question is , if some ...
2
votes
1answer
69 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
0answers
43 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
681 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
215 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
36 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
613 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
2answers
291 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 ...
2
votes
0answers
143 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
142 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
221 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
133 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
94 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
0answers
43 views

TEMP Database is not showing Encrypted in TDE in SQL Server 2008R2

As per my knowledge, when we encrypt any user database in SQL Server through TDE, the TEMP database automatically gets encrypted. I have done TDE in SQL Server 2016, and when I check through SELECT ...
2
votes
0answers
502 views

Stored Procedure: How remove Comments, spaces, and extra lines, or companies binaries?

We are trying to compare stored procedure codes. Example, these two stored procedure codes produce same result. (1) Is there method to convert stored procedures into binaries to validate they ...
2
votes
0answers
249 views

Aggressive Tempdb spill process

We are having an issue with aggressive tempdb spills that significantly effect overall server performance and, in rare cases, cause AlwaysOn failures. Tested on SQL2016 and SQL2017 Enterprise (recent ...
2
votes
0answers
68 views

Data Warehouse Kimball Interest Rate and Interest Payment Amount

I am modelling a bank account. Should interest rate be in the DimCheckingAccountId (which shows ProductDimensions, or should it be in fact table, or both). Same with another LoanAccount, should ...
2
votes
0answers
188 views

is there any advantage when troubleshooting service broker, to create the queue with retention=ON?

from CREATE QUEUE (Transact-SQL): RETENTION Specifies the retention setting for the queue. If RETENTION = ON, all messages sent or received on conversations that use this queue are retained in ...
2
votes
0answers
210 views

Foreign Key Error with Transactional Replication from Backup

I'm getting a missing foreign key error when I begin transactional replication. Steps taken: Created a pull publication (live server) Created a copy only full backup Restored backup on reporting ...
2
votes
0answers
932 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 ...
2
votes
0answers
105 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 ...
2
votes
0answers
695 views

SQL Buffer Pool and Plan cache

Does SQL Buffer Pool size = Max server memory.? I have been trying to find out the reason why plan cache in my SQL server 2016 gets cleared every few hours. I have 128 GB Physical Memory. Earlier i ...

1
2 3 4 5
9