Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
Join us in building a kind, collaborative learning community via our updated Code of Conduct.

SQL Server 2016 (major build version 13.00.xxxx). Please also tag sql-server.

2
votes
2answers
13 views

How to query against exact values in XML column set

I have a table that contains 80+ sparse columns along with a column set column, this is a brief example: DROP TABLE IF EXISTS #ColumnSet GO CREATE TABLE #ColumnSet ( Id INT NOT ...
1
vote
1answer
23 views

Sqlserver IPV6 handling function or numeric conversion

I'm looking for ways to handle IPv6 CIDR address ranges in SqlServer. The data I obtained is formatted as bellow and has these columns : Int_IP_Start, Int_IP_End, CIDR_Range, ASN, Name for IPv4 "...
0
votes
2answers
38 views

how to run once a bunch of generated scripts with exception handling?

Before I restore a database, I need to save the contents of a table called dbo.ref_systemSettings if it exists. So I created a script that calls a procedure that will save the contents of this table ...
3
votes
2answers
226 views

Understanding CleanupTime in Ola Hallengren's SQL Server scripts in relation to FULL backups and LOG backups

I am having trouble understanding what exactly to expect from the CleanupTime option in the Ola Hallengren Server Maintenance Solution. I'm finding some related questions, and elaborate answers, but ...
0
votes
1answer
13 views

SQL Server 2016 Availability Group on DPM 2016

I'm trying to backup my availability group in SQL Server 2016 using DPM 2016, but after I add the cluster in DPM, availability group show nothing to back up. I've set every secondary node to be ...
0
votes
0answers
37 views

Drop Table Problem: No catalog entry found for partition ID in database Server 2016 SP1 Enterprise [on hold]

I am getting an error while dropping a table or its partitioned column with the following command: DROP TABLE [dbo].[TABLE_NAME] The error message is as follows: Msg 608, Level 16, State 1, Line ...
5
votes
2answers
384 views

SQL Server: can dynamic data masking be safe when providing a database backup?

I have a SQL Server 2016 SP1 with data that I am ready to share with a partner (developer), except for a few columns, example: email varchar(64) MASKED WITH (FUNCTION = 'email()') NULL I give the ...
1
vote
0answers
50 views

SQL Server was unable to communicate with the LaunchPad service

