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

Questions tagged [sql-server]

All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag, like sql-server-2016, since it is often relevant to the question.

Filter by
Sorted by
Tagged with
-1
votes
0answers
21 views

Why did SQL Server acquire a page lock when I accessed a record by its primary key? [closed]

We have a DML that's updating a SQL Server table: UPDATE mytable SET Column1 = @Column1, Column2 = @Column2, Column3 = @Column3, Column4 = @Column4, Column5 = @Column5, Column6 = @...
1
vote
0answers
11 views

SQL Agent stopped recording history (SQL Server 2017)

I have a Dev server that has been running without issue for at least 2 years. This dev machine was a VM Clone of the Production server. On 2021-06-05 something happened on this server that altered ...
0
votes
0answers
11 views

SQL Server 2016 : upgrade from SP2-GDR to CU15

I tried to upgrade one of my SQL Server from 2016 SP2-GDR to 2016 SP2 CU15, but I got nothing can be upgraded warning. Is this normal? Thanks
-4
votes
0answers
27 views

duplicate values with left join

I am getting more rows then I expect returned by my query. I believe it has something to do with my join statements. There are multiple tables that have different information in them from SAGE X3 ...
-3
votes
0answers
37 views

SQL Server freezes up till restart [closed]

We have a MS SQL Cluster Standard Edition with 26 cores on a single Numa node and 146GB memory. All CPUs maxed out 90+%, All disks slowed down with high latency (~50ms) and low throughput (3MB/sec) ...
-3
votes
1answer
46 views

I Want to fill missing days with zeros for all columns [closed]

I want to find missing days and fill with zero value using DueDateKey column
1
vote
2answers
108 views

How is the clustered index different from the table itself?

If the SQL Server clustered index is the physical order of the table and all columns are included, is it the table itself? How is the clustered index stored physically? I have seen the Q & A What ...
-2
votes
1answer
43 views

Will index on a partition that doesn't include partition column work fast?

Let's say I have a huge table with columns [CloseOfBusinessDate], [DataSourceID], etc. [CloseOfBusinessDate] is a partition column and I have a clustered index on that column (on ...
1
vote
0answers
16 views

Getting insight into partitions of a table in Azure Synapse (similar to SQL Server) [duplicate]

I'm using Dedicated SQL-Pools AKA Azure Synapse (which is different from serverless/on-demand Synapse that comes with Azure Synapse Analytics). And According to Azure: While the syntax of ...
0
votes
0answers
54 views

Why does Index Seek Used When Order By Is Present [duplicate]

I have this query: SELECT t.some_id AS id, t.* FROM some_table t WHERE (t.first_col = '123' OR t.second_cold = '321') AND t.third_col != 'aaa' AND t.some_date >= :someDate AND t.some_date <= :...
1
vote
2answers
74 views

What does Nested Loops join operator has to do with a Scalar?

As far as I understand the docs, the Nested Loops operator is a join operator, i.e., it requires two tables as input. If that's correct, then why the Nested Loops operator is used with a Scalar input? ...
3
votes
1answer
46 views

Estimate database growth caused by Accelerated Database Recovery

Accelerated Database Recovery (ADR) is on by default in Azure and an option in SQL Server 2019. It has some really interesting potential. I have experimented by turning ADR on in a static test ...
0
votes
2answers
53 views

Do wide included column indexes affect inserts?

I had to create indexes with included columns due to high I/O, nested loops joins, and key look ups that blocked inserts: SELECT * FROM Table WHERE (Column1 = 'ED69K') I created an index on ...
0
votes
1answer
43 views

Could LCK_M_SCH_S cause a spike in read/write latency for tempdb files?

I noticed a big uptick in tempdb read/write. This was during the same period of time that someone ran Alter Index and I noticed that the biggest wait at that time was LCK_M_SCH_S. I'm trying to track ...
2
votes
1answer
51 views

When to use BEGIN DISTRIBUTED TRANSACTION instead of BEGIN TRANSACTION in SQL Server?

For this question lets assume there are two servers, ServerA and ServerB, and there is a linked server setup on ServerA to ServerB. Let's also assume I have REMOTE_PROC_TRANSACTIONS set to ON for both ...
0
votes
2answers
55 views

Solution for syncing a database between two server and secondary can be used for all DML operation

I need a solution to synchronize a database (SQL Server 2016 Enterprise) between two servers, and I would be able to connect my secondary application to the secondary database which is getting ...
0
votes
1answer
27 views

Using parameter with cursor in stored procedure

