Questions tagged [sql-server-2016]
SQL Server 2016 (major build version 13.00.xxxx). Please also tag sql-server.
2,291
questions
1
vote
2answers
112 views
Is it better to have one instance of a continuously running agent job or to fire off the job multiple times throughout the day?
I have a SQL query I need to run every second for about 12 hours a day.
It's a pretty simple query that logs some data to an isolated table.
Right now I've been leaving it running in my local SSMS ...
2
votes
3answers
123 views
Weird memory pressure for SQL Server 2016
We have been battling an issue with weird memory pressure on a specific server for a couple of months now. Here's what the last incident looked like in SentryOne:
System Memory
SQL Server Memory
...
1
vote
1answer
42 views
SQL Server: Help in writing a query to get 3 rolling months average for a table
Query Thus far:
select month
, year
, vol
--, sum(vol) over (order by month,year ROWS BETWEEN 2 PRECEDING AND current row) sumnetvol,
, avg(vol) over ( order by month,year
...
1
vote
1answer
131 views
SSIS Package execution fails when started by user logging in with FreeTDS
I am attempting to let a login kick off an SSIS package via the built-in SSISDB stored procedures instead of doing so through SQL Agent.
The login will be connecting through an Airflow DAG on a ...
1
vote
1answer
58 views
XA transactions - timeout errors
We have recently setup XA Transactions as per the documentation.
However, app team is facing the issues as below
Performance Test 1: XXXX Internal users [get XXXX token API ] – 3 concurrent users ...
0
votes
1answer
146 views
Does ISOLATION LEVEL READ UNCOMMITTED affect Deletes?
I have an exceptional circumstance that made me use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED. (I am using sp_getapplock to ensure single process access to a given row.)
I am trying my best ...
0
votes
1answer
36 views
Register the time a machine running in a timerange using sql
machinename startdatetime enddatetime timerunning(mins) timerange
vxx:2019 2020-02-07 13:45:18.790 2020-02-07 16:00:38.920 135 12:00
the above machine ...
0
votes
0answers
17 views
GROUPING SETS equivalent in MDX?
I'm creating an SSRS report that allows the user to select the different type of aggregations that are displayed in the report (e.g. overall, by type, by discipline, by type/discipline).
Currently ...
1
vote
2answers
118 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" ...
0
votes
1answer
21 views
“Could not find login” failures for remote LocalSystem that exists
I imagine security changed in some way for Windows 2016, but I'm unable to determine what exactly.
For context: We have a variety of remote clients that handle data processing within a (non-SQL) data ...
0
votes
0answers
54 views
Should I install all cumulative updates/service packs when doing a clean install of SQL Server 2016? 2019?
Going to set up a new database instance from scratch. Debating between SQL Server 2016 and 2019.
I've never installed a service pack or cumulative update on previous instances, mostly because ...
0
votes
2answers
62 views
Sql server database synchronization on 2 different server
I need advice on setting up synchronization between 2 SQL Server 2016 databases on standalone servers in different sites. What techniques should I best use and how should I go about setting up ...
6
votes
2answers
222 views
Disable top (n) Sort Optimization for a specific view
I have a a view with a complex logic and three levels of deepness (nested views). Because of the complexity I can't paste the execution plan.
As the purpose of the view is to provide some business ...
1
vote
0answers
65 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
2answers
68 views
Querying JSON data
I am struggling to extract data from a table, which was partially inserted from a JSON file. The table has two fields (name nvarchar(50) and details nvarchar(max). The values of the fields are:
name ...
1
vote
1answer
49 views
Why do I have to change Media Sets to create a Full Backup to a new file and what does that mean?
I'm trying to create an ad-hoc Full Backup as a snapshot of the database in time and when I added a new file to backup to I get the following error:
I see I can surpass this error by going to Media ...
0
votes
1answer
24 views
Is there a way to specify which schema the database task “Export Data-tier Application” exports for the other entities besides the tables?
I noticed in the Export Data-tier Application database task, you can specify which schema to export data from in regards to tables, but I also want to exclude certain schemas for the other object ...
1
vote
1answer
34 views
Can I add new tables to a transactional replication subscriber
I'm looking into using transactional replication in MS SQL Server 2016 to create a reporting database from our production HR system.
I'm wondering if it's possible to create new stored procedures and ...
0
votes
2answers
77 views
What's the best way to migrate a database to a server of a lower SQL version?
I have Database A on SQL Server 1 that is SQL Server version 2016.
I need to migrate it to Server 2 that is SQL Server version 2012.
Database A is pretty small, only about 5 GB total.
I don't think ...
0
votes
1answer
23 views
How To Grant A User In AD Groups Which Is Denied To DML Commads, To Update On A Spesific Tables
I have a Active Directory user account named SQLQRY in my sql server. SQLQRY is permitted only read data. Those users can use onyl select statement. I need to give just update and delete permission on ...
0
votes
0answers
75 views
SQL Server Replication with Temporal Tables
After a bit of advise. I have an existing database that extensively uses Temporal tables (i.e. System versioned tables). We are looking at an environment where we have a near to real time replica of ...
0
votes
2answers
30 views
Log file truncation on AG database
I am POCing AlwaysON DAG for my company, and i have come into a very interesting debacle.
Seems like even though primary and replica's and all synced up, the log file on the primary DB does not get ...
0
votes
0answers
66 views
SSRS ReportServer blocking from a CREATE PROCEDURE
We have a PowerBI Report Server that will just continue to load on the homepage (http://reportserver/reports) and just times out after certain minutes. When we investigated the ReportServer database, ...
0
votes
1answer
16 views
Maintenance plan: Confirmed server as primary but switched to secondary during backups?
While investigating a failed job that occurred over night I stumbled on to a novel situation that I thought was pretty interesting.
A server that was part of an AOA group was performing nightly ...
0
votes
1answer
33 views
SQL Server 2016 TempDB physical file size is different than SQL System Catalog And Proportional Fill Algorithm is not working as expected
When I see size of file physically on drive, it is different for one of the tempdb secondary data file when compared to size mentioned in system catalog. What can the reason for it?
In tempdb ...
0
votes
0answers
28 views
Is it possible to have SQL server look for entities under other schemas than DBO when the schema name isn't specified in the query? [duplicate]
Not sure if this is a thing, and I understand it's better practice to write your queries using the two-part naming convention, but it would be cool if SQL Server had a way to setup a schema priority ...
1
vote
0answers
19 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 ...
0
votes
2answers
100 views
If I manually failover my AlwaysOn AG primary replica to the secondary and take the primary database offline will it auto-sync when back online?
I have an AlwaysOn AG setup between Database A (primary replica) on Server 1 and Database B (secondary replica) on Server 2.
If I force a manual failover from my primary replica on Server 1 to the ...
0
votes
1answer
49 views
What steps do I need to take to move my SQL instance to a different drive on the same server but with the same drive letter?
I need to move my SQL instance to a different drive on the same server but plan to rename the drive to the same drive letter as the original drive the instance was on (after renaming the old drive).
...
2
votes
1answer
19 views
Will the source dataset for a cursor update on each iteration if within the cursor loop I'm updating one of the tables that dataset references?
In this example:
DECLARE @KeyField INT, @ValueField INT
DECLARE CursorName CURSOR FAST_FORWARD
FOR SELECT T1.KeyField, T1.ValueField
FROM Table1 AS T1
LEFT JOIN Table2 AS T2
ON T1.ValueField = ...
0
votes
1answer
37 views
Is it possible to get the name of a procedure by it's plan handle or query plan hash if the execution plan is already cleared from the plan cache?
I ran a home-brew trace for the length of 1 day that captured a few things that were running. The trace includes the specific query text, the parent entity text of that query, the Plan Handle, and the ...
0
votes
1answer
60 views
List users permissions to tables and schemas on MSSQL 2016
List all permissions for a given role? has a nice query for listing users and their roles on a DB.
How could we list all tables and users with their permissions on these tables?
0
votes
4answers
112 views
What role is able to grant role permissions on MSSQL 2016?
Unfortunately, we'll have to remove db_owner role from not admin users, because people were abusing it.
We'll split roles permissions in 3 groups:
1) readonly (SELECT) with specific write (INSERT,...
1
vote
1answer
63 views
SQL Server 2016 table corrupt
I have an SQL Server 2016 v13.0.5101.9 hosting Sharepoint databases. The weekly DBCC CHECKDB job discovered database corruption (or inconsistency): Msg 8928, and Msg 8965 popping up.
I narrowed it ...
2
votes
2answers
63 views
Storing data files in separate physical drives
Does storing sql server data files for 1 database in different drives gain performance? Is it recommended to do so? Any benefits gained from splitting the data files to multiple physical drives? There ...
0
votes
1answer
19 views
Update table with data from table in a separate database
TableA
ID Address Location Country
1 NULL Region1 Austrailia
2 NULL Region2 Antartica
3 NULL Region3 Egypt
TableB
ID Address Name
1 test avenue james
2 test ...
1
vote
0answers
96 views
Meaning of different icons in the SSMS Object Explorer [duplicate]
I have noticed that the Database symbol to the left of the Server name in the object explorer tends to differ without any obvious reason. Two servers have a database symbol with a Green arrow on them ...
0
votes
0answers
141 views
Cumulative Update 11 for SQL Server 2016 SP2 - Consent to install Microsoft R open - forced
We are planning to roll CU 11 for 2016 SP2 servers, and see that Microsoft R services had to be installed to proceed further. We do not need R services, how can i skip it? Any thoughts/ideas please.
0
votes
1answer
65 views
Replication with Always On availability groups
I have a three server set up:
SQL01 – Primary server
SQL02 – Secondary server
SQL04 – Remote distribution server
SQL04 – Subscribe
SQL01 / SQL02 DB's are part of basic availability groups and i am ...
1
vote
0answers
51 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 ...
0
votes
2answers
79 views
What's the easiest way to move tables with identity key columns while keeping the referential integrity between them?
I have four tables in database A that have identity key columns and have foreign key references to each other on those columns (without actual foreign key objects created, just the columns themselves)....
0
votes
2answers
59 views
pre login initialization =29237; handshake=26244
Connecting to one of my servers (no mirror or availability group running), from a local machine and I get this error message below related to handshake when the firewall is up.
when the firewall is ...
0
votes
0answers
43 views
Database mirroring with change history and least performance impact
I have a production DB which I need to effectively mirror and in addition stream changes to applications for their own purposes. (using something like AWS DMS)
The catch is that the source database ...
16
votes
3answers
12k views
SQL Server - Select most recent record from each group when performance is critical
I run a SQL Server 2016 database where I have the following table with 100+ millions rows:
StationId | ParameterId | DateTime | Value
1 | 2 | 2020-02-04 15:00:000 | 5....
1
vote
1answer
73 views
Deny All DDL & DML Commands In Sql Server
I want to deny all ddl and dml commands to AD and SQL Server users over all the databases on my database server. How can i do that?
0
votes
1answer
69 views
Not able to update one of the key value by using JSON_MODIFY function in SQL Server
I have the following json record and I'm trying to update the 'counter2' value to 2000 using JSON_MODIFY function. The query doesn't throw any exception but at the same time the value is not getting ...
0
votes
0answers
16 views
Multiple Failover Clusters SQL Server 2016 Standard
My requirement is configure SQL Server 2016 (Standard) Failover Clustering (Active/Active)
I'm planning to install it on each node, does SQL Server Standard Edition supports multiple Failover Cluster ...
5
votes
1answer
278 views
Is there any difference with specifying the primary key as an include column in a nonclustered index?
Don't nonclustered indexes inherently store a reference to the primary key on a table so that it can do a key lookup as needed?...if so, is it any less or more performant to specify the primary key as ...
1
vote
1answer
42 views
Are there differences between which AlwaysOn AG server (Primary or Secondary Replica) I store my backups and retention maintenance plans on?
I have an AlwaysOn AG system in place between two servers. Server A hosts the primary replica, Server B hosts the secondary replica.
Are there any differences if my Full & Transaction Log ...
2
votes
1answer
158 views
How big of a table or how many data pages in an index is worth considering running index rebuild or index reorganize operations on?
In the Books Online for Reorganize and Rebuild Indexes Microsoft mentions:
Rebuilding or reorganizing small rowstore indexes often does not reduce fragmentation.
What magnitude of number of rows ...
