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
0answers
117 views

How to delete specific range of data from Service Broker > System Queues data table?

Due to sudden increase of my database .mdf file we found there are 4 internal queue message table are increase unexpectedly. so we decided to delete some range of data from these table. we try this ...
0
votes
1answer
99 views

Killed SPID on xp_LogInfo not rolling back and growing tempdb [duplicate]

I received a notification that the space on the drive my tempdb sits on was running low, when checking the running process it pointed to a server agent task. I checked the scheduled tasks and we had ...
0
votes
1answer
42 views

SQL Server second transaction log seems to be used as well - not only after first one is full

we have come across a strange phenomenon using a second transaction log file for a database. Microsoft SQL Server 2016 (SP2-CU7) (KB4495256) Standard Edition. On Windows Server 2016, a single instance,...
0
votes
1answer
90 views

Affinity side effects / TF 8002- how to measure

Environment: We have Microsoft SQL Server 2016 (SP2-CU15) Standard Edition (64-bit) on Windows Server 2016 Standard 10.0. We have multiple SE instances on one physical server. We use processor ...
0
votes
0answers
10 views

SSIS Dataflow package works with Native Client but rejects data with OLE Db connection

I've been trying to improve an SSIS ETL package to see if I could get better throughput. I was trying to change the buffer settings as per this link, and thought i'd try using a newer driver/provider. ...
0
votes
1answer
25 views

Stored Procedure Signed by Certificate Unable to Add Members to Database Roles

I created a Stored Procedure that performs the Following tasks: Creates database Change owner to sa Set recovery model Creates users in database Adds users to database roles. Grants execute and view ...
0
votes
1answer
392 views

Passing a variable in OPENJSON WITH statement in SQL Server

Below script calls an API with base and other currency as input and gets foreign exchange(FX) value from its response. While reading the response from api we have to specify '$.rates.GBP' in openjason ...
4
votes
1answer
335 views

SQL Server with French_CI_AS Collation date conversion

I have a French installation of SQL Server using French_CI_AS Collation. For legacy reasons, a table column stores data as VARCHAR which is later converted to the right type, akin to variant in C++. ...
0
votes
1answer
33 views

Replication of only updates and inserts

I have two sql servers, in the first I want to keep the data from the last three months and in the second I want to keep all the data with the purpose of getting historical reports. I know how to do a ...
1
vote
1answer
118 views

SQLServer Query Store not saving failed queries run by SP_ExecuteSQL

We are on SQL Server 2016 Query Store is activated on the database in question. We have had some timeout problems reported on a nightly batchjob, and we have located the query in question. The query ...
2
votes
1answer
36 views

Why when I use variables on SQL the Database Engine avoid the indexes?

I'm improving our SSIS process passing the variables through a declare and I found this: Here you have the plan execution using variables brentozar.com/pastetheplan/?id=rkSxs7VPv for the following ...
-1
votes
1answer
62 views

Get records where no column is null [duplicate]

I've a table with 100+ (nullable) columns and 10,000+ rows. I'd like to fetch rows that have no columns null. id|f001|f002|f003|....f100 1 |null|aa | ss |sada 2 |sdad|aa | ss |sada 3 |as |aa | ss |...
0
votes
2answers
73 views

Analyse differential backup to see why it is so big

We quickly have very big diff files. Although the database is a highly transactional database, there is also a lot of data at rest. 1 or 2 days after a full backup was taken, the diff backup is bigger ...
0
votes
1answer
25 views

Join to get most specific record for tenant without PIVOT

