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
18
votes
1answer
5k 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?...
35
votes
4answers
53k 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 ...
1
vote
1answer
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 ...
27
votes
3answers
12k 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 ...
31
votes
4answers
2k 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 ...
11
votes
2answers
1k 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 ...
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 ...
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 ?
2
votes
3answers
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 ...
0
votes
1answer
288 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 ...
5
votes
2answers
860 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: ...
9
votes
1answer
1k 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 ...
5
votes
1answer
2k 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
2k 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 ...
4
votes
2answers
4k 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
1answer
623 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
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'...
1
vote
0answers
366 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
2answers
1k 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: ...
0
votes
1answer
2k 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 ...
25
votes
3answers
36k 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
207 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 ...
14
votes
1answer
424 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
836 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
753 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
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 ...
11
votes
1answer
287 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 ...
9
votes
2answers
7k 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 ...
7
votes
1answer
6k 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:...
6
votes
2answers
4k 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
3answers
4k 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].[...
5
votes
1answer
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 ...
4
votes
1answer
13k 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 "...
4
votes
2answers
121 views

sysmessages table issue

I'm experiencing an annoying problem with a table named SysMessages in one of our databases. When I run a select statement I don't get the contents of this table, but of the sys.sysmessages view in ...
2
votes
2answers
2k 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) ...
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 ...
6
votes
2answers
6k 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 ...
6
votes
3answers
3k 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 ...
6
votes
4answers
8k 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. ...
5
votes
1answer
1k 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
3answers
14k 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 ...
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
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 ...
3
votes
2answers
8k 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 ...
2
votes
1answer
806 views

Backup to Azure Storage Container: Shared Access Signature the only way?

RESTORE FILELISTONLY FROM URL = '' WITH CREDENTIAL = '' fails in SQL Server 2016. Copied script from SQL Server 2012 environment, successfully restoring from Azure Storage Container, with create ...
2
votes
1answer
1k 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
0answers
183 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
1answer
360 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
2 3 4 5