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

Is a status column an effective column for partitioning?

We have a table that we would like to partition horizontally. Our goal for partitioning is to reduce maintenance time by only performing maintenance on the smaller set of newer data. Also, we would ...
user avatar
-1 votes
0 answers
30 views

SQL SERVER: High LATCH_EX AND CXPACKET Waits

Environment SQL 2016 Standard Edition Mem: 128GB Max DOP = 0 (default) Cost Threshold for Parallelism = 5 I'm trying to diagnose a performance problem using wait stats. I have a procedure which runs ...
user avatar
  • 11
15 votes
5 answers
3k views

Can ransomware embed itself in to a SQL backup file?

One of the best protections against ransomware is to back up all of your database files to a completely separate system. Which we have done. But one thought is the backup of the database could ...
user avatar
0 votes
0 answers
22 views

AG node getting "misaligned log IOs which required falling back to synchronous IO" events in error log

SQL 2016 AOAG. 3 node cluster. Primary and 1 secondary on-prem configured with synchronous availability mode. 1 node in Azure using asynchronous availability mode. We are using AOAGs to move data ...
user avatar
2 votes
2 answers
31 views

Find Which Queries will be affected by Proposed Change to CTFP

The Cost Threshold for Parallelism Setting on one of our servers is set at what is generally considered too low (15) and we are considering increasing to 50 in hope to reduce CPU as it is becoming ...
user avatar
  • 1,450
0 votes
0 answers
38 views

SQL statement runs fine in SSMS but does not work in SSIS SQL task

I have SSIS package which loads data to one of the table using SSIS sql task. it is using project parameter. However, it is not loading data into the table at all when package runs. I tried to execute ...
user avatar
  • 95
0 votes
1 answer
32 views

In Always on AG How can we setup jobs with a failover

In SQL Server 2019, We have primary and secondary replicas. if primary goes down then secondary will become the primary server, In such case how to handle the SQL jobs. Please help me to get it ...
user avatar
6 votes
2 answers
513 views

DateTime2 and Page Life Expectancy (PLE)

As I understand it, when you define a column on a table you define its precision. This precision takes 1 byte and is stored at the column level. If you use a precision of 5 or more, then a DateTime2 ...
user avatar
  • 2,394
4 votes
1 answer
51 views

SET SINGLE_USER WITH ROLLBACK IMMEDIATE only disconnects sessions when account is a database user

The following code creates a user that is able to restore a database: CREATE LOGIN RestoreUser WITH PASSWORD = 'MyPassword' ALTER SERVER ROLE dbcreator ADD MEMBER RestoreUser This works fine and the ...
user avatar
  • 1,450
0 votes
1 answer
25 views

What does it mean to reach a stable point during the execution of a job?

Trying to use sp_stop_job to stop a simple job that inserts data on a table when 100 rows were inserted I got confused to see the table end up with more than 100 rows every time. Under the Remarks ...
user avatar
  • 4,384
0 votes
1 answer
27 views

upgrading sql version with ssis and ssrs on that server

If I upgrade a SQL server 2016 STD to ENTERPRISE and that server has the following services installed: SSIS and SSRS can I just upgrade the version of the SQL and it will automatically upgrade the ...
user avatar
  • 325
1 vote
2 answers
46 views

SQL Server Backup Jobs Failing. The error seems to be related to MaximumErrorCount, but I am not sure how to fix value or if changing it will resolve?

My backup jobs are failing with following error when check it's history. I have already checked this link regarding maximumerrorcount value: SQL Server 2008 - Change the MaximumErrorCount or fix the ...
user avatar
1 vote
1 answer
50 views

How to modify the Replication Subscription Job/script to make it point an IP Address and a Port number?

I have an SQL Server 2012 server set up replicating one Database. It is behind a firewall. I have poked a hole in the firewall so I can access the server on Port 1433. I am using an IP Address and a ...
user avatar
  • 73
5 votes
2 answers
244 views

dateadd in the where clause - how can I avoid a full scan in this particular situation?

I have the following query: SELECT u.userId, app.applicationId FROM app.application AS app INNER JOIN app.applicant AS ap ON ...
user avatar
5 votes
1 answer
205 views

Why are the majority of cached plans missing from sys.dm_exec_query_stats?

I'm trying to understand some execution plan caching metadata on a SQL Server 2016 SP3 system, and I can't reconcile what I'm seeing with the docs. The docs for sys.dm_exec_cached_plans say it ...
user avatar
  • 27.1k
1 vote
1 answer
57 views

How to avoid deadlocking in a stored procedure with SERIALIZABLE isolation level, INSERTs, UPDATEs, and DELETEs

I'm having trouble with deadlocks in a stored procedure. This stored procedure is part of a custom full-text-like search system. It's called after a record is updated, and inserts/updates a list of &...
user avatar
  • 129
0 votes
1 answer
81 views

