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
39 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
37 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
50 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
39 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
66 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
68 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
53 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
116 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
58 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
79 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
59 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
246 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
76 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
60 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
51 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
85 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
vote
2answers
76 views

Cannot SUM the value of a sales amount field - various criteria, sales rep id and date ranges

I wonder if someone can help me with this SQL Server 2016 query. I have two tables with following data TblSalesRep This is the table holding the history of the territories that the sales rep looked ...
0
votes
1answer
42 views

Is there any option to enable Window NT logs in SQL Server Logs

I'm trying to view SQL Server logs on a specific server and is missing Windows NT section in same. Though other server are showing that section. In the screenshot shared below, I highlighted section ...
1
vote
1answer
73 views

intra-query parallel thread deadlocks with only Page Locks with snapshot isolation and change tracking

I've been trying to optimise a data loading process. This is on Sqlserver 2016 (13.0.5850.14 - latest SP&CU). I've switched to change tracking for internal database changes and set tablocks on all ...
2
votes
2answers
80 views

Difference between avg Disk sec/write and io_stall_write_ms

We have a SQL Server 2016 SP2 Enterprise with latest CU with the database files spread out over different disks. So we have data,log, tempdb and system db's have each their own drive. Date and log ...
1
vote
1answer
60 views

Purging SP getting deadlocked with App…Not able to completed understand Deadlock Graph

We have a stored procedure which purges old data. On a regular basis, it is getting deadlocked with an application stored procedure. Below is the deadlock graph: Deadlock XML: <deadlock> <...
0
votes
2answers
85 views

What Is The Best Way To Clone A Database From Production To Development Environment? [closed]

We use SQL Server at our company as DBMS. I have a scheduled job working monthly. The job restores every databases on production server to development server at the end of the month but there is ...
4
votes
3answers
374 views

How to prevent parallel running of SQL Agent jobs

What would be good ways to design a group of jobs such that they can never run at the same time? I am looking for a generic concept, like a semaphore, which makes it easier to ensure that some jobs ...
1
vote
2answers
60 views

Custom index maintenance percentages for a very large table

I'm using 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 Windows Server 2012 R2 ...
5
votes
1answer
209 views

Indexed view not used in actual execution plan?

I've got a modest use case for an indexed view, which collapses out a column and sums all entries from a large table: CREATE VIEW dbo.Losses_CombinedPortfolio WITH SCHEMABINDING AS SELECT [...
1
vote
2answers
80 views

Why does the SQL Server build number sequence not correlate with the release date sequence?

I'm looking at the Microsoft SQL Server 2016 Builds (see that section heading). The list of builds is given in build number order. That is, at the top of the list is the "highest" build ...
2
votes
1answer
41 views

TransactionLog not getting truncated in AlwaysOn Cluster

Folks, stuck with a wierd transaction log truncation(reuse) problem in an AlwaysOn cluster and running out of options, the transaction log is not getting truncated and keeps growing. Only one database ...
0
votes
0answers
21 views

Unable to call SQL Agent Job on another server

I am trying to start a SQL Agent job on another SQL Server and keep getting the error message that the job is not found. EMSSQL3 is the server from which I'm trying to call the job which is located on ...
0
votes
2answers
54 views

Transaction Time Tables in MSSQL and Constraints

I am trying to implement transaction time tables on a MSSQL Server 2016 following the book by Richard Snodgrass (Developing Time-Oriented Database Applications in SQL). A table with a sequenced ...
0
votes
2answers
181 views

how to add a user to local sql server when one has only windows admin rights on the server

I'm in the admin group of a server, but yet I don't have access to the sql server, is there anyway to add myself without the need to reinstall the sql server?
0
votes
2answers
74 views

Why does SQL Server Profiler show some completed statements in pairs (twice)?

In SQL Server profiler I am seeing pairs of completed statements. A pair is shown below. The TextData (query) is exactly the same. The SPIDs are different (86 & 88). The # of reads and writes are ...
0
votes
1answer
34 views

Unusual behaviour of a query when using case expression in group by, some records are being missed

