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
2 votes
1 answer
18 views

Minimise duration of table lock

I've got a SQL table that needs to be updated daily. There may or may not be queries against that table while the update is happening. It's around 500,000 rows. We have an issue where there is locking ...
-6 votes
1 answer
37 views

Does offline index rebuild block user's SELECT/UPDATE/INSERT/DELETE queries?

I understand that index rebuild requires schema stability (Sch-S) lock which means it will block any query that tries to do the schema modification (Sch-M). Does an offline index rebuild also block ...
0 votes
3 answers
6k views

SQL agent jobs hung state (SSIS jobs hung)

For a couple of weeks I have been experiencing a weird issue on SQL Server 2016 SP1. We migrated a critical server from SQL Server 2008 R2 to SQL Server 2016 with SP1 using side-by-side. We migrated ...
0 votes
0 answers
19 views

How to figure out which procedure or function is causing the most logical reads on a particular table?

One of my tables only has about 5k rows, but is the table with the most logical reads, of all the tables in the database (based on dm_db_index_usage_stats). I'd like to work on the indexes to this ...
0 votes
0 answers
15 views

xp_sqlagent_enum_jobs - Unable to enumerate Jobs due to failure to obtain serialization object

I a procedure whose job is to ensure that certain SQL agent jobs do not overlap with other jobs, the logic is as follows(Simplified): CREATE TABLE #Job ( job_id UNIQUEIDENTIFIER NOT NULL ,...
0 votes
0 answers
18 views

How to filter out some database roles from sql server login audit?

I have in place a Server audit that collects logins. Every 5 minutes I import that data into a table in another instace where I build and send by email a small report. That's fine but i need to set up ...
0 votes
2 answers
55 views

Using Boolean Fields in a View to Identify Linked Tables

I am using SQL Server 2019. I am using SQL Server Management Studio on the client side. Suppose I have the following data model: -- A record of the overall test of a widget CREATE TABLE [TestRecords] ...
0 votes
0 answers
20 views

Can't identify which server is primary and which is secondary in a SQL Server Availability group

I'm writing an application that queries the backup history for a number of databases on a number of SQL Server 2017 servers. Assuming a pair of servers (SQL001 and SQL002) and a database called "...
-4 votes
1 answer
27 views

Why is SQL AG sync commit mode classified as no data-loss HA solution even when the session timeout can make the mode async?

Assuming the SQL AG is in sync commit mode. Suppose the secondary becomes unresponsive or has secondary has network issues, then upon the session timeout expiry the AG will automatically get converted ...
2 votes
2 answers
166 views

Non-clustered Primary Key and Clustered Index

It is my understanding that in SQL Server, you can have a Primary Key that is non-clustered, and have another index that is the clustered one. To me, this seems the same as just having a Primary Key, ...
0 votes
0 answers
20 views

How to check that auto-Numa is enabled?

I run query select cpu_count, hyperthread_ratio, softnuma_configuration, softnuma_configuration_desc from sys.dm_os_sys_info and got results, where said, that numa is off: cpu_count ...
0 votes
0 answers
23 views

Two views yielding the same results switch places performance-wise when called remotly

An SQL Server 2000 has a view "A" that produces around 700 rows of data in about 30 seconds. I was tasked with optimizing "A" and created a view "B" that produces the ...
1 vote
1 answer
2k views

How to find cause of SQL-Server Full-Text Index Auto-Population Error?

