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
1answer
63 views

Tracking incrementing id's for hundreds of thousands of objects

We have an ever growing list of guid's that we need to be able to track an ever incrementing list of integer id's for. Our current implementation creates a sequence with no cache for each guid. ...
1
vote
1answer
32 views

How to track or understand if a "timeout" was caused by SQL Server issues or network, server and etc?

This is a question that haunts me for ages. every job I got I see plenty of people complaining for the database administrators about "timeout" errors: System.Data.SqlClient.SqlException: ...
1
vote
2answers
2k views

ODBC Driver for Salesforce: Access Linked Server Object in SSMS

I have problems to access a linked server for a Salesforce ODBC data source with our MS SQL Server 2016 (Standard 64-Bit) using the Devart ODBC Driver for Salesforce in SSMS and SQL Studio Management ...
2
votes
1answer
63 views

SQL Server log_reuse_wait_desc is ACTIVE_TRANSACTION but there are no active transactions

Just had an issue with a SQL Server 2016 database where this query returned ACTIVE_TRANSACTION for log_reuse_wait_desc: select name,log_reuse_wait_desc from sys.databases where name='database name' ...
3
votes
1answer
238 views

Upgrade and migrate SQL Server 2014 AlwaysOn AGs to SQL Server 2016, using the existing WSFC name

Here are the components: WSFC Name: SQLPROD1 Existing Nodes in this "cluster": SQL2014OnSite and SQL2014OffSite New Nodes to be added (?) to SQLPROD1: SQL2016OnSite and SQL2016OffSite All 4 servers ...
1
vote
1answer
461 views

Generic log-table for CRUD operations that support both single and composite Primary Keys

I'm trying to come up with a decent table design for a table that will log CRUD operations for a small application. There won't be a lot of traffic, which could justify a single table for all events. ...
-4
votes
0answers
24 views

SQL SERVER 2016 SP1 Directly to SP3 [duplicate]

Can I move directly from SQL Server 2016 SP1 to SP3 or do i need to install SP2 then go to SP3. I was assigned to upgrade my server 2016 just to make sure I don't fall into problems
0
votes
0answers
52 views

SQL Server linked server query issue

I am using SQL Server 2016 and recently one of the stored procedures I have been using is throwing error. I use a select statement with joins on many tables to the remote server using a linked server ...
0
votes
3answers
41 views

How does enabling instant file initialization lead to information disclosure (when SQL server encrypts the data at rest)?

How does this lead to information disclosure when SQL server encrypts the data at rest?
-2
votes
1answer
58 views

What is the best way to order Partitioned table?

What is the most efficient way to get ordered data from a partitioned table? I have this query: SELECT TOP (4000) * FROM dbo.MS ORDER BY MANDT , MBLNR , MJAHR , ZEILE Table is partitioned by MBLNR. ...
0
votes
0answers
45 views

Created an index for a table, adding just one more column, and SQL still suggests a new index

