Questions tagged [sql-server-2016]

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

420 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
1
vote
0answers
42 views

Can I do a combination of SQL Server Basic availability groups with SQL Replication?

I need to use SQL Server Standard 2016 to keep the costs low but achieve HA. In addition we need to have two Read nodes for reporting purposes. I am wondering if I can use SQL Server Replication to ...
1
vote
0answers
66 views

How can I make this query faster without adding indices?

I have a read-only database, from which I have to report on all our Wuhan virus cases. The execution plan and query can be found here. I can't share the data, since it's health records, but this ...
1
vote
2answers
801 views

What configurations do I need to fix the “unable to begin a distributed transaction” error when trying to run a remote procedure?

This is the error message I'm receiving: Msg 7391, Level 16, State 2, Procedure spStoredProc, Line 62 [Batch Start Line 1] The operation could not be performed because OLE DB provider "SQLNCLI11" ...
1
vote
0answers
70 views

Incorrect Estimates (CE Model 130) on uniqueidentifier columns

We want to change our application from compatibility level 100 to 130. As part of the tests, we came across a curious problem regarding estimates on uniqueidentifier columns. The select is very ...
1
vote
0answers
23 views

SSRS 2016 ReportServer DBs in Basic AG

I have a 2 node cluster on which I need to setup AlwaysOn since it is a standard edition we would be going with Basic AGs. I can easily get the user databases added into the AGs but I am not sure ...
1
vote
0answers
104 views

Online Index Rebuild shows self blocking

I've been doing some testing on rebuilding some larger clustered indexes (with online + sort_in_tempdb on) and I'm observing something that I can't immediately explain. I have a hunch that this is ...
1
vote
0answers
27 views

How to select specific databases in a backup retention policy maintenance plan cleanup task?

I have full backups of three different databases. Right now all of their backups follow the same retention policy, but I need to setup the retention to a different amount of time for each database. ...
1
vote
1answer
51 views

Do filtered indexed views cause contention on the underlying table when rows are added to the table that fall outside the filter?

If I create an indexed view on a table and the query on that table includes a filter, when new records are added to that table that don't fall into that filter (and therefor wouldn't show in the ...
1
vote
0answers
43 views

Does dynamic SQL executed by sp_ExecuteSQL typically take longer to parse and compile, especially as the query complexity increases?

I'm noticing a batch of queries that used to parse and compile pretty instantaneously now take anywhere between 5 to 15 seconds to parse and compile as a dynamic SQL statement that is executed with ...
1
vote
0answers
11 views

Is there a way to provide a client without administrator access additional information when they receive Error 18456?

When a user fails to log in to SQL Server, they receive this error: Login failed for user 'foo'. (Microsoft SQL Server, Error: 18456) On the client side, this error is always State 1. This is ...
1
vote
0answers
1k views

Installation failure with error code 2068119551

I have been making attempts at installing MS SQL Server 2016 SP2 on my Windows 10 physical machine. Installation keeps failing with the following exit code: 2068119551, saying that it "attempted to ...
1
vote
0answers
71 views

SQL Server 2017 Distributed AG's manual failover

We have configured SQL Server 2017 SQL Distributed Always-ON from DC1 to DC2. Can you please review below steps for manual failover from DC1 to DC2 and vice-versa. Please add steps If i missed any ...
1
vote
0answers
47 views

Sequence number for COMMITTED concurrent inserts

For example, I have a table create table Queue (OrderNum int, Data varchar(1000)) --int - may be a different type and many concurrent inserts into this table. Is there is a way to fill the OrderNum ...
1
vote
0answers
41 views

SSMS Connect to remote SQL instance with Windows Authentication

