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
18 views

What are the implications of creating an Integration Services Catalog (SSISDB) where msdb-deployed packages exist?

I have inherited a production 2016 SQL Server with SSIS installed, which has many SSIS packages deployed to msdb (mostly warehouse ETL workloads that run nightly). I would like create a new ...
1
vote
2answers
23 views

in-place upgrade native SSRS without the SQL Server portion

We have a single server (2012R2) running SQL 2008R2 + reporting services native mode 2008R2 Can the reporting services portion be upgraded alone without the database engine? Or can the SSRS db be ...
4
votes
1answer
221 views

DATEADD and DATEPART with milliseconds yields strange results

I have a table containing the date in unix time and a separate milliseconds field. I now try to create a date from the two fields for later calculation (e.g. filtering on a time range). After adding ...
0
votes
1answer
40 views

How To Tell if Trigger is failing

I have a trigger, that I want to try to update a field based off the value of another table. I have manually verified that the data exists in the other table so I know the join will work. However, ...
0
votes
1answer
64 views

SQL Server Database size - doubled running DBCC

I was running a dbcc command using ola hallengren checkdb on a large database (775Gb) when half way through the process the tempdb ran out of space. I have run this in other environment on different ...
0
votes
0answers
14 views

Missing records in cdc capture instance - MSSQL Server

We have a Source table (MSSQL Server database) and cdc is enabled on it. Most of the time cdc captures all changes that happened in Source table correctly, but time to time changed records are missing ...
5
votes
0answers
417 views

In SQL Server, what is “Partitioned Table Parallelism” that is only available in Enterprise Edition?

I am reviewing the differences between Standard and Enterprise editions of SQL Server and can't reproduce the differences advertised in this demo that explains the differences - the performance I ...
0
votes
0answers
21 views

SQL AOAG QUORUM clarification

Setup: SQL AOAG(SQL ALways On Availability Group) PDC(Primary Data Center) has 2 Nodes(PDCDB01 and PDCDB02) DRC(Disaster and Recover Center) has 1 Node(DRDB01) FSW(File Share Witness) Quorum ...
8
votes
1answer
947 views

Is the query optimizer able to optimize away IS NOT NULL conditions if the column has a NOT NULL constraint?

I am trying to work out ways to improve the performance on a very slow query. It has more obvious problems but one thing I noticed is that one of the conditions in the WHERE clause is 'AND t.data IS ...
0
votes
0answers
35 views

SQL Server 2016 logins and roles - non english names

We have English versions of Windows 10 Pro and SQL Server 2016 and third party software (building automation) for reports. That software requires "NT Authority\SYSTEM" to be in English not ...
0
votes
1answer
88 views

Query takes long time to execute while there is an index configured

We have a table with 190M records. there is an index on a fairly unique column but still, if I run a query on the table selecting top 10 with only the indexed column in the where and select clause it ...
0
votes
0answers
53 views

PFS Corruption after restoring SQL Server 2012 database to 2016

We migrated few SQL Server 2012 databases over to SQL Server 2016 over the weekend. Old Server Details SQL Server 2012 SP4 11.0.7493.4 DB set to compatibility 110 TDE Enabled - Yes Recovery Model is ...
2
votes
0answers
87 views

Replication error masked by sp_MSdetect_nonlogged_shutdown returning 'String or binary data would be truncated'

One of our replication jobs is failing on step 3, which runs sp_MSdetect_nonlogged_shutdown, returns a 'String or binary data would be truncated'. Which of course led us down the path of trying to ...
0
votes
0answers
13 views

Second Instance on SQL 2016 server - SQL Browser not seeing instance?

I just installed a second SQL instance onto a development server. The first instance is remotely accessible from a SSMS session from my PC, but the second instance is not. I have the server set up ...
0
votes
1answer
33 views

Handling Reporting Services and SharePoint Integration considering deprecation

With one of our clients, we're currently faced with a dilemma. SQL Server Reporting Services Report Server (SharePoint mode) seems to have been deprecated, since the last version to support it is SQL ...
0
votes
0answers
19 views

Sampled rows is very less for auto update stats in SQL Server 2016 [duplicate]

I have SQL Server 2016. One of the table is having 9.24 million records. One of the stats (no filter on stats) on it is getting auto updated by SQL Server with sampling rate of 1 % (120023 rows). As ...
0
votes
0answers
27 views

Best way to track data changes where there are no historical items - CDC or custom programming?

SQL Server 2016 Standard. We're being asked to provide data that we just don't have. I'm looking for a way to obtain it. We have an ERP system that has order statuses that are important to our ...
2
votes
1answer
106 views