I have run the below code: execute sp_configure 'external scripts enabled'; and it is giving me the output as: name minimum maximum config_value run_value external scripts ...
5
votes
1answer
117 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(...
0
votes
0answers
18 views

Change Database Availbility Group on Same Instance

Is it possible to move a database from one availability group to another on the same instance without having to remove the database from the current AG and adding to the new AG on SQL Server 2016 ...
0
votes
1answer
52 views

Inner Join until null on self-referencing tables

I have the following SQL Table: OrganizationalUnit ID - PK ParentOrganizationalUnit - FK Title Department_Code (optional) Division_Code (optional) Person ID - PK ...
0
votes
1answer
46 views

Migrating SSRS from SQL Server 2012 to 2016

I have a requirement of migrating my whole SQL Server from SQL Server 2012 to 2016. I have already migrated the database engine part. Since I have not really worked on SSRS (reporting part), so need ...
0
votes
0answers
14 views

Permission for user to enable a Queue

On a test box I need to give a user the ability to enable a queue if it becomes disabled. I gave the user Control on the queue, but they keep getting this error: Cannot find the user 'owner' ...
-1
votes
0answers
53 views

Select count(*) query very slow response [on hold]

On our dev server running MS SQL 2016 with similar data as our SQL 2016 val server, this query comes in at 1 second (on dev), but our val server comes in around a minute. SELECT Count(*) AS ...
2
votes
1answer
34 views

Add SQL Server Agent to existing SQL Installation

If there is a SQL server installation that is physically missing the SQL Server Agent is there a way to add it without reinstalling SQL Server? Will installing a service pack do it? Today I was asked ...
1
vote
0answers
39 views

Remote harden of transaction 'CREATE INDEX' - when moving an index to a different filegroup

Remote harden of transaction 'CREATE INDEX' (ID 0x0000000082a3e3fc 0000:8d8f7911) started at Aug 14 2018 6:14PM in database 'cola' at LSN (2599205:10500:111) failed. The statement has been ...
1
vote
1answer
42 views

SQL Server 2016 SP2 CU2 Availability Group Online but Cluster Offline

Am I missing something basic here? My AVG is online and working, but the Cluster on which it resides is saying offline. I didn't think this is possible, so I either have a basic misunderstanding of ...
0
votes
0answers
10 views

When to run sp_refresh_parameter_encryption while using Always Encrypted?

I'm implementing Always Encrypted feature for an ASP.NET Application using SQL Server 2016. I have applied deterministic encryption on first five tables and created few stored procedures that ...
2
votes
1answer
84 views

Why SELECT query is waiting on HADR_SYNC_COMMIT?

We have two AlwaysOn nodes in sync commit. Occasionally SELECT queries are waiting on HADR_SYNC_COMMIT. What is the underlying process that causes it? SQL Server 2016 SP1 CU5. client sessions are ...
1
vote
1answer
59 views

Session close unexpectedly destroying temporary table [closed]

I am using Temporary table to delete multiple row at the same time in the database. Every time I try to use temporary table on a specific Sql-Server-2016 it fails. I did try to reproduce the issue ...
0
votes
0answers
74 views

New SQL Server 2016 Ent only using 4GB of 768GB RAM [closed]

This is a new server with no users or databases other than tpcc because I ran HammerDB against it to try to get it to use more memory. I added the instance to Sentry One and got these two warnings ...
4
votes
2answers
538 views

Is Microsoft SQL Server 2016 fully ANSI SQL-92 compliant?

I am trying to find a compliance confirmation for MS SQL 2016 - if it is fully compliant with ANSI SQL-92 starndard. I found this article on Microsoft Docs which states it is not, but it refers to ...
0
votes
1answer
44 views

how to shrink(and also reduce size on disk) a very big log file in SQL server 2016 [duplicate]

I have a datawarehouse with database log of 800 GB.now I want to shrink it or reduce the disk space occupied by Log.ldf file. I tried to shrink file in several ways - took full backup , performned ...
-1
votes
0answers
28 views

Insert update history of sql child table

I'm recording all insert and update on TaskDetail table using a trigger,Now I want to assign multiple staff to a task, But if staff id stored in different child table how can I track audit history, I ...
1
vote
4answers
59 views

replace all references to a drive inside the jobs to another drive

I have a server where all my sql server backups are currently being saved to the G: drive. Now there is no enough disk space on drive G: to keep backups for the last 5 days. I have asked to add ...
0
votes
1answer
19 views

Extracting date from file group name and converting to date fails when filtering

In sys.sysfielgroups I have a list of filegroups with dates in them, and I'm trying to extract these dates. Here's an example list of the file groups: Event_Header_Daily_LeftNull_FG ...
1
vote
3answers
37 views

How to get the definition of a database trigger?

when I run the following script in one of my databases: SELECT * FROM SYS.triggers when I try to find out about sys.triggers Contains a row for each object that is a trigger, with a type of TR ...
2
votes
0answers
32 views

Query store works in dev, but not production in Amazon RDS

I'm having an issue getting query store to collect and record data on our production workload, however, it works as expected on our non-production instances (development and staging). The only ...
0
votes
1answer
56 views

SSRS report ERROR “Could not load folder contents Something went wrong. Please try again later. ”

One of our report servers is raising errors when trying to reach it from clients computers and works just fine from the actual server. the error is: I found that it was a bug in sql 2016 using this ...
3
votes
1answer
88 views

Begin transaction doesn't lock table

I have a table with this structure: CREATE TABLE [dbo].[a]( [id] [INT] IDENTITY(1,1) NOT NULL, [aa] [INT] NULL, CONSTRAINT [PK_a] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF,...
1
vote
0answers
37 views

Creating 'temporary' memory optimised tables with only read/write roles

I'm currently trying to get an application to use memory optimised tables instead of temporary tables, but have hit an issue. The issue is related to permissions, as users of the application are only ...
1
vote
0answers
43 views

SQL Server 2016 CDC (change data capture) missed data issue

I faced some interesting behaviour with CDC mechanism. I have a table (for example table 'A'), and i've turned on capture changes on it. So in result I have a CDC table 'cdc.dbo_A_CT' that contains ...
0
votes
0answers
44 views

problem with service broker in always-on

one of my clients has a database that works with service broker, last week we tried to move the database to an always on environment without knowing the application uses the service broker. after ...
0
votes
0answers
26 views

TCP / IP SQL configuration

I currently have two instances. Initially, I had only one network card, but I added one more. I have configured the new IP of the second card in the TCP / IP corresponding to one of the instances, the ...
1
vote
1answer
41 views

Non-Clustered Index takes much longer to create than clustered Index

I have a procedure that creates a clustered and a non-clustered index on a table in order to do a partition swap. The problem I have is the clustered index takes about 2 minutes to create: CREATE ...
0
votes
1answer
23 views

Transaction log for standalone DB vs AlwaysON availability Group

I have this question on the generation of transaction logs. For a high transaction active DB what is the ratio of log generation if it is a standalone DB and when it is part of SQL Alwayson ...
1
vote
2answers
73 views

How to grant more memory to run R processes in SQL Server 2016 R Services

I'm running a fresh install of SQL Server 2016 Standard Edition (13.0.5026.0) with R Services on Windows Server 2016 Standard. With all sorts of happiness I went to run an R script that completes ...
0
votes
2answers
56 views

Getting only the necessary row

I am working on a query for a project and sometimes the results have multiple rows for the same entity. What I need to do is: If the results look like this: Type | Entity ----------------...
1
vote
1answer
42 views

Failure with ongoing replication from SQL Server RDS using AWS DMS

I'm trying to replicate from an SQL Server RDS database using DMS, and running into problems. I am able to load all data from one table into the destination (~1M rows) as a single 'Migrate existing ...
1
vote
1answer
31 views

SQL Server 2016, temporal tables and compressed indexes

We use temporal tables in our database, and I have just run Brent Ozar's sp_BlitzIndex on it with @mode=0 (urgent issues only). It has come up with 33 issues, all of which are "Abnormal Psychology: ...
0
votes
1answer
56 views

Microsoft SQL Server error 14545

I was trying to use copy Database wizard from one server to another to make a certain database identical and i got the error (The @flags parameter is not valid for a job step of type 'SSIS'. (...
1
vote
1answer
56 views

How to enable TLS 1.2 for sql server 2016 database mail?

I have been puzzled by this issue for almost 1 week and hopefully someone in our SO community have experienced the same issue and already find a solution. So here is my problem: As per our company ...
2
votes
1answer
478 views

I just deleted 19k hi-res images from a table column and have a few questions about shrinking

We just moved to a new image-storage method for our application, which means we don't need to store image byte arrays in the database any longer. To that end, I have deleted (via an UPDATE statement) ...
1
vote
1answer
41 views

Sql Server Plan - what is a Filter after an Index operation?

I've encountered this condition while optimising a SP, there's a part of the execution plan I really don't get: SQL Server does an Index scan, then it filter the results. What are the reason for SQL ...
0
votes
0answers
46 views

length of the result exceeds the > length limit (2GB) of the target large type

Getting the following error message on the Sql Server Error Log: SQLServer Error: 599, WRITE: The length of the result exceeds the length limit (2GB) of the target large type. [SQLSTATE 42000] (...
0
votes
2answers
25 views

Auto Switch DB to primary back (persistent mode?) when available in a High Availability Always On settings [duplicate]

We have Always On Availability Group settings, and we have a preferred DB that needs to be in primary all the time even after it reboots. Once a month or so, these 2 servers DB1 and DB2 reboot for ...
0
votes
1answer
46 views

job send email to developers upon complexion and include DBAs if it fails

I have a job that sends an email to an operator (which contains a list of emails within it) upon complexion. Some of these people that are on that list are the developers who build everything in that ...
0
votes
0answers
35 views

how to find out if it is safe to uninstall an application from a production sql server?

I noticed Microsoft Office components installed in a production SQL Server Box (which is part of an availability group - currently the principal) OS is microsoft windows server 2012 r2 datacentre, ...
0
votes
1answer
40 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? ...
0
votes
3answers
48 views

Restore deleted data from a Table which occurred daily

I am not sure whether this is restore data scenario is possible. Scenario: I have a table with few 100 data inserts every day.I didn't realize that because of some reason(identified later as a bug in ...