People who code: we want your input. Take the Survey

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
vote
1answer
55 views

Stale Statistics and View performance on SQL Server 2016

I have a report query which behaves abnormally, but rebuilding the indexes/refreshing statistics seems to sort it momentarily. So my doubt essentially is - What could be the reason that even with ...
2
votes
1answer
75 views

SELECT query in a SP in AlwaysOn Availability Group Read Only Routing

There is a stored procedure which has select statement as well as insert and other statements. What will be the behaviour of the stored procedure in a Read Only Routing configured in AlwaysOn ...
1
vote
0answers
60 views

Is it possible to create a Linked Server from SQL Server 2000 to SQL Server 2016?

I am trying to create a Linked Server from SQL Server 2000 to SQL Server 2016 R2. However, while creating I am getting the below error when trying to create Linked server from SQL Server 2000. TCP ...
1
vote
4answers
59 views

How to enforce SQL Server policies in order to avoid users enabling bad practices

I have some rogue Windows Administrators who give themselves SA and then grant end-users SA. These users then end up enabling auto-shrink, auto-growth percentage and all kinds of other poor practices -...
0
votes
2answers
40 views

T SQL, Get new customer added or deleted from historical table

I have a Table containing historical data. Every day the same staff list will be added with the new dates (between 1200-12500 staff each day however only a few staff might be newly added todaysDate or ...
0
votes
1answer
43 views

SQL Server License Migration [closed]

We need to move our SQL Server 2016 standard License. Our plan is to install SQL Server on the new box with the same license key , test the application / databases and then delete the old SQL server ...
0
votes
1answer
40 views

Will an underpowered mirror host cause: The query processor could not start the necessary thread resources for parallel query execution [duplicate]

We recently moved our 2016 sql server mirror into Azure, but it's on an underpowered machine. Our main database server has 16 physical cores with hyper threading and 2 numa nodes. We have set the ...
1
vote
1answer
36 views

Is it possible to “bind” sp_executesql to the calling procedure, as recorded in the Query Store?

Statements executed with sp_executesql appear to be generally "unbound" from the procedure in which they appear. By "bind", I simply mean "associated to the calling object&...
2
votes
3answers
122 views

Slow Parallel SQL Server query, almost instant in serial

I have a SQL Server query as follows (obfuscated): UPDATE [TABLE1] SET [COLUMN1] = CAST('N' AS CHAR(1)) FROM [TABLE1] WHERE (COLUMN1 = '2' AND COLUMN2 IN('VAL1', 'VAL2', 'VAL3')) OR ...
1
vote
0answers
44 views

MS SQL Server Enterprise 2016 is truncating the audited select statements, how do we fix it?

TL;DR; I am getting SELECT as the audit output instead of the actual (suspected) query SELECT DISTINCT CONVERT(varchar, DecryptByPassphrase(getkey(N'key1'), FIELD1)) as DECRYPTED_FIELD1 FROM view1 ...
0
votes
1answer
66 views

Recursive CTE to get Grandparent, parent, children [duplicate]

I have a data structure that relies on a parent/child model. The top level parent can have multiple children where they can also be parents. Here's an example of how things can look in the tree: ...
0
votes
0answers
87 views

Slow query SQL Server 2016

I have a query that is taking more than 7 hours and doesn’t finish, as it doesn’t finish I can’t generate the actual execution plan. Here's brentozar's Paste The Plan It has a temporary table, and ...
0
votes
0answers
14 views

Enable query logging for single user on SQL server 2008 and SQL server 2016

I need to monitor queries executed by a specific set of users on a database. I need to check them on a weekly base. Is that possible on SQL server 2008r2? And 2016?
2
votes
3answers
52 views

Preserving full refresh data source

I have a SQL Server database that gets updated every week with a completely new set of data. I am currently using two staging databases. A series of view databases are connecting to these and one week ...
-1
votes
2answers
24 views

Tool to Execute SQL Server Scripts and Automatically Recognize and Prompt for Scripting Variables [closed]

I have a folder of scripts that contain multiple objects and jobs that I roll out every time I deploy a new SQL Server Instance. The scripts utilize scripting variables, as an example, here is an ...
4
votes
2answers
77 views

In-Memory OLTP SQL Server 2016 - insufficient system memory

I am trying to place an In-Memory OLTP table, Here is the memory estimate you will need: But when the migration starts, the following error message is displayed: The size of my table is ...
0
votes
1answer
98 views

High Latch Wait time with Logon trigger enabled while full backups are running

I have 14 production servers with SQL 2016 standard version, 128 gb of RAM, 16 CPU and SSD drives. Microsoft SQL Server 2016 (SP2-CU15-GDR) (KB4583461) - 13.0.5865.1 (X64) Oct 31 2020 02:43:57 ...
1
vote
1answer
67 views

In SQL Server, what's the best way to split multiple delimited strings into rows without a function?

I know there are numerous examples of how to split strings in SQL Server. However, they are often for a single delimited string. What's the best way to split multiple delimited strings without using a ...
-1
votes
1answer
55 views

TSQL throw weird error when commenting a line of text [duplicate]

Here is a strange situation. Following code works perfectly if I run it as is, but if I comment the first CHARINDEX line, it throws an error. Msg 536, Level 16, State 4, Line 1 Invalid length ...
3
votes
1answer
44 views

Procedures using TVPs are slower when the TVP numeric value gets larger?

A legacy application has a nightly job that repeatedly calls some store procedure using a TVP and passes in batches of 10,000 ids that are in sequential order that it needs to process. Now that the ...
1
vote
3answers
57 views

Microsoft SQL Server 2016 Restore command many files

I have a question regarding proper syntax for restoring a 2016 SQL Server database. One database 1 mdf no issue. But I need to restore a database that has 3 mdf files and 73 ndf files and then apply 2 ...
0
votes
2answers
51 views

What could be the reason for shrinking the transaction log file before and after the database backup?

I'm an accidental DBA. I'm the only person in a company who has some experience with SQL Server and I have to deal with a system inherited from another company after it was acquired. People who ...
0
votes
1answer
546 views

Cannot fetch a row from OLE DB provider “SQLNCLI11” for linked server

I have two SQL Servers on two different machines somewhere in the cloud (maybe Azure). One is Microsoft SQL Server 2012 (SP3-CU10) (KB4025925) - 11.0.6607.3 (X64) Jul 8 2017 16:43:40 Copyright (c) ...
0
votes
0answers
85 views

Improving the run time of a DW query

I have a user running query which is sometimes under 10 seconds while other time going over 30 minutes. This is not a store proc , he is doing it manually from his machine to get data and later after ...
0
votes
1answer
7 views

Query To Show All Users Who Have Multiple Managers

I have a database that holds user information. I need a way to find out what users have multiple managers. Below is a subset of data to illustrate the data structure. Using MS SQL Server 2016 what ...
0
votes
1answer
49 views

Running select filename from dbo.sysfiles returns short foldernames with tilde

I am running the following statement in SQL Server 2014(in Windows 2012R2): select filename from dbo.sysfiles where fileid = 1 However it returns: c:\progra~1\micros~1\mssql1~1.sql\mssql\data\master~...
1
vote
2answers
163 views

SQL Server Availability Group LeaseTimeout and slow IO

Our 5 main databases runs on a physical (2 * 8 cores, 512GB, Hypertreading) SQL Server 2016 SP2 Enterprise in a single Availability Gruop and sometimes we get errors that the lease-timeout has expired....
5
votes
1answer
96 views

Merge with multiple records at once

There is a piece of code, from a previous dev, that is doing a merge statement with variables, and processing one record at time. I'm sure the merge can deal with multiple records at one. create table ...
0
votes
1answer
47 views

Connect windows 2000 to sql server 2016

I have a really old piece of industrial equipment that connects from windows professional to a sql server 2008r2 on win 2008R2, not completely patched. We cannot replace the windows 2000, but would ...
0
votes
1answer
21 views

Setting up SQL server multiple Names but within the same Database

Can we create different SQL Name (so will not change the previous connection strings) in a single SQL Server with the same content of the Database and log-in credentials?
2
votes
2answers
36 views

Indexes all Exist in non Primary Filegroup - Advantages?

I have come across a database where all the indexes are stored on a filegroup that is not the primary filegroup. The database has 4 files, one of which is in the primary filegroup and the other 3 are ...
0
votes
1answer
38 views

SQL Server: Determine backup location from physical_device_name field

I am working on a SQL Server database and ran the following command to figure out where the database is backing up to: SELECT TOP 1 M.physical_device_name FROM msdb..backupset S JOIN msdb.....
-4
votes
1answer
36 views

Check If Today Is First Of Month [closed]

On my search engine I have seen multiple results to get the first day of a month, but I want to do something slightly different. In SQL 2016, I want to check if Today GetDate() is the first day of ...
1
vote
1answer
40 views

What is the best way to find rows where a datetime is included in the range of two datetime columns [duplicate]

I'm using SQL Server 2016 Microsoft SQL Server 2016 (SP2-CU14) (KB4564903) - 13.0.5830.85 (X64) Jul 31 2020 18:47:07 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows ...
-4
votes
1answer
29 views

Append String To Int [closed]

I need to add a % sign to a calculation I'm doing in MS SQL Server. This is the syntax I am using Cast(Round(Coalesce(totalsaleamt,0) * 100,0) As Int) + '%' but I get the error Conversion failed ...
1
vote
0answers
37 views

Error [36, 17, 145] occurred while attempting to drop allocation unit ID ##### belonging to worktable with partition ID #####

We regularly get this error on a SQL Server 2016 SP2 instance. (Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64) Mar 18 2018 09:11:49 Copyright (c) Microsoft Corporation Enterprise ...
0
votes
2answers
38 views

Table compression on 500GB table using Enterprise edition with online=on

I am using MS SQL2016 enterprise edition and we have several tables that need to be compressed to save memory and backup space. Right now they are each around 500GB and I am trying to get some space ...
0
votes
0answers
47 views

Fix the SQL Server R-Service (in-database) installation error

I have reset my windows 10 machine and tried to install SQL server 2016 developer edition. Everything else installed except the the R-Service (In-Database ). it has thrown the following error. But I ...
1
vote
1answer
66 views

Why does the log file grow larger than data file for an availability group database on the secondary replica?

I've got a SQL Server 2016 AG set up with only two replicas (primary and secondary). Both synchronous, non-readable secondary, and backup the primary only. Transaction Log backups occur on the primary ...
1
vote
2answers
52 views

What is the best way to split a string into integers and units?

I've got a table of medication prices, each with their own units. I am trying to split the strength into four columns: Numerator_integer, Numerator_uom, denominator_integer and denominator_uom. I'm ...
1
vote
1answer
84 views

Accessing Memory Optimised Tables Under Snapshot Isolation

We have a high traffic database, that I am looking to convert to using IM-OLTP for performance gains. Due to the large size of the table in question, I'm looking to do a hot/cold solution, with a view ...
-1
votes
2answers
56 views

Index usage across databases [closed]

I would like counseling as to what is the best way to get performance in the following scenario. This is on a SQL2016 server. I have a newly created central database where data is replicated to from ...
0
votes
1answer
62 views

Sql_text Data Missing When Querying Extended Event File Target Using TSQL

I set up an Extended Events session to capture SQL Server error events. Below is the TSQL I used to create the XE session: CREATE EVENT SESSION [ErrorCapture] ON SERVER ADD EVENT sqlserver....
5
votes
1answer
223 views

Why is changing data types not effecting the database size?

I have a table with four columns and ~660 million rows. Two of the columns are bigint although they are not really required to hold any content that could not also fit into int. So as a test I changed ...
1
vote
1answer
58 views

How to know a backup command failed or worked in a script [duplicate]

I have a command in a script to do a 'backup database [name] to [file] and all the other parameters. Now, we have a problem where we run out of disk space, the script keeps on trucking, just issuing ...
2
votes
2answers
223 views

Improve performance of index reorganize on volatile table

This is occurring on SQL Server 2016 (Enterprise). Example Table structure: CREATE TABLE [dbo].[Foo]( [FooId] [bigint] IDENTITY(1,1) NOT NULL, [FirstFkId] [bigint] NULL, [SecondFkId] [...
1
vote
2answers
66 views

How to reduce fragmentation of nonclustered index on date partitioned log table

Looking for some advice before I follow my gut on this and rebuild the nonclustered index to lead with our partition key. This is an insert-only table which is never updated or deleted, we keep a ...
0
votes
0answers
49 views

In SSIS, how to force an open connection to to SQL Server while package is executing (hint: its not retainsameconnection=true)

We have an SSIS package with an OLE DB connection to a SQL Server database. The database restores every 15 min from log shipping unless a connection is open. When the SSIS package runs (takes ...
1
vote
2answers
48 views

DBCC CloneDatabase - sys.partitions rows value wrong

I'm using DBCC CLONEDATABASE to create a shell of a schema. One of the side-effects of the new shell clone database is that I left with wrong values at sys.partitions. as a matter of fact, the values ...
1
vote
0answers
61 views

SQL Server linked server losing Windows authentication credentials

We are seeing a sporadic authentication issue between two SQL Server instances with linked servers where the Windows authentication credentials are sometimes lost on the remote server. When this ...

1
2
3 4 5
50