I have a Settings table and a Tenant table. There is a hierarchy for that an Account can have 1 or many Companies and a Company can have 1 or many `Facilities'. Account 1 ---> Company 1 ...
1
vote
1answer
93 views

Why does my execution plan show a Clustered Index Scan is occurring when using an index hint that specifically uses a Nonclustered Index?

The Backstory: I have a table with the following structure: CREATE TABLE WideTable1 ( BoringColumn1, BoringColumn2, CellPhoneNumberColumn Phone(VARCHAR(20)), -- User-defined types, see ...
1
vote
0answers
84 views

SQL server 2016 (13.0.5026.0) Standard. High CPU ussage even without user queries

So SQL server CPU ussage is around 20% even when no users are making queries I query the sysprocesses, and i can see some internal spid's: select * from master.dbo.sysprocesses order by cpu desc Any ...
-1
votes
2answers
29 views

Concatenated string gets truncated

I have a query that i am writing to convert some heap tables with primary keys into clustered tables, but the string that i'm generating truncates on objects past a certain length it seems. SELECT ...
0
votes
0answers
25 views

Did user connect via AG Listener or directly to the server? [duplicate]

How do I tell whether a SPID connected via the AG Listener name or the server name (or its IP address)?
0
votes
2answers
77 views

Do user defined data types always cause implicit conversion to occur when fields of those UDDTs are used as predicates?

I have a database (from a vendor application) that for some unknown reason has a UDDT on almost every column, even when the UDDT is logically the same as the underlying type it implements. E.g. a ...
0
votes
1answer
50 views

Allow entering application while in transaction

We've got a process that needs to run a certain set of stored procedures in a transaction and fail if one of those fails... Simple we have something similar to: BEGIN TRY BEGIN TRANSACTION; ...
0
votes
2answers
55 views

Unique-like constraint in a complex scenario

I have a table in SQL Server 2016, as shown below. Records are inserted into the table each quarter, for all partners. Constraint Needed: Should allow only one BatchID for a partnerID for a year-...
1
vote
2answers
32 views

Halting a multi-step Agent job without resorting to “Quit the job reporting success”?

I have a multi-step process that sometime should stop in the middle, not because of any failure but for business reasons which are not failures. How can I tell Agent to successfully exit the whole job?...
0
votes
3answers
86 views

Import daily csv with varying available headers

I receive a csv daily from an external company capturing feedback for us. Unhelpfully the csv headings vary. There could be any combination of headers. For example: day1: heading1, heading2, heading3, ...
1
vote
2answers
82 views

Availability Group Listener and old client software

We've got a multisegment AG Listener named SQLAGL01 which points to these two addresses: 10.143.162.32 (the primary server) 10.140.165.32 (DR server) Because the 3rd party (which means we can't fix it,...
0
votes
0answers
729 views

RDLC report not working after upgrading from Visual Studio 2008 to Visual Studio 2019 with SQL Server 2016

After RDLC report updated with Visual Studio 2019 is not working with SQL Server 2016: Microsoft.Reporting.WinForms.LocalProcessingException HResult=0x80131500 Message=An error occurred during ...
0
votes
1answer
302 views

Is it possible to specify a different collation for the data vs the entities within a single database?

I have a database with a case sensitive collation because the data inside of it should be treated as case sensitive in the context of how it's used. The entities and their column names within that ...
3
votes
1answer
136 views

Understanding DBCC TRACEON and TRACEOFF Globally and Session-wise

For those of you that keep track of what I discuss in The Heap™ you might have been following my endeavours whilst migrating a database from SQL Server 2008 R2 to SQL Server 2016 and battling with ...
0
votes
1answer
54 views

Query optimizer triggering statistics update seems to be slowing down queries

We are running SQL Server 2016 and have a very simple stored procedure that does a select using a clustered index seek of a single row in a table with 140 million rows. In that table, we keep around ...
1
vote
2answers
116 views

Avoid downtime of Microsoft SQL Server during restarts

We are running an SQL Server Standard 2016. From time to time Microsoft updates need to be installed and the server will be restarted. We have been asked to look into keeping the database available ...
1
vote
0answers
98 views

High CPU on Unloaded Server — Performance Monitor

We recently upgraded a very lightly used SQL server (running < 5% CPU 99% of the time) from 2012 to 2016. In conjunction, we moved the server from an AWS t2 to a t3 instance and installed the new ...
0
votes
0answers
49 views

SQL Server Persisted Computed Columns, Issues during Mass Insert or Update?

I have a database with a computed, persisted column which validates columns, checks if its an integer, and converts. Sometimes streetnumber is alphanumeric, like 5C, other times it's simply an integer....
0
votes
2answers
529 views

Splitting a column on underscores [duplicate]

What SQL syntax would split this column into three new columns (split would be on the underscore)? There are other columns in the table, but I would like to split these into three (underscore based).
2
votes
1answer
421 views

What Microsoft DBA certificate should I acquire?

I am thinking about getting Microsoft DBA certification, but trying to find out which exactly certificate should I study for gets a little confusing. My company uses on premises SQL Server 2017 but ...
1
vote
1answer
26 views

SQL SERVER 2016, Restore database get 15517

I got "15517" error When I tried to restore database when the process reached 100% I googled several hours,the recommended solution is to change the database owner , I did so ALTER ...
-2
votes
2answers
66 views

TSQL - Sql Injection

DECLARE @TSQL NVARCHAR(MAX) = N'SELECT 1, ''One'''; CREATE TABLE #ThisIsATable (Id INT, VAL NVARCHAR(10)); INSERT INTO #ThisIsATable EXEC [dbo].[sp_executesql] @TSQL ; Is it safe to have input ...
3
votes
1answer
121 views

What Method / Formula does a Nested Loops Operator use for row estimation?

The following, simple query in AdventureWorks: SELECT * FROM Person.Person p JOIN HumanResources.Employee e ON p.BusinessEntityID = e.BusinessEntityID Gives the following ...
1
vote
1answer
149 views

How to fix SQL Agent Job using Windows Security after password change

I have some sql agent jobs that are using Windows NT Integrated Security to run (as specified in the Maintenance Plan's Manage Connections window)...eg. Translog backups. While logged in as this ...
0
votes
1answer
109 views

SQL Server storage - evaluate the number and types of read write operations for the existing deployment

Training for SQL Server, one of the questions in Learning material says: Before migrating on-premise SQL Server deployment (single server) to Azure VM, you need to evaluate "number and types of ...
0
votes
1answer
297 views

Generate random row order in a subquery

I know other answers here (and here) say to order by newid(). However if I am selecting top 1 in a subquery - so as to generate a random selection per row in the outer query - using newid() yields the ...
0
votes
0answers
77 views

How to renew certificates in an availability group without downtime?

OK, here is the setup. We have two nodes, both running SQL Server 2016, participating in an availability group. Both servers are running on Windows Server 2016 and are NOT joined to an Active ...
0
votes
1answer
43 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
30 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
250 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
71 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
82 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
38 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 ...
6
votes
1answer
770 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
35 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 ...
9
votes
1answer
1k 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
43 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 ...

1 2 3
4
5
50