What is the difference between `ALTER TABLE DISABLE TRIGGER` and `DISABLE TRIGGER`?

My company logs DDL on one database using a Database Trigger, looking for DDL including: ALTER_TABLE CREATE_TRIGGER ALTER_TRIGGER DROP_TRIGGER When I disable or enable triggers using ALTER TABLE x ...
0
votes
1answer
20 views

Transaction backup plan and size [duplicate]

I am using SQL Server 2016. I have setup SQL Agent for Full backup (Weekly) and Transaction Log backup (Daily). Backup transaction log into a single file using: BACKUP LOG [XXX] TO DISK = N'E:\SQLDB\...
0
votes
1answer
24 views

Select from a table where column row has Zero (0) or Zeros (000000)

How do I select from a table column row where only the result set should contain a table column rows where there are Zero(0) or Zeros(00000) and Not any other. e.g from the table below: i want rows 1,...
1
vote
2answers
78 views

Multi-Statement Table-Valued Function with a Cursor: How to Check If The Cursor is Still Open On Error?

I have a stored procedure preparing a dataset using an employee listing as base query and performing a CROSS APPLY to a MSTVF to build a small resultset per employee. In the MSTVF, a cursor is used to ...
1
vote
2answers
43 views

SQL server AlwaysOn alternate option for readintent=true

I know applications need certain(newest) version of ODBC drivers to connect an AlwaysOn SQL instance replica and to use the option "applicationIntent= readonly" Now my question is , if some ...
1
vote
0answers
53 views

CPU load detalization does not work in SSMS Performance Dashboard

