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
19 votes
1 answer
6k views

SQL Server--If logic in stored procedure and the plan cache

SQL Server 2012 and 2016 Standard: If I put if-else logic in a stored procedure to execute one of two branches of code, depending on the value of a parameter, does the engine cache the latest version?...
Nicole G.'s user avatar
  • 315
39 votes
4 answers
85k views

Blue icon with question mark - what does it mean?

The SQL Server instance is accessible and seems to be fine. Microsoft SQL Server 2016 (SP1-CU2) (KB4013106) - 13.0.4422.0 (X64) Mar 6 2017 14:18:16 Copyright (c) Microsoft Corporation ...
Marcello Miorelli's user avatar
31 votes
4 answers
3k views

What is a scalable way to simulate HASHBYTES using a SQL CLR scalar function?

As part of our ETL process, we compare rows from staging against the reporting database to figure out if any of the columns have actually changed since the data was last loaded. The comparison is ...
Joe Obbish's user avatar
  • 32.2k
1 vote
1 answer
2k views

error code 3417 - after installing CU for SQL SERVER 2016 SP1

I am getting below error message after installing cumulative updates for SQL Server 2016 Sp1. The description for Event ID 7024 from source Service Control Manager cannot be found. Either the ...
Marcello Miorelli's user avatar
28 votes
3 answers
17k views

SQL Server clears plan cache and execution stats periodically

After upgrading SQL Server 2014 to 2016, the server keeps resetting cached execution plans and dm* views (like dm_exec_query_stats) etc. every couple of hours As if someone executes DBCC ...
jitbit's user avatar
  • 755
13 votes
2 answers
2k views

Never ending Query Store search

I'll say from the beginning that my question/problem looks similar to this previous one, but since I'm not sure if the cause or the starting info is the same, I decided to post my question with some ...
Marian's user avatar
  • 15.5k
10 votes
3 answers
2k views

Unable to restore TDE enabled database when MAXTRANSFERSIZE and CHECKSUM is used

Update: @AmitBanerjee - Senior Program Manager for the Microsoft SQL Server Product Group confirmed that MS will look into the issue as it is a defect. Has anyone encountered issue restoring backups ...
Kin Shah's user avatar
  • 62.2k
6 votes
2 answers
6k views

when applying a sql server cumulative update or service pack on a virtual machine - what could be a safe way of stopping VMWaretools?

While applying cumulative updates to sql server 2016 SP1, there are a bunch of services and applications that need to be stopped for the update to go on. Among those is the very core of the virtual ...
Marcello Miorelli's user avatar
6 votes
1 answer
4k views

Type conversion in expression may affect “CardinalityEstimate” - on a computed column?

I had a look at this question: Type conversion in expression may affect “CardinalityEstimate” in query plan choice? But that was related to collation and not with computed columns. I have the ...
Marcello Miorelli's user avatar
2 votes
3 answers
5k views

Problem getting SQL Server 2016 R Services (In-Database) working

SQL Server 2016 CU2 (Enterprise) here and one of my users has requested that we get R-Services installed. I followed what appeared to be very simple instructions from this page Set up SQL Server R ...
Scott Hodgin - Retired's user avatar
2 votes
2 answers
5k views

Quorum for 3 nodes AlwaysOn Availability Group

If I am going to set an Alwayson Availability group with 3 nodes, with read-only routing. Do I need to configure a Quorum ?
sebeid's user avatar
  • 1,381
0 votes
1 answer
475 views

is this time to remove the databases from the availability group? which ones?

While patching one of my servers that is part of an availability group, as a secondary, I had some problems - the real problem was that the host machine of one of the virtual servers in the ...
Marcello Miorelli's user avatar
10 votes
1 answer
2k views

Do temporal tables log changes when there are none?

Unfortunately I don't have ready access to a SQL 2016 instance to test this. If I had a temporal table with the following schema: create table blah ( foo int identity(1,1), baa int ) and I ...
Jeff's user avatar
  • 313
9 votes
2 answers
24k views

Could not obtain exclusive lock on database 'model' - who is using it?

when trying to run the following query in order to create a new database: CREATE DATABASE [Lunch] CONTAINMENT = NONE ON PRIMARY ( NAME = N'Lunch', FILENAME = N'E:\Data Files\...
Marcello Miorelli's user avatar
5 votes
2 answers
1k views

The database mail configuration saved into a temp table

I am currently dealing with a bunch of servers and they have different Database Mail Configurations. I can see the Database Mail configuration for each of those servers. I use the following script: ...
Marcello Miorelli's user avatar
4 votes
1 answer
367 views

Badly-formed histogram causes bad estimates on Nested Loop

We have a query on a SQL Server 2016 SP2 CU12 Enterprise where the Query Optimizer estimates that only 1 row would come out of the Nested Loops Join operator, in reality 108501 rows came back. This ...
Frederik Vanderhaegen's user avatar
2 votes
1 answer
2k views

How to script out the grant view on login permission?

I create the following login without any permissions. USE [master] GO IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'Radhe') CREATE LOGIN [Radhe] WITH PASSWORD=N'...
Marcello Miorelli's user avatar
2 votes
1 answer
1k views