In one of the Table index size is 1.6 TB, we are not able to Rebuild that index

In Sql server 2017 Enterprise Edition, one of our Clustered Indexes is more than 1.6 TB. We are unable to rebuild this index, it is failing while rebuilding due to log file full. We have added ...
user avatar
0 votes
2 answers
26 views

DBCC shows corruption (test db) but suspect_pages shows nothing

So, I got a corrupted database. This is the DBCC: Msg 8944, Level 16, State 13, Line 4 Table error: Object ID 2105058535, index ID 1, partition ID 72057594038845440, alloc unit ID 72057594039762944 (...
user avatar
  • 7,100
5 votes
3 answers
883 views

Patching SQL Server 2016

Most of my SQL Servers are on 2016 SP2. The security group have identified that I need a later security update applied and as SP2 is out of support this year I planned to just patch it up to SP3. The ...
user avatar
  • 63
2 votes
1 answer
33 views

Isolation Level Conflict

Hi i was asked to look at a third party app recently. There were a few queries doing giant reads and that was easy enough to fix. However, while i was working on the queries i kept seeing different ...
user avatar
1 vote
1 answer
61 views

dbcc checkdb errors, or msg 601 when restoring SQL 2012 backup to SQL 2016

I need to move two databases, WES and WILL from a Win2012/SQL2012 instance to a Win2016/SQL2016 instance for a software upgrade. I'm using the full backup files from the SQL2012 maintenance plans. I'...
user avatar
  • 11
1 vote
1 answer
60 views

Can you backup a replicated database?

I would like to backup a publication SQL Server database that is being replicated with transactional replication. Would it cause any issues (with replication or any other issue) if I took a backup of ...
user avatar
  • 253
0 votes
1 answer
31 views

View to insert records into history table and then find changes with Except query

I want to insert records to a history table and show the changes from the previous day in a view. The script works, but when I try to create a view I get an error message that incorrect syntax near ...
user avatar
  • 5
1 vote
2 answers
220 views

How get old versions from MS SQL Developer Edition?

I'm trying to get SQL Server 2016 Developer Edition but when I access to Microsoft Dev Essentials page I only can see Express Edition versions: And when I visit SQL Server Download page I only can ...
user avatar
0 votes
1 answer
69 views

upgraded the database from 2012 to 2016 and specific query runs slow

I successfully restored a database from SQL Server 2012 to SQL Server 2016. Now some queries are running slow. Especially this one. SELECT * From DayCalDupLocGradeDayVW A Where A.DistCode = '...
user avatar
-1 votes
1 answer
49 views

Where is the Installation Media needed to perform a Repair on a SQL Server 2016 installation?

I have a SQL Server 2016 Express installation that I recently performed an edition upgrade on to make it a Standard edition so that I could have access to SQL Agent. After performing the edition ...
user avatar
3 votes
1 answer
183 views

Does Query Store force plan persist following server restart?

Would like to ask because cannot find this information. Does Query Store force plan persist following server restart? In which cases will forcing a plan via query store not remain in effect?
user avatar
  • 307
0 votes
0 answers
88 views

Linked Server Error Msg 7321, Level 16, State 2, Line 1

I am getting the following error when I execute a simple linked server query. OLE DB provider "MSDASQL" for linked server "name" returned message "HTTP request returned status ...
user avatar
0 votes
1 answer
80 views

Shrink Log file in Always ON DB

We have Sql Server 2016 and set up Always ON with 2nd replica (Primary and Secondary) Currently looks like Log file filling up, getting disk pace full. I tried to resume the secondary database which ...
user avatar
  • 11
4 votes
2 answers
236 views

SQL Server - Trace flag 8041 - no documentation

Has anyone encountered trace flag 8041 in the wild, and can you explain what it does? I inherited a SQL Server 2016 (13.0.5865.1) and there are some trace flags enabled. One of them is 8041. I've ...
user avatar
1 vote
2 answers
110 views

Memory utilization by SQL Server 2016 [duplicate]

I am running a SQL Server 2016 Enterprise Edition on Windows Server, with 128 GB of memory. During installation, I assigned 124 GB to the SQL server, since the server is dedicated to SQL only, leaving ...
user avatar
  • 11
2 votes
4 answers
196 views

NONCLUSTERED index seek on mslid is slow

I have an issue in SQL Server where a NONCLUSTERED index seek is performing poorly. Below is the actual execution plan https://www.brentozar.com/pastetheplan/?id=Sk3-4JGAK How can I enhance ...
user avatar
0 votes
0 answers
20 views

on line date dimension table for sql server 2016

I am quite old school and for date dimension table with the usual stuff in like date, day of year, week number, month, month name, quarter, year etc I create a static table and every month increase ...
user avatar
  • 227
-2 votes
2 answers
67 views

How to use IN with string comma separated

I have value in table1 like this: Value ------- JT, KF, LF On select in other table how can I get a value like this Select a.Field from table2 Inner Join Table1 on Table1.field = Table2.field Where ...
user avatar
-1 votes
1 answer
40 views

Best method for isolating long running locks?

On a SaaS production database with 7 users (5 human, 2 backend) many are experiencing SQL cliënt timeouts at 30 seconds, our standard .Net CommandTimeout. This SQL Server instance (out of our ten) ...
user avatar
  • 59
0 votes
0 answers
40 views

SQL Redistribution Logic

I have modified the query based on the requirements of this group. There is a create and insert table query at the bottom of this post to help create the required table. The expected result is also ...
user avatar
1 vote
1 answer
89 views

Sql addition query

If the sum of a group comes as negative. The negative numbers are to be increased (biggest negative first) to bring the total sum as 0 Example Group 1 has this 4 values -4,-3,1,1 The total is negative ...
user avatar
0 votes
0 answers
60 views

Is a columnstore index appropriate when all columns are (mostly) unique?

I have a longish table ( 60M rows ) where there are only 7 columns. One is a unique ID, two are datetimes, and two are notes and descriptions. There the notes and descriptions are very regular, except ...
user avatar
  • 101
2 votes
1 answer
22 views

Set DB to single-user mode, now I cannot open it in SMSS

In order to allow me to rename a database I set it to single-user mode. The rename worked fine but now trying to open the DB or even view its properties gives me connection errors. I've tried ...
user avatar
  • 121
0 votes
0 answers
533 views

Linked server error- The table either does not exist or the current user does not have permissions on that table

There is a view that is being called from Server A (SQL 2016) and this view needs to read data from Server B (SQL 2014). There is a linked server set up between these two servers and the connection ...
user avatar
0 votes
1 answer
68 views

Why is the result of DATABASEPROPERTYEX not consistent for AdventureWorks?

I'm using the function DATABASEPROPERTYEX to check the Recovery property of databases, but the result is wrong for the database AdventureWorks (later I noticed the same strange behavior for ...
user avatar
  • 4,384
2 votes
3 answers
247 views

Constant rebuilding indexes to fix performance - why?

We have a database server (2016 SQL Server), that we have added a step of 'rebuilding indexes' to the deployment process. In decades of working with MS SQL Server at a many companies, I've never ONCE ...
user avatar
0 votes
1 answer
53 views

Importing CSV Flat File - how can we change defaults of all columns to n characters

I have to import a CSV file into a table that has 75 columns. The Flat File connection defaults all the columns to 50 character strings. Some of the fields are much smaller and some are 255. The only ...
user avatar
  • 101
5 votes
1 answer
176 views

Door Number 1, 2, or 3?...Security change, bug, or failed upgrade with an in-place upgrade from SQL Server 2016 to SQL Server 2019?

What I Did: Recently I tested doing an in-place upgrade on ServerA, a development server that was running SQL Server 2016 (SP 2 CU17) Standard Edition to SQL Server 2019 Standard Edition. (I know this ...
user avatar
  • 24.1k
0 votes
1 answer
134 views

Ensure that SQL server "avoids" the clustered index when a non-clustered index can be used

I have a query, the short of which looks like the following. While this is not exact, it should show how the problematic behavior is introduced and what I am looking to prevent. And "prevent"...
user avatar
0 votes
1 answer
53 views

How to identify primary/secondary AOAG when running jobs in certain databases?

we have 3 servers, 1 one of them host a primary and a secondary aoag, 2 also hosts a primary ( secondary on node 1 ) and a secondary (primary on node 1). 3 is just DR. All of them have the same jobs. ...
user avatar
  • 7,100
4 votes
1 answer
239 views

Monitoring SQL Server Encryption With Extended Events

Is there a way to monitor for encrypted connections through Extended Events? I've monitored for TLS 1.0, 1.1 and 1.2 handshakes in the past using sqlsni.TRACE but cannot find something along the same ...
user avatar
0 votes
0 answers
44 views

Multiple Date rows into One single Date row(date range)

An employee was sick during different period of time, when there is not a single day difference between the multiple dates range, or the date is continuous then it should be shown in one row covering ...
user avatar
  • 1
1 vote
1 answer
97 views

Error Severity 16 Alert being raised for events where is_event_logged = 0

I have an error level 16 alert set up as follows: USE [msdb] GO EXEC msdb.dbo.sp_add_alert @name=N'Error - Severity 16', @message_id=0, @severity=16, @enabled=1, @...
user avatar
  • 1,450
0 votes
1 answer
66 views

SQL Server Distributed Availability Group databases not syncing after a Global Primary Replica Failover

SQL Server Distributed Availability Group Environment AG1 => Node1 (AG1-Global Primary) and Node2 (AG1 - Secondary Replica) AG2 => Node3 (AG2-Forwarder) and Node4 (AG2 - Secondary Replica) DAGE ...
user avatar

1
2 3 4 5
53