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
40 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
29 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
15 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
27 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
64 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
22 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
24 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
20 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
31 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
13 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
30 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
16 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
47 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
41 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). ...
1
vote
1answer
16 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
34 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
42 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
51 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
0answers
52 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
3answers
55 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
18 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 ...
0
votes
0answers
25 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
115 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
25 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
46 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
76 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
46 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
31 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 ...
0
votes
1answer
77 views

T-Log file size increased so Truncate or Shrink? [closed]

For one of the database, T-log file got increased by 82 GB because that I am not able to restore the database on another server as drive don't have sufficient space. It has a FULL recovery model. I ...
16
votes
3answers
8k 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
49 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
26 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
13 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
263 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
37 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
149 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 ...
-1
votes
0answers
43 views

Is there an easy / quick way to create some index fragmentation on a large table?

For testing purposes I'd like to create index fragmentation on a large table (~500 million rows). I'm looking to create at least 5% fragmentation on at least one of the indexes. Is there a quick ...
0
votes
1answer
60 views

How can I check index fragmentation in the quickest way possible? [duplicate]

Checking index fragmentation in my database seems unruly slow. Regardless if I use the DMV sys.dm_db_index_physical_stats (for a specific database, table, or even index) or if I use the SSMS Index ...
0
votes
1answer
20 views

SQL Server Command Line install - Default database directories

Is there any way using the Command Line install method, I can specify a custom location for the system databases? I want them to be in D:\SqlServer\Data or is this only way to do this to install ...
5
votes
1answer
887 views

Why am I getting nondeterministic results with the deterministic function STDEV()?

This is type of query I'm trying to run: WITH CTE_Ordered AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY PartitionField ORDER BY DateField) AS PartitionRowId FROM SourceTable ), ...
0
votes
0answers
14 views

MDS Add-in for Excel Office 365 - Error

I did setup Microsoft SQL Server 2016 Master Data Services (MDS) successfully but hitting an error (bug?) while installing the add-in for Excel - which can download/upload data from/into MDS ...
0
votes
2answers
64 views

Are key lookups from non-clustered indexes always slower than a second query that does the lookup?

I've noticed in my system, whenever a non-clustered index is used in a query that has to also do a key lookup to get the additional fields being selected, it's faster for me to instead do two queries. ...
0
votes
2answers
44 views

MSSQL NULL's Between specific dates

I have a TSQL script that counts transactions for companies based on all approved, declined and failed trans in the previous month(s). I also show companies that have 0 approved, declined and failed ...
0
votes
4answers
56 views

How to visualize joins between inner queries to their respective outer queries?

I seem to always have trouble visualizing what the meaning is when an inner query is joined to it's outer query. Here's an example: SELECT NM.MovieId, NM.MovieTitle FROM NinjaMovies AS NM WHERE ...
0
votes
0answers
25 views

Why would sys.dm_exec_requests show a query in Database A blocking a query in Database B when neither query references the same tables?

I have a query I use in-house to monitor slow running processes in the data layer. It essentially looks at a few fields from the following system tables and views joined in the following way: SELECT ...
0
votes
1answer
32 views

Is each query batch in a dynamic SQL statement executed sequentially or can it be parallelized, when using sp_ExecuteSql?

I have about 500 databases that are all structured the same but the data is unique in each database. Sometimes I write dynamic SQL statements to hit all (or a subset) of those databases and will ...
1
vote
1answer
62 views

Can I create an AlwaysOn replication of a DB where some tables don't have a primary key?

Using SQL Server 2016 and I have a 3rd party application that uses a large database on my SQL Server for data storage, processing, everything. I have a BI team that needs access to this data to run ...
0
votes
1answer
63 views

Is “SQL Server Auditing” what I'm looking for?

Man... this feels like a broad question, so apologies in advance. The word "auditing" seems to have some depth in the DBA world, and I may not be asking the right question when I google. I'm trying ...
0
votes
2answers
37 views

How to automate creating credentials in a secure way

We have created an automated process to stand up a new SQL Server using TFS and powershell. As part of a new server build, we deploy a database and a set of jobs for dba use. As part of the install ...

1 2 3 4 5 42