sparse column or DATA_COMPRESSION?

in one of my databases I have the following table: CREATE TABLE [app].[applicantSkill]( [ApplicantSkillID] [int] IDENTITY(1,1) NOT NULL, [applicantID] [int] NOT NULL, [skillID] [tinyint] ...
Marcello Miorelli's user avatar
2 votes
1 answer
4k views

Comprehensive troubleshooting of DatabaseMail

I am having a problem sending email from one of my servers. I want to use DatabaseMail and/or msdb.dbo.sp_send_dbmail I have other production servers that use all the same databaseMail settings ...
Marcello Miorelli's user avatar
2 votes
0 answers
836 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 ...
Marcello Miorelli's user avatar
1 vote
2 answers
2k views

how to grant permissions to all the synonyms inside a view?

I have to grant permissions to a view. dbo.my_view, however, this view joins many tables, and other views and synonyms. using the following script I can find all all the synonyms in my server: ...
Marcello Miorelli's user avatar
0 votes
1 answer
3k views

Powershell Run Stored Procedures in Parallel in Database

We want to run all stored procedures in parallel. The powershell script finds a list of stored procedures in the database (only have 5 right now), and runs them simultaneously. This code seems to be ...
user avatar
0 votes
1 answer
5k views

SQL Mail Failing To Send

SQL Sever 2016 with the latest updates. Running on WS2012R2. Following error message is received: The mail could not be sent to the recipients because of the mail server failure. (Sending Mail ...
DARKOCEAN's user avatar
43 votes
7 answers
9k views

What's a good use case for SELECT * in production code?

Out of habit, I never use SELECT * in production code (I only use it with ad-hoc scrap queries, typically when learning the schema of an object). But I ran across a case now where I'm tempted to use ...
J.D.'s user avatar
  • 37.5k
34 votes
3 answers
52k views

varchar(255) or varchar(256)?

Should I use varchar(255) or varchar(256) when designing tables? I've heard one byte is used for the length of column, or to store metadata. Does it matter anymore at this point? I saw some posts on ...
user avatar
24 votes
4 answers
53k views

Enable TLS 1.2 for SQL Server 2016 database mail

I have been puzzled by this issue for almost 1 week. Hopefully someone in our community has experienced the same issue and already found a solution. So here is my problem: As per our company policy, ...
jyao's user avatar
  • 3,043
14 votes
1 answer
769 views

Why does NOLOCK make a scan with variable assignment slower?

I'm fighting against NOLOCK in my current environment. One argument I've heard is that the overhead of locking slows down a query. So, I devised a test to see just how much this overhead might be. I ...
Forrest's user avatar
  • 4,169
12 votes
1 answer
2k views

Query Store Force Plan feature doesn't work

Query Store Force plan feature doesn't appear to be enforcing the plan. I'm aware of Query Store - Forced Doesn’t Always Mean Forced; however, my plan may not change insignificantly, but query ...
Janis Veinbergs's user avatar
11 votes
1 answer
717 views

What does it means when you have "sql_exit_invoked" in System Health?

I'm having problems with one of my SQL Server 2016 Standard servers. I have 8 production servers and this one is the only one who crashes randomly without having any trace in the log. I have ...
Danielle Paquette-Harvey's user avatar
11 votes
2 answers
9k views

SQL Server 2016: Temporal table performance vs. Triggers and CDC

It seems to be quite difficult to find comparisons between system-versioned temporal tables and the older options, such as DB triggers and CDC. I currently don't have the time to write an extended ...
Kahn's user avatar
  • 1,813
9 votes
1 answer
2k views

Is the NOLOCK hint completely safe to use on a table that's guaranteed not to change?

If I have a table where the data is guaranteed to never change, is it completely safe to add the NOLOCK hint to my SELECT queries against that table? If I have a table where some of the rows will be ...
J.D.'s user avatar
  • 37.5k
9 votes
5 answers
5k views

Linked Server connection started failing with "Cannot generate SSPI context ... SQL Server Network Interfaces: The logon attempt failed" error?

The Issue I have two SQL Server 2016 Standard Edition servers on the same domain. ServerA has two linked server connections setup to ServerB. The first linked server connection is using a dedicated ...
J.D.'s user avatar
  • 37.5k
8 votes
2 answers
10k views

What Triggers This Warning: Type Conversion in Expression May Affect "CardinalityEstimate" in Query Plan Choice

Using SQL Server 2016 I'm trying to understand why I receive this warning while running this T-SQL statement (I chose the table variable for ease of testing. This happens with DB tables that have ...
gbeaven's user avatar
  • 213
7 votes
2 answers
1k views

Trigger on partitioned table and performance

I'm not sure if what I've found is a bug, but it certainly looks like it. I wasn't able to find much information about it, so I've decided I'll put it here. So, in a nutshell, I'm facing terrible ...
Roman Pekar's user avatar
7 votes
1 answer
7k views

SQL Server detected a logical consistency-based I/O error: incorrect pageid on RESTORE

The restore of our database to new server hardware failed with a page error. Message SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 49:8125916; actual 49:...
Henrik Staun Poulsen's user avatar
6 votes
3 answers
5k views

SQL Server FCI installation with Mount Point permissions issues

Please help me get past this installation issue. I am trying to install a SQL Server 2016 (SP1 slipstreamed) Failover Cluster Instance (FCI) on two Windows Server 2012 R2 VMs. I have 3 cluster disks....
SQL Hammer's user avatar
6 votes
3 answers
6k views

How to retrieve a large text from inside a table?

for my own records I have a table in one of my servers where I like to save my activities and scripts the table definition is: IF OBJECT_ID('[dbo].[activity]') IS NOT NULL DROP TABLE [dbo].[...
Marcello Miorelli's user avatar
6 votes
4 answers
11k views

Passing variable as parameter to json_query function

I am getting error: The argument 2 of the "JSON_VALUE or JSON_QUERY" must be a string literal ...while trying to pass variable as parameter to JSON_QUERY function. Please help me to resolve it. ...
Arvind Mishra's user avatar
6 votes
2 answers
4k views

How to update statistics for a database's system tables

We recently upgraded many of our instances to 2016. As a result, a SELECT statement from sqlpackage.exe is timing out on some instances. After some testing, I found that by updating the statistics on ...
Craig Efrein's user avatar
  • 9,640
5 votes
1 answer
539 views

How to connect to microsoft azure?

We have been saving the backups of one of our production servers (which is actually an Always On availability group) this is how I am doing my backups and saving to azure: sqlcmd -E -S $(...
Marcello Miorelli's user avatar
5 votes
1 answer
231 views

Advice on T-SQL script, change to use dynamic SQL

The below script will query Ola's Maintenance Solution results table (CommandLog) and come up with a comparison on the bi-weekly reindex exec duration, for the last 4 weeks. The way it looks right ...
Razvan Zoitanu's user avatar
5 votes
1 answer
2k views

Move transactional replication subscriber to another server without re-snapshotting

I have publisher and subscriber at same server. I need to move subscriber's DB to another server but without creating new snapshot. I've tried this steps: Stop all agents Backup subscriber's DB ...
Ivanoff's user avatar
  • 53
5 votes
3 answers
19k views

SQL Server LaunchPad "The service did not respond in a timely fashion" + No Log in ExtensibilityLog

I'm trying to start LaunchPad service but it fails to start with: Error 1053: "The service did not respond in a timely fashion" I've been reading this was common in RC versions but I'm ...
Dan's user avatar
  • 151
4 votes
1 answer
1k views

Why is query choosing horrible execution plan?

I am trying to make a query that our application sends in more efficient. I slightly modified the query in SSMS and it will execute in about 1 sec. Query A SELECT O.Code AS 'Code', O.[Action] AS '...
Jason's user avatar
  • 339
4 votes
2 answers
27k views

The Distributor has not been installed correctly. Could not enable database for publishing

while in the process of creating a publication in one of my servers I continuously get the following error message The Distributor has not been installed correctly. Could not enable database for ...
Marcello Miorelli's user avatar
4 votes
2 answers
778 views

sp_Blitz, >1000 plans for CREATE PROCEDURE

I'm using Brent Ozar's sp_Blitz, and one of its results is this: Many Plans for One Query 1146 plans are present for a single query in the plan cache - meaning we probably have parameterization ...
Christian Specht's user avatar
4 votes
1 answer
14k views

Msg 39011 SQL Server was unable to communicate with the LaunchPad service

I was playing with the new "R" scripting in SQL Server 2016 CTP 30. Finally got it working, thanks to this site. I've had a problem that I'm sure others will run into as well. All of a sudden the "...
Henrik Staun Poulsen's user avatar
4 votes
2 answers
1k views

Is there any reason to stop transaction log backups during a maintenance window?

We have recently had an issue where the transaction log grew and maxed out the storage space. This happened during a maintenance window (Ola Hallengren index Optimize scripts are being used.) For ...
Tracy M's user avatar
  • 145
4 votes
2 answers
6k views

Failure during install of SQL Server 2016 sp2

I'm trying to install the new SP2 for SQL Server 2016, but it fails during the install with an Engine error. This is what I can read in the errorlog> Error: 468, Severity: 16, State: 9. Cannot ...
Henrik Staun Poulsen's user avatar
4 votes
2 answers
3k views

Is SQL Server sp_who2 deprecated?

I keep hearing sp_who2 is deprecated soon, unsupported. We are on SQL Server 2016 Enterprise. What is a good official Microsoft replacement item or DMV which team should utilize instead of sp_who2? ...
user avatar

1
2 3 4 5