I have recently added a few full text indices to a database and AUTO populating is not working as I would have expected. Sample Table Definition CREATE TABLE [wrk].[WorkOrder] ( [id] ...
0 votes
1 answer
31 views

Restrict access to a new schema to a specific role despite users having db_datareader?

I'm working in a SQL Server 2016 database that is not of my design nor can I fundamentally change the security structure. I know it is likely trash/worst practice but I have to play the hand I am ...
2 votes
1 answer
170 views

SQL Agent stopped recording history (SQL Server 2017)

I have a Dev server that has been running without issue for at least 2 years. This dev machine was a VM Clone of the Production server. On 2021-06-05 something happened on this server that altered ...
0 votes
0 answers
24 views

How to use "with execute as" while querying a linked server from SQL Server

I have a strange permission issue with SQL Server 2019 (same thing happened also with 2016 version). I have a database ReportDB, where reportuser (login: reportlogin) can run several reports. I also ...
0 votes
1 answer
37 views

What is recommended approach for fixing corruption related errors?

When there are errors of code 823 in the log or dbcc has detected corruption, then should the 1st choice to solve this be: dbcc checkdb with no data loss or restore from full backup+tlog backups?
1 vote
1 answer
39 views

Unlike FCI, doesn't AG make use of heartbeat to check for server down?

SQL AG in WSFC checks whether server is down via the lease timeout Where as SQL FCI checks whether server down via heartbeat Does the SQL AG not make use of heartbeat at all? Below is a sample log ...
1 vote
1 answer
21 views

Does SQL server provide automatic page repair in the async commit mode AG?

I understand that SQL AG supports the automatic page repair when the primary is synchronized with the secondary. Suppose the AG is configured in an async commit mode, then does also mean that the ...
-1 votes
0 answers
30 views

Does restart in FCI/AG mean node restart or sql server cluster aware services restart?

There are failure condition levels for FCI and AG. I read the keyword restart. Just want to confirm whether restart means node restart or sql server cluster aware services restart?
2 votes
1 answer
228 views

SQL Server: Is it worth checking for change in a field before the update?

I'm investigating this statement performance on SQL Server 2019 (I've extracted the important part): UPDATE m SET A1 = src.A1, A2 = src.A2, A3 = src.A3, ...
0 votes
1 answer
21 views

Does dynamic quorum having any solution for partition in time?

Assume 2 node 1 file share cluster (Node 1 is active), where say the file share is down for some reason. The dynamic quorum will allow cluster to continue working and assign the voting capability ...
3 votes
2 answers
19k views

SQL Server - Linked Server - Using OPENROWSET with windows integrated security

I have seen many articles regarding OPENROWSET using integrated security (Windows Authentication), but I could not make it work for me. It is working fine using SQL Server authentication: select * ...
-4 votes
0 answers
34 views

How to check whether there was an manual or automatic failover in an AG? [duplicate]

I have configured SQL AG (WSFC). How to check whether there was an manual or automatic failover in an AG? Is there any specific extended event or log that tells this?
0 votes
1 answer
26 views

Is disk witness still the recommended best option for 2 node cluster (versus file share) in SQL server 2019?

Link: https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2008-r2-and-2008/cc770830(v=ws.10)#local-two-node-cluster It says that the disk witness is the recommended best ...
0 votes
1 answer
22 views

How does SQL server handle the password change of the service account?

When installing the SQL server, when asked for the sql service account, I have entered a domain user and its password. In future, when the password of the domain account will change, then will I have ...
0 votes
2 answers
203 views

Error 23 on transaction log backup, full backup works fine

I have a database with the database files on the E: drive, and logs files on the D: drive. I'm getting the following error when trying to run a transaction log backup: Read on "D:\LOG\AOA_Log.ldf" ...
-1 votes
0 answers
40 views

Could not find an entry for table or index with partition ID in SQL

How to fix this error? Many links mentioned upgrading the SQL Server and I have already updated my SQL Server 2014 to SP3, but I'm still getting the error. Also, I am not able to drop a table having ...
0 votes
0 answers
8 views

In WSFC, can the child volume be shared among instances? and if so, then what is the purpose of this (sharing)?

Link: https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/install/before-installing-failover-clustering?view=sql-server-ver15#Hardware The base drive, the one with the drive letter, ...
0 votes
0 answers
8 views

During scheduled maintenance of secondary node, should I set the non-primary node weights to 0 or should I set the FailoverConditionLevel to 0?

During scheduled maintenance of the secondary nodes in an FCI, should I set the non-primary node weights to 0 or should I set the FailoverConditionLevel to 0 to prevent the failover/restart?
-1 votes
0 answers
12 views

When the connection is lost, then does the Resource DLL restart the timeout ignoring the previous calls?

The following items describe how this property affects timeout and repeat interval settings: The resource DLL calls the sp_server_diagnostics stored procedure and sets the repeat interval to one-third ...
0 votes
0 answers
10 views

What are the specific changes that need to be made in client code to handle multi-subnet FCI?

The client application then retrieves all registered IP addresses from the DNS server and attempts to connect to the addresses either in order or in parallel. This means that client recovery time in ...
0 votes
0 answers
8 views

