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
1
vote
2answers
256 views

Consolidate to multiple or single instance SQL Server on a VM with many third party vendor databases?

I need to consolidate several older instances to SQL Server 2016 Enterprise on a single VM. Since all of the databases are third party vendor systems, they are a sprawling mess of SQL Agent jobs, ...
5
votes
6answers
3k views

ALTER DATABASE MODIFY FILEGROUP [myfilegroup] AUTOGROW_ALL_FILES

I would like to change all file groups in my database to AutoGrow_all_Files, but only if it is currently AutoGrow_Single_File, like this: ALTER DATABASE mydatabase MODIFY FILEGROUP [myfilegroup] ...
0
votes
1answer
81 views

In what order does sqllogship.exe process groups of log files?

In what order does sqllogship.exe processes groups of log files based solely on filename order or does it does it do it based on file timestamp primary sort then filename secondary sort? It appears ...
1
vote
1answer
1k views

Do you still need both the 32 bit and 64 bit connectors installed for SSIS 2016?

We've recently installed SQL Server 2016 on development including SSIS, and we're in the process of migrating our work over to this new server. Part of this work involves migrating packages that ...
1
vote
1answer
264 views

Error while creating CMK(column master key) in always encrypted

I am trying to explore SQL 2016 always encrypted feature but i am getting an error while generating new column master key, after entering name and selecting 'Windows Certificate Store-Current User' as ...
1
vote
1answer
7k views

MS SQL Server 2016 Oprimal Disk Configuration [closed]

New server build for SQL SERVER STANDARD 2016 install. Total of 12 disks: 6 standard 15K HDs and 6 SSDs. This is the disk setup I am going for. Could this setup be improved on, if so how what would ...
11
votes
4answers
767 views

Sql Server fails to use index on simple bijection

This is another query optimizer conundrum. Maybe I'm just over-estimating query optimizers, or maybe I'm missing something - so I'm putting it out there. I have a simple table CREATE TABLE [dbo].[...
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 ...
2
votes
2answers
1k views

SSMS table properties show an exception for Index space on every table

When getting table properties from a SQL 2014 server (12.0.4449) using SSMS 2014 (12.0.4100) or 2016 (13.0.15600) on any database or table I get the following text for the "Index space" property on ...
2
votes
1answer
93 views

Trace flag 10260 on SQL Server 2016

Our SQL Server 2016 development servers have trace flag 10260 set on start up. The other servers that run SQL Server 2016 do not. I've searched high and low, but have not been able to find any info on ...
0
votes
0answers
1k views

Deadlocks on primary key row locks race condition with temporal tables

I have a procedure dbo.DeleteTemporal which begins a transaction and then deletes records from a list of 100 or so tables all based on a column named ServerName. The list of tables and the order of ...
0
votes
1answer
182 views

Is it safe or possible to change SQL Server AutoGrowth when in a SQL Server AlwaysOn Cluster

I am wondering it is possible or advisable to change the AutoGrowth settings of a database while it is part of an AlwaysOn SQL Server 2016 database cluster. I realized I used the default settings when ...
2
votes
2answers
3k views

Suggested SQL Server Backup Schedule using Ola Hallengren scripts