I have 4 table with below structure: Table "Customer_Info" create table Customer_Info ( Customer_Number int, Customer_Issued_Branch int, <Other Columns related to customer&...
0
votes
3answers
94 views

Why are those estimates way off?

I have problems understanding the following query plan: https://www.brentozar.com/pastetheplan/?id=SyoD_PosP First there is the bottom right index seek. The estimation says over 22 million rows but ...
1
vote
1answer
42 views

SqlServer Database replication in remote datacenter. Which kind of replication? [closed]

Disclaimer: I am a developer, not a DBA. Scenario Currently two servers (Web Edition) in a private cloud in one single datacenter in Europe. 1 DB per client + 1 common DB used by all clients. We are ...
5
votes
1answer
179 views

Dirty buffer pages after issuing CHECKPOINT

I am currently working on a test system and due to the nature of the queries I want to optimise, I am trying to simulate a "cold" read a well as I can. Part of that is clearing the buffer ...
1
vote
1answer
874 views

Error Failover in Always on high availability groups

We have a problem with Always on high availability groups in Microsoft SQL Server 2016 (SP2) , When we want to failover to the secondary node manually, it fails because of this error: Failed to ...
2
votes
1answer
76 views

How can a database have a logical name that is different than displayed in SSMS?

I found that on a production server there is a database named MYOBInterim. On other environments (development, testing), there is, instead, a database named MYOBTraining. (Don't ask me why - I'm still ...
1
vote
1answer
28 views

Why use an intent lock at a higher level in the hierarchy, why not just a regular lock?

This article suggests intent locks are used at a higher level of the lock hierarchy to indicate that a lock has been taken on a lower level. The idea of this mechanism is that SQL Server doesn't need ...
2
votes
3answers
255 views

Query to update one column of a table based on a column of a different table

I have two tables with below structure : Create table Customer_info (Customer_num varchar(50), Customer_Branch int); Create table Customer_Transaction_Info (Customer_num varchar(50), ...
0
votes
2answers
23 views

Is it possible to edit a partition function on MSSQL 2016?

I've inherited a partitioned database with millions of rows and I've noticed there is a typo in the partition function: CREATE PARTITION FUNCTION [Partition_by_customer](varchar(10)) AS RANGE RIGHT ...
0
votes
1answer
54 views

What is the best way to replace a SQL Server database with an updated one?

I have a SQL Server instance with 3 databases in it (SQL Server 2016 Web on Win Server 2012 r2). All databases have relations with each other. It is live and is feeding some web services. I need to ...
3
votes
2answers
140 views

Extremely bloated transaction log during delete

Now, I know that the log can get large during big deletes and one should try to chop it into batches. But I find this scenario strange and if someone could explain it to me that would be GREAT!. I ...
0
votes
2answers
339 views

Maximum Error Count Reached

I created a maintenance plan to backup all the databases locally on a folder in E Drive. Out of nowhere, the job is failing occasionally. sometimes the job runs successfully and sometimes sends an ...
1
vote
1answer
206 views

Disable password policy for partially-contained database

I'm stuck in a situation where I need to create a new user for a partially-contained database (SQL Server 2016). The password is short, so I get an error: Password validation failed. The password ...
0
votes
1answer
56 views

No One Can Login SQL Server Instance Even Though Its Services Are Running

I have an issue. Today, our developers had reported to me a problem that they couldn't reach SQL Server database server several hours ago. I have checked the services and i saw that they were in ...
0
votes
2answers
50 views

Parent/Child summary rollup

I'm struggling to wrap my head around this one. I'm sure someone will simplify it. We have a parent/child relationship that I'm needing to roll-up based on sold quantities and sales dollar amounts. We ...
0
votes
1answer
67 views

SQL Server 2016 (SP2-GDR) - correct upgrading path

I've found in my organization VM with SQL Server 2016 (SP2-GDR) (KB4532097) - 13.0.5102.14 (X64), which I would like to patch to the latest available version. Can I just install CU15 (KB4577775) on it ...

1 2
3
4 5
50