Why does WSFC recommendation include configuring odd number of votes when this is automatically handled by dynamic quorum and dynamic witness?

Link: https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/wsfc-quorum-modes-and-voting-configuration-sql-server?view=sql-server-ver15#RecommendedAdjustmentstoQuorumVoting Odd ...
0 votes
0 answers
9 views

How to prevent split brain scenario in multi subnet cluster during network failure between the subnets?

Suppose I configure a multi subnet cluster say with 3 nodes in subnet1 and 3 nodes in subnet2. Since the total count is 3+3=6, which is an even number, one of the nodes will be auto assigned 0 weight (...
1 vote
0 answers
10 views

What is the voting process in WSFC?

Node and File Share Majority. Similar to Node Majority quorum mode, except that a remote file share is also configured as a voting witness, and connectivity from any node to that share is also counted ...
0 votes
2 answers
1k views

Database was shutdown due to error 9001 in routine 'XdesRMFull::CommitInternal'

I have encountered this situation on one of our production server. The database came backup and DBCC Check reported no errors. Also, there were no other errors logged in Event Viewer. I know I am ...
11 votes
3 answers
20k views

EXEC vs SP_EXECUTESQL Performance

Recently we used a sql code reviewing tool against our database. It is suggesting to use SP_EXECUTESQL instead of EXEC. I know SP_EXECUTESQL helps us to avoid sql injection. Is there any difference ...
-1 votes
1 answer
24 views

What is the concept of service master key in FCI?

In a single sql server instances, sensitive data like passwords in linked server and credentials are encrypted using the server master key. The key or objects can be decrypted only by the sql service ...
1 vote
1 answer
110 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 ...
44 votes
7 answers
259k views

How to handle TimeZone properly in SQL Server?

My local development server is in the Middle East, but my production server is in the UK. I need to show the date to the user in their time zone. For example, if a user is in Saudi Arabia then I ...
0 votes
1 answer
367 views

SQL Server Agent unable to view network drives

I am unable to get agent jobs to output to a network path. I have pushed the IT guy to set up a domain authenticated user that logs in when the agent starts. That login does have access to the domain ...
3 votes
2 answers
465 views

SSRS Azure SQL Database Active Directory - Universal with MFA

Does anyone know when support for Active Directory - Universal with MFA will be included in Reporting Services? It's available in SSMS. Visual Studio 2017 Data Source Properties Reporting Server ...
15 votes
5 answers
3k views

Can ransomware embed itself in to a SQL backup file?

One of the best protections against ransomware is to back up all of your database files to a completely separate system. Which we have done. But one thought is the backup of the database could ...
0 votes
1 answer
31 views

How is the size of the buffer pool (data cache) calculated?

What is the formula for calculating the maximum size of the buffer pool (data cache) for SQL Server (when there is only one instance of the machine). Say the machine has 1 GB of physical memory, what ...
0 votes
1 answer
22 views

How to filter extended event session by sp execution time (seconds)?

Trying to view all executions of a named stored procedure where execution time exceeds a given number of seconds. How do you filter on execution time (seconds)? This is what I have cobbled together ...
1 vote
1 answer
136 views

Shredding Query Plan XML For Potential Skewed Estimates - Data Type Conversions

I'm using the below T-SQL to check for root level estimate skews in execution plans by pulling the estimated rows from the execution plans and comparing to the last rows from the sys....
1 vote
2 answers
57 views

Bypassing LCK_M_SCH_S lock when requesting partition information

I have code to query partition data. But when the database creates an index, my query does not work because of the LCK_M_SCH_S lock, although I use the NOLOCK hint for all tables. Is there any way ...
0 votes
1 answer
366 views

Security user audit

This has probably been asked a million times already and for this i apologise, but is there a way or any T-SQL I can run that will tell me, the server, the database, users login name, what permissions ...
0 votes
0 answers
16 views

can we prioritize replication of some tables in replication job

We have a replication job that runs daily. It replicates all the tables in some order. We wanted to check if we can prioritize replication of some tables over others
3 votes
1 answer
532 views

NUMA configuration

We have clustered servers, each of which has 32 CPU cores and 512 GB memory. The cluster is home to 7 instances of SQL Server 2012 Standard Edition. With Standard Edition allowing a maximum of 64 ...

1
2 3 4 5
637