I'm using SP that gets date as parameter, I'm trying to use the varable inside the query but SQL doesn't seems to "see" it alter procedure PipeReport @1 as DATE as begin DECLARE @SqlCD ...
0
votes
0answers
16 views

MSSQL query FREETEXTTABLE filtering

SELECT * FROM tblA --Result: --[DocFileId] Status --1 0 --2 1 --3 0 --4 1 --5 1 SELECT * FROM FREETEXTTABLE(DB_B..DocOcrText, OcrText, '...
0
votes
1answer
35 views

Can't remove “synchronizing/suspect” database after removed from availability group Sql Server 2019 Enterprise

I've removed a db from a synchronous availability group.The db shows "synchronizing/suspect" as it should on the secondary. I'm trying to remove this database cannot. I have tried: DROP ...
0
votes
0answers
19 views

Page level Deadlock on same delete query

In SQL Server I am issuing the following query: DELETE FROM TableA WHERE tableB_id IN (SELECT ID from TableB WHERE FileID = @P0) The query is being called in parallel by multiple processes and which ...
0
votes
1answer
25 views

SQL Server PIVOT returns only one row [duplicate]

I have a simple table of of 2 columns(varchar): name |occupation ___________________ Sandra |Medic Julija |Actor Marija |Actor Martyna |Manager Aiste |Teacher Kristina|Teacher Virg |Teacher ...
0
votes
2answers
44 views

Database backup won't work with a long path

I use the following C# code sample to export a database into a .bak file. However, when the user selects a long file path, the file is not generated. I've also tried to change the @outputfile from ...
0
votes
1answer
34 views

Updating SQL column value with previous row data that said to UPDATE a column to SET previous row value to NULL cells in SQL Server

I have the table as id value 1 music 2 3 movie 4 5 6 book 7 8 For the missing rows, and I want to assign the value of previous row like listed below id value 1 music 2 music 3 movie ...
-2
votes
0answers
38 views
+50

Implementing Resource Governor to balance the workload on readable secondaries (AG)

On one of our readable secondaries we daily have issues with redo queue building up and goes during business hours upto 300 GB, which is a worry as this is also the DR. The database users query is ...
0
votes
2answers
36 views

Hiding visual relation between two tables with related data

Sorry for sharing a confusing title I have got a requirement to design a DB for a highly sensitive application. There are virtually two set of tables. One is totally demographic information or its a ...
0
votes
0answers
20 views

SQL Server 2019 Machine Learning Service Issues

We're running a SQL Server 2019 instance for our data warehouse, and our data science team is using the Machine Learning Services quite exntensively. Lately we're seeing a lot of error messages like ...
1
vote
1answer
31 views

Visual Studio Data Tools (SSDT) schema comparison tool seems like it's not working correctly

I recently started using the SSDT schema and data and comparison tools. For years I was using Red Gate's SQL Compare and SQL Data Compare, but I figured the VS tools are probably enough and figured I ...
0
votes
0answers
23 views

Can we track historically or see queries on server is landing on which schedulers

The ask may sound weird but we are seeing sort of awkward behavior. Or may be we do not have historical data to confirm if it is true. For our 4 sockets 16 core processor SQL server with total of 64 ...
0
votes
1answer
41 views

An expression of non-boolean type specified in a context where a condition is expected, near 'AND' [closed]

ALTER PROCEDURE [dbo].[usp_BaseScan_CycleCount_Report] @StartDate varchar(10), @EndDate varchar(10) AS BEGIN IF @StartDate AND @EndDate IS NOT NULL SELECT * FROM dbo....
0
votes
1answer
17 views

Query store - Is it possible to tell - WHICH stored procedure calls to functions cause the most resource usage?

The database that I'm working on unfortunately has a lot of functions, with select statements in them, that are heavily used in where clauses in multiple stored procedures. This is not good design or ...
0
votes
1answer
32 views

Selecting all columns from row with specific max value [duplicate]

Imagine that I have the following table: ItemCode WhsCode MinStock MaxStock AvgPrice 123 01 1 4 600 123 02 0 1 200 512 01 0 2 100 489 01 0 6 50 489 02 2 7 72 489 03 5 10 80 489 04 1 4 65 258 ...
0
votes
1answer
51 views

Linked server only works if I am on the machine itself. I can't use it from my local computer. what is missing?

I have a few queries that I use to monitor log shipping. Sometimes I want to run those queries on a different server. I usually do that through a linked server. I have a server called SQLDEV4-TS that ...
0
votes
2answers
41 views

Adding Dates for Each Day of Stay

Let's say we have a small inn. People make reservations online. I receive a reservation number, the date they will arrive and the length of the stay. I would like not just the number of days of their ...
0
votes
1answer
30 views

Performance issue when multiple columns of same table are grouped

I am dealing with large amount of data approx 1 million rows with 100s of columns. I have this proc which performs some calculation over this data based on grouped by a colum1. Now the same ...
0
votes
2answers
19 views

Remove Query Tuning Assistant (msqta) schema and tables

When running the Query Tuning Assistant (using the New Database Upgrade Session) on SQL Server 2017 I have a new schema msqta created in the corresponding database as supposed, along with six tables ...
2
votes
2answers
91 views

Multiple instance properties

I'd like to understand how tuning works on instances that are living inside the same machine/VM. I couldn't find a straight answer to the question if properties of each separate instance have some ...
0
votes
0answers
23 views

Root Directory for multiple instances

I ve setup a new SQL Server 2019 and the default root path (during installation) was left at c: drive. Changed all the other paths for user-data-log files as well as tempdb and backup to different ...
0
votes
2answers
33 views

Why choose SQL FCI over Database Mirroring

I have SQL 2016 Standard Edition to install and configure a HA/DR solution. I need automatic failover in a cluster of two nodes. I am proposing SQL FCI over database Mirroring for the following ...
0
votes
2answers
40 views

SQL Server Configuration - Disabling the “Allow Remote Connections to this Server” Option

My organization had a security audit and was told that we need to DISABLE the "allow remote connection to this server" option for all of our SQL Servers. What impact would this have on ...
0
votes
0answers
87 views

Delayed stored procedure execution

We are having issues with one of our servers. When executing a stored proc it can take seconds to return data. Sometimes it is milliseconds. I don't think it is a parameter sniffing issue as it ...
0
votes
0answers
13 views

Define a default value that is a concatenation of attributes in SQL Server Master Data Services

It is possible in SQL Server Master Data Services to define the default value of an attribute as being the concatenation of the values of other attributes? Or even a mathematical computation? For ...
-1
votes
1answer
34 views

Duplicates while doing 5 multiple joins [closed]

I have 5 tables I need to merge with the max prep_attempt. I keep getting duplicates and I don't understand why. If a student attended the course more than once, I like to keep the last attempt. ...
0
votes
0answers
22 views

Bad estimates for 'dbcc checkdb with estimateonly'

I'm trying to compute the estimated tempdb space needed to run CHECKDB on one of our largest databases of size 7TB using the command DBCC CHECKDB WITH ESTIMATEONLY. The estimates came out to be 1.7 TB,...
0
votes
0answers
19 views

SQL Server 2019 CU10 on Linux Crashes When Running a PolyBase Query [closed]

This has been plaguing me for some time now. I run a tiny SQL Server 2019 CU10 Standard instance on Oracle Linux 7 (RHEL 7-compatable) that uses PolyBase to reference tables on an Oracle 19c Instance ...
1
vote
0answers
47 views

Windows Failover Cluster/Availability Groups - Issues because all servers of one site where unreachable

we had some issues because of maintenance and hope someone can explain what happened. Our cluster looks like this: Site A: Node A1 (Quorum vote) Node A2 (Quorum vote) Node A3 (Quorum vote) Node A4 (...
0
votes
1answer
48 views

How to reduce the maximum degree of parallelism at session level?

I have inherited a legacy application that includes a job that creates performance issue at instance level. The DBA told us about this and our team needs to fix the stored procedure that is the most ...
-1
votes
1answer
37 views

restore a corrupted page

I have Damaged a page in my database and now I want to restore it let me get this straight 1- I created a full backup 2- then insert info in some tables 3- creat a deferential backup 4- specific a ...
0
votes
1answer
54 views

String aggregation using STUFF

Let's say I have table #DOC like this: polNum boxNo batchNum 111111 null qwe1234 111111 ff/55 rto1235 111111 ee/59 yhn1238 111111 ww/55 rto1235 222222 dd/58 jkl1234 222222 null fgh1234 333333 ...
3
votes
1answer
143 views

SQL Server locking behaviour on repeated reads and one write

I am curious: suppose that SQL Server (or any other database management system) receives a lot of read traffic on one table, such that at any point in time, at least one read lock is requested (and ...
0
votes
0answers
16 views

External verification of `SIGNBYCERT()` signatures from SQL Server 2016

I want to sign plaintext using a SQL Server certificate and then verify that signature using the certificate's public key on an external system. Here's what I've tried. -- Create a self-signed ...

1
2 3 4 5
604