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
15
votes
1answer
4k 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?...
25
votes
3answers
10k 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 ...
27
votes
4answers
37k 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 ...
7
votes
1answer
843 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 ...
10
votes
3answers
1k 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 ...
4
votes
2answers
2k 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 ...
2
votes
3answers
4k 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 ...
1
vote
1answer
1k 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 ...
31
votes
4answers
1k 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 ...
5
votes
2answers
702 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: ...
10
votes
2answers
812 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 ...
2
votes
1answer
445 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] ...
2
votes
2answers
3k 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 ?
1
vote
0answers
235 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 ...
0
votes
1answer
181 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 ...
0
votes
1answer
1k 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 ...
0
votes
1answer
3k 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 ...
22
votes
3answers
25k 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 ...
5
votes
1answer
174 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 ...
12
votes
1answer
344 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 ...
4
votes
2answers
706 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 ...
4
votes
1answer
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 '...
2
votes
3answers
596 views

SQL How to Convert Single Tenant Databases to a Multitenant Database

We have a legacy application. For some reason, someone decided to create a database for each customer. So we have 500+ databases with the same schema. I would like to convert into One multi-client ...
12
votes
1answer
1k 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 ...
9
votes
3answers
1k views

Create database level constants (enumerations) without using CLR?

I have a several SQL objects that need to take alternate actions based on a desired state of the request. Is there a way to create database level constants (enumerations) that can be passed to stored ...
7
votes
1answer
6k 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 ...
6
votes
2answers
3k 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....
6
votes
1answer
177 views

Query is fast but becomes sluggish when created as a view

Execution Plan as Query SELECT VP.Branch , VP.ROUTE AS Route , VP.SAPCustomerID , S.CustomerID , S.ProductID , S.Date -- Group by Customer , CustomerQuantity = SUM(...
4
votes
1answer
892 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 ...
2
votes
1answer
1k views

Multi-tenant database

We are building a database for a SaaS web application. We have been told to use a multi-tenant approach and to expect over 100,000 tenants to be using the system over the next 5 years, so we're ...
2
votes
2answers
924 views

Generate SQL Table DDL from a View

How do I generate table structure from a view, in one script? Will only need data types, I am seeking a Table Create Script with some type of dynamic sql (1) can remove primary key/constraint, (2) ...
6
votes
3answers
2k 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 ...
5
votes
1answer
853 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 ...
5
votes
1answer
11k 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 running SQL ...
5
votes
3answers
6k 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. ...
4
votes
2answers
5k 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 ...
3
votes
3answers
2k 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].[...
3
votes
2answers
7k views

SQL Server 2016 slow restore

We have a 300 GB database that I am test restoring to a new SQL Server 2016 instance, on a virtual Windows 2016 server, used for testing purposes. The VM has 32 GB RAM, running on a 1 socket CPU with ...
3
votes
2answers
2k views

Encryption of Data, Log and Backup files using Always Encrypted in SQL Server 2016

When we implement the Always Encrypted feature in SQL Server 2016, do the .mdf files, .ldf files and .bak files get encrypted as well? I would like to know if the data/log/backup files gets encrypted ...
2
votes
1answer
1k 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'...
2
votes
1answer
856 views

The backup or restore was aborted - what could it be?

use master go ALTER DATABASE Apcore SET SINGLE_USER WITH ROLLBACK IMMEDIATE RESTORE DATABASE Apcore FROM DISK=N'C:\Backups\QG-V-SQL-TS$AIFS_DEVELOPMENT_APCore_FULL_20180909_230034.bak' WITH FILE = ...
2
votes
2answers
2k 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 ...
1
vote
1answer
191 views

Having the backup file, how to work out the total disk space required to do the restore?

I have a nice query that gives me the date of last full backup, size and location, even when I backup to multiple backup files. what I don't have is an estimate of the total backup size (for each of ...
1
vote
3answers
73 views

MSSQL Server Upgrade, go for latest or version before that [closed]

A dilemma probably faced by quite a few DBAs. Does one upgrade to the latest version, in this case SQL SERVER 2016, or play it safe and upgrade to 2014, which hopefully has most bug fixes in place by ...
0
votes
1answer
200 views

How pass a local variable in Json path during data extraction using Json_value function in sql sever select statement

My Query is declare @id INT set @id = 69 select (JSON_VALUE(cast(ad.data_map as varchar(max)),'$.dataMap."69".value')) CorrespondanceNumber, (JSON_VALUE(cast(ad.data_map as varchar(max)),'$....
0
votes
1answer
1k views

why my split string functions are not working with large OBJECT_DEFINITION?

I have in one of my databases a stored procedure called PROCEDURE [repl].[usp_upd_repl_out_application], that is not exactly a very clever stored procedure but it has over 389 lines. As I need to ...
0
votes
1answer
189 views

Migration 100 TB of data from SQL Server 2008 to 2016 [closed]

We are planning to migrate to 100 TB of data from one of our client to new SQL Server environment. We just started receiving information from client. It will take couple of months to complete the ...
0
votes
1answer
315 views

SQL SSDT Database Projects: Source Control Lookup Data

Does SQL Server SSDT Visual Studio 2017 Database Projects support Source control data for lookup tables, or do we still have to utilize messy Post-Deployment Merge Scripts for multiple tables? ...
-1
votes
3answers
35 views

How to convert multiple duplicate columns as single row with appreciated values

how to convert multiple duplicate columns as single row with appreciated values see the image my actually table and expected result.
-2
votes
1answer
109 views

Working with NULL Date Fields [closed]

I have a derived VARCHAR date field that I need to use for deriving another field, based off of whether or not the date field has a value. The derived date field is called "AmortDate" and I'm ...