We are building out a new AlwaysOn SQL Server 2016 database cluster and I am in the middle of setting up the backups using Ola Hallengren (https://ola.hallengren.com) backup scripts and I was looking ...
1
vote
1answer
6k views

How do I add an existing shared data source and dataset to my SSDT/Visual Studio SSRS Project?

I am trying to add a shared data source and data set to an SSRS project in SSDT/Visual Studio, also called Report Designer. There are plenty of ways to add this into a Report Builder. I am looking on ...
2
votes
2answers
834 views

Is there a way to pass two SQL tables w/o JOIN for processing into R using MS SQL Server 2016?

Is there a way to pass two SQL Server tables without a JOIN for processing into R using MS SQL Server 2016? For Example, @input_data_1=TABLE, passes the 'TABLE' as a dataframe into R with default ...
-1
votes
3answers
1k views

TSQL - Check if table items have no quantity or user input is invalid

I have a table that contains a list of stock; user input comes from a parameter named @StockID. An important condition of the query is only showing stock with a quantity greater than 0. SELECT ...
1
vote
1answer
1k views

SSISDB user “AllSchemaOwner” is not SQL Server Login

Did some research in SSISDB and found a user AllSchemaOwner which is not associated with any SQL Server login: SELECT p.name, p.type_desc FROM SSISDB.sys.database_principals as p LEFT JOIN sys....
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 ...
0
votes
1answer
413 views

SSRS2016 external parameters for mobile report

How can external parameters be passed to a SSRS 2016 mobile report in order to pre-select values / settings for the report? The same scenario is possible for paginated reports as it always was. It's ...
5
votes
2answers
1k views

What are options for super volatile tables?

I've inherited a very volatile table which is a map of who holds what resource in the system. At any given moment, there could be a dozen inserts/deletes/reads going against that table. However, ...
4
votes
3answers
10k views

What's the most efficient way to load a csv file into SQL Server 2016?

Alright, so this question seems simple up-front. Well, there's a bit more to it than just loading the file. The entire story goes like this: For some reason, our client sends us what can only be ...
3
votes
2answers
757 views

SSRS 2016 Mobile Report Color Settings

In SSRS 2016 Mobile reports are a new option for Native mode report server. I have created several now and there appear to be consistencies between colors on different charts and graphs if the same ...
8
votes
4answers
4k views

SQL 2016 SQL Server Assertion: File: <pageref.cpp>, line=951 Failed Assertion

I'm currently upgrading our Data warehouse from SQL 2012 to SQL 2016. I have both my old and new DW's running side by side in parallel. My ETL process (A framework developed in SSIS by a 3rd party) ...
2
votes
1answer
791 views

Fastest method for inserting large number of records from query between tables

I have a query that returns 14 million results that I need to store in a table, the SELECT query itself returns in a little over 2 minutes but INSERT statement takes well over 30 minutes (which is ...
2
votes
1answer
1k views

Join Tables of Varying Criteria in Order of Priority

I am trying to join two tables based on varying criteria in order of priority. The situation I have inherited a clinical database which has inherently bad data quality from years of poor input and ...
0
votes
1answer
346 views

SSMS 2016 Maintenance Plan Designer Perms

We have some older maintenance plans that were built with SSMS 2014 on a Sql Server 2014 instance. The connection defined for the plan uses a Sql Server account with sysadmin perms. When you edit ...
1
vote
0answers
295 views

How to design efficient queries and structure for data logging table (numerical stats)

I'd like to create a logging table that logs numeric values (.NET application). Being a programmer and not really a database person, I'd like to ensure myself with the advice of more experience ...
9
votes
3answers
2k views

Why do temporal tables log the begin time of the transaction?

When updating a row in a temporal table the old values for the row are stored in the history table with the transaction begin time as the SysEndTime. The new values in the current table will have the ...
1
vote
0answers
973 views

SQL Server tempdb high IO

Using SQL Server 2016 Enterprise, having just recently upgraded from 2008 R2. The server has 192GB of memory, 24 logical codes, and is running around 30 databases with a total size of around 500GB. ...
4
votes
3answers
2k views

Distributed Availability groups & Listeners

I'm currently doing my first setup with Distributed Availability groups. This is currently a POC setup but will normally be implemented soon. I have 4 nodes, 2 nodes located together and 2 other also ...
1
vote
1answer
894 views

SQL Server 2016 Standard Version Features Evaluation

We're looking to test the new Basic Availability Groups (BAG) feature in SQL Server 2016 Standard Edition. So far, I have not been able to find an evaluation copy of Standard. Is there a way we can ...
2
votes
1answer
2k views

High Availability SQL Server

We currently have a asp.net application running on a Win 2012 dedicated server, with a local copy of SQL 2014 Express. We are looking to add a second web server and have them in a NLB cluster. But ...
4
votes
2answers
429 views

Can SQL Server R Services be enabled on a readable secondary?

To avoid excess load on the primary SQL Server, I'd like to install and enable R Services on a secondary replica of the primary server. Is this possible? Also, can I push computations from a remote R ...
0
votes
0answers
2k views

SQL 2016 Installation Error

I am trying to install sql 2016 standard on a windows server 2012 standard edition, and I am running into an odd error, hopefully someone can point me in the right direction. I am getting account ...
4
votes
1answer
3k views

Problems caused by SQL Server 2016 Query Store

I have been testing SQL Server 2016 (Standard) on an Azure VM and have encountered a problem. In my SQL logs I am seeing SQLExceptions and eventid 1001 and this is happening every 15 minutes. 2016-...
6
votes
2answers
12k views

.NET SQLCLR Assembly not working in SQL Server 2016 (Error msg 10314)

I am migrating a database application from Windows 2008 R2/SQL Server 2008 R2 to Windows 2012 R2/SQL Server 2016 that uses a third party .NET CLR assembly to parse strings. The error I get is: An ...
7
votes
1answer
165 views

Understanding SQL Server statistics better

I have a table with 5,000,000 records with the DateOfBirth column spread between 1950 and 2007. The statistics histogram for the index only has two RANGE_HI_KEYs. I feel like the histogram should have ...
3
votes
2answers
1k views

SQL Server 2016 - SSRS Unable to Upload Report Models - smdl Files

SSRS 2016 has major improvements and we've decided to migrate our 2008 servers. However, we and ran into a issue where we can't upload our old Report Models (.smdl files) via the web interface. All of ...
17
votes
5answers
50k views

SQL Server 2016 DB Mail Not Sending

I'm on SQL Server 2016 and having a blast... my DB Mail isn't sending and I'm running out of places to look. I double checked the SQL account permissions to the DBmail executable - it has read and ...
4
votes
1answer
5k views

Microsoft SQL Server 2016 performance degradation for stored procedure called 1000's of times

Context : Small table, stored procedure to update table, first two thousand hits no problem. After those first hits it starts to crawl to a snails pace. This stored procedure has no problems in our ...
1
vote
0answers
1k views

SQL Server - can't import data-tier application from bacpac file (trying to move db + data from Server 2016 to 2014 SP1)

I'm trying to get a workable method to transfer a database schema and data from a SQL Server 2016 install to a SQL Server 2014 install on a different network. It looks like Import/Export Data-Tier ...
1
vote
2answers
3k views

Connect SQL Server 2016 SSDT with TFS

I've just upgraded the SQL client tools on my PC to SQL Server 2016. I no longer have a "Team" menu option in SSDT. Under Tools > Options > Source control there is a drop down box for Source ...
3
votes
2answers
949 views

SQL Server R Services - Could not find stored procedure 'master..xp_ScaleR_init_job'

I'm trying to run a data science experiment as explained in this tutorial. The tutorial has 5 lessons, and each lesson has a few sub-sections in it. For this tutorial, I am using a SQL Server 2016 ...
1
vote
1answer
202 views

SQL Server local instance user login [closed]

I am working on an Azure Mobile Apps backend service, which I am trying to connect to a local SQL Server database, as described here. I have followed all the steps, except I am using SQL Server 2016 ...
4
votes
1answer
1k views

Connecting a standalone Microsoft R Server to a SQL Server with R Services and a SQL Server without R Services

Let's assume I have 3 machines with me. My first machine has SQL Server 2016 with R Services installed on it. My second machine has SQL Server 2016 without R services enabled on it. My third ...
4
votes
1answer
789 views

How can I handle unwanted, non-erroneous situations better?

When writing procedures, I occasionally encounter situations in which I want to abort the procedure, even if the situation didn't necessarily trigger an error. Let's say, if I don't want John to be ...
1
vote
2answers
1k views

XML column performance in TSQL

I'm finding very limited reading on how a large xml column affects table performance in sql server 2014/6. For reference, it is a 36gig table and minus one xml column, it is 36megs. Some queries ...
2
votes
1answer
941 views

SQL Server 2016 - Is the Microsoft R Client any different from a standalone Microsoft R Server?

Along with the integrated R services on SQL Server 2016, Microsoft is also offering enterprise level Microsoft R Server as a standalone installation. Some of the documentation on MSDN shows that the ...
1
vote
0answers
622 views

SQL Server 2016 - row level security by AD group membership

I'm wondering if it is possible to use row level security based on a users AD group membership. I would love to have something that could support users in different groups and some super users who ...
24
votes
1answer
925 views

Why does this MERGE statement cause the session to be killed?

I have the below MERGE statement which is issued against the database: MERGE "MySchema"."Point" AS t USING ( SELECT "ObjectId", "PointName", z."Id" AS "LocationId", i."Id" AS "Region" ...