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.
34,054
questions
0
votes
0
answers
6
views
Why do Databases use HEAP to store table data while other structure like B-tree exists?
Database Engines like MS SQL Server , PostgreSQL etc uses HEAP to store table data while using B-tree to store index data. Time complexity for most operations in B-tree is O(log n) while for HEAP it ...
0
votes
1
answer
14
views
How to change insert values from 0 to NULL for a specific field in order to avoid foreign key violation?
I have a foreign key linking INSTRUMENT.VENDOR to VENDOR.NAME.
Unfortunately the program where users enter information is attempting to insert into INSTRUMENT with a value of 0 for vendor. I need to ...
0
votes
0
answers
5
views
Purely in performance terms, how do CONTAINS([MyCol], 'foo') and [MyCol] LIKE '%foo%' compare?
Consider CONTAINS([MyCol], 'foo') and [MyCol] LIKE '%foo%'. Assume the best possible indexes on [MyCol], whatever those may be (at minimum, we need a unique key and a full-text index). I know that the ...
0
votes
0
answers
11
views
ALTER ROLE DROP MEMBER succeeds but doesn't drop member
So this one has me stumped. We're doing some permissions cleanup and we have some users we'd like to remove from a database role.
We run ALTER ROLE MyRole DROP MEMBER [domain\user] and get Commands ...
0
votes
1
answer
17
views
Which SQL Availability Group Replica should the AG cluster name connect to in SSMS
My AGs are all healthy, different ones on different clusters, all SQL 2019.
When I connect to the cluster name from SSMS, should I expect it to connect to the current primary replica? If so, any ideas ...
0
votes
0
answers
22
views
Dynamic SQL errors out on 2nd reference to non-existing database, but not on the 1st – SQL Server
I have a procedure that transfers archive data from 2 tables to monthly databases. A new monthly database is created every month.
Because the data transfer process is automated, dynamic SQL is used to ...
0
votes
0
answers
10
views
LocalDB with linked table to SQL Server error 7412 switching from non-admin to admin privileges
We are experiencing a strange issue using a localDB with linked tables to an SQL Server instance using SQL 2019. We see the issue in our own software but can also reproduce it using SQLSMS.
The steps ...
0
votes
1
answer
33
views
Upgrade SQL Server Server Express to Licensed Enterpise edition?
we have sql server express edition 2017 with default instanc ,now we want to upgrade it to Enterprise Licensed Edition 2017 with default instance in the same server
At first I thought I had to ...
0
votes
0
answers
10
views
SQL Server Replication - Transactional
I am trying to do SQL Server transactional replication. Created publisher, distributor & subscriber.
Publisher & distributes in SQL server A and subscriber on SQL server B.
From server A I ...
0
votes
0
answers
10
views
Initialized Replication Subscriber from Backup with @allow_anonymous,@immediate_sync off
I have initialized Replication Subscriber from Backup with @allow_anonymous,@immediate_sync = flase
It is working good.
Do I need t o change the setting back to true or just leave with false.
0
votes
0
answers
22
views
Does trigger order matter for enforcing integrity constraints in a trigger?
The database I am working on has integrity constraint requirements too complicated for a check constraint/foreign key. The only option is to enforce these constraints is in a trigger. On the surface, ...
0
votes
1
answer
37
views
Is it possible to hit a full-text index without using a function specifically designed for them?
There are several functions that are specifically designed to hit full-text indexes. I think that the following is a complete list:
CONTAINS
CONTAINSTABLE
FREETEXT
FREETEXTTABLE
Is it possible to ...
0
votes
1
answer
32
views
SQL server 2019 alwayson problem
I've SQL server 2019 STD always on set up with 2 nodes and file share witness (located on third server). yesterday we had an network issue that the main switch was down for 15 minutes. after the ...
1
vote
1
answer
59
views
How can I verify if Read Committed Snapshot Isolation is causing application problems that Read Committed would not?
Suppose that I've turned on RCSI and my server is doing just fine; There have been no spikes in resource usage and the increased load on tempdb has been minimal. This is all good, but how can I tell ...
1
vote
2
answers
76
views
Recommendations to reduce table rows
I'm looking for some recommendations when dealing with tables with large number of rows.
I have a table which has around 1.5 BILLION rows. Assuming there is a column ref_id. There could be 300K rows ...
-1
votes
0
answers
34
views
Select records by applying condition on a tag in an XML field [closed]
My DBA has shared the below SQL statement from the logs. I am trying to parse this and create the final SQL statement to execute. In this statement the xml path /row/c1 contains the customer id.
...
0
votes
0
answers
34
views
DST Adjustments to Ola Hallengren's backup jobs not working as intended
I have been using Ola Hallengren's backup/maintenance scripts for a few years but one thing that usually plagued me once a year was our backup location going near capacity after spring forward DST.
I ...
0
votes
1
answer
103
views
MS SQL Server - High "Modified Memory" consumption
We operate an instance of the MS SQL Server 2022 (standard 16.0.4095.4) on Windows Server 2022 (virtual machine with 32GB RAM) and having issue with the "Modified Memory" consumed by the ...
7
votes
4
answers
890
views
Is my queue table implementation race condition safe?
Hello people smarter than me! I've created a sort-of-a-queue table system, but it seems too simple to be safe from race conditions. Am I missing something or is the following race condition safe?
The ...
0
votes
0
answers
28
views
SQL Server Replication Snapshot Error - Exception Message: An invalid option was specified for the statement "CREATE/ALTER FUNCTION"
The replication process was working fine. But when I added Views, UDF and Stored Procedures to replication process it throws this error.
The replication agent had encountered an exception.
Source: ...
0
votes
1
answer
69
views
Is there a version of System.Data.SqlClient that does not support Encryption for Connections?
I am looking at IIS server that hosts tens of applications. Some of them are pretty old. All of them are using connection strings that are stored in machine.config in C:\Windows\Microsoft.NET\...
1
vote
3
answers
83
views
Enforce uniqueness on combination of columns + aggregate function
I have a SQL Server database with the following tables:
CREATE TABLE [dbo].[Rule]
(
[RuleId] [int] IDENTITY(1,1) NOT NULL,
[Value1] [int] NOT NULL,
[Value2] [int] NOT ...
1
vote
2
answers
101
views
Why OFFSET / FETCH causing Performance issues with Queries in MS SQL 2019?
I have this select query:
SELECT ITEM.ID ITEM_ID,
ITEM.CODE ITEM_CODE,
ITEM.DESCRIPTION ITEM_NAME,
ITEM.CODE_CATEGORY_ID CATEGORY_ID,
ITEM_CAT.CODE CATEGORY_CODE,
...
0
votes
0
answers
28
views
SQL Transactional Replication Limits
I am seeing strange behavior in a Transactional Replication configuration.
Details:
Publication Server SQL 2017 Standard
Distribution Server SQL 2017 Standard
Subscriber Server SQL 2017 Standard
55 ...
2
votes
3
answers
608
views
How can I get an average from a query dataset in SQL Server?
I am building a parts inventory system, and have a transaction table that stores how often an item in used.
Let's say Item A is used 10 times in 2021, 20 in 2022, and 30 in 2023. Of course each ...
1
vote
1
answer
85
views
MSSQL Varchar field accept only int
On my test server (SQL 16.0.1110.1), have this table defined this way:
CREATE TABLE [dbo].[mytable](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[IDOpSt] [bigint] NULL,
[Order] [smallint] NULL,
...
3
votes
1
answer
61
views
How can I prevent SQL Server from logging the starting and stopping of XE sessions to the server log files?
I have a process which starts and stops Extended Event sessions in order to transfer data from event files into SQL tables. On a SQL Server 2022 RTM server, I see many lines written to the log files ...
1
vote
0
answers
55
views
Metadata query slow on one specific database
I have quite a weird problem (who doesn't).
Our ETL Software scans source databases to claim object data. It extracts information from INFORMATION_SCHEMA about objects (tables and views)
SELECT
t.[...
0
votes
2
answers
28
views
database involved in availability group - showing state as online but it is synchronizing and does not accept connection - how can I find that out?
how can I find the list of databases I actually can connect to?
I am using sys.master_files to check the state of my database files
however, on the secondary, the database is synchronizing
and it is ...
0
votes
0
answers
17
views
Problem with SQL Server Merge Replication 2017
I have multiple replications: merge replications that sync the data on both Headoffice and Stores but from past two days I am getting these error messages and replication doesn't complete successfully ...
0
votes
0
answers
15
views
Bulk Fix Datasources on imported SSRS Reports
I've been using ReportingServicesTools, a powershell tool for managing SSRS to migrate from a 2016 SSRS server to an SSRS 2022 server. Reports came over, datasources came over, yet every report gives ...
1
vote
1
answer
49
views
SQL Server - Log Shipping to Azure VM
I'm looking for some advice regarding Log Shipping from SQL Server 2016 On-Premises to SQL Server 2016 on an Azure VM.
From my existing knowledge and research, there are a few ways of going about this....
5
votes
0
answers
67
views
Why is the lock on the key not held?
When analyzing a locking error in SQL Server I found a design decision which causes deadlocks.
I want to understand why this decision was made. As for context: If somebody updates a table I have a ...
-1
votes
1
answer
36
views
Select ALL matching rows from a target table if they match ALL rows from a source table
I need to retrieve data from the target (table 2) which has ALL matching values from the source table (table 1), by the same CategoryID.
If all of the resources in the source table don't have a ...
-1
votes
0
answers
43
views
3-node SQL AG log file is HUGE and keeps growing on each node
Have a 3 node SQL AG. Flip primary form node 1 and 2 every 3 weeks. node 3 is used for Readable secondary for reporting and the such.
The issue is the log file for the database is now over 500GB and ...
0
votes
0
answers
17
views
SQL 2022 ssisdb always on support - patching requires AG recreation? And does auto failover detection still apply to SQL 2022?
My setup is a 2 node AG.
SQL 2022 Enterprise.
SSISDB is part of the AG, and I have configured SSIS high availability (enabled SSIS Support for Always On) as per this documentation: https://learn....
0
votes
2
answers
60
views
I could not add sp_BlitzIndex store procedure to SQL Server 2014 version
I cannot successfully add the sp_BlitzIndex store procedure for Microsoft SQL Server 2014 (SP3-CU3-GDR) and earlier versions. I think there is something related to the version. Because I was able to ...
0
votes
0
answers
59
views
Microsoft SQL Always On failover cluster goes offline when adding a new node
I'm in a process of migrating my windows servers which contains SQL from windows 2012 to windows 2016.
At the moment my Always On failover cluster contains 3 2012 servers and i want to replace 2 of ...
0
votes
1
answer
55
views
Synchronizing two databases on different servers
I have two databases on different servers. Each one of them contains 350+ tables and users have read and write access on both databases.
I want to synchronize them, but in special cases, like if a ...
0
votes
0
answers
23
views
High BPSORT waits resolve after server reboot
SQL Server 2022 CU10 enterprise edition. This is a 2 node AG (sync mode) with readable secondary. The issue occurs on the primary node.
Query store enabled. Intelligent query processing features ...
1
vote
1
answer
80
views
What happens when RCSI is enabled in one database but not another, and you query both?
Suppose that I have two databases on the same server, RCSI_Yes and RSCI_No. In the first, RCSI is enabled. In the second, it is not. How does SQL Server decide what transaction isolation level should ...
0
votes
0
answers
24
views
SQL windows authentication fails sort of
SQLSERVERABC is on domain abc.local.
If I connect to SQLSERVERABC via remote desktop, I cannot login to the database via SSMS on the local machine that I have remoted to.
At SSMS login screen:
Server ...
0
votes
0
answers
35
views
SQL Server Database Audit used with a trigger to monitor CREATE TABLE events
Got SQL Audit enabled on a database with SQL Server 2016 SP1. Trying to catch a CREATE TABLE event with a trigger, and add that table to the audit to log data operations.
Problem is that changes to ...
0
votes
1
answer
46
views
MS SQL 2019 Always on AG - Readable to secondary
I have a 2-node cluster that uses Always on AG, with multiple AGs and databases. All the AGs have the same properties:
Connections in primary role: Allow all connections
Readable secondary: no
I ...
0
votes
1
answer
38
views
What are recommended values for block size, buffercount and maxtransfer size for Ola backups to Azure?
I have SQL servers running on onprem and Azure VM hardware.
The backups write to a file share located on a network VM.
I am wanting to change the backup destination to Azure blob storage.
For this I ...
-2
votes
0
answers
31
views
Are there any in-memory OLTP features available in SQL Server 2022 but not SQL Server 2019?
I know that in-memory OLTP has a lot of new features in SQL Server 2016, but I have heard nothing since then. Are there any in-memory OLTP features available in SQL Server 2022 but not SQL Server 2019?...
3
votes
2
answers
84
views
MS SQL Server: How to alias a linked server
This is regarding MS SQL Server.
I have two DB servers for my application named Torch
TorchProd and TorchTest are the db servers.
There is also a data mining server DataMineProd and DataMineTest
I ...
0
votes
1
answer
57
views
How does different forms of EXISTS() affect performance in MS Sql Server?
I've been told that when checking for the existence of a row in a table, this will work:
EXISTS(SELECT 1 FROM....)
but that a "better" way in regards to speed performance of the query would ...
0
votes
1
answer
22
views
Move the file from local folder to One Drive (Cloud) by using SSIS Package
I try to move the file from the local folder to one drive(cloud). So, I have to use the Premium File Transfer Task from KingswaySoft(Free Developer License). Then
I created the SSIS Package and ran it ...
1
vote
1
answer
47
views
SQL availability group failover latency experienced is 35 seconds, which is 10 seconds higher than older SQL Mirror failover
Encountered problem:
Our use-case has 3 x Server 2022 in a topology of a domain controller with two node cluster. This is a SQL Always On AG architecture. The cluster hosts the SQL availability ...