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.
33,468
questions
1
vote
0
answers
61
views
Same update on different rows results in deadlocking
We are testing our SQL Server database for concurrency issues and it appears that a single, simple update statement being run concurrently in transactions is resulting in deadlocks.
Here is the ...
- 11
5
votes
2
answers
2k
views
What order should my index's columns be for a SELECT a, b, MAX( c ) FROM d WHERE e = 1 GROUP BY a, b?
Supposing I have this table (in a multi-tenant SaaS database:
CREATE TABLE dbo.Messages (
TenantId int NOT NULL,
RecipientId int NOT NULL,
MessageId int ...
- 554
4
votes
3
answers
274
views
Is there any way to improve performance on like searches that have the % to the left of the searched value?
I have a query that does a like statement on a column that stores fullpath locations of files on a computer.
Example
select *
from table
where fullpath like '%hi.exe'
Which never seems to use an ...
- 43
3
votes
1
answer
210
views
SQL Server - memory issue after upgrade to 2022 CU7
In one of the SQL Server instances I am not able to execute any query. I thought it's due to some heavy load so I try to execute sp_execeute whoisactive and get:
]
Last night, the DBA has performed ...
- 3,741
0
votes
0
answers
34
views
Deleted SQL Agent Job Sending Failed Notifications
I have deleted the job and I don't see it in the object explorer nor in sysjobs, and yet it is sending out notifications when it 'fails'. I've done the following solution:
Restart MSSQLSERVER & ...
0
votes
0
answers
51
views
connect to mssql with pgloader
I'm trying to convert some data from MS SQL Server to Postgres from a Windows 10 WSL partition.
From the Ubuntu prompt on WSL, I can run the following with no issue:
~$ sqlcmd -C -S tcp:localhost,1432 ...
- 229
-1
votes
0
answers
46
views
MacOS Microsoft SQL Server query plan viewer?
I've been using Azure Data Studio when I have to work with SQL Server, but the way it displays query plans is pretty rough for queries of any complexity. It appears that I have a choice between a low-...
- 99
0
votes
1
answer
74
views
No connection possible after TrustServerCertificate=True => The certificate chain was issued by an authority that is not trusted
I have a machine which could connect to an MS SQL Server in the past. I have SSMS installed and the connection was possible when I activated TrustServerCertificate checkbox in SSMS. I could connect ...
-1
votes
1
answer
52
views
SQL Server Cursor preceed by IF statement? [closed]
The SELECT statement to populate my Sql Server CURSOR can (often) return zero rows.
Is it advisable to enclose it within an IF check so the CURSOR it is not Declared, Opened, Closed, & Deallocated ...
- 21
0
votes
1
answer
92
views
How to fix Recovery Pending on local SQL Server database?
There is an article here that I tried following:
https://stackoverflow.com/a/52325761/22019322
It includes these steps:
ALTER DATABASE [DBName] SET EMERGENCY;
GO
ALTER DATABASE [DBName] set ...
- 1
0
votes
0
answers
35
views
How do I filter down the latest Date from multiple columns
I've been working with it and got it to work somewhat, but my WHERE filter has errors, I need only the max date that is Actual in each category.
left JOIN ( SELECT Customer_ID
, MAX(...
0
votes
0
answers
31
views
Why are there entries with no runtime stats interval values for DOP_FeedBack feature verification query
In the SQL2022 Workshop files there is a query used in the demo for DOP_Feedback, which is the below. However, it doesn't have the runtime stats interval so I can't see when the affected queries ran. ...
- 388
0
votes
0
answers
63
views
SQL 2019: Insufficient disk space to generate checkpoint
I have SQL Server 2019 and a few databases. One works fine, but to the 2nd my app cannot connect:
There is insufficient disk space to generate checkpoint files and as a
result the database is in ...
- 101
3
votes
1
answer
104
views
Row greater than max size (8060) but I can't see why
I have a table that has 483 columns.
A date not null, a char(6) not null, and then 481 x float sparse null.
By my maths, even if every sparse float is non-null, the row should still only be:
date: 3 ...
- 133
0
votes
1
answer
88
views
Finding when columns are added- SQL Server
In SQL Server is there any way to get information about when columns are created and which columns are new? Also, I need to find any change in columns, for example which columns' data type changed. I ...
-1
votes
1
answer
48
views
Nonclustered columstore Index memory allocation
We are planning to use a non-clustered columnstore index on 30 nvarchar(100) columns. The table can have rows ranging from a few hundreds to millions. These columns are selected during query execution ...
- 1
0
votes
2
answers
61
views
Weak entity Auto-increment composite primary key
I have a problem with weak entity and auto-increment.
Here is my 2 tables :
CREATE TABLE [sales].[orders](
[order_id] [int] IDENTITY(1,1) NOT NULL,
[customer_id] [int] NULL,
[order_status] ...
- 1
0
votes
1
answer
37
views
How to notify by email if CPU usage is above a certain threshold
In Microsoft SQL Server 2022 Standard I cannot get notification for CPU % usage to trigger.
I have found the following question but it does not apply as the resource governor is not available to my ...
- 111
0
votes
1
answer
44
views
Unencrypted Database Mirroring connections despite correct endpoint and service configuration
Despite correct installation/configuration (in my opinion) the query ...
SELECT DISTINCT [net_transport]
, [protocol_type]
, [encrypt_option]
, [auth_scheme]
FROM [master].[sys].[...
- 53
0
votes
1
answer
56
views
Check if an index exists in another database
Is there a way to check if an index exists in another database?
AFAIK, the usual ways (sys.indexes and INDEXPROPERTY) only run in the context of the current database.
- 437
0
votes
2
answers
157
views
Converting multiple comma-separated columns into rows
I have an SQL Server database table that holds comma separated values in many columns. For example:
id
Column B
column c
1
a,b,c,
1,2,3,
2
d, ,f,
4,5,6,
3
g,h,i,j,
7, ,9,8,
I want to split all ...
-2
votes
2
answers
61
views
In Snowflake, or SQL Server, how do I order results in the same order as my query?
In Snowflake, I'm querying the Information_Schema for the number of columns in each of a series of listed tables. How do I write my query so that Snowflake will return the results in the same order ...
- 65
0
votes
0
answers
47
views
User unable to login into SSMS or their excel spreadsheets that are connected to a DB
This is a strange issue. I have a user (let's call them A), that needs to 'refresh data' on an Excel spreadsheet. The user is able to log into Excel using their domain credentials. They can input and ...
- 1
0
votes
0
answers
31
views
How do I handle parameterisation for custom user filters?
I'm managing a MS Access application where one of the features is a list of projects with about 20 attribute columns. Most of these columns can be filtered individually. Naturally, this has led to ...
- 101
5
votes
1
answer
162
views
Partitioned view aggregation query not optimized
Given the following two tables:
CREATE TABLE SalesLedger (
Id int PRIMARY KEY IDENTITY,
Date date NOT NULL,
Total decimal(38,18),
INDEX IX (Date, Total)
);
CREATE TABLE Purchases (
Id int ...
- 11.5k
2
votes
1
answer
251
views
Can we exchange logical disjunction for UNION ALL?
I saw in that somebody exchange below code:
SELECT
PK1
, PK2
, PK3
, PK4
, C
, B
, SUM(NUMERIC_1)
, SUM(NUMERIC_2)
, MAX(NUMERIC_3)
, SUM(NUMERIC_4)
FROM ...
- 283
0
votes
0
answers
62
views
SQLServer Physical Log shrinking automatically with AutoShrink off
Running in SQLServer 2016 I have a weird situation where the Physical log is shrinking without any interaction and with AutoShrink=false set.
Also, on occasion the size is shrinking below the initial ...
- 1
0
votes
0
answers
44
views
Replication Setup: 'Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication'
We are currently working on setting up replication for our web server, where the publisher/distributor server is a web server and the Subscriber is a local server - express edition. During the process ...
0
votes
0
answers
55
views
Removing/disabling a single SQL instance on a failover cluster?
I need to retire a single SQL instance on a (classic) failover cluster while leaving other SQL instances running. SQL version is 2012 in case that matters, but this could come up for other versions in ...
- 9,784
0
votes
1
answer
40
views
Schedule a job in SQL Server between two days
How to schedule a job in SQL Server that starts on Wednesday afternoon and it should end on Thursday EOD and between this the SSIS package should execute for every one hour if any error occurs in ...
Charan G
4
votes
1
answer
511
views
Compilations/sec high when load testing a simple procedure
I am trying to load test a simple insert stored procedure:
CREATE TABLE _test(ID BIGINT)
GO
CREATE OR ALTER PROCEDURE dbo.test_sp
AS
BEGIN
SET NOCOUNT ON;
BEGIN
INSERT INTO _test
...
- 1,451
0
votes
1
answer
48
views
Text Search on Multiple Columns without enabling FULL TEXT Search
I have a scenario where we need to enable a multi-keyword search within a text field. This search should involve scanning the keywords across several columns (approximately 4-6) in multiple tables (...
- 4,043
0
votes
1
answer
32
views
do log backup on secondary node (sync or async) can truncate transaction log on primary?
i have 4 nodes. 2 nodes in sync mode, 2 nodes in asyn mode. Can i relocate all backup jobs (full, tlog) on any secondary? does the backup truncate the transaction log on primary?
0
votes
1
answer
72
views
How to optimise a query with inner join and order by
I'm struggling with optimising my query. I've tried to solve the issue with various indexes by none of them seems to be helpful.
There are 3 tables: transfers(id, amount, type, timestamp, file_id), ...
- 101
0
votes
1
answer
36
views
How to transfer server level login permissions to a higher sql version?
I have SQL 2017 with several server level logins/roles/permissions, linked servers, agent jobs.
While I can easily script agent jobs and linked servers, and backup/restore the user dbs to transfer the ...
- 3,360
2
votes
0
answers
29
views
What is the meaning of principal that has access to the machine's credentials?
The service master key can only be decrypted by the service account
under which it was created or by a principal that has access to the
machine's credentials.
What is the meaning of ...principal that ...
- 3,360
-2
votes
2
answers
45
views
Does a DR plan need to be in place for an AlwaysOn availability group
I have two SQL servers 2022 in a normal availability group. I have heard that AG is capable of disaster recovery. Is this true or do I need to make an extra plan?
Another question: Why is Failover ...
-1
votes
1
answer
81
views
DMV sys.dm_exec_procedure_stats sometimes returns weird (incorrect?) values in successive object snapshots
I'm working on a small project to monitor activity and execution metrics of user stored procedures. Periodically (every 10 minutes or so), I'm taking snapshots of the sys.dm_exec_procedure_stats DMV, ...
- 3
0
votes
1
answer
44
views
How to corrupt a SQL Server page for test purpose? [duplicate]
I'd like to corrupt a page of any table from AdventureWorks Database. How to do it?
I'd like to test page level restore and, also, bring a suspect database online again using dbcc checkdb
- 550
0
votes
0
answers
17
views
SQL Server Merge Replication using Web Synchronization - RESUMING SYNC
We are using MSSQL Merge Replication using Web Sync over extremely slow connection with many SQL Express subscribers.
For some reason, occasionally, connection is dropped and the subscriber is trying ...
- 13
0
votes
1
answer
80
views
Count of Identifiers
I am trying to validate an Id count. I have a table - Address_rankings with the following columns
Address - Nvarchar
Id - Int
Date created - DateTime
Sample data looks like the below:
Address
ID
...
- 3
0
votes
1
answer
51
views
How exactly does SQL Server execute an UPDATE statement?
Below two-sessions-trying-to-update-the-same-row scenario was provided by Paul White.
Scenario 1:
Session 1 obtains a U lock on the base table row while reading.
Session 2 blocks, waiting to acquire ...
- 1,041
0
votes
2
answers
93
views
SQL Server Audit log format changed in 2022?
I have an audit setup on various versions of SQL Server to dump their log files to a network share.
The server that reads the files (using sys.fn_get_audit_file) to gather the data from them is 2019.
...
- 31
0
votes
0
answers
31
views
Does SQL Server backup corrupted pages?
If my db has some corrupted page, SQL Server copy those page normally without warning about it? it's necessary to run RESTORE VERIFYONLY to find any corruption in backup?
- 550
0
votes
1
answer
25
views
SQL Server Sync Connection Usage
We're seeing very high waits for HADR_SYNC_COMMIT at times and after some diagnostic work with iperf3 and the like we think there might a network bandwidth problem.
However we're not central sql staff ...
1
vote
0
answers
30
views
Tracking down reason for growing version store
Hello all and thank you for reading!
Im trying to find the issue for a growing and growing version store in an app database.
With the help here:
Find transactions that are filling up the version store
...
2
votes
2
answers
104
views
Group by running totals with reset
Sample Data
Expected Data
Need to append trades under an order in a record, was able to do that using string_agg, now I need to split the orders with a maximum value of 10. That is, the quantity for ...
- 23
0
votes
1
answer
57
views
Unable to SWITCH IN a partitioned table
I have created two tables one is OrdersLatest and another is OrdersMarch. I want to switch data from the partitioned table to the staging table(OrdersMarch) on the same filegroup and vice versa.
It ...
0
votes
0
answers
40
views
Azure Data Studio does not recognize schema changes until I close and reopen
I've been searching for an answer to this, but no one seems to be asking exactly this question. I created a Notebook in ADS to run a lengthy series of scripts to populate and then export from a temp ...
- 291
0
votes
0
answers
34
views
Unable to set finite retention period for Temporal tables
I'm using Azure DevOps Classic Release pipelines to deploy an on-premise SQL database (DACPAC file, Target Platform 2019) using the "SQL Server Database deploy" task.
https://learn.microsoft....
- 101