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,839
questions
0
votes
0answers
20 views
Why does xp_logininfo shows less members for an AD group?
This question might be stupid, but it has been confusing me for a while...
First, I used xp_logininfo 'groupA', 'members' to show members of GroupA, as a result, member1 and member2 are listed, but ...
-1
votes
0answers
21 views
SQL Case statement with multiple condition
SELECT DISTINCT ST.ACCOUNT , ST.BALANCE , ST.DESCRIPTION
, case
when ST.BALANCE > 0 then 0
when ST.BALANCE < 0 then 1
when ST..DESCRIPTION like 'Fee%' then 2
end COMMENTS
FROM ...
0
votes
0answers
11 views
SQL RESTORE FILELISTONLY query brings error in event viewer - BackupIoRequest::ReportIoError: read failure on backup device
I use the following query:
(SQL VSS makes metadata2012.bak)
RESTORE FILELISTONLY FROM disk = N'E:\Tmp\metadata2012.bak' with CONTINUE_AFTER_ERROR
--or even without the following settings - same result
...
1
vote
0answers
21 views
SQL AAG Cluster Partner Quorum Failure - Duplicate DNS Records
Recently we had a failure of our SQL Server (2016) Always On Availability Group, where our fileshare witness node was briefly inaccessible to our two nodes due to a CPU spike.
We have two SQL server ...
-1
votes
1answer
38 views
Strange behavior with Quotename and substring
I am seeing some behavior that I cannot figure out. We have a sproc that joins a bunch of tables and filters down some pipe-separated (|) values in a column. We cross apply a string splitter (I ...
1
vote
1answer
37 views
Find Agent job name from PROGRAM_NAME in sys.dm_exec_sessions
I would like to find the name of an Agent job, but e.g.
Select login_name,
program_name,
host_name,
nt_domain,
nt_user_name
From sys.dm_exec_sessions
Does not show the ...
2
votes
3answers
81 views
How to get historical queries in SQL Server, based on time
Please how do I get the query that was running at a specific time in SQL Server 2016? Something similar to AWR report in Oracle? We need to know what was happening at a specific time in the past ...
0
votes
2answers
37 views
MS SQL: SSD Trim after TRUNCATE / DROP?
I consider to use one of those fancy new SSD-only SAN systems for our new Microsoft SQL Server. "Fancy" means, that it supports / use stuff as block compression / deduplication / 0x00-...
0
votes
0answers
24 views
Can I try SQL Server BIG Data Cluster for free?
I can not find information about test SQL Server Big Data Cluster in a development environment. We have some AKSes and would like to try installing SQL Server Big Data Cluster.
Is it possible to do it ...
0
votes
0answers
26 views
The data table cannot be updated when deleting the table change tracking records
A table is updated very frequently, with 50 million pieces of data. Change tracking is enabled for this table. The table records cannot be updated for a few minutes occasionally during production and ...
1
vote
2answers
34 views
generate alert if someone trigger ad-hoc backup and it's not copy_only backup
We are using 3rd party tool to backup SQL server databases. I am looking to setup an alert which gets generated whenever Ad-hoc backup is taken and it's not copy_only. We are using 3rd party ...
2
votes
1answer
92 views
Remove database from Distributed Availability Group replica AG ONLY!
all!
I have an existing always on availability group on-prem and last week created a second one in AWS. After that, I created a distributed availability group with on-prem as primary and AWS as the ...
0
votes
1answer
27 views
Sql server Backup & Restore From FileGroup
I Use Sql Server 2019 And need to backUp & Restore the FileGroup.
Execution steps :
1.Create The Database With Two FilesGroup and [TestFileGroup] name.
2.Insert data in two tables, one in First ...
-1
votes
0answers
30 views
Aggregate continually with group size specified in joined table
I think it's easiest to show what I want by an example.
I have a Deliveries table with columns DeliveryBatchID and DeliveryBatchSize and a Production table with columns ProductionBatchSize, ...
0
votes
1answer
70 views
Table Lock Escalation - Performance Questions
I have few questions on table lock escalations, please confirm or disprove:
Index Scan operator in a query results in Table Lock Escalation ? always or not (small tables) ?
When Shared lock is ...
7
votes
3answers
1k views
Improper use of logical operators has led to poor query performance
I have a table with huge amount of data (Nearly 15 million) and below structure.
create table test
(a int,--> /* There is a normal index on this column */
b int,
<other columns>)
There is a ...
0
votes
1answer
53 views
Strange Error ... Conversion failed when converting the varchar value 'www.com' to data type int
The issue that I am running into is happening on Microsoft SQL Server.
I have a view that is unioning together 2 tables. It is returning a little over 7 million records.
When I attempt to JOIN ...
5
votes
2answers
61 views
Index with IGNORE_DUP_KEY = ON filters out non-duplicate records
Below is a script which can be used to re-produce the issue I am facing.
Basically the question is this: why is it that the record with (ID1 = 6, ID2 = 7) is not populated into the final #tt2 table? ...
0
votes
1answer
59 views
Does the Query Optimizer use Incremental Statistics yet?
I was considering using incremental statistics in my data warehouse but I read an article by Erin Stellato that says the query optimizer doesn't use incremental statistics. The article was written in ...
2
votes
1answer
67 views
Azure SQL Database - Modify cost threshold for parallelism
I attempted to update the value of cost threshold for parallelism but since my database is an Azure SQL database I can't run the command:
EXEC sp_configure 'cost threshold for parallelism', 40 ;
GO
...
-1
votes
0answers
51 views
Attach of renamed files conflicts with original files
I am having difficulties attaching DB in a certain environment while the same scenario works in my dev or other production instances.
SCENARIO
There is a backup of database files (MDF, NDF, LDF, ...
0
votes
2answers
41 views
What is the best way in terms of non blocking table for creating NONCLUSTERED INDEX for partitioned tables?
We are using SQL Server 2016 and have 3 partitioned tables (every table around 300-500 milion of records). For every table would like to create NONCLUSTERED PARTITIONED INDEX. What is the best way to ...
0
votes
2answers
60 views
High SQL Server Usage
I run SQL Server 2019 with CU13 in Hyper-V vm and it seems that my hosting company is saying my sql server is doing too much disk io.
I am not a dba so i did poke around and found some queries that I ...
-1
votes
2answers
40 views
Linked server - When is it recommended to use SQL Server or the 4 Other data sources for SQL Server?
To configure linked server pointing to a SQL server, there are 4 possible options.
Server Type: SQL Server
Server Type: Other data source; Microsoft OLE DB Provider for SQL Server
Server Type: ...
-2
votes
0answers
30 views
stored procedure to create a variable for the two columns from different databases and make compare raise exception if don't match [closed]
I have two tables from different databases
Table1 with fields flid(int), pid(int)
Table2 with fields flid(int), pid(int)
All I want is to create the variables with integer for two columns(flid)and ...
1
vote
1answer
28 views
Should the remote user value be configured to map users defined in Local login or map users not defined in Login login?
Link: https://docs.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine?view=sql-server-ver15#to-create-a-linked-server-to-another-instance-of-...
1
vote
2answers
58 views
Are pages with free space always reused?
Microsoft documentation on the allocation of pages and extents includes the following sentence:
The SQL Server Database Engine allocates a new extent to an allocation unit only when it cannot quickly ...
3
votes
1answer
863 views
Why are there no "Daylight Time" time zones in SQL Server?
I am converting the times of some datetimeoffset columns from UTC to Eastern. I am doing this via the command
UPDATE MyTable SET MyColumn = MyColumn AT TIME ZONE 'Eastern Standard Time'
This seemed ...
4
votes
1answer
102 views
Updating rows in Azure SQL Server causing unexpected page splits
I'm getting a lot of page splits in a live environment using Azure SQL Server PAAS that I don't understand. The update that's occurring should not increase the size of the row and therefore never ...
0
votes
0answers
21 views
Visual Studio Data Tools (SSDT) schema comparison tool does not match database objects
I am using Visual Studio Pro 2019 (16.9.4) and SQL Server Data Tools (16.0.62103.10080) but this happens since the beginning of Visual Studio 2019.
I use the Schema Comparison tool to compare ...
1
vote
0answers
32 views
PostgreSQL tds_fdw Column Truncated to 2048 Characters
I set up several tds_fdw foreign tables on a Postgres 13 server accessing a MSSQL 2014 server. Everything has worked just fine, until I had a need to select from a table with a large column (nvarchar(...
-2
votes
1answer
55 views
Does linked server local login value support a windows AD group based sql login?
Link: https://docs.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine?view=sql-server-ver15#to-create-a-linked-server-to-another-instance-of-...
1
vote
0answers
52 views
Many sessions AWAITING COMMAND
I need some guidance please.
An application is going through migration and the processes seem to be quite cpu intensive. CPU utilisation on the server is very high - in the 90's.
The headblocker ...
-1
votes
1answer
42 views
Suggestions to create a table or to design a stored procedure to remove while loops
The company I am currently working for, has a scenario for which we have to use while loops or cursors in SQL. Which has a huge performance impact in our case, hence, I want to optimize it.
The ...
1
vote
1answer
45 views
What elements of sql server are impacted due to removal of domain/AD?
Sql server contains logins from 3 different domains (Under security\logins). These are mapped to database users. There are 15 databases.
One of the domain/AD is planned to be decommissioned next year.
...
0
votes
0answers
40 views
MS SQL 2019 operator in k8s is ready for production?
ITNOA
I am searching about MS SQL Server 2019 K8s operator very much, but I don't find any comprehensive documentations or help pages
I just found Availability Groups on Kubernetes in SQL Server 2019 ...
2
votes
2answers
42 views
Are SQL Security Updates Cumulative? Are the vulnerabilities corrected with one Security Update also covered by the next Security Update?
I recently patched SQL Servers that had SP3 and CU4 with the latest available Security Update. After CU4 there are 2 Security Updates available, one from 2020/02/11 and the other one from 2021/01/12.
...
0
votes
1answer
32 views
How to do a count/calculation in a query
I am trying to figure out a way to do the following sort of query..
I have a table that has a bit value and I want to calculate the percentage of records where the bit =1.
An example of the table ...
0
votes
2answers
99 views
Reduce rows read by the database engine for this aggregate query
I would like to tune this query - it is reading more rows than I would like. I've added an index as suggested by the Database Tuning Advisor.
The query goes like this:
SET STATISTICS TIME, IO ON;
...
0
votes
1answer
40 views
CREATE DATABASE permission denied in database 'master'. Cannot attach
In a T-SQL script, using a connection string like this:
Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=C:\Temp\ContextTest.mdf;Initial Catalog=ContextTest;Integrated Security=True
... I ...
...
1
vote
1answer
40 views
How do I prevent a column being replicated in SQL server?
I'm on MSSQL 2016 using push-based transactional replication to an Azure instance. I have a few columns in our modeled data that I really do not want to replicate.
Is there a way to flag a column as &...
0
votes
1answer
43 views
Handling MSSQL Insert degradation
I have a SQL Server 2019 Express with no extended configuration aside from custom memory allocation which is as followed:
10GB Memory Usage Maximum
0 Minimum
0 Set for Dynamic Index Creation
10240KB ...
0
votes
0answers
16 views
Cannot restore the snapshot due to option group issue
There's an rds instance which uses ms sql ee with a database that uses TDE
The rds itself is configured with the option group which has the TDE option within it and the snapshot gets created with the ...
3
votes
1answer
165 views
Can optional foreign key cause trouble in the long term?
In a nutshell, we have two tables:
Document 1 (strong entity)
Document 2 (weak entity)
Document 1 has many Document 2 and Document 2 has only one Document 1.
That being said, sometimes we need to ...
-1
votes
0answers
80 views
How to replace clustered index with non-clustered fast? [closed]
Can clustered index be effectively used in this case? We have a large table that keeps logs of users’ actions. 189-190GB clustered index. We want to analyze data there daily and run more complex ...
0
votes
1answer
38 views
SQL Server 2019 Always On - Ensure that cluster name object (CNO) is granted permissions to the Secure DNS Zone
I have here a SQL Server 2019 Always On with a lot of errors like this one:
2 nodes configured in a cluster with file share witness.
Cluster name: SQLCLS
Listener name: SQLLSN
Event:
Cluster network ...
2
votes
1answer
89 views
How to check which pages are part of an extent?
I have some data corruption and was not able to restore the data via backups. It was affecting mainly non-clustered indexes, so after drop-create of the indexes the issues were resolved.
Finally, I ...
0
votes
1answer
69 views
SQL Server: Nested IF 3 level
I'm trying to update my control table whenever data is getting inserted or updated on the database. For this, I have created a stored procedure that will get the table name(@TableName), the number of ...
1
vote
0answers
39 views
What is the minimum hardware requirements for SQL Server Big Data Cluster?
I am in process of making POC and would like to test SQL Server Big Data Cluster solution. My first idea is to host it on my local computer and test it but I am not sure If it possible. In found ...
0
votes
2answers
56 views
Sum of pages in the data file does not equal the file size
I am not trying to solve a specific problem, but just trying to understand.
I have read several places that the data file (let us just assume one mdf file) is split into 8 KB pages. But when I sum ...