I have a remote SQL instance (Microsoft SQL Server 2016 (SP2-CU3) (KB4458871) - 13.0.5216.0 (X64) I can connect to the instance fine locally on the box through SSMS using both Windows and SQL ...
1
vote
0answers
69 views

If a Login is not granted the db_ddladmin role on a database, but is individually granted CREATE VIEW on it, will they still be denied?

Since SQL Server permissions work as "deny first", I was wondering if that applies downward to individual permissions that are granted, if the parent permission isn't granted e.g. db_ddladmin is not ...
1
vote
0answers
560 views

Error 9003 Log Scan is Invalid Error When Restoring Transaction Log After SQL2016 SP2 + CU8 Upgrade

We are using SQL 2016 Enterprise Edition and have recently applied SP2 + CU8. We were previously on SP1 + CU6. Since the update we are getting the following error when restoring transaction logs on ...
1
vote
0answers
65 views

Syncing On Prem SQL Server with Azure SQL

We have a SQL 2016 instance running production systems at a pretty healthy load within AWS. On top of this we host instances of production databases in Azure for PowerBI and analysis usage. At ...
1
vote
0answers
21 views

Impact of changing server machines names with SQL Server 2016 Standard in mirror state

It's an enterprise network domain. Two existing Windows server 2016 machines, one each for SQL Server 2016 Standard edition and other is also SQL Server 2016 Standard edition in a passive mirror ...
1
vote
0answers
41 views

SQL Server suddenly using a lot of CPU: 300 compilations/s vs 2 batches/s

A instance of SQL Server Express 2016 that I'm in charge of has suddenly started to use a lot more CPU that usual. It's the backend database for a website, but doesn't see all that many queries due to ...
1
vote
0answers
417 views

Recover data from ransomware-encrypted databases

I'm doing some work for an organization that was hit by the RYUK ransomware (a newer variant of HERMES), which has encrypted some large databases (around a half TB each). I don't know if backups are ...
1
vote
0answers
35 views

Read performance of SQL Server data files data from Azure BLOB

I have a setup where there is SQL Server on premises (MS SQL Server 2016 13.0.5366.0) but database files (log and data) are stored in cloud storage account. Setup described here. The interesting ...
1
vote
2answers
937 views

Copy a database to a new schema in an existing database

Just curious if there is a way to create a new schema that is based on an existing database. Assuming the existing database has dbo schema only. I will be taking an entire database and moving it to an ...
1
vote
0answers
67 views

When does the quarantinethreshold counter reset in WSFC cluster?

An outage that occurred on our SQL availability group this morning caused us to look into the quarantine settings on WSFC - something we never needed to configure. Now there seems to only be two ...
1
vote
0answers
24 views

DCM page in Filestream_Data_Filegroup

I'm doing some research about the DCM (Differential Change Map) page in SQL Server 2016. This is what I discovered so far: page type: 16 each DCM page covers a GAM interval each data file has a DCM ...
1
vote
0answers
434 views

Converting show-plan XML to graphical view plan

I am using the performance dashboard to set up baseline monitoring for my inventory of SQL servers. As per the performance dashboard available within SSMS, when i click on any query to view the plan ...
1
vote
0answers
174 views

VS 2019 - Schema Compare - “The certificate chain was issued by an authority that is not trusted” when TrustServerCertificate=True

I'm trying to do a schema compare in VS 2019 with Active Directory Password Authentication. The account I'm using works in SSMS, but fails in VS with the following error, even when ...
1
vote
0answers
42 views

SQL Server 2016 query Azure AD

I need to query our (Azure) AD to get a user username based on on an email address I am being passed and then write it to a table ideally via an SP. From my brief bit of reading, Azure AD supports ...
1
vote
0answers
342 views

How to find out everything that is affected by a master key?

Following up after a situation on an important LIVE server - where I have the master key backed up, but the decryption password for that master key backup file is missing. I can't open the master ...
1
vote
0answers
36 views

Searching for and parsing out specific text parts within the values of a column

I'm working on a CASE expression that I need to have in order to search for, and parse out, specific text values. Sometimes the relevant column will contain a person's name, and sometimes it will ...
1
vote
0answers
29 views

How to handle/separate big database with blobs

We have a specialized business software (transportation) running on SQL Server. The software company decided to store all sorts of files (invoices, scans, pdfs, emails, pictures, ...) that have to be ...
1
vote
0answers
681 views

Database not showing in sys.databases over linked server

I have a query that I can run on my server (Server1) as follows: SELECT * FROM master.sys.databases WHERE name = 'MyDB' This returns a row. I have a linked server setup on Server2 which points to ...
1
vote
0answers
221 views

SQL Linked Server Connection Success but simple query returns Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

I linked a SQL Server in Management Studio and when I test the connection, I do get "the test connection to the linked server succeeded." However, when I try to run a simple query, I get a message ...
1
vote
0answers
200 views

Time Interval Partitioning in SQL Server

I have a table with time stamp (UTC) and for several reasons, like purging from this table (done at later point of ETL), I am trying to find out the feasibility of partitioning the table by 5 minutes ...
1
vote
0answers
38 views

Record the duration of a stored procedure with multiple parameters

Is there a way to automatically record the duration and row count of the results of a stored procedure execution and save the plan? I have a procedure i'm troubleshooting for possible parameter ...
1
vote
0answers
606 views

How do I parse Address into Street Number, Street Name, Type, Unit Number?

How do I parse Address into Street Number, Name, Type, Unit Number? Is there a function which will conduct this? 2280 MEYERS AVE ALDERGROVE AVE & S ...
1
vote
0answers
209 views

Query Store- force plan not working as expected

Have similar issue as was ask in other question. In my case none of cases from microsoft docs seems to apply Can something else be the reason? Schema changes not happening, but after index ...
1
vote
0answers
44 views

sql server connectivity broke down to server after its IP address has moved to a subnet. How to restore its connectivity?

I a server called Vermont on a different domain, with a trust relationship. I can ping the server there. But when I try to connect to it, from my SSMS I get the following error: I used to connect ...
1
vote
1answer
39 views

SQL Find Code for Each Separate Column in View

We have a SQL View, trying to find the calculation/business logic for each column into a select or table. create dbo.CustomerVw as FirstName + ' ' LastName as FullName datediff(yy,BirthDate,...
1
vote
0answers
158 views

Detecting disk throttling on Azure VM

Is it possible to monitor disk IO usage on an Azure VM or in SQL Management to detect whether disks are being throttled? How would you do it? Background: I'm looking for a way to raise an alert when ...
1
vote
0answers
55 views

Problem with partitioning non-clustered indexes

I have a database with partition function defined. Over this partition function there are 2 schemes defined, one for Data files and one for Indexes files. Data and index files are divided quaterly (...
1
vote
0answers
749 views

ODBC from Win10 32bit client only connects using IP address, but not using hostname

I'm experiencing a pretty strange problem. We're using a MS SQL based quality assurance software. The database is hosted on a MS SQL 2016 Std. server running on a Windows Server 2012 R2 VM. Our ...
1
vote
0answers
32 views

Availability Group and Transfer SQL Server Objects SSIS task

We have two db instances on Always On Availability Group. And I need to transfer data from selection of tables to the primary DB. I have created SQL Agent Job for SSIS Transfer SQL Server Objects ...
1
vote
0answers
955 views

Service Broker - A previously existing connection with the same peer was detected during connection handshake.

There are good questions about service broker here, here and here. In none of those questions I see the following error message: Service Broker login attempt by user 'MYCOMPANY_USA\sqlservice.' ...
1
vote
0answers
1k views

IntelliSense is enabled but not working in SQL Server 2016 with Azure SQL DB

I have installed SQL server 2016 whose details are as in below screenshot: My queries are pointing to Azure SQL DB. Below are the screenshot of some other settings that I have found over the ...
1
vote
0answers
119 views

Adding Databases to Availability Group

I'm attempting to add databases to an existing availability group using automated seeding but I randomly receive this error: "Seeding Check Message Timeout". I'm pulling this error message from sys....
1
vote
0answers
1k views

service broker - poison message - how to handle? how to enable poison messase handling?

I am getting constantly this error message on service broker: POISON MSSAGE - Message Removed from the queue to allow other messages to proceed - ApplicationID: 377411 select top 1000 * from ...
1
vote
0answers
62 views

Filegroup per Table partition - remove filegroup and re-add filegroup

I've seaching all over the web for a solution, if it is even possible... I'm not a savvy in Partitioned tables in different filegroups... Our software is supported by SQL 2008 R2 SP2 to SQL 2017 for ...
1
vote
0answers
70 views

Configure Always On for publisher

We have a pull transaction replication.Publisher runs MSSQL 2016. Distributer runs on separate server with MSSQL 2016. Is it possible to define Always On over publisher and another new server, so ...
1
vote
1answer
62 views

Enable Replication/CDC from High Availability Group

I am a newbie to this Replication concept and I am trying to build some scripts to automate our Build Process where we will be disabling/re-enabling Replication on a regular basis. Background: We ...
1
vote
0answers
61 views

Transactional Replication from Copy Only Backup

Is it possible to use a copy only backup when initializing a subscriber from a backup? I've been trying it but getting all kinds of issues with: Trying to update records that don't exist Inserting ...

1 2
3
4 5
9