Starting from some version (it's actually earlier than the documented 17.2), SSMS has the Performance Dashboard built in, so you don't have to download and install it separately. Great. We have SQL ...
0
votes
0answers
98 views

Debugging T-Sql code using Visual Studio 2019. Error message: frame not in module

DBAs, Has anyone had success using Visual Studio 2019 for debugging T-SQL code, the way SSMS allows Step-into & Step-over? When I hit debug (Alt-F5), gives me this error, after making some ...
0
votes
0answers
58 views

Why do select Queries create additional queries to sys tables

I have an external application querying our database with an SELECT statement via Linked Server. When I check Queries with SQL Server Profiler - I see that these queries are accompanied by an enormous ...
4
votes
1answer
344 views

How do I prevent my Windows Server from being automatically rebooted by SQL Server updates?

Some SQL Server updates have happened outside the maintenance window, which have caused the server to be rebooted. How can I stop this from happening again in future? Below is what I can see when ...
2
votes
1answer
61 views

Performance tuning of Purging table Data in SQL Server 2016

I have to Automate the Purging huge volume of data from main table to Archive. Steps I am following : I am inserting the data from main table to archive table. After cross verifying the count in ...
0
votes
0answers
29 views

SQL Failover and Failback Script for SRM Recovery Plan(preferably power shell or CMD or Batch script)

OS: Windows Server 2016 Standard SQL: SQL Server 2016 Enterprise SQL Setup: Two Nodes(Primary and Secondary Node) in PDC(Primary Data Center) having Always On Availability Group with Synchronous Mode ...
1
vote
0answers
30 views

SQL Server 2016, history temporal table not compressed by default in Standard Edition

I have a BI database running on SQL Server 2016 Standard SP2 CU14 (13.0.5830.85) in which there are multiple temporal tables defined with default history tables. According to Microsoft, such history ...
0
votes
1answer
75 views

How can you split multiple delimited strings of varying sizes into rows?

I have a situation where for each ID in my table there are multiple fields that each contain a comma-delimited string. The strings don't necessarily have the same number of elements as one another. ...
0
votes
0answers
20 views

Upgraded DB Driver SRVSQL High Waits PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICY

Upgraded our PHP SQL driver and now the Top 3 waits are below? Wait_Type Wait_Time_Seconds Waiting_Tasks_Count Percentage_WaitTime PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICY 5.551000 53732 21....
1
vote
0answers
55 views

service broker queue index growing beyond control

We have a queue that was set up about ten years ago in 2008. We have been running it on an Azure VM Standard edition sql 2016 for about 3 years no with no problems. In the last 3 months, with no ...
2
votes
1answer
30 views

Select query slow in the big table because of expired stats in Microsoft SQL Server

I have an application to select/search records from the database (application and database in separate servers). In the database, one table only is fast growing table (more than 2 Millions per day). ...
1
vote
2answers
65 views

Creating an Index based on Execution Plan

I've been rewriting a view that was performing poorly - it had 28 LEFT JOINs to another view and I have managed to modify this to a single LEFT JOIN and we are seeing a pretty good improvement in the ...
6
votes
1answer
188 views

Is there any side effect if select statements are wrapped in BEGIN TRAN … COMMIT?

I am working on a rather old .NET project and have introduced some new features (on top) that have generated the following side-effect: all generated SELECTs (or groups of) are wrapped in BEGIN TRAN .....
0
votes
1answer
26 views

MS SQL Server 2016 - Database Security - Login Permissions

I have an SQL Server 2016 database with Always On structure. I have a login that I called x. My x login's properties are: Default database: abc Server roles: public (ticked) User mapping: Map (ticked)...
1
vote
0answers
29 views

sp_prepare vs sp_execeute PHP MSSQL

This question is regarding the PDO_SQLSVR driver for PHP and Microsoft SQL Server and how it interacts with the database, whether via sp_prepare (prepare statement execution) or sp_execute (direct ...
0
votes
0answers
48 views

Large number of connections to an SQL server results in read write bottleneck on hard drive?

I have the following problem which I can't find a solution for. We have a learning School System for more than 10000 students that upload photos and PDF of Homeworks as binary to an SQL server. During ...
1
vote
1answer
45 views

SQL Server 2016: Table can't grow while 313 MB is still available in the database

Yesterday, the below error was reported: Could not allocated space for object 'dbo.X'.'Y' in database 'Z' because the 'PRIMARY' filegroup is full After deletion of some records from the table by an ...
0
votes
1answer
52 views

MS SQL Server 2016 - Always On Node-2 Stuck In Resolving State

For testing the Always On structure; we suddenly shut down the machine on which Node-1 was running. So we wanted to see that Node-2 turns primary. However, Node-2 was stuck in resolving mode. 3 ...
0
votes
1answer
217 views

How to check who has permission to execute a stored procedure?

I have two environments/servers. Each has two databases, say DATABASE_A and DATABASE_B. There is a stored procedure in DATABASE_A (in both environments) that queries DATABASE_B and also calls multiple ...
0
votes
1answer
45 views

SQL database mail configuration to use a domain account that is not the SQL Service account

We have been asked to use a domain service account for everything releated to email (smtp). I looked at the SQL Database Mail and the only option that looked like Windows Auth. is the first one: The ...
2
votes
3answers
69 views

Query to find sql server services currently running [duplicate]

Is there any query to find all SQL services currently running on the server including SSRS, SSIS, SSAS?
0
votes
2answers
182 views

SQL Server 2016 -> 2019 AlwaysOn Rolling Upgrade Migration

I have a migration concept with an AlwaysOn configuration from SQL Server 2016 (on windows server 2016) to SQL Server 2019 (on Windows Server 2019) which are in different sub-networks. So with ...
0
votes
1answer
56 views

Install old Cumulative Update SQL Server 2016 Standard

We were running on SQL Server 2016 Standard Edition 2016 SP2-CU4 on our ALWAYS-ON HA cluster. Due to some recent issues one of our nodes got corrupted and we had to replace it with a new node. Since ...
0
votes
2answers
157 views

Grant an AD Group permission to edit all MSSQL Agent jobs

We have a team of people, we all log in MSSQL Server from SSMS using our Active Directory accounts, by Windows Authentication. Our AD accounts are added to an AD group, which is used to grant all ...
13
votes
2answers
821 views

Unclear update conflict

I have two questions: 1. Why do I get update conflict in this situation instead of just blocking: -- prepare drop database if exists [TestSI]; go create database [TestSI]; go alter database [TestSI] ...
2
votes
2answers
128 views

MSSQL TempDb full

I have a problem with my MSSQL cluster. When switching to SQL Server 2016 (from 2014) an error occurs with the tempdb, which is full after a certain time for no apparent reason. Is there a proven way ...
1
vote
1answer
66 views

sp_send_dbmail: Failing When Adding Attachment

I have a simple script that should send an attachment using send_dbmail. It works when I exclude the attachment or send a very small file, e.g. 5KB. The prod file I want to send is 280KB. Exec ...
-2
votes
1answer
45 views

Is Buffer Pool Extension (BPE) needed if my database is already on SSD?

Here is my simplified scenario, let's say I have one (and only one) database of 500GB on SQL Server 2016 EE, this database is on SSD drives already (both data and log files), and my server has 64GB ...

1 2
3
4 5
49