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.
31,006
questions
0
votes
0answers
10 views
Linked server Error on SQL Agent
I have a linked server that is scheduled to run every 10 min. But I keep getting errors. "login timeout expired". Can anyone help me with this
1
vote
0answers
27 views
Altering a column and cancelling the alter greatly improves SQL performance
We have a table that has roughly 5.5 million rows. We use Entity Framework and run a query on this table and get our row of interest based on 2 columns. One FK column that has an index (Type) and ...
0
votes
1answer
10 views
Row-Level Security with Azure AD Groups on Azure SQL Managed Instance
I have an Azure SQL Managed Instance on which I need to implement Row-Level Security on some tables to filter the data queried by some users.
All my users authenticate with Azure Active Directory ...
0
votes
0answers
38 views
The INSERT statement conflicted with the FOREIGN KEY constraint - although value exists in referenced table
I am trying to insert a row into a table that has a foreign key constraint, with below query:
INSERT INTO Table1 (
ID,
tNum,
CompanyID, -- this column has foreign key on it
...
0
votes
1answer
20 views
Can I retry every SQL query in case of a deadlock exception (MSSQL)
Is it always safe to retry an SQL query in case of a deadlock exception? Can this lead to unexpected behavior or executing an operation twice? I think a deadlock exception always means a rollback even ...
-1
votes
0answers
30 views
SQL Indexes are Missing post database restore into new server
We have 2 sql servers.
Database full backup has been taken from server 1 (Read only Database) -This Database is only set to Read Only.
And restored the same full backup file into Server 2.
Post ...
-1
votes
0answers
40 views
SQL Indexes are Missing post database restore
We have 2 sql servers.
Database full backup has been taken from server 1 (Read only Database)
And restored the same full backup file into Server 2.
Post restore there in server 2 found many indexes ...
2
votes
2answers
145 views
Columnstore: terrible execution plan - filter instead of seek
See the execution plan on https://www.brentozar.com/pastetheplan/?id=SyLQIPDtF (SQL 2016 Enterprise)
I have a datawarehouse table peak_reporting_data which tracks the activity per day and hour and ...
0
votes
1answer
43 views
SQL 2005: Service don't start after master db restore
I need to restore master Db on my SQL 2005. But I get an error.
From prompt I stop the sql instance, then restart it in single user mode with /m command. Then launching sqlcmd -s nameistance I've this ...
0
votes
2answers
49 views
How Important is the buffer pool really?
I have been doing some testing of a new (virtual) server which is to replace an existing production server. We suspect the current production server is overspec'ed and therefore are tweaking the ...
1
vote
1answer
21 views
Finding source query in execution plan of nested views and UDFs
I have a view containing N levels of nested views with UDFs and TVFs that I have to tune and my execution plan has 630+ nodes.
I found the performance issue in the plan but I can't find the source ...
0
votes
1answer
23 views
How to pivot using multiple columns in SQL Server?
I am having trouble creating pivot table that follows the schema described below.
For example, with the following table (not all records are shown here):
Route
Bus_Fare_Payment_Method
...
1
vote
2answers
195 views
SQL Server how to upload big json into column, performance issue
I am experiencing poor performance on a single row insert/update statement over a table when a nvarchar(max) column has few MB of data.
Here my table structure :
CREATE TABLE [dbo].[tbl_set_Cart](
...
0
votes
2answers
40 views
Can you embed DBCC Shrinkfile into a IF statement?
I have an issue where my maintenance job Transaction logs do not always shrink when DBCC SHRINKFILE is run.
My thought was to place DBCC SHRINKFILE within an if statement that checks the size of the ...
2
votes
1answer
136 views
Performance difference between environments when adding column
We have 2 exact same Database Environments. The second environment contains a copy of the production database and hosts approx 11M records in the Invoice table. The goal of this environment is used to ...
1
vote
0answers
29 views
Technical challenges for composite key
I'm sure this question has been asked before, but I'd rather put a twist on it to keep it not opinion based, and as informative as possible. (Also please forgive any mistakes I make in annotation, I'm ...
0
votes
0answers
56 views
SQL Server unexplained deadlocks events
We are using Microsoft SQL Server 2017 Standard Edition (64-bit) on AWS and we are suffering from unexplained deadlocks events.
We are trying to update different unique rows from a different processes,...
1
vote
1answer
34 views
Allocation of resources query
I have two tables, one for resources and another for the required allocation.
For instance:
Resource
Count
A
3
B
2
C
8
Required
Count
X
4
Y
3
Z
5
The resources table tells how many things I'...
-3
votes
2answers
97 views
Slowly running a query that always runs fast
I have two tables that 99% of operation on it is simple select. Rarely we have insert/delete and updates.
There is a inner join on these tables and execution plan of the query is index seek. 99% of ...
2
votes
1answer
49 views
Availability groups across sites: what happens if severs can't contact each other but both still can reach file share witness?
I'm currently planning a new SQL Server 2019 Enterprise Edition setup with HA/DR, possibly with availability groups.
Let say we have the following setup:
Site A: one SQL Server instance (SQL1)
Site B:...
8
votes
2answers
189 views
Is it possible to use OPENROWSET to import fixed width UTF8 encoded files?
I have an example data file with following contents and saved with UTF8 encoding.
oab~opqr
öab~öpqr
öab~öpqr
The format of this file is fixed width with columns 1 to 3 each being allocated 1 ...
2
votes
2answers
97 views
Unused Indexes - How Few Is Too Few?
When looking at seeks, scans and updates for indexes in SQL Server, what's a good rule of thumb for determining whether an index is 'unused enough' to be dropped? Maybe some ratio of updates to seeks ...
0
votes
0answers
41 views
Delay in prelogin response when connecting to Azure SQL
I have set up a linked server to an Azure SQL 2014 managed instance. The vendor gave me a SQL login and password to use. I can open a direct connection in SSMS and run queries. But when I try to run ...
-1
votes
1answer
47 views
Timestamp to indicate the latest event writing in a table [duplicate]
I want to know if there is any way to find out when the latest event was writing to a table in SQL Server.
Assuming the table does not have a created_at/updated_at columns and CDC and Change Capture ...
1
vote
1answer
73 views
Unable to restrict user from built in SQL Server Functions
We have a SQL Server (Standard) database with a user whom we are attempting to restrict permissions to only CRUD Operations to certain tables (CREATE, SELECT, UPDATE, DELETE) and only allow connect ...
-1
votes
0answers
32 views
Timestamp to indicate the latest event writing in a table [duplicate]
I want to know if there is any way to find out when the latest event was writing to a table in SQL Server.
Assuming the table does not have a created_at/updated_at columns and CDC and Change Capture ...
0
votes
1answer
74 views
Ola Hallengren maintenance solution
When I am executing the below query:
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@...
1
vote
1answer
55 views
SQL Server and Windows on the same disk (allocation unit size 64kb-4kb)
I am running a server with SQL server with 2 x 960GB NVME Datacenter SSD in RAID1, windows files and database and default database files on the same disk.
This server is using the default format ...
1
vote
2answers
52 views
Restore of database fails with '5(Access is denied)'
I am trying to restore database from another server, but I keep getting this error. I have tried changing security settings (giving permission) but still it keeps giving me this. Anybody have a clue ...
1
vote
1answer
111 views
Query Store internal queries being executed thousands of times per minute
I have an SQL Server 2016 Standard production server with more 59 clients Databases.
Microsoft SQL Server 2016 (SP2-CU17) (KB5001092) - 13.0.5888.11 (X64) Mar 19 2021 19:41:38 Copyright (c) ...
0
votes
1answer
51 views
MSSQL Large table rolling using partitions
I have a large DB (6TB) consisting of 3 tables: “messages”, “messagesDetails” , “unprocessedMessages”.
Here are simple descriptions of them:
TABLE [messages](
[id] [bigint] IDENTITY(1,1) NOT ...
0
votes
2answers
75 views
Do select statements start a transaction in auto commit mode?
I knew that update, insert, delete start a transaction in auto commit mode, I would like to ask if select statements make their own transaction also in auto commit mode or there is no need for that ?
...
-1
votes
1answer
42 views
Database Tuning for 3rd Party Software Database [closed]
I'm starting out as a SQL Server consultant trying to focus on maintenance and performance improvements. I'm using Brent Ozar's first responder's kit and tools. I've had good success with one client ...
2
votes
1answer
35 views
Transactional Replication vs AO for MS Sql Server
When we set up web servers in AWS, we have a load balancer which allows highly scalable performance improvements per instance provided.
I would like something similar for MS Sql server. I have been ...
-2
votes
1answer
32 views
Need to create a SQL Server Linked Server to an Oracle data source
Can someone kindly point me to a good documentation where I can get all the information I need to be able to pull data out of an oracle database from a SQL Server database please? For example, what ...
0
votes
0answers
25 views
Unable to perform the 'rds_restore_database' procedure on AWS RDS SQL Server Express
I'm facing the error Maximum supported database size on SQL Server Express edition is: 10 GB. when executing the restore procedure msdb.dbo.rds_restore_database.
The weird thing is the actual database ...
2
votes
1answer
61 views
+200
Powershell SMO changing database context from master when setting database to SINGLE_USER
I am trying to set a database to single user using SQL Server SMO and have the code below. This code is an abridged excerpt from a larger restore script
$Credential = Get-Credential
[System....
10
votes
0answers
184 views
+100
Does SQL Server 2019 have task scheduling differences between enterprise and standard editions?
In How It Works: SQL Server 2012 Database Engine Task Scheduling, Bob Dorr explains some of the changes for worker scheduler assignment in SQL Server 2012. He mentions that some of the improvements ...
-3
votes
0answers
42 views
group by and output based on count
Based on the following business determination that ONE 'exit' comprises (MIN time of entry and MAX time of exit), (meaning the cardholder has exited the facility). In reality there can be many '...
0
votes
0answers
27 views
read a nested JSON and find max values with dynamic keys [closed]
I am trying to parse a JSON string in SQL Server to find the Max value as follows:
DECLARE @json nvarchar(max) = ''{"value": [
{
"AEDAT": "20211119",
"ERDAT&...
0
votes
1answer
37 views
AWS RDS MSSQL cannot alter the availability group 'RDSAG0'. because it does not exist or you do not have permission
In AWS RDS MSSQL we have mirroring running between primary and replica via Always On.
We want to remove/drop a couple of databases, but when we try:
ALTER AVAILABILITY GROUP RDSAG0 REMOVE DATABASE [db-...
1
vote
1answer
44 views
Is there anyway in sql server transactional replication to not drop the target tables?
I have transactional replication setup between Server1 database and Server2 database on few tables.
Now the problem is when I start the replication for the first time, it drops the table on target ...
0
votes
1answer
19 views
Is Log stream compression enabled by default for asynchronous replicas in Distributed Availability groups as well?
I am seeding a database using distributed availability group. Seeding is automatic but synchronization mode is asynchronous. Log stream compression for automatic seeding is NOT enabled via TF 9567. ...
-7
votes
2answers
69 views
why does MS Sql Server not have active-active mirrors like other competing dbs? [closed]
MS Sql has a fake active-active system. They are all active-passive or 'pretend' active-active, with no load balancing. You cant just throw in 10 sql servers with non shared storage, attach a load ...
1
vote
1answer
56 views
Isolation level with SQL Server and Azure Sql Database Read-Only replica
I read here and here that, when connecting to a Sql Server read-only replica, the isolation level used is ALWAYS SNAPSHOT. It says it ignores SET TRANSACTION ISOLATION LEVEL statement and any locking ...
1
vote
0answers
22 views
Client to debug SQL stored procedure in Mac
I recently switched to Mac from Windows. I've started with Azure Data studio and VSCode. Is there a way to debug MS SQL stored procedure in Azure Data Studio? When I say debugging, I expect being able ...
0
votes
2answers
51 views
SQL Server data drive (.mdf file ) running out of space
What if my SQL Server data drive (where the .mdf file is stored) is running out of space?
There is only the .mdf file on the drive.
How to free up the space without taking database offline and without ...
1
vote
2answers
48 views
Execution time/cost of function in assembly
I have a query calling XmlTransform from Microsoft.MasterDataServices.DataQuality on about 1000 rows or so and it takes 30s to do the whole set (the XSL is in a fixed variable), is there any way to ...
0
votes
2answers
39 views
updating values in table to remove specific word
Some of my data has - Automated two or more times. I need to replace the text so it only appears once
I first tried to get the value to display by using this failed query
SELECT TRIM('- Automated') As ...
6
votes
3answers
430 views
Views versus a function for getting the top records of a table?
I'm talking with a coworker about a way to approach a problem.
We have a table that keeps track of a filling process, that has unique index columns (or should at least) on columns Lot and TestGrade.
...