I got a recommendation for SQL Server for this query: SELECT field1 FROM table WHERE ( field2 < getdate() - 2557) Obviously the recomendation was CREATE INDEX idx_name on TABLE (field2) INCLUDE (...
2
votes
1answer
68 views

Load assembly from a certificate in SQL Server

We received an assembly from external vendor as CREATE ASSEMBLY MYCALC_DLL AUTHORIZATION dbo FROM 0x42A728....<300,000 binary values> WITH PERMISSION_SET = UNSAFE GO And this DLL is been ...
1
vote
1answer
40 views

SQL Server 2016 Standard Edition TDE

I have Standard Edition of SQL Server 2016. Is it possible to enable TDE? I understand that it was an enterprise feature but it is now a feature for SQL 2019 Standard and therefore I wondered if there ...
1
vote
0answers
109 views

How to determine the Tipping Point of which Physical Join Operator gets used to process a query?

I've been noticing some strange occurrences lately on my development SQL Server instance where the Nested Loops Physical Join Operator is used when a Hash Join would be much more efficient, within a ...
2
votes
3answers
146 views

Large database partitioning and backups of only updated data (partial backups)

I'm setting up a large SQL Server database (2-4tb), but only a small percentage of it will be updated. I'd like to partition by year so that my backups can be small. HOWEVER, some of the old data may ...
1
vote
1answer
45 views

Azure Data studio - connection to server in the same network - Error: The received certificate has expired

I am trying to connect to SQL server on my network with Azure Data Studio, and I am getting error: A connection was successfully established with the server, but then an error occurred during the ...
0
votes
2answers
965 views

How to rotate the CMK of Always Encrypted using SSMS?

I have implemented the encryption in SQL Server 2016 using Always Encrypted. I want to know how to rotate the column master key after the initial one year validity using SQL Server Management Studio ...
0
votes
2answers
551 views

Side By Side Upgrade and Migration from 2008 to 2016

Side By Side Upgrade and Migration from 2008 to 2016 Old Server Name Test1 New Server Name Test1 Default Instance Test 1 My question is when the new server be named as the old one 'Test1" and it ...
-4
votes
0answers
42 views

SQL SERVER 2019 resource locked and ram utilization still constant [closed]

Migrated from Sql Server 2016 to 2019 after migration with the increasing load sql server is blocking the resources and ram utilization is not increasing but CPU is going to 100% but in 2016 same ...
0
votes
0answers
21 views

SQL Server 2016 - There was a failure to calculate the applicability of setting INSTALLSHAREDDIR

I am trying to install new instance on the server with already installed instance but short after running the setup.exe I am getting the error: SQL Server Setup has encountered the following error: ...
0
votes
5answers
72 views

Identify how many CPUs our database server is using

Our server where the Microsoft SQL Server 2016 Web Edition server is installed is running very slow. We are thinking of increasing the number of cores licensed on it. However, we need to check how ...
5
votes
3answers
146 views

Alternate Solution For REPLACE() In ntext Column

Overview I have a picture database that catalogue information of pictures taken with cameras. The database contains a table pictures which has 256 columns which contain information about the picture ...
0
votes
1answer
508 views

Importing a .bacpac from SQL Azure into a local SQL Server 2016 server gives errors

I'm trying to import a .bacpac created from SQL Azure into a SQL Server 2016 server that has no existing databases beforehand. It will run but run into issues such as: Unhandled Exception: ...
1
vote
1answer
17 views

How to rebuild Full Text indexes after MSSQL upgrade from 2014 to 2017

When I updated from 2014 to 2017 I chose the option to migrate Full-Text Indexes instead of rebuild them during the update. The update went well, and I'd like to repopulate those indexes now, but I'm ...
2
votes
1answer
216 views

SQL Server using index scan instead of seek, all of a sudden

We have a stored procedure that runs daily. In one of the sub queries (query 20, in the execution plan), SQL Server was using an index seek till 2021/09/24. But all of a sudden when the query ran on ...
1
vote
2answers
2k views

SQL Server 2016 - Configuration Manager WMI Problem

Background: Windows Server 2016 Core SQL Server 2016 Enterprise (obviously 64-bit) Windows Failover Cluster SQL Failover Cluster I am attempting to connect to SQL Server Configuration Manager via my ...
0
votes
1answer
739 views

Unable to connect to Listener after rebuild

I dropped an AG with DROP AVAILABILITY GROUP [MyDb] it had a listener. I left the listener open in the object Explorer of SSMS 17.3. While the AG and Listener were down. I hit refresh on the ...
0
votes
1answer
600 views

Migrating Oracle Views to SQL Server

In the process of retiring an Oracle 11g host for an HRIS database, I've been asked to migrate as many assets as possible to a SQL Server 2016 instance for read-only archival. The volume of table ...
1
vote
1answer
100 views

Ola hallengren SQL Server Index and Statistics Maintenance solution

We are using the Ola hallengren SQL Server Index and Statistics Maintenance solution from the past 6 months in our production system. Script is only used for Update Statistics not Index maintenance. ...
2
votes
1answer
95 views

Why does adding a WHERE clause break my working query, with error "Query processor could not produce a query plan because of the hints defined..."?

I have the following (stupidly-simplified) query that leverages two CTEs that refer to the same table and joins them to each other: WITH CTE1 AS ( SELECT dbo.RemoveNonNumericCharacters(PhoneNumber)...
1
vote
1answer
1k views

Cannot deploy SSIS packages after upgrade to SQL Server 2016 SP2 Build 5201.2

We recently upgraded from SQL 2016 SP1 build 4466 to SP2 build 5201 (CU2 GDR) on an AG. On one server an analyst was deploying SSIS using Visual Studio Enterprise 2015 (version 14.0.25431.01 Update 3 ...
5
votes
1answer
670 views

How do I reclaim space in filestream data files for database in a SQL Server Availability Groups

I have a SQL 2016 Ent. Edition AG with 3 nodes. We have a database in the AG with 5 filestream tables. Each table is in it's own filestream data file. Today I fixed a bug where all of the files were ...
0
votes
2answers
482 views

Maximum Error Count Reached

I created a maintenance plan to backup all the databases locally on a folder in E Drive. Out of nowhere, the job is failing occasionally. sometimes the job runs successfully and sometimes sends an ...
0
votes
0answers
56 views

SQL Server - BackupDiskFile - Referencing to deleted TR Backup file [duplicate]

We have a backup job which creates transaction log backups every 10 mins for databases. Backup files which older than 2 days are deleted. In ERRORLOG file - I see the record which I cannot get/...
0
votes
0answers
23 views

Does collation effect SQL Server replication

I've been getting a replication error for some time now between my Publisher and Subscriber databases (SQL 2016). The error is the classic: "42000 The row was not found at the Subscriber when ...
0
votes
0answers
59 views

Multiple SQL instances on the same server and how to manage TempDB

Probably a bizarre approach but I don't really have much say in the setup. We have a large server deployed in Azure and the plan is to get 16 separate instances on this machine. We plan on using the ...
4
votes
1answer
2k views

Is it possible to use cross database queries in both Azure and SQL Server 2016?

I'm working on a project that is designed to run on Microsoft's Azure platform. At present we have a few developers working locally on their own machines with the intent of being able to publish the ...
0
votes
2answers
68 views

What are the more important columns to include in NONCLUSTERED INDEX - from Group by or Where?

I am thinking about adding a NONCLUSTERED INDEX to a table in SQL Server 2016. I found that most queries refers to WHERE clause like: WHERE varcharColumn <> '' and varcharColumn2 = '2' and ...
0
votes
0answers
36 views

Why is the FILESTREAM data not accessible, after AG listener is created?

I'm having a hard time figuring out why my app can't access filestream data, after we enable AG listener. Background info: Initial environment: APP Server Web Server IIS Windows Server 2012 R2 DB ...
0
votes
1answer
26 views

SSIS debuging tool in SSMS 2016

I want to trace the execution and see the changes in variables in the package during the runtime. the Microsoft report tool for the SSIS package is not sufficient. I want more details and don't have ...
0
votes
1answer
197 views

How to update JSON using logic equivalent to WHERE clause

My question is similar to another (and also another for postgresql) except in the other question the person wants to update values for a specific key. In my case, I want to detect elements having a ...
0
votes
1answer
113 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
1answer
98 views

Comma separated value string and compare against other values

Hey all I have been trying to wrap my head around how to go about doing this for a while now and I'm not really coming up with anything. Here is my query: DECLARE @ATID AS VARCHAR(3) = 12 DECLARE @...
0
votes
0answers
24 views

SET FAILOVER PARTNER command gives A severe error occurred on the current command when not run as sysadmin

I am trying to create an account which has permissions to start a mirroring session between two servers. Some Googling suggests that the permissions an account needs to start a mirroring session is ...
12
votes
1answer
956 views

Seemingly identical WHERE clauses returning different results

I have a table named [User]. Below is a simple query to find one user from the table: SELECT [Username] ,[FirstName] ,[LastName] ,[EmailAddress] ,[IsStaff] ,[ExternalID] ...
2
votes
1answer
746 views

Connection's MAC address randomly changes on Windows 10, then stops

Because reasons (tm), we want to identify the client's MAC address on SQL Server side (local network, not about security, enables a very convenient feature in way transparent for applications). We do ...
0
votes
2answers
81 views

tune WHERE datediff

My query is running a little slow and I think it's because of the where datediff function. WHERE DateDiff(minute, TB1.stDate, getdate()) > 50 (With this approach at least is using the index I ...
0
votes
1answer
343 views

Security user audit

This has probably been asked a million times already and for this i apologise, but is there a way or any T-SQL I can run that will tell me, the server, the database, users login name, what permissions ...
0
votes
1answer
1k views

Drop a replicated subscription table that has been orphaned after restore migration

We restored a database on a new server for a migration. When attempting to set a subscription to a transnational publication, we get the error: Can't drop the table because it is set for ...
0
votes
1answer
314 views

Tables not showing up when linking SQL Server to Access using ODBC connection

I am learning to link the back end of a database in SQL Server express 2016 to the front end in Access 2016. The tables were originally in Access and then I exported them to SQL Server. Now I am ...

1
2 3 4 5
51