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,671
questions
0
votes
0
answers
39
views
Why is my SQL providing more than the number of data set I have?
I have a total of 5 records but here is the result of my query
0
votes
0
answers
57
views
Sql server database restoring from backup, sql users without login
After restoring a database from another SQL server, set database comes with database users without login. Do these users have any actual functionality or they are practically dead users? I've looked ...
- 79
0
votes
1
answer
46
views
SQL Server Worker Threads Management and Understanding
Hello SQL Server Community,
I'm trying to deepen my understanding of SQL Server's worker threads, especially with regard to the max worker threads setting in a busy OLTP system. I'm currently working ...
- 1,159
0
votes
0
answers
25
views
Mismatch Between Transactions and Sessions in DMVs sys.dm_tran_session_transactions and sys.dm_tran_database_transactions
I've been working with several Dynamic Management Views (DMVs) in SQL Server to monitor transactions, specifically sys.dm_tran_session_transactions, sys.dm_tran_database_transactions, and sys....
- 1,159
0
votes
0
answers
62
views
How do I determine if SQL OLE DB drivers are being used?
Today Azure Defender notified me that some OLE DB drivers are outdated on our two Azure VM's. However, I wasn't aware these are needed. Our VM's perform the following duties:
VM 1: Windows Server ...
- 840
0
votes
1
answer
28
views
Do SQL Server have the utility same as Oracle Wrap Utility?
i have made a useful scripts in my environment and i want to wrap the script same as Oracle Wrap utility can do. is there any other way to the same in sql server?
- 11
-2
votes
2
answers
42
views
What port is each instance using in a cluster?
I have two nodes in a Failover Cluster. There are three SQL roles / instances installed. I can connect to any instance with SSMS from a different computer by using RoleServer\InstanceName. One of the ...
0
votes
1
answer
164
views
SQL Server 2019, utf-8, replication problem
I know this is late to the game, but we're just upgrading to Sql Server 2019 after years and years on 2008.
I started using the Latin1_General_100_CI_AI_SC_UTF8 collation as an experiment and at least ...
- 207
1
vote
1
answer
88
views
SQL 2019 - BatchModeOnRowStoreUsed - Legacy Cardinality - IQP
Have recently migrated from SQL 2016 where the DB's compatibility level was all set to 2008 to SQL 2019 where the compatibility level for all DB's are now 150.
Upon running workload for a while, I ...
- 319
1
vote
2
answers
141
views
Is it possible to create a database without a transaction log?
I was recently told that it is possible to create a new database in SQL Server without a transaction log. I've worked with SQL Server for a very long time and I've never seen it done and I didn't ...
0
votes
2
answers
42
views
What to consider for choice of database account email - prevent 'out of office' reply problems
Here's the problem we are having: I have Job Notifications turned on for several SQL Server nightly Jobs. Sometimes those email notifications come to me when I'm out of office, and my Outlook is set ...
-1
votes
1
answer
53
views
For row-level or page-level, how to undo a compression in SQL Server? With SQL command and/or GUI Management Studio
We want to compress a table, using SQL statements like the samples below. And, just in case any issue happened after the compression, and we need to roll back to the last good point. How do we do it?
...
- 139
2
votes
2
answers
131
views
What happens if you stop the IndexOptimize job, because it is running long and causing blocking?
When the dbo.IndexOptimize job is running for long time and causing blocking, if we stop the job, does it cause Rollback of everything that was reorged or rebuilt? Or it just stop where it is and ...
0
votes
2
answers
122
views
Adding a Windows Server 2016 computer to a Windows Server 2022 Failover cluster
Is it possible to join a Windows Server 2016 computer to an existing Windows Server 2022 2-node Failover cluster? The cluster is domain-independent.
Hypothetical setup:
Node 1 (windows server 2022 + ...
- 47
0
votes
0
answers
57
views
Why is my Oracle database link to MSSQL not working after successful creation?
I am trying to establish a database link between Oracle, which is installed on a Linux server, and MSSQL, which is installed on a separate Windows server. I have already:
Successfully tested the DSN
...
0
votes
2
answers
52
views
Where to store DB files for SQL Failover Cluster Instance on Hyper-V
I am upgrading old off-lease hardware to brand new / full warranty enterprise-class host servers and 10gb iSCSI SAN. In the old environment, it was made up of a bunch of old Dell servers with lots of ...
- 3
0
votes
0
answers
38
views
What is the significance of failback setting in a SQL AG?
I have a 2-node AG with WSFC setting configured as allow failback between 3am and 7am.
I restarted node 1 at 1am which caused an automatic failover to node 2. However there was no automatic failback ...
- 3,442
0
votes
0
answers
77
views
Open file Azure Data Studio with CLI and connect
I am trying to start Azure Data Studio(1.46.1) from command line to open a file and connect to server and database. I can get it to work separately:
connect with new query
azuredatastudio --server ...
- 3,204
0
votes
2
answers
32
views
How to design a one-to-one relationship that can be either one-to-(parent) or one-to-(child)?
I'm creating a site where people can rate/review liquors ("Spirits"). Given that some spirits can vary wildly from bottle-to-bottle, I would like users to have the option to review either ...
- 103
1
vote
1
answer
129
views
How to perform SQL vulnerability assessment in SSMS 19.1 onwards?
I'm using on-prem SQL 2022 enterprise edition.
SQL vulnerability assessment option is taken out from SSMS 19.1 onwards.
I'm reading that this feature is now part of defender for cloud.
If anyone has ...
- 3,442
-3
votes
1
answer
37
views
Where can I check if the PARAMETER_SENSITIVE_PLAN_OPTIMIZATION is enabled or not on a db?
Where can I check if the PARAMETER_SENSITIVE_PLAN_OPTIMIZATION is enabled or not on a db?
I tried looking for it in sys.databases and also under the database properties UI.
Is query store enabling a ...
- 3,442
0
votes
1
answer
69
views
sql server replication issue
My SQL Server version is 2012 R2. I have one publishing machine with several publications, one distribution machine, and several subscription machines. Everything is working fine.
However, when I try ...
- 21
0
votes
1
answer
35
views
Developer with SQLAgentUserRole only has View access
I have a developer who needs to be able to create jobs in SQL Server Agent. They are part of the security group SQLDA and I granted that group SQLAgentUserRole in msdb. They still have view only ...
- 161
4
votes
0
answers
520
views
Non parallel plan with row level security predicate to check server role
While exploring Row Level Security I picked up that whenever I had it enabled it was not creating any parallel query plans.
In SQL Server 2019 this gave me
NonParallelPlanReason="...
- 41
0
votes
1
answer
106
views
How to compress data in SQL Server? The data warehouse raw data is times bigger than the MySQL OLTP database for the same tables
For reporting purposes, we have a DWH (data warehouse) doing ETLs (extract-transform-load) to retrieve data from selected tables in a production OLTP (on-line transaction processing) database.
The ETL ...
- 139
4
votes
2
answers
138
views
Table size increased after changing datatype from nchar to varchar
I am facing a strange behavior when changing datatype from nchar(100) to varchar(100) for one column.
I understand if I change datatype of a column where data is present then it can increase size but ...
- 113
0
votes
0
answers
78
views
Scalar udf abysmal performance
I have a udf. It is essentially a case statement, using ifs instead and multiple "broken" assignments of the same variable, see code below.
It does not access any table. Still, on one of my ...
- 437
0
votes
0
answers
27
views
Restore SQLServer Encrypted Backup on a server with higher version
I want to implement Backup Encryption. I have tested in Test server this process with success.
Before implementing on production I want to make sure that I am considering all risk.
The only thing that ...
- 210
0
votes
0
answers
98
views
WSFC - Unable to set node weight in Failover cluster
I have a two node Windows failover cluster configured with Always on availability groups which are configured for disaster recovery not for high availability. In this windows failover cluster we are ...
- 317
0
votes
1
answer
35
views
Can we do restoration using full backup on secondary and , differential and log backup on primary?
i have strange requirement in my environment but i need to ask this question to meet the business objective.
Can we do restoration using full backup on secondary and differential and log backup on ...
- 11
0
votes
1
answer
40
views
How can I obtain a set of results such that each row's date is x numer of days after the previous one?
Lets say I have a table of purchases that looks like this:
Purchases
---------
Item_ID
Purchase_Date
Customer_ID
How would I be able to obtain the set of purchases, starting from the first purchase, ...
- 149
0
votes
1
answer
52
views
What is the best option to remove duplicates in big heap?
Our Server is SQL Server 2016 Database with Full Recovery model.
We have table "A" - heap which has 280 millions of records and size 90 GB with 50 columns. This table contains around 5 ...
- 295
0
votes
0
answers
28
views
Visual Studio SQL Data compare: equal records, but it flag as delete/add
I'm comparing two table's database data, using Visual Studio SQL Data compare, updating target.
Once it finished, I do rescan, and it show me delete/add on some rows, even if the are the same:
Why ...
- 243
0
votes
1
answer
64
views
Looking for a better design approach while migrating to Oracle from SQL Server having too many databases
We are in the process of migrating from Microsoft SQL Server to Oracle. In SQL Server we have 60 databases(like XMLDB_1,XMLDB_2,XMLDB_3, ...... , XMLDB_60) to insert data after reading 60 XML files at ...
- 5
0
votes
1
answer
166
views
How do I modify session timeout on a replica for a Distributed Availability Group?
I have a Distributed Availability Group in my production environment that occasionally has a timeout.
When it does I get the following message in the AlwaysOn_health on the forwarder:
A connection ...
- 95
0
votes
3
answers
113
views
What are some sensible approaches for dealing with 4,000 vulnerabilities across 700 databases in 70 servers
I'm looking for ideas here. I've inherited a large SQL estate with multiple servers in multiple configs that has grown over 10-15 years with no specific DBA-skilled individual in charge. I'm the first ...
- 1
0
votes
2
answers
180
views
SQL Server Always-On adding database fails with insufficient free space
We had a crash on our SQL AAG where our secondary database ended up being corrupt.
The Primary database is all goood, and still working, and was not affected in any way.
So now I try to get the ...
- 1
0
votes
0
answers
32
views
Query takes really long
I am running into issues with this query. I am running on a VM, windows server 2019, MSSQL Server 2022, 128 GB of ram, and 8x of these processors:
Processor Intel(R) Xeon(R) CPU E5-2697 v4 @ 2.30GHz,...
- 1
2
votes
2
answers
993
views
Conditional WHEREs if columns exist
I have several tables which typically have nullable "CreatedDate" and/or "ModifiedDate" datetime columns. I would like a query that can select from any single table where one, both,...
- 133
1
vote
0
answers
23
views
Cleanup job for CDC failed
The clean up job for CDC failed with this error:
Msg 596, Level 21, State 1, Line 0
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 0
A severe ...
- 210
0
votes
1
answer
86
views
Wait time on server replies is sometimes very long when running the query over and over
As seen in this screen shot, I'm running the same query over and over again, comes back with 0 rows (as expected). For some reason I'm seeing very large wait times on server replies while running this ...
- 53
0
votes
1
answer
35
views
Clarification on Automatic Checkpoints and IO Latency Thresholds in SQL Server
I have been reading the SQL Server documentation and came across the following statement about automatic checkpoints:
Issued automatically in the background to meet the upper time limit suggested by ...
- 1,159
0
votes
0
answers
75
views
MS SQL server runs slowly on new Linux server
I am running a relatively small Access application with an SQL server 2019 backend, and have recently updated my ~10+yr old Linux system that is running the server. I was surprised to find that SQL ...
- 1
0
votes
1
answer
73
views
Failover in AG without listener
We have SQL Servers with versions SQL Server 2016 and SQL Server 2019. We already check that we can do failover on SQL Server 2019 without listener but I can not test it on SQL Server 2016. Do you ...
- 295
-1
votes
1
answer
119
views
SP_SERVER_DIAGNOSTICS_SLEEP wait type
I have one problem. SP_SERVER_DIAGNOSTICS is in suspended state and starttime is not refreshed. I execute procedure SP_SERVER_DIAGNOSTICS manually and it is working normally. Some forums say its ...
4
votes
2
answers
938
views
Balancing Indexing and Database Performance: How Many Indexes Are Too Many?
I'm part of the database team at my company, and I'm currently facing a dilemma regarding query optimization and performance. Whenever some of my colleagues come across a slow query, their default ...
- 1,159
0
votes
1
answer
26
views
Understanding the Relationship Between Log Records and Modifications a Log Record Can Do
I am currently playing with SQL Server's fn_dblog function and have come across a concept that I'm having trouble understanding. In the function's result, there is a 'Page ID' column and a 'Slot ID' ...
- 1,159
0
votes
1
answer
73
views
How do I identify what appears to be a CPU bottleneck within SQL Sever?
I have virtual machines on two different types of hosts (vmware).
Host type A has CPUs with a clock speed of 2.09GHz
Host type B has CPUs with a clock speed of 2.69GHz
All other resources are the same ...
0
votes
1
answer
53
views
Understanding of the %%lockres%% and %%physloc%% pseudo columns with index hint
I am experimenting with clustered and non-clustered indexes in SQL Server and I have noticed something interesting.
Here is a simplified version of my T-SQL code:
IF OBJECT_ID('dbo.dept') IS NOT NULL ...
- 1,159
0
votes
1
answer
109
views
Strange CPU assignment behavior in MS SQL Server 2022
Environment
We have a legacy system with a MS SQL Server 2012 database, which should be migrated to a new server with MS SQL Server 2022. Specs of the new system:
vServer with dedicated processor ...
Matthias