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.
30,178
questions
-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 ...
