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.

Filter by
Sorted by
Tagged with
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 ...

1
2 3 4 5
617