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
24 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
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
923 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] ...
0
votes
2answers
76 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 ...
2
votes
2answers
71 views

How do I resolve lock issue when replicating a table with over 16Billion records

I have two SQL Server databases that I am trying to replicate. I am able to replicate small tables without any issues. However I have a table with over 16 Billion rows that is quite problematic. I am ...
0
votes
0answers
18 views

SSIS Dynamically Loop through SQL Tables

I have been fighting with an SSIS package for sometime now. I did successfully build a package that will extract data from a specific table by creating a date list from a script task, use a ...
0
votes
0answers
14 views

How to purge old data in OperationsManagerDW 2016 database?

OperationsManagerDW (SCOM 2016) database size is around 850GB. The data which is older than 1year needs to be purged. How to purge the the old data? This instance have other databases as well. Is ...
4
votes
0answers
122 views

How to debug the cause of my suddenly extremely slow running backup maintenance plan? [closed]

Alexander and the Terrible, Horrible, No Good, Very Bad...backups. The Setup: I have an on-premise SQL Server 2016 Standard Edition instance running on a virtual machine from VMWare. @@Version: ...
0
votes
0answers
13 views

Function to create SQL TABLE does not works well using Powershell [migrated]

Hope you are well I'm going to explain my situation I need to get the values from a Excel Sheet (in a shared folder) to export it and then create a SQL Table in my instance. After of this I will ...
2
votes
1answer
867 views

Is it ok to change drive names/labels (NOT Drive Letters!)

I realize this is likely a simplistic question but my searches only return results for Drive Letters or Database names if I include SQL Server. Please feel free to point me to existing articles or ...
3
votes
0answers
101 views

SQL Server should I format with ReFS to get rid of operating system error 665? [duplicate]

I have 13 production SQL Servers Microsoft SQL Server 2016 (SP2-CU11) (KB4527378) - 13.0.5598.27 (X64) Nov 27 2019 18:09:22 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on ...
1
vote
0answers
23 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 ...
1
vote
0answers
45 views

SQL Server: Track new rows across all tables

I am in the middle of collecting data from SQL Server to other locations for processing and storing and I need to find a way to only SELECT new rows since last nightly data collection so I dont need ...
-2
votes
1answer
20 views

Trace flags 1117 and 1118 discontinued. does this mean it's not working anymore? [closed]

I was messing around on our servers and I could see that theres a lot of servers using T1117 and T1118 as a startup parameter. but I remember ( and I just read some posts from pinal ) that they're ...
4
votes
1answer
122 views

SQL Server 2016 suddenly produces Error:402,State:1,Class:16 updating a sproc with DATETIME/DATE/TIME arithmetic, that was fine before

In one of stored procs I need to capture the duration of a live event. It is guaranteed to never exceed 24 hours, so the datatype for the result column is chosen as TIME(3) (mapping to TimeSpan on ...
0
votes
1answer
48 views

Backup and restore of large SQL Server database with least downtime

I am trying to migrate a SQL Server 2016 database to a SQL Server 2019 server. The database is huge at about 500 GB. It takes about 3 hours to create a Full compressed Backup of about 300 GB and 2.5 ...
0
votes
1answer
25 views

Is there a way to setup one Alert for all Replication errors?

I have a SQL Server 2016 (Standard) instance, with DatabaseA, who is the Publisher in a Transactional Replication (push subscription) topology. A second SQL Server 2016 (Standard) instance, with ...
1
vote
1answer
54 views

Query to divide each column value to two categories: [Common , Not_Common]

I have a table with below structure and data: create table PTEST ( col_name VARCHAR(50), col_value VARCHAR(50) ) COL_NAME COL_VALUE ----------------------- first apple ...
-3
votes
1answer
58 views

Striping disks with MAXDOP 1

We're working with a vendor who is insisting we use MAXDOP = 1 (I know...). They are also emphasizing the importance of striping our data files across different disks. As I understand it, an I/O ...
0
votes
0answers
19 views

Availability Group Backups For Ola H.'s Software

Using the Ola Hallengren's DatabaseBackup software on a SQL Server 2016 AG. Trying to use copy-only and tran log backups on secondary node, with Backup Preference set to 'Prefer Secondary'. Given ...
0
votes
4answers
159 views

Slow CTE update query

I have a query that is running for more than three hours without completing. I realized that the bad part is the Filter operator which has a cost of 89%. WITH FASES_NUMERADAS_CTE AS ( ...
0
votes
0answers
26 views

Timeout issues MS Sql Server Management Studio?

We have Windows Server 2019 Standard 10.0 with a Microsoft SQL Server 2016 (SP2-CU17). This server is inside network OurCompany. I'm client desktop, I've been getting many Execution Timeout Expired ...
1
vote
0answers
43 views

Version store has reached its maximum size

I am attempting to restore a .bacpac file via powershell to sql server. Sometimes this works fine, other times it fails. I'm attempting this on a VM running in Azure. Windows Server 2016 Datacenter ...
1
vote
2answers
47 views

Correct way to continuously receive from a queue

I have to receive messages from a queue continuously. My current code looks like this: DECLARE @status INT=1 WHILE @status IS NOT NULL BEGIN WAITFOR (RECEIVE @status=status, ...
1
vote
1answer
53 views

Get user who ran Query MSSQL

Is there a way to obtain user who ran a query if I have the sql_handle, query_hash, plan_handle, query_plan_hash? Could I use any of these data points to find out the user who ran the query?
0
votes
1answer
19 views

Replicating to 3 databases one is web server

I have a customer that is looking for a very unusual replication configuration. I am hoping for some insight on why this is a bad idea, or even the possibility that it can be done. There are two ...
1
vote
1answer
37 views

We are using MSSQL 2016 / SSMS 18.9.2 and trying to script out a system versioned table with 2 hidden columns (is_hidden=1 is the sys.columns table)

When the table was created, we used this for the 2 columns in question: [EffectiveDateUTC] [DATETIME2](7) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL, [ExpirationDateUTC] [DATETIME2](7) GENERATED ...
0
votes
1answer
34 views

Can you omit a database from one replica of an Availability Group

Question: Is it possible to add a database to specific replicas of an availability group and not add it to another replica? Use Case: A production database is required for operations but does not need ...
0
votes
1answer
35 views

Is there any specific differences on table level integrity check between DBCC CHECKDB and DBCC CHECKTABLE?

I've faced with allocation error in one of our databases while running DBCC CHECKDB on it. It throws below-mentioned error: Msg 8947, Level 16, State 1, Line 5 Table error: Multiple IAM pages for ...
0
votes
0answers
17 views

CDC on secondary from different replication schemas - analysis

We have a mission critical database in Sql Server 2016 (Std) and we are not authorized to alter the schemas because of legal issues with the software provider. We need a way to have CDC in that ...
0
votes
1answer
104 views

SSIS: Extract SQL Data and Split across Flat files based on row count

I have created a simple SSIS package that queries a table and extracts data to a flat CSV file. In production this extract could be millions of rows and I want to split the flat file destinations into ...
0
votes
0answers
39 views

Restore sql server DB between Cumulative updates

Respected Sir, Please I want to know - Can we restore one database on version- 13.0.5888.11(5001092 Cumulative update 17 (CU17) for SQL Server 2016 Service Pack 2 Latest CU) To 13.0.5103.6 (4583460 ...
0
votes
0answers
35 views

SQL Server Replication - accidentally unselected table from snapshot: how do I add it back in again?

Using SQL Server merge replication, one of the tables was accidentally unselected (check box unticked). How do I add it back in to the snapshot again? It was unfortunately a large table with plenty ...
-3
votes
1answer
46 views

Expose SQL server externally [closed]

I currently have a sql server database and I want to be able to expose this over the internet so that someone external to my network can access the tables/data. How best can I achieve this and what ...
0
votes
2answers
40 views

changing SSMS settings via Powershell

in order to enable the dark theme to SSMS (not available on the picture below as you see): following this webpage: SQL SERVER Management Studio 18 - Enable Dark Theme - SQL Authority with Pinal Dave ...
1
vote
1answer
88 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. ...
0
votes
1answer
58 views

How to restrict Users to see only their databases where has access? [duplicate]

I need to restrict the list of database for our users in a SQL Instance In order to move forward this I revoked the view any database permission to the user desired. But I figured out that only ...
6
votes
2answers
526 views

Can a server wide setting of 'Max Degree of Parallelism' = 1 cause Brent Ozar's sp_BlitzCache to flag execution plan as 'forced-serialization'?

I am using Brent Ozar's sp_BlitzCache store procedure and I'm attempting to nail down why it is reporting: "Something in your plan is forcing a serial query. Further investigation is needed if ...
1
vote
1answer
35 views

Automatically expiring records in a temporal table?

I have a need to store some results that are only valid for a rolling 5 day window. An application will insert or update records and the results should be viewable for the 5 day window. However, there ...
1
vote
1answer
52 views

Distribute Guests over Rooms Evenly

I have a table of packages: create table Package ( PackageID int, Guests int, Rooms int ); insert into Package(PackageId, Guests, Rooms) values (1, 1, 1), (2, 2, 1), (3, 2, 2), (5, 3, 2),...
0
votes
1answer
79 views

Optimizer Not using Non Clustered Column Store Index for Update / Delete Statements

My Table tbl_FieldeData_ColStore Data Example: /NonClustred Column Store Index on Table/ CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI ON tbl_FieldeData_ColStore ( PDID,FieldID ) /Below Select was ...
0
votes
1answer
34 views

How to Ensure all Log backups are restored on secondary server in log shipping

I have some concerns regarding the restore job on secondary server in log shipping in Microsoft SQL Server 2016 for below scenario : I have two servers, primary and secondary. The backup, copy and ...
3
votes
2answers
52 views

Question Regarding ISDATE Function and DATENAME Functions

We have had an issue this morning with a report failing due to the following error: "Conversion failed when converting date and/or time from character string" The issue is that in the ...
-1
votes
1answer
63 views

SQL service stop date

is there a Powershell command or through windows to know since when my SQL service is stopped Thanks
0
votes
1answer
28 views

Help in querying hash PK and FK

I was trying to get the name of the PK and FK. However, this is the output that I get: PK: PK__adm_lib___3214EC067E4F7CCA FK: FK__adm_lib_S__fk_Co__7093AB15
1
vote
0answers
21 views

Can't delete secondary mirror DB stuck in (Mirror, Disconnected / In Recovery) [duplicate]

i'm facing an issue with a corrupted secondary database on SQL Server 2016. After a massive outage we were able to restore most of the databases immediately without issues. Two databases were in the &...
4
votes
3answers
298 views

How can we restrict tempdb usage for specific users or queries on SQL Server?

In our SQL Server production environment some users run ad hoc queries that run huge data extracts into tempdb and fills it up, causing issues on a production server. And as these reports/queries run ...
0
votes
0answers
46 views

Super complex Schema will not migrate to PaaS

Using a Dedicated 2016 datacentre SQL Server 2 vcpus, 14 GiB memory for testing. We have currently found a migration issue with the database Migration SQL 2016 to Azure PaaS. The transfer of schema ...
0
votes
1answer
34 views

SQL job sending email even if not configured to send notifications

I am using this version of SQL Server. Microsoft SQL Server 2016 (SP2-CU12) (KB4536648) - 13.0.5698.0 (X64) Feb 15 2020 01:47:30 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on ...
0
votes
1answer
24 views

SQL Mirroring Replace Certificates

I have inherited two SQL 2016 servers with two mirrored databases. They have been built with certificates as they are in a workgroup, the certs expire in six months. I am trying to understand the ...

1
2 3 4 5
51