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
0
votes
2answers
28 views

Mssql database old bakup restored on production server

I am using mssql server 2016, I have accidentally restored the 24 hours old database backup file on production server, so I have lost the data for today, means I have lost the data of 24 hours, is ...
3
votes
1answer
39 views

Seting MAXDOP for SQLserver 2016 and above

I was going through MS documentation reg: setting MAXDOP starting SQL2016 as that Automatic soft NUMA enabled by default and getting confused on how to make the changes to current SQ2014 and SQL2012 ...
1
vote
2answers
64 views

Sql Server Restoring Process Time Takes Too Long Time

I have 7 databases on my reporting server. I restore those databases every night with differetial backups. Normally, the differential restore time was taking about 1 or 1.5 hour for each databases. ...
1
vote
1answer
36 views

Multiple availability groups--can each availability group have its own listener?

I have created two availability groups containing multiple databases on the same SQL Server instance. Can I have two different listeners for these two availability groups?
1
vote
0answers
50 views

Error 9003 Log Scan is Invalid Error When Restoring Transaction Log After SQL2016 SP2 + CU8 Upgrade

We are using SQL 2016 Enterprise Edition and have recently applied SP2 + CU8. We were previously on SP1 + CU6. Since the update we are getting the following error when restoring transaction logs on ...
-4
votes
0answers
28 views

select from a view is not working [on hold]

This is weird for me Select * from vw_xxxx is not showing any results for a certain ids and select from view working fine. Any help would be greatly appreciated. select * from vw_clientdetails where ...
0
votes
0answers
44 views

Error 18456 when moving database to new environment

Last night I moved a database from Server Q (QA) to Server P (prod). Part of the process was to remove some QA logins we use and replace them with production logins. Things seemed to go well but ...
8
votes
1answer
58 views

Database Mirroring protocol TCP ports used. One default, one dynamic?

When running below query on the primary/secondary replica of a SQL Server Always On Availability Group™ SELECT DISTINCT local_tcp_port,protocol_type,num_reads,num_writes FROM sys.dm_exec_connections ...
2
votes
1answer
43 views

Query store audit

We frequently use query store force plan feature. We would like to audit when and who forced each plan. Is extended events session is needed auditing this event, or there are dmvs or catalog views ...
0
votes
1answer
31 views

Amazon RDS Ola Hallengren

Very new to SQL on AWS RDS so. Need to create maintenance jobs on an AWS RDS SQL2016 instance. I know parts of Ola Hallengren's life saver maintenance script works, Index, stats and integrity. ...
1
vote
2answers
55 views

Simulate DELETE CASCADE

I need to delete many rows from one table persons, that is referenced by many other tables as FK. If I had DELETE CASCADE, I could just delete the records from persons, and automatically delete from ...
0
votes
0answers
15 views

mssql server 2016 merge replication article properties keep changing after a while

We have on our setup merge replication between two servers running the same versions of MSSQL server. We have some fields of the geography datatype and we fix that by converting filestream to nvarchar(...
-3
votes
0answers
33 views

How can I correct this Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '1'. in my query [closed]

I have this query, I want to do it by sending parameters, which are the first ones, since I will use it in several calls DECLARE @acMascara NCHAR(10)='N99999'; DECLARE @anLongitud INT =6; DECLARE @...
0
votes
2answers
54 views

SQL Server 2016: Granting security to do everything but read / write data

We have an offshore support team coming online to support our servers as DBAs for 24x7 support. However, there are several instances that have a limitation due to contracts that limit offshore access ...
1
vote
1answer
85 views

Explain this statement about filtering in SQL Server vs MySQL

My company is in the midst of an IT overhaul and considering a shift away from MS SQL Server (2016 Standard) to a MySQL database (version 5.6.10). I have used MS SQL Server for years, so I am ...
0
votes
1answer
29 views

Default value format of a stored procedure parameter(of datatype : money)

With my query: SELECT (CAST(0.00 AS MONEY)) I am expecting a result : 0.00 But it shows me the result: 0,00(with comma) I checked this query ,when i found that,the CREATE PROCEDURE script ...
1
vote
1answer
25 views

SQL Replication components install in 2016 standard edition

i would like to install the components of Replication in sql server where the initial installation version is 2016 standard with SP1 (KB 3182545) - 13.1.4001.0 but now the SQL version is 2016 standard ...
1
vote
1answer
27 views

Log Shipping between 2 AGs

I'm having one AG with two nodes (active-passive) in one data center and another AG with two nodes (active-passive) in a different data center. I need to have log shipping enabled between these two ...
3
votes
0answers
43 views

Moving from dynamic disk allocation to direct space disk via storage pool in SQL server

Pardon me if I am not clear in asking the question since I have very less understanding from storage terms used or how it's used with Sql server. Currently our storage team is testing to move disk ...
6
votes
2answers
230 views

Unknown,AppDomain 66 (master.sys[runtime].65) is Marked for Unload Due to Memory Pressure

I have noticed this error occasionally in the SQL error log: spid20s,Unknown,AppDomain 79 (master.sys[runtime].78) is marked for unload due to memory pressure. I am using SQL Server 2016, SP1 CU5 (...
-1
votes
1answer
61 views

Prepared statements and terrible performances

There was an application that was changed, so that all queries are performed using prepared statements, rather than regular statements. What happens is that, for every query, the prepared statement is ...
3
votes
2answers
98 views

Is it normal to share CPUs on a VM of SQL Server?

Our IT set up a SQL Server as a VM on a big VMWare box that contains other VMs. The CPUs are setup as shared. As a result, any queries that might need multiple CPUs take 30x longer than if I limited ...
0
votes
0answers
28 views

Trigger to insert new row into second table causes “Connection is busy with results for another command”

I have created this trigger to insert new row into table @AL_ORDR_TRACKER after new row is inserted in table ORDR. CREATE TRIGGER dbo.trgAfterInsertORDR ON dbo.ORDR AFTER INSERT AS BEGIN INSERT INTO ...
2
votes
0answers
56 views

BulkAdmin Role Doesn't Work

There is a query that our BI team use. They can not execute the query because of the error below. Ad hoc access to ole db provider 'MSDASQL' has been denied. You must access this provider ...
3
votes
1answer
93 views

Availability Groups automatic failover is not so automatic

Behavior when I stop SQL Server Service : The availability group fails (resolving on SECONDARY SQL SERVER and fail state on Cluster management). SQL Server does not connect with the Cluster ip (I'm ...
1
vote
1answer
9 views

SQL Connector for Azure Key Vault - Cannot use more than one key vault

I am using SQL Server 2016 Enterprise Edition with SQL Connector for Azure Key Vault. I am trying to attach more than one Key Vault to my SQL instance but it is not working. I went through these ...
1
vote
1answer
28 views

SQL Server 2016 Standard Edition Replication

I have a single database running on SQL Server 2016 Standard edition in our primary datacenter. I would like to keep a live copy as close to realtime (1-2 minutes) in our disaster recovery datacenter ...
-2
votes
1answer
62 views

How to create a VIEW when Table1 is having relation to Table2 on several of it's fields?

My situation (greatly simplified) is as follows: Table1 ID Name Ver1 Ver2 Ver3 Ver4 Table2 ID Name ... This table holds the versions The columns Ver1 - Ver4 are foreign keys and are IDs of ...
0
votes
1answer
48 views

Does using secondary replica for read operations still required license? [closed]

I wanted to move some of the reporting workload to the secondary replica but the following things stopped me: no writes can be performed (including creating temporary tables); a license is required; ...
-2
votes
2answers
65 views

Size of an empty database too large

Related to SQL Server 2016. I have a database with five empty tables, let's say db1. I have another database with almost 100 tables and with a data in it, db2. I observed that the size of db1 ...
2
votes
2answers
34 views

Azure blob storage container vs VM managed disk for SQL on Azure VM database files

Are there benefits specific to SQL Server on Azure VM when using the SQL Server Data Files in Windows Azure feature? Aka using Azure blob storage container for database files versus an Azure VM data ...
0
votes
1answer
50 views

CU8 for 2016SP2 error

When upgrading to CU8 on 2016SP2, I get the following error Final result: The patch installer has failed to update the shared features. To determine the reason for failure, review the log files. ...
1
vote
1answer
45 views

AD groups and ms SQL security: is this default behavior?

I have a question regarding security in MS SQL Server (right now, I'm trying this on a SQL 2016). Scenario: User AD\testuser is in both AD security groups (AD\testgroup1 and AD\testgroup2). A new ...
1
vote
1answer
36 views

What impact on the index storage size does overprovisioning of varchar types have?

Does over-provisioning varchar columns (as in using varchar(100) to store values from 1 to 10 characters long vs using varchar(10)) have any impact on the index storage size and if yes than why? I ...
0
votes
1answer
28 views

Does Data Migration Advisor migrate system databases?

I would like to know if Data Migration Assistant copies system databases (master, model, msdb) into the new server.
0
votes
2answers
107 views

Schedule Full, Differential and Transactional Backup in SQL Server 2016

I studied about scheduling backups with SQL Server Agent and Maintenance Plans (which leads to the SQL Server Agent). My first concern is, what is the best way to schedule my backups? I want full ...
0
votes
1answer
49 views

Why are execution plans wildly different when selecting 1000 rows vs 2000?

Consider the queries below. The only difference is 1000 vs 2000 rows returned. SELECT TOP 1000 lwa.Message INTO #foo FROM dbo.LogWidgetsAPI lwa (NOLOCK) ORDER BY lwa.TimeStamp vs SELECT TOP 2000 ...
2
votes
1answer
29 views

Saved state for SQL Server Always On on Hyper-V

For our SQL DEV/QA environment our system-engineers did setup an Hyper-V enviroment - 3 hosts - 2 VM on each host (1 for DEV an 1 for QA) Patching for the hosts is scheduled once a month but I ...
0
votes
1answer
26 views

Moving SQL server 2016 DB's from mirroring to HA

We have Windows Server 2016 machines with SQL server 2016 standard edition. Currently mirroring is implemented and now are planning to move to HA with 1 listener for each DB. We have multiple DB's ...
3
votes
1answer
131 views

how to script out the user defined table types?

I can Get name and definition of all table types using either of the following scripts: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED IF OBJECT_ID('TEMPDB..#RADHE') IS NOT NULL DROP TABLE #...
0
votes
0answers
28 views

Importing BACPAC file from azure to SQL creating an empty database

Exported Data-Tier_application from an Azure database to create a BACPAC of it. SQL editions I'm trying to use to import the BACPAC: SQL Server 2016 express. SQL Server 2017 Developer ...
0
votes
1answer
68 views

Read Only Routing Does Not Work After Failover in SQL Server 2016 Always On [closed]

I have 2 nodes in an Always On Cluster - Node A and Node B I have configured read only routing. When Node A is Primary and node B is secondary , the read only routing works and read only connections ...
-1
votes
1answer
27 views

How to kill a stored procedure which is in open transaction?

I have a stored procedure which is running since four hours but it's uncommitted. How to check if it's running currently and kill it.
0
votes
0answers
38 views

How would you move data for only the tables that exist in the destination?

I have Database A (which has Tables a, b, c, d) on Server1 and Database B on Server2 (which has tables c, d, e, f). The tables are added/edited/removed at both locations randomly and may change at any ...
1
vote
1answer
56 views

TFS Installation Fails over to mirroring secondary despite not having a Fail-over partner specified

We have TFS Version 15.105.27412.0 installed on a server (Server A) and have it configured to point at our database server (Server B). We have the two TFS databases (tfs_defaultcollection and ...
3
votes
1answer
44 views

Is it normal for Snapshot Replication job to be constantly running?

Our SQL Server 2016 already has Merge replication running successfully for some tables in 2 databases. We then needed to restore Snapshot replication for another set of tables between same pair of ...
1
vote
2answers
77 views

Do long-running and locking queries on subscriber databases cause performance issues on the publisher dbs?

One of our servers has a subscriber database. I've not used replication before and cannot find information on how the mechanism works. Does the publisher have to wait on the replication completing ...
0
votes
1answer
23 views

SQL Server 2016 login language display is wrong

Even after changing the language for a login from Arabic to us_english it doesn't save it. It continues to show Arabic. The server default language is us_english. How do I fix this. Thanks !
7
votes
2answers
483 views

How can I see if the data in a SQL Server table is page-compressed?

This is a follow-on question to a question which I asked yesterday: Can I bulk insert into an empty page-compressed table and get full compression? The answer to that question (paraphrased from Randi ...
1
vote
1answer
24 views

Agent Job Running as Computer Domain Account Rather than Service account

I have two SQL Servers as follows Microsoft SQL Server 2016 (RTM-CU9-GDR) (KB4058559) - 13.0.2218.0 (X64) Dec 27 2017 17:10:01 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on ...