SQL Server Support Blog articles

Caution When Using a Database in Single User Mode

Yohei_Kawajiri — Tue, 24 Mar 2026 01:33:54 GMT

By setting a database to single-user mode in SQL Server, you can make it accessible to only one user.If there is a session using a database set to single-user mode, any other session attempting to access the database will be put in a state of waiting to acquire an exclusive lock.

Given this behavior, caution is needed when performing operations that acquire locks on the database from the session that set it to single-user mode.
After setting single-user mode, the session holds a shared lock on the database. If, within that session, you attempt to acquire an exclusive lock on the database while another session is already waiting to acquire an exclusive lock, a deadlock may occur, causing one of the operations to fail.

For example, when using the log shipping feature with the secondary in standby mode, this behavior can affect the restore job.
Here is a specific sequence of events:

The log shipping restore job sets the database to single-user mode before performing the restore. At this time, it acquires an exclusive lock on the database.
The restore job transitions the exclusive lock on the database to a shared lock.
Another session attempts to access the database and waits to acquire an exclusive lock.
The restore job tries to acquire an exclusive lock to perform the restore, but is blocked by the session waiting for the exclusive lock, resulting in a deadlock.
If the restore job becomes the deadlock victim, it will fail with an error.

Normally, the time between steps 2 and 4 is very short, but in environments where there is frequent access to the database, this issue is more likely to occur. Even if the restore job fails with an error, the next time the job runs, it will also restore the transaction log backups that failed previously. Therefore, it is recommended to check for any impact according to the service level of your system.

(The following content is in Japanese)

SQL Server ではデータベースをシングルユーザーモードに設定することで、そのデータベースを 1 ユーザーでのみアクセス可能な状態にすることが出来ます。シングルユーザーモードに設定したデータベースを使用中のセッションがある場合、それ以外のセッションでそのデータベースにアクセスしようとすると、排他ロックの獲得待ちの状態になります。
この動作を前提として、シングルユーザーモードを設定したセッションでデータベースに対してロックを獲得する操作を行なう場合は注意が必要です。

シングルユーザーモードに設定した後、そのセッションはデータベースに対して共有ロックを保持します。そのセッション内でデータベースへの排他ロックを獲得しようとする処理を実行する際に、既に他のセッションがアクセスしようとして排他ロックの獲得待ちとなっていた場合、デッドロックとなり、いずれかの処理は失敗する場合があります。

例えば、ログ配布機能でセカンダリをスタンバイモードで使用していると、復元ジョブでこの動作の影響を受ける場合があります。

以下に具体的な流れを紹介します。

1) ログ配布の復元ジョブは復元を実行する前にデータベースをシングルユーザーモードに設定します。この時データベースに対して排他ロックを獲得します。

2) 復元ジョブはデータベースに対する排他ロックを共有ロックに移行します。

3) 他のセッションがデータベースにアクセスしようとして排他ロックの獲得待ち状態になります。

4) 復元ジョブが復元を行うためにデータベースに排他ロックを獲得しようとしますが、排他ロック獲得待ちとなっているセッションにブロックされ、デッドロックが発生します。

5) 復元ジョブがデッドロックの Victim となった場合、復元ジョブはエラーとなります。

通常、2) と 4) の間は非常に短時間ですが、対象のデータベースに対して頻繁にアクセスが発生する環境ではこのような現象が発生する可能性が高まります。

なお、復元ジョブがエラーとなった場合でも、次のジョブ実行タイミングで前回失敗した分のトランザクションログバックアップも合わせて復元されます。

そのため、利用システムのサービスレベルに応じて影響の有無を確認することをお勧めします。

Troubleshooting SQL Server Backup Failures to Azure Blob Storage

v-sanone — Wed, 18 Mar 2026 14:47:42 GMT

This document helps to Troubleshoot SQL Server Backup Failures to Azure Blob Storage.

Backing up SQL Server databases directly to Azure Blob Storage using BACKUP TO URL is a common and recommended practice on Azure SQL Virtual Machines. However, we often encounter backup failures that are not caused by performance issues but by hard Azure Blob size limits.

This article explains why SQL Server backups fail due to Page Blob or Block Blob limits, how to recognize the symptoms, and the correct configuration patterns to prevent these failures.

This blog walks through the most frequent causes of backup failures and explains how to identify and resolve them.

1. Backup Failures Due to Page Blob Size Limit (1 TB):-

One of the most common causes of backup failure occurs when SQL Server writes backups to Page Blob–based storage and the database size exceed 1 TB.

When this limit is reached, backups may fail with errors such as:

Operating system error 50 (The request is not supported)

Why This Happens

Page Blobs have a hard 1‑TB size limit per file.
Older or legacy storage accounts may still default to Page Blob usage.
Single‑file backups for large databases can easily exceed this limit.

Recommended Resolution :-

The most effective solution is to use Block Blob storage with striped backups:

Use a General Purpose v2 (GPv2) storage account.
Split the backup into multiple files (striping) so that the workload is distributed.
Enable compression to reduce backup size.

One of the most common causes of backup failure occurs when SQL Server writes backups to Page Blob–based storage and the database size exceeds 1 TB.

2. Block Blob Limit Reached During Backup :-

Even when using Block Blobs, backups may still fail if a single backup file becomes too large.

Symptoms :-

Backup starts successfully but fails midway.
Errors indicating that the blob size limit has been exceeded.
Backup files growing rapidly but stopping before completion.

Root Cause :-

Block Blobs have a practical size limitation per file.
Using a single backup file for very large databases.
Insufficient number of stripes for the database size.

Also tune performance parameters such as:

MAXTRANSFERSIZE
BUFFERCOUNT

This reduces throttling and improves reliability during large backups.

Rule of Thumb for Backup Striping:-

1 backup stripe ≈ 150–200 GB

Database SizeRecommended Strategy< 200 GBSingle backup file200 GB – 1 TB2–4 striped backup files> 1 TB8–64 striped backup files

Example :-

For a 2 TB database:

2 TB ÷ 200 GB ≈ 10 stripes

Recommended: 12 or 16 backup files

3.SAS Token and Stored Access Policy Issues:-

Authentication problems are another frequent cause of failures when backing up to Azure Blob Storage.

Common issues :

SAS token begins with?
SAS token is expired
Required permissions are missing
SAS is scoped to the wrong container or account
Stored access policy was changed or deleted

Required SAS Permissions :

Read (r), Write (w), Create (c), List (l) — recommended

Resolution :-
Regenerate the SAS token with correct permissions and scope
Remove the leading? if present
Recreate the SQL Server credential
Test the SAS token using Azure Storage Explorer or CLI before retrying the backup

4. Operating System Error 50 – “The Request is Not Supported”:-

This error is often misleading but commonly appears during backup failures to Azure Blob Storage.

What Triggers OS Error 50

Page Blob exceeding the 1‑TB size limit.
TLS 1.2 not enabled on older operating systems.
Storage account firewall blocking access from the VM.
Legacy SQL Server or OS versions lacking modern protocol support.
Key Checks

Confirm SQL Server version (SQL Server 2016 or later recommended).
Ensure TLS 1.2 is enabled.
Verify outbound access to Azure Blob Storage on port 443.
Confirm the storage firewall allows the VM network or enables “Trusted Microsoft services”.

In many cases, switching to Block Blob + striped backups immediately resolves this error

SQL Server Backup Failure Troubleshooting Flowchart :

This flowchart outlines the step‑by‑step diagnostic path for identifying and resolving SQL Server backup failures.

Other errors and resolutions:

Below flowchart represents troubleshooting process for various types of errors while performing backup.

Authors:

Sara Tabassum ( v-sanone@microsoft.com)

Vinay Prasanna Sarangam ( vinay.sarangam@microsoft.com)

Troubleshooting Replication Performance Issues

VinaySarangam — Fri, 12 Dec 2025 23:10:34 GMT

This Document helps to troubleshoot or Resolve Replication performance related issues.

Get information about “Topology Big Picture”:-

Before you dive into solving any issue, you need to fully understand the type of environment you have as there might have been changes you are unaware of. An easy way to do that is to run ReplScripts/Replication Topology Script.sql at master · ReplTalk/ReplScripts which gives output like below.

Review the output for details about publishers, publications, and subscriptions.

1. Check for replication issues using tracer tokens:-

Purpose of tracer tokens:-

Latency measurement:- Measure the time it takes for data to travel from the publisher → distributor → subscriber.

Connection validation:- Validate whether replication connections between components are functioning correctly.

Troubleshooting: Identify bottlenecks or delays in the replication process.

Using Measure latency & validate connections (Transactional) - SQL Server | Microsoft Learn, We can identify whether the issue is from Publisher to Distributor or Distributor to Subscriber.

Below image says how data flows from Publisher to Subscriber through Distributor After applying initial Snapshot.

As mentioned in the below image

If the latency is in between publisher -> Distributor then the issue might be in either

- LogReader-Reader Thread

- LogReader-Writer Thread

If the latency is in between Distributor -> Subscriber then the issue might be in either

- Distribution-Reader Thread

- Distribution-Writer Thread

2. Performance Statistics Tools:-

Performance statistics were added to the mslogreader_history table and the msdistribution_history table on the distribution database in Microsoft SQL Server. You can use these statistics to see the recent performance history of the Replication Log Reader and Replication Distribution agents.

Every five minutes, the performance statistics for the Log Reader and Distribution agents are recorded in the history tables. By default, only the data for the last 48 hours is retained. A cleanup process removes the data that is older than 48 hours. The default value can be changed by executing the sp_changedistributiondb stored procedure and specifying a new value for the history_retention parameter.

The following is a sample performance output from the history table for the Log Reader Agent:

- <stats state="1" work="9" idle="295"> <reader fetch="8" wait="0"/> <writer write="9" wait="0"/> <sincelaststats elapsedtime="304" work="9" cmds="52596" cmdspersec="5753.000000"> <reader fetch="8" wait="0"/> <writer write="9" wait="0"/> </sincelaststats> </stats>

There are three state events that can be recorded:

Here’s the Markdown representation of your table:

State	Description
1	Normal events that describe both the reader and writer thread performance
2	Raised events that occur when an agent's reader thread waits longer than the agent's `-messageinterval` time. (By default, the time is 60 seconds.) If you notice State 2 events that are recorded for an agent, this indicates that the agent is taking a long time to write changes to the destination
3	Raised events that are generated only by the Log Reader Agent when the writer thread waits longer than the `-messageinterval` time. If you notice State 3 events that are recorded for the Log Reader Agent, this indicates that the agent is taking a long time to scan the replicated changes from the transaction log

2.1. Log Reader Agent Reader Thread :- The following performance statistics demonstrate a situation in which there is latency in the replication topology and in which the bottleneck is the Log Reader Agent reader thread. The Log Reader Agent reader thread scans the published database transaction log for commands to deliver to the distribution database(<Distribution server>..MSlogreader_history.Comments)

</sincelaststats>

</stats>

The sincelaststats writer wait statistic of 288 seconds appears high. This is the time that the writer thread waits for the reader thread to supply buffers to apply. The Log Reader Agent reader thread executes the sp_replcmds stored procedure. If you notice high writer wait threads in the Log Reader Agent performance statistics, you should investigate the performance of the Log Reader Agent execution against the publication server(Which means Logreader Agent Reader Thread) and database and then investigate the execution time of the sp_replcmds stored procedure.

2.2. Distribution Agent Reader Thread:-The following performance statistics demonstrate a situation in which there is latency in the replication topology and in which the bottleneck is the Distribution Agent reader thread. This thread queries the distribution database (< Distribution server >..MSdistribution_history.Comments table) for commands to apply at the subscriber

</sincelaststats>

</stats>

The sincelaststats writer wait time (212 seconds) appears high. This is the time that the writer thread waits for the reader thread to supply buffers that the writer thread can apply at the subscriber database. The Distribution Agent reader thread executes the sp_MSget_repl_commands stored procedure.

If you notice high writer wait times in the Distribution Agent performance statistics, you should investigate the performance of the Distribution Agent execution against the distribution server and database. In particular, you should investigate the execution time of the sp_MSget_repl_commands stored procedure.

2.3. Distribution Agent Writer Thread:- The following performance statistics demonstrate a situation in which there is latency in the replication topology and in which the bottleneck is the Distribution Agent reader thread. This thread queries the distribution database (< Distribution server >..MSdistribution_history.Comments table) for commands to apply at the subscriber.

Notes:- The state is 2, and the output is somewhat different from State 1 statistics. State 2 state data indicates that the reader thread had to wait longer than the Distribution Agent's configured -messageinterval value. By default, the -messageinterval value is 60 seconds.

If the -messageinterval value is increased, you may again receive State 1 statistics that resemble the following:

Note:- The sincelaststats fetch wait time of 505 seconds is very high.

If you notice high reader wait times in the Distribution Agent performance statistics, you should investigate the performance of the Distribution Agent execution against the subscriber server and database. Use the profiler trace tool to investigate the performance of the execution of the replication stored procedures. Usually the stored procedures are named as follows:

sp_MSupd_<ownertablename>
sp_MSins_<ownertablename>
sp_MSdel_<ownertablename>

Additionally, to determine whether the bottleneck is hardware-based or system-based, use the performance monitor to monitor system performance. For example, use the performance monitor to monitor the Physical Disk counters.

Kindly refer below link to know more about Replication Performance Tools: Statistics for Log Reader and Distribution agents - SQL Server | Microsoft Learn

In some scenarios, we will not be able to get the required information from above mentioned history tables. In such scenarios we need to generate Replication Verbose Logs as mentioned in the link: https://learn.microsoft.com/en-us/sql/relational-databases/replication/troubleshoot-tran-repl-errors?view=sql-server-ver17#enable-verbose-logging-on-any-agent

3. Other Issues and Resolutions:-

Issue1:- In the environment where we have many publications some are moving forward and some are not moving forward, some are moving slowly.

Observations1:-

From the distributor verbose log, we observe the following error message: “2025-03-05 12:54:20.538 The concurrent snapshot for publication '<PublicationName>' is not available because it has not been fully generated or the Log Reader Agent is not running to activate it. If generation of the concurrent snapshot was interrupted”
This is coming from the procedure “sys.sp_MSsubscription_status”. This happens because the status of the subscription 3 ("initiated") and the sync_type is automatic. The log reader must read when the snapshot agent started and ended from the log, after this, it will then update the MSsubscription table.

Resolution1:-The problem was there is a large volume of data for the log reader to process. It must be allowed to move forward and read through the log to completion after waiting some time.

Issue 2:- In transactional replication to replicate data to a client's Azure SQL VM in their Tenant/Subscription. The data is slow to complete it's push during peak times.

Observations 2:- We cannot find an issue with our Distributor VM (This VM) and their VM doesn't have an apparant issue.

Resolution 2:- Root Cause here is you have two publications that are pushing large amounts of data during the late night. One of these tables, on the Subscriber side, is very large. Most of the space is taken up by indexes. (More than 50%). It has roughly 13+ billion rows. When we push data to this table and other large tables it is causing excessive reads, causing multi-threading and is resulting in the latency which won't allow the Distributor to apply the transactions in a timely fashion.
-- Removing Duplicate or unused Indexes

-- Archiving Data

-- Index Rebuild

-- Creating Missing Indexes

-- Updating Statistics

-- Table Partition

Issue 3:- Lag from the Pub/Dist to the Subscribers.

Observations 3:-

--> In SQL Server error log, there is a SQL service reboot. Distribution database taking long time to do recovery, after that seems the jobs start to have query timeout

-->During the troubleshooting, we noticed blocking issues. And we confirmed the block header was the Log Reader agent session. The log reader agent session was not stuck or hung. It’s just because it’s handling a huge transaction and it was taking that long time. And caused the blocking.

Resolutions 3:- Below mentioned agent profile properties are modified and executed update statistics, which helped improve the Log Reader agent performance.

- MaxCmdsInTran 10000 ,

- ReadBatchSize 5000 ,

-LogScanTreshhold = 750000

Issue 4:- Merge job is continuously running, and we see blow message in replication monitor "Waiting 60 Second(s) Before Polling For Further Changes."

Resolutions 4:- We reproduced the same issue in the internal lab and confirmed that using the parameter – Continuous display this message. ( if this parameter is added agent never stops and continuously checks for changes)

-Continuous:-Specifies whether the agent attempts to poll replicated transactions continually. If specified, the agent polls replicated transactions from the source at polling intervals, even if there are no transactions pending.

Reference : https://learn.microsoft.com/en-us/sql/relational-databases/replication/agents/replication-merge-agent?view=sql-server-ver16#arguments

--> After removing this parameter, we did not receive the message

--> Additionally, we need to use – QueryTimeout parameter , so that we can keep a tab on the long running queries.

-QueryTimeOut query_time_out_seconds

Is the number of seconds before the query times out. The default is 300 seconds. The Merge Agent also uses the value of QueryTimeout to determine how long to wait for generation of a partitioned snapshot when this value is greater than 1800.

Issue 5:- Data Replication is taking huge time

Observations 5:- We observed below observations that

VM has been constantly at 100% of its overall disk bandwidth during the last 24 hours prior to data collection
Data Limit: This VM has a data limit of 256 MBps. The server has multiple P30s capable of reaching 200 MB of throughput. Regardless of whether stripes are used, the disks are limited to the VM's maximum capacity.
Lazy Writes Per Second: Ideally, the value for Lazy Writes Per Second should be close to zero.
Page Life Expectancy (PLE): A higher PLE indicates better performance, as it means pages stay longer in the buffer pool (memory cache). This reduces the need to read data from the hard drive. Currently, the PLE value is very low.
Head Blockers: There are a significant number of blockings, mostly involving the cleanup job. The log reader agent was the head blocker in two instances, with an average duration of under 20 seconds.
Log Reader Agent: The log reader agent appears to be blocking some index operation jobs. It has an inactive status with open transactions due to continuous mode.
Large Batches of Transactions: When the Log Reader Agent processes a large number of commands or transactions, it can lead to delays in the replication process, potentially causing blocking as other processes wait for the agent to complete its work. The Log Reader Agent can cause blocking issues in SQL Server transactional replication primarily due to large batches of replicated transactions or a high percentage of non-replicated transactions within the transaction log, leading to increased latency and resource contention.
High Percentage of Non-Replicated Transactions: If a significant portion of the transaction log contains transactions that are not marked for replication, the Log Reader Agent needs to scan over these transactions, resulting in increased latency and potentially causing blocking.
Resource Constraints: If the server has insufficient resources (CPU, memory, disk I/O), the Log Reader Agent might be delayed, leading to blocking issues.

as previously discussed, the last two options above are the likelihood for the blocking.

Resolution 5:-

ETL Operation/to or from non-replicated:- As previously discussed, my top recommendation is to reach out to the Dev team to see if they can move their ETL operation to another database without replication. If a significant portion of the transaction log contains transactions that are not marked for replication, the Log Reader Agent needs to scan over these transactions, resulting in increased latency and potentially causing blocking.
Adjust ReadBatchSize:- Another option to reduce blocking might be to adjust the ReadBatchSize parameter. Although we have done this in the past, it is worth experimenting with different values until we find the optimal setting. Please note that a higher value might not always improve performance, especially for workloads with large transactions.
Upgrade VM SKU:- This server is constantly reaching its capacity. I recommend upgrading the VM SKU to better meet your workload capacity.
Blocking Concerns:- If blocking is the main concern, another option is to create a separate job that checks for blocking. If the log reader agent is identified as the head blocker, the job can restart the log reader agent as shown below.

Issue 6:- Replication Performance Issues

Resolution 6:- We found that the SQL Server and disk configuration is not configured as per best practices. After following best practices from Disk and SQL Configuration perspective. Issue got resolved. Kindly refer the below link for your reference: Checklist: Best practices & guidelines - SQL Server on Azure VMs | Microsoft Learn

Issue 7:- Log reader agent delay

Observations 7:-

After restarting log reader agent, it starts working again.
We switched the -ReadBatchSize between 500 and 5000 and compare the performance, now keep using 500 for log processing.
From log reader agent history, the transaction log is processing but there is much pending log. The processing seems to be slow. From the log reader agent history, get below statistics information.
Basically, In the publisher server, the logread process has a reader thread connecting to the publisher database to run SP_REPLCMDS. In the distributor server, the logread process has a writer thread connecting to the distribution database to write commands into distribution distribution.dbo.sp_MSadd_replcmds. The data reading from the publisher database should write to distribution. From below, we can see the ‘reader fetch’ is 2940s with 1s wait, which means the reader thread fetched data for 2940s and wait 1s in last calculating period. And the writer thread writing 99s with 2439s waiting.

<stats state="1" work="2539" idle="0" ><reader fetch="2940" wait="1"/><writer write="99" wait="2439"/><sincelaststats elapsedtime="526" work="526" cmds="407220" cmdspersec="772.000000"><reader fetch="526" wait="0"/><writer write="13" wait="513"/></sincelaststats><message>Normal events that describe both the reader and writer thread performance.</message></stats>

Here the writer thread long wait is actually waiting for data buffer reading in, so the slowness is in reading data stage.
Based on VM disk performance checking, LUN's which belongs to log file do not have latency
In log reader agent history, we noticed many messages like ‘Approximately xxx log records have been scanned in pass xx, 0 of which were marked for replication.’ These messages mean the log records scanned are not for replication so they are skipped. If there are too many unrelated log records , log reader agent will cost much time on scanning unrelated logs which will slow down the replication transaction sync.
There was authentication issue on log reader agent yesterday which blocks the log syncing for long time, so today after resuming it accumulates large number of transactions. The large pending set also need time to consume.

Resolution 7:- There is no disk latency on log drive, the log reader agent slowness is mainly due to too many non-replication transactions and large number of accumulating batches. Best practices to follow in this scenario is

Shrink log file before configuring replication
Configure Replication in off-peak hours

4. Enhance Transaction Replication performance:-

1. Design Level:-
a. Database Design:-

Minimize transaction size in your application design.
By default, transactional replication propagates changes according to transaction boundaries. If transactions are smaller, the Distribution agent is less likely to resend a transaction due to network issues. If the agent is required to resend a transaction, the amount of data sent is smaller.

b. Distributor Configuration:-

Configure the Distributor on a dedicated server.
You can reduce processing overhead on the Publisher by configuring a remote Distributor. For more information, see Configure Distribution.
Size the distribution database appropriately.
Test replication with a typical load for your system to determine how much space is required to store commands. Ensure the database is large enough to store commands without having to auto-grow frequently. For more information about changing the size of a database, see ALTER DATABASE (Transact-SQL).

c. Publication Design:-

Replicate stored procedure execution when making batch updates to published tables.
If you have batch updates that occasionally affect a large number of rows at the Subscriber, you should consider updating the published table using a stored procedure and publish the execution of the stored procedure. Instead of sending an update or delete for every row affected, the Distribution Agent executes the same procedure at the Subscriber with the same parameter values. For more information, see Publishing Stored Procedure Execution in Transactional Replication.
Spread articles across multiple publications.
If you cannot use the -SubscriptionStreams parameter, consider creating multiple publications. Spreading articles across these publications allows replication to apply changes in parallel to Subscribers.

d. Subscription Considerations:-

Use independent agents rather than shared agents if you have multiple publications on the same Publisher (this is the default for the New Publication Wizard).
Run agents continuously instead of on frequent schedules.
- SQL Server Management Studio: Specify Synchronization Schedules
Setting the agents to run continuously rather than creating frequent schedules (such as every minute) improves replication performance, because the agent does not have to start and stop. When you set the Distribution Agent to run continuously, changes are propagated with low latency to the other servers that are connected in the topology. For more information, see:

2. Agent Properties Level

a. Log Reader Agent:-
ReadBatchSize:-

Increase the value of the -ReadBatchSize parameter for the Log Reader Agent.
Description:-
The Log Reader Agent and Distribution Agent support batch sizes for transaction read and commit operations. Batch sizes default to 500 transactions. The Log Reader Agent reads the specific number of transactions from the log, whether or not they are marked for replication. When a large number of transactions are written to a publication database, but only a small subset of those are marked for replication, you should use the -ReadBatchSize parameter to increase the read batch size of the Log Reader Agent. This parameter does not apply to Oracle Publishers.

Workloads of smaller transactions (fewer than 500 commands) see an increase in how many commands are processed per second when ReadBatchSize is increased up to 5000.
For larger workloads (transactions with 500 to 1000 commands), increasing ReadBatchSize has little performance improvement. Increasing ReadBatchSize results in a greater number of transactions written to the distribution database in one roundtrip. This increases the time transactions and commands are visible to the Distribution Agent and introduces latency to the replication process.

PollingInterval:-

Decrease the value of the -PollingInterval parameter for the Log Reader Agent.
Description:-
The -PollingInterval parameter specifies how often the transaction log of a published database is queried for transactions to replicate. The default is 5 seconds. If you decrease this value, the log is polled more frequently, which can result in lower latency for the delivery of transactions from the publication database to the distribution database. However, you should balance the need for lower latency against the increased load on the server from polling more frequently.

MaxCmdsInTran:-

To resolve accidental, one-time bottlenecks use the –MaxCmdsInTran parameter for the Log Reader Agent.
Description:-
The –MaxCmdsInTran parameter specifies the maximum number of statements grouped into a transaction as the Log Reader writes commands to the distribution database. Using this parameter allows the Log Reader Agent and Distribution Agent to divide large transactions (consisting of many commands) at the Publisher into several smaller transactions when applying commands at the Subscriber. Specifying this parameter can reduce contention at the Distributor and reduce latency between the Publisher and Subscriber. Because the original transaction is applied in smaller units, the Subscriber can access rows of a large logical Publisher transaction prior to the end of the original transaction, breaking strict transactional atomicity. The default is 0, which preserves the transaction boundaries of the Publisher. This parameter does not apply to Oracle Publishers.

Note:-
MaxCmdsInTran was not designed to be always turned on. It exists to work around cases where someone accidentally performed a large number of DML operations in a single transaction (causing a delay in the distribution of commands until the entire transaction is in the distribution database, locks being held, etc.). If you routinely fall into this situation,review your applications and find ways to reduce the transaction size.
MaxCmdsInTran is not supported if the given publication database is enabled for both Change Data Capture and replication. Using MaxCmdsInTran in this configuration may lead to data loss in CDC change tables. It may also cause PK errors if the MaxCmdsInTran parameter is added and removed while replicating a large Transaction.

b. Distributor Agent:-

SubscriptionStreams:-

Increase the –SubscriptionStreams parameter for the Distribution Agent.
Description:-The –SubscriptionStreams parameter can greatly improve aggregate replication throughput. It allows multiple connections to a Subscriber to apply batches of changes in parallel, while maintaining many of the transactional characteristics present when using a single thread. If one of the connections fails to execute or commit, all connections will abort the current batch, and the agent will use a single stream to retry the failed batches. Before this retry phase completes, there can be temporary transactional inconsistencies at the Subscriber. After the failed batches are successfully committed, the Subscriber is brought back to a state of transactional consistency.
A value for this agent parameter can be specified using the @subscriptionstreams of sp_addsubscription (Transact-SQL).

Blocking Monitor Thread:-

Distribution Agent maintains a blocking monitor thread that detects blocking between sessions. If the blocking monitor thread detects blocking between the sessions, Distribution Agent switches to use one session to reapply the current batch of commands that could not be applied previously.
The blocking monitor thread can detect blocking between Distribution Agent sessions. However, the blocking monitor thread cannot detect blocking in the following situations:

One of the sessions where blocking occurs is not a Distribution Agent session.
A session deadlock freezes the activities of Distribution Agent.

In this situation, Distribution Agent coordinates all the sessions to commit together as soon as their commands are executed. A deadlock among the sessions occurs if the following conditions are true:

Blocking occurs between the Distribution Agent sessions and a session that is not a Distribution Agent session.
Distribution Agent is waiting for all the sessions to complete executing their commands before Distribution Agent coordinates all the sessions to commit together.

CommitBatchSize:-

Increase the value of the -CommitBatchSize parameter for the Distribution Agent.
Description:-
Committing a set of transactions has a fixed overhead; by committing a larger number of transactions less frequently, the overhead is spread across a larger volume of data. Increasing CommitBatchSize (up to 200) can improve performance as more transactions are committed to the subscriber. However, the benefit of increasing this parameter drops off as the cost of applying changes is gated by other factors, such as the maximum I/O of the disk that contains the log. Additionally, there is a trade-off to be considered: any failure that causes the Distribution Agent to start over must roll back and reapply a larger number of transactions. For unreliable networks, a lower value can result in fewer failures and a smaller number of transactions to roll back and reapply if a failure occurs.

Refer the below link for details:
https://learn.microsoft.com/en-us/sql/relational-databases/replication/administration/enhance-transactional-replication-performance?view=sql-server-ver15

SQL Server Database Corruption: Causes, Detection, and some details behind DBCC CHECKDB

MSFTPawelM — Fri, 10 Oct 2025 10:55:57 GMT

SQL Server Database Corruption: Causes, Detection, and some details behind DBCC CHECKDB

Database corruption in SQL Server is rare but usually high-impact. When it occurs, it threatens ACID compliance - the foundation of transactional integrity - and can lead to downtime, data loss, and operational risk. This article explores:

Common causes of corruption
How DBCC CHECKDB works under the hood
Performance tuning tips for running CHECKDB
Sample error messages and what they mean
Best practices for prevention and recovery

Why ACID Matters in Corruption Scenarios

Before diving into causes and detection, remember that SQL Server guarantees Atomicity, Consistency, Isolation, and Durability:

Atomicity: Transactions are all-or-nothing. If any part fails, the whole transaction has to fail. Corruption can break this, leaving partial writes.
Consistency: Every transaction moves the database from one valid state to another. Corruption violates this by possibly introducing invalid states.
Isolation: Concurrent transactions shouldn’t interfere with each other. Corruption in shared pages can cause phantom reads or deadlocks.
Durability: Once committed, data must persist, even in the event of system failure or crash. Disk-level corruption undermines durability guarantees.

DBCC CHECKDB exists primarily to validate consistency and durability, ensuring that the logical and physical structures adhere to ACID principles.

In any internal decision-making that a database engine needs to do – ACID will be the main motivating factor, overriding concerns about performance, high availability or any other considerations. This is by design – ACID is the single most important principle that the engine cares about.

Common Causes of SQL Server Database Corruption

Corruption usually originates outside of SQL Server, often in the I/O path. Below I list the main causes, ordered in terms of probability - the higher on the list, the more probable it is:

Hardware Failures

Disk errors, RAID controller cache issues, or faulty RAM can corrupt pages during writes.
Even with write-ahead logging, if the physical medium fails, durability is compromised.

I/O Subsystem Issues

SAN/NAS instability, outdated drivers, or virtualization misconfigurations can cause torn writes.
SQL Server relies on the OS and storage stack for atomic page writes; instability breaks this assumption.

Improper Shutdowns

Power loss during write operations can leave pages partially written, violating atomicity.
Torn-page detection mitigates this, but only if checksums are enabled.

OS or SQL Server Bugs

Rare, but missing cumulative updates can expose edge cases in buffer pool or checkpoint logic.

File System Misconfiguration

Compressed/encrypted volumes or sector size mismatches can corrupt allocation maps in edge cases.

Human Error

Manual deletion of MDF/LDF files or incorrect restore sequences can orphan pages in some unsupported scenarios.

Malware

Ransomware or malicious scripts altering system tables can break referential integrity.

How DBCC CHECKDB Works Under the Hood

DBCC CHECKDB is SQL Server’s integrity verification tool, validating both physical and logical consistency:

1. Snapshot Creation

Creates a transactionally consistent snapshot using sparse files (page changes that happen at runtime are tracked in the files to ensure consistency).
Ensures checks run without blocking user activity, preserving isolation, unless WITH TABLOCK option is used to run on live database taking locks during execution.

2. Phases of Execution

Allocation Checks

Validates GAM, SGAM, and IAM pages for correct page allocation.
Detects orphaned extents or double allocations.

System Table Checks

Verifies metadata in system catalogs like sysobjects and sysindexes.
Ensures schema-level consistency.

Table and Index Structure Checks

Traverses B-trees, validating key order and linkage.
Detects broken pointers or incorrect page splits.

Page-Level Validation

Reads every page, checks checksums or torn-page bits.
Critical for durability verification.

LOB Checks

Ensures integrity of large object chains in IAM and target pages (text, image, XML).

Cross-Object Consistency

Confirms referential integrity across tables and indexes.

3. Error Reporting

Errors include severity and repair recommendation:
REPAIR_REBUILD: Non-destructive, fixes structural issues.
REPAIR_ALLOW_DATA_LOSS: Last resort, may delete corrupt pages – the integrity of the “repaired” database is not guaranteed when used.

Performance Tuning Tips for DBCC CHECKDB

CHECKDB is I/O and CPU intensive. Here are some options you can use to optimize its performance while running:

Use PHYSICAL_ONLY for Faster Checks

DBCC CHECKDB ('YourDatabase') WITH PHYSICAL_ONLY;

Skips logical checks, reducing load. This could be used for daily scans, assuming a full scan still happens, just more rarely.

Run on a Restored Copy

Offload workload to a non-production server using recent backups.

Leverage Availability Groups

Execute on a readable secondary replica to protect the primary node.

Control Parallelism

DBCC CHECKDB ('YourDatabase') WITH MAXDOP = 4;

Explicit MAXDOP ensures predictable performance. You can adjust the parallelism in accordance with available CPUs.

Schedule During Low Activity

Avoid peak hours; combine with Resource Governor for throttling.

Break Down Large Databases

Use DBCC CHECKTABLE for individual large tables if full CHECKDB is too costly.

Sample Error Messages and Interpretation

Msg 824
“SQL Server detected a logical consistency-based I/O error: incorrect checksum.”
→ Page-level corruption, often disk-related.
Msg 8905
“Extent (1:12345) in database ID 5 is marked allocated in GAM, but not in SGAM.”
→ Allocation map inconsistency.
Msg 2533
“Table error: Object ID 123456789, index ID 1. Page (1:98765) failed checksum.”
→ Corruption in index or data page.
Msg 8928
“Object ID 123456789, index ID 2: Page (1:54321) could not be processed.”
→ Structural issues in index B-tree.

Apart from the errors above, we also sometimes see issues directly related to our use of sparse files when creating the snapshot:

The operating system returned error 665(The requested operation could not be completed due to a file system limitation) to SQL Server during a write at offset 0x00002a3ef96000 in file 'DBFile.mdf:MSSQL_DBCC18'
The operating system returned error 1450 (Insufficient system resources exist to complete the requested service.) to SQL Server during a write at offset 0x00002a3ef96000 in file with handle 0x0000000000000D5C. This is usually a temporary condition and the SQL Server will keep retrying the operation. If the condition persists, then immediate action must be taken to correct it.

Those are directly caused by NTFS File Record Segment objects running out of space due to ATTRIBUTE_LIST_ENTRY list growing out of bounds, and not by CHECKDB command itself - for more details check the article https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/database-file-operations/1450-and-665-errors-running-dbcc-checkdb.

Prevention and Recovery Best Practices

Run DBCC CHECKDB weekly either directly on the database or on restored backups. This makes sure that if corruption happens you have a recent last known good backup to restore from.
Maintain verified backups with WITH CHECKSUM and regular restore tests. It’s very important to make sure a created backup can be fully restored to make sure there’s no unpleasant surprises if an issue happens.
Use enterprise-grade hardware (RAID 10, ECC memory, UPS).
Apply cumulative updates for SQL Server and Windows.
Avoid unsupported storage configurations (compressed/deduplicated volumes).

Key Takeaways

Corruption is usually hardware or I/O related, not SQL Server bugs.
DBCC CHECKDB is your first line of defence - schedule it regularly.
Always restore from a clean backup if in any way possible; use repair options only as a last resort.

Critical Note on Repair Options

What triggered this blog was a number of issues we saw where the plan in case a database goes suspect was essentially to:

Switch database to emergency mode
Switch to single user mode
Run DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS options without any previous tests to check if a corruption is present
Switching the database back to multi user and using it normally

Note that a database can go suspect for multiple reasons, not all related to corruption – basically if we hit something that stops the recovery process and doesn’t allow us to finish. For example – a deadlock with the recovery thread causes the thread to be killed? Database goes to suspect mode. This plan is not the recommended approach - neither in corruption cases nor other reasons for suspect database.

Using REPAIR_ALLOW_DATA_LOSS can leave the database logically inconsistent. You always need to validate data post-repair and address root causes (hardware, OS issues) before attempting recovery. For detailed guidance, see https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/database-file-operations/troubleshoot-dbcc-checkdb-errors.

Note that the repair recommendation provided after running is the lowest level of repair that can address all errors reported by CHECKDB. However, “minimum” doesn’t mean it will fix everything it finds - some errors simply can’t be repaired. You might also need to run the repair process more than once, since removing some data might expose additional non-linked pages that themselves needs to be dropped as part of repair. Keep in mind that not every error requires this level of repair and using REPAIR_ALLOW_DATA_LOSS doesn’t always lead to data loss. The only way to know if fixing an error will cause data loss is to actually run the repair and verify the data afterwards.

A helpful tip: You can use DBCC CHECKTABLE on any table that shows errors. This will tell you the minimum repair level needed for that specific table.

It’s extremely important to remember that after running CHECKDB repair with data loss, you must manually validate your data. The repair process doesn’t guarantee logical consistency. For example, REPAIR_ALLOW_DATA_LOSS might remove entire data pages with inconsistent data. If that happens, tables with foreign key relationships could end up with rows that no longer have matching parent keys.

I hope this post was able to bring a bit of clarity to the complicated topic of corruption causes and recovery. It’s important to remember that once corruption happens your options are limited – you have to be prepared before the issue occurs.

Further Reading

How to Capture Running Queries in SQL Server

prakashkc — Wed, 15 Oct 2025 04:39:15 GMT

(本記事の最後に日本語でもご案内しております)

Sometimes, you need to check which queries are running in your SQL Server. If you want to understand what’s happening inside your SQL Server—such as diagnosing errors or slow performance—xEvents can be very helpful.

🔍 What is Extended Events (xEvents)?

Extended Events is a lightweight performance monitoring system built into SQL Server.

It helps you:
✅ Capture detailed information about queries, errors, and performance.
✅ Diagnose issues like slow queries or unexpected errors.
✅ Monitor safely with minimal overhead, compared to older tools like Profiler.

Think of xEvents as a flight recorder for SQL Server: you start it, reproduce the issue, stop it, and then review what happened.

You can find a step-by-step guide in the reference below.

📖 Quickstart: Extended Events - SQL Server | Microsoft Learn
https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/quick-start-extended-events-in-sql-server?view=sql-server-ver17

In this blog, I am sharing a T-SQL script that I usually provide to customers for troubleshooting support cases.

🛠 Step-by-Step Instructions

The captured data will be saved as .xel files in the default SQL Server log folder (you can change this path if needed).

Step 1: Create the xEvent Session
Run the T-SQL script below on the target instance using an account with sysadmin rights or sufficient privileges to create an xEvent session.

CREATE EVENT SESSION [XEventTmpl] ON SERVER ADD EVENT sqlserver.attention ( ACTION(package0.event_sequence,sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.client_pid, sqlserver.database_id, sqlserver.database_name, sqlserver.query_hash, sqlserver.session_id, sqlserver.sql_text, sqlserver.transaction_id, sqlserver.transaction_sequence, sqlserver.username) ), ADD EVENT sqlserver.error_reported ( ACTION(package0.event_sequence,sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.client_pid, sqlserver.database_id, sqlserver.database_name, sqlserver.query_hash, sqlserver.session_id, sqlserver.sql_text, sqlserver.transaction_id, sqlserver.transaction_sequence, sqlserver.username) ), ADD EVENT sqlserver.rpc_completed ( ACTION(package0.event_sequence,sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.client_pid, sqlserver.database_id, sqlserver.database_name, sqlserver.query_hash, sqlserver.session_id, sqlserver.sql_text, sqlserver.transaction_id, sqlserver.transaction_sequence, sqlserver.username) ), ADD EVENT sqlserver.rpc_starting ( ACTION(package0.event_sequence,sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.client_pid, sqlserver.database_id, sqlserver.database_name, sqlserver.query_hash, sqlserver.session_id, sqlserver.sql_text, sqlserver.transaction_id, sqlserver.transaction_sequence, sqlserver.username) ), ADD EVENT sqlserver.sp_statement_completed ( ACTION(package0.event_sequence,sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.client_pid, sqlserver.database_id, sqlserver.database_name, sqlserver.query_hash, sqlserver.session_id, sqlserver.sql_text, sqlserver.transaction_id, sqlserver.transaction_sequence, sqlserver.username) ), ADD EVENT sqlserver.sp_statement_starting ( ACTION(package0.event_sequence,sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.client_pid, sqlserver.database_id, sqlserver.database_name, sqlserver.query_hash, sqlserver.session_id, sqlserver.sql_text, sqlserver.transaction_id, sqlserver.transaction_sequence, sqlserver.username) ), ADD EVENT sqlserver.sql_batch_completed ( ACTION(package0.event_sequence,sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.client_pid, sqlserver.database_id, sqlserver.database_name, sqlserver.query_hash, sqlserver.session_id, sqlserver.sql_text, sqlserver.transaction_id, sqlserver.transaction_sequence, sqlserver.username) ), ADD EVENT sqlserver.sql_batch_starting ( ACTION(package0.event_sequence,sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.client_pid, sqlserver.database_id, sqlserver.database_name, sqlserver.query_hash, sqlserver.session_id, sqlserver.sql_text, sqlserver.transaction_id, sqlserver.transaction_sequence, sqlserver.username) ), ADD EVENT sqlserver.sql_statement_completed ( ACTION(package0.event_sequence,sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.client_pid, sqlserver.database_id, sqlserver.database_name, sqlserver.query_hash, sqlserver.session_id, sqlserver.sql_text, sqlserver.transaction_id, sqlserver.transaction_sequence, sqlserver.username) ), ADD EVENT sqlserver.sql_statement_starting ( ACTION(package0.event_sequence,sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.client_pid, sqlserver.database_id, sqlserver.database_name, sqlserver.query_hash, sqlserver.session_id, sqlserver.sql_text, sqlserver.transaction_id, sqlserver.transaction_sequence, sqlserver.username) ) ADD TARGET package0.event_file ( SET filename = N'xeventtmpl.xel' ) WITH (STARTUP_STATE = OFF);

✅ Step 2: Start Collecting Events

Run this command to start the xEvent session so it begins capturing data:

ALTER EVENT SESSION [XEventTmpl] ON SERVER STATE = START

What this does: Turns on the session you created earlier.

💡Tip: To confirm it’s running status in SQL Server Management Studio (SSMS):

Go to Management → Extended Events → Sessions
Look for XEventTmpl in the list
If it’s running, the icon will show a green arrow. If it’s stopped, it will show a red square.

🔍 View the Results

To check the data you have collected with Extended Events in SQL Server Management Studio (SSMS):

Go to Management → Extended Events → Sessions.
Find and right-click on XEventTmpl.
You can choose Watch Live Data to See the events as they happen, while the session is running.

Alternatively, you can use the following query to read the .xel files directly:

SELECT * FROM sys.fn_xe_file_target_read_file('xeventtmpl*.xel', NULL, NULL, NULL)

👁️ What This Captures

The xEvents session will collect the following types of activity:

sql_batch_starting / sql_batch_completed:
Records when an ad-hoc T-SQL batch starts and ends.

rpc_starting / rpc_completed:
Records when a remote procedure call (RPC), such as one from an application using sp_executesql, begins and finishes.

sp_statement_starting / sp_statement_completed:
Records when each statement inside a stored procedure starts and completes.

sql_statement_starting / sql_statement_completed:
Records when a standalone T-SQL statement starts and finishes.

error_reported:
Captures whenever SQL Server reports an error, including the severity, error number, and message.

attention:
Indicates when a query is canceled or times out (for example, if a user presses cancel or there is a client timeout).

For each event, the session also collects helpful details, such as:

Action

Description

sql_text

The exact T-SQL command that was run.

client_app_name

The name of the application (like SSMS or a .NET app) that sent the request.

client_hostname

The computer name where the request came from.

client_pid

The process ID of the client application.

username

The SQL Server login or user who ran the query.

session_id

The session ID (SPID) on SQL Server.

✅ Step 3: Stop the Session

When you’re done collecting data, stop the session:

ALTER EVENT SESSION [XEventTmpl] ON SERVER STATE = STOP

If you no longer need the session, remove it completely:

DROP EVENT SESSION [XEventTmpl] ON SERVER

❓ FAQ

Q. Will this slow my server?
A. No, Extended Events is lightweight and safe for short captures.
https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/extended-events

Q. What permission do i need to run this query?
A. You need either the ALTER ANY EVENT SESSION permission or, typically, sysadmin rights.

Q. How do I share with Support?
A. Please compress (zip) the .xel files from your target folder and attach them to your support case.

Also, include the time when you reproduced the issue.
Make sure you have reproduced the issue after starting the session in step 2, not just after creating the xEvent in step 1.

If you have not changed the default output location, you can use the "Basic Log" scenario with the SQL_LogScout tool. This tool will also collect other important logs that are helpful for troubleshooting.
For more details about SQL_LogScout, please visit:
https://github.com/microsoft/SQL_LogScout

Q. Can I use the same query to collect xEvents logs on Azure SQL Managed Instance (SQL MI)?
A. On Azure SQL Managed Instance, xEvents cannot save files to a local server folder. Instead, you need to save the event file to Azure Blob Storage. To do this, please create a credential that matches your storage container’s URL, as shown below:

CREATE CREDENTIAL [https://<storage-account>.blob.core.windows.net/<container-name>] -- change here WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = '<your-SAS-token>'; -- change here

Next, update the filename in your event session to use the blob storage path instead of a local file path. For example:

CREATE EVENT SESSION [XEventTmpl] ON SERVER …….. ADD TARGET package0.event_file( SET filename=N'https://<storage-account>.blob.core.windows.net/<container-name>/'xeventtmpl.xel' -- it should be blob url not local filepath )

📘 SQL Server で実行中のクエリをキャプチャする方法

SQL Server の内部で「どんなクエリが流れているか」「どの操作でエラーが出ているか」を確認したいときは、拡張イベント（xEvents）が便利です。拡張イベントは SQL Server に標準搭載されている機能であり、パフォーマンスへの影響も軽微です。

拡張イベントの設定手順及び詳細につきましては、下記の公開情報に掲載されています。

📖 クイックスタートの拡張イベント - SQL Server | Microsoft Learn
https://learn.microsoft.com/ja-jp/sql/relational-databases/extended-events/quick-start-extended-events-in-sql-server?view=sql-server-ver17

本ブログでは、サポート対応時にお客様へご案内することが多い T-SQL スクリプトを使って、最小ステップでキャプチャを始める方法をご紹介します。

🛠 拡張イベントログの収集方法

以下の手順では、SQL Server の既定ログフォルダーにログを保存する設定となっておりますが、保存先は変更可能です。

✅ ステップ1：拡張イベントセッションを作成する
対象のインスタンス上で、下記クエリを sysadmin のような拡張イベントセッションの作成に必要な権限を有するアカウントにて実行します。

✅ ステップ2：拡張イベントの開始
以下のクエリを実行することで、拡張イベントの収集を開始できます。

ALTER EVENT SESSION [XEventTmpl] ON SERVER STATE = START

💡Tip: SQL Server Management Studio（SSMS）上で拡張イベントが有効になっているかをご確認いただく方法：

➤ SQL Server Management Studio（SSMS）を開き、[管理] → [拡張イベント] → [セッション] の順に展開します。
➤ 一覧の中から「XEventTmpl」を見つけます。
➤ セッションが実行中の場合は、アイコンに緑色の矢印が表示されます。停止中の場合は、赤色の四角形が表示されます。

🔍 結果（収集データ）の確認方法

SQL Server Management Studio（SSMS）で拡張イベントの収集データを確認するには、以下の手順を実施してください。

SQL Server Management Studio（SSMS）を開き、[管理] → [拡張イベント] → [セッション] の順に展開します。
一覧の中から「XEventTmpl」を探して右クリックします。セッションが実行中の場合は「Watch Live Data」を選択し、リアルタイムでイベントを確認できます。

また、以下のクエリでも確認可能です。

SELECT * FROM sys.fn_xe_file_target_read_file('xeventtmpl*.xel', NULL, NULL, NULL)

👁️ 取得できる情報
このセッションでは、以下の種類のイベントを収集します。

sql_batch_starting / sql_batch_completed:
アドホックな T-SQL バッチの開始と終了

rpc_starting / rpc_completed:
sp_executesql などの RPC の開始と終了

sp_statement_starting / sp_statement_completed:
ストアドプロシージャ内の各ステートメントの開始と完了

sql_statement_starting / sql_statement_completed:
単独の T-SQL ステートメントの開始と終了

error_reported:
SQL Server が報告したエラー情報（重大度、エラー番号、メッセージなど）

attention:
クエリのキャンセルやタイムアウト時の記録

また、各イベントごとに以下のような詳細情報も収集されます。

アクション

説明

sql_text

実行された T-SQL コマンドの内容

client_app_name

リクエスト元のアプリケーション名

client_hostname

リクエスト元のコンピューター名

client_pid

クライアントアプリケーションのプロセス ID

username

実行ユーザーの SQL Server ログイン名

session_id

SQL Server 上のセッション ID（SPID）

✅ ステップ3：拡張イベントの停止
以下のクエリを実行することで、拡張イベントの収集を停止できます。

ALTER EVENT SESSION [XEventTmpl] ON SERVER STATE = STOP

拡張イベントが不要な場合は、下記クエリを実行して削除します。

DROP EVENT SESSION [XEventTmpl] ON SERVER

🗂 保存先について：

収集されたログは、xeventtmpl_*.xel という名前で、SQL Server の既定ログフォルダーに保存されます。お問い合わせの際には、該当フォルダー全体を ZIP ファイルにまとめてご提供いただけますと、よりスムーズに調査を進めることができます。

SQL Server IaaS Agent 拡張機能の概要 (What is the Windows SQL Server IaaS Agent extension?)

Eijin_Ota — Wed, 17 Sep 2025 08:12:28 GMT

English follows Japanese.

こんにちは、 SQL Server サポートチームです。

今回は、SQL IaaS Agent 拡張機能と Azure 上の SQL 仮想マシンリソースについて解説します。

SQL Server IaaS Agent 拡張機能とは

SQL Server IaaS Agent 拡張機能は、Azure Portal 上から Azure VM 上の SQL Server を管理・運用できるようにする拡張機能です。
SQL Server がインストール済みの Azure VM が Azure 上の「SQL Server IaaS Agent 拡張機能」に登録されると、その Azure VMに紐づいた「SQL 仮想マシン」リソースが Azure Portal 上に作成され、SQL Server のライセンス等を管理するための画面をご利用いただけるようになります。

//SQL Server IaaS Agent 拡張機能とは

SQL Server IaaS Agent 拡張機能とは (Windows) - SQL Server on Azure VMs | Microsoft Learn

拡張機能に登録する利点

SQL Server IaaS Agent 拡張機能を使用することで、様々な管理機能を有効化することが可能です。代表的な機能を後述いたします。

前提として、 SQL Server IaaS Agent 拡張機能に登録すると、既定では基本的な機能（SQL 仮想マシン管理画面の [ライセンスの種類] ）のみが有効化された状態となります。

下記の弊社公開情報に「SQL IaaS Agent 拡張機能が必要です。」と記載されたいずれかの機能を有効化すると、SQL IaaS Agent 拡張機能用のサービスが Azure VM 上にインストールされます。

// Windows SQL Server IaaS Agent 拡張機能を使用して管理を自動化する - 機能面の利点

https://learn.microsoft.com/ja-jp/azure/azure-sql/virtual-machines/windows/sql-server-iaas-agent-extension-automate-management?view=azuresql&tabs=azure-portal#feature-benefits

代表的な機能

SQL IaaS Agent 拡張機能が持つ機能は、前述の弊社公開情報にまとめられております。

そのうち、代表的な機能をご紹介します。詳細は、前述の公開情報や、それぞれの説明に添えた公開情報を必要に応じてご参照ください。

自動バックアップ

Azure VM での SQL Server 上のすべての既存および新規データベースのための Microsoft Azure へのマネージドバックアップが自動的に構成されます。

// Azure 仮想マシンでの SQL Server 2014 の自動バックアップ - SQL Server on Azure VMs | Microsoft Learn

https://learn.microsoft.com/ja-jp/azure/azure-sql/virtual-machines/windows/automated-backup-sql-2014?view=azuresql

//Azure VM 上の SQL Server の自動バックアップ

SQL Server 2016 以降の自動バックアップ - SQL Server on Azure VMs | Microsoft Learn

tempdb の構成

Azure Portal 上から tempdb のストレージ構成を行うことが可能になります。

//Azure portal を使用して SQL Server VM を管理する - ストレージ

https://learn.microsoft.com/ja-jp/azure/azure-sql/virtual-machines/windows/manage-sql-vm-portal?view=azuresql#storage

Microsoft Entra 認証

SQL Server への認証に Microsoft Entra ID を使用することが可能になります。

//Azure VM 上の SQL Server に対する Microsoft Entra 認証を有効にする

Microsoft Entra 認証を有効にする - SQL Server on Azure VMs | Microsoft Learn

FAQ

SQL Server IaaS Agent 拡張機能は有料ですか？
SQL Server IaaS Agent 拡張機能を使用する際、追加の費用等は発生いたしません。
SQL Server IaaS Agent 拡張機能に登録する方法を教えてください。
Azure PowerShell や Azure CLI での手動登録、 Azure Portal でサブスクリプション単位でのIaaS Agent 拡張機能の自動登録機能を有効化する方法がございます。また、弊社の定期メンテナンスにより登録が行われる場合もあります。

//SQL IaaS Agent 拡張機能への自動登録
SQL IaaS Agent 拡張機能への自動登録 - SQL Server on Azure VMs | Microsoft Learn

//Windows SQL Server VM を SQL IaaS Agent 拡張機能に登録する（手動登録）
SQL IaaS Agent 拡張機能に登録する (Windows) - SQL Server on Azure VMs | Microsoft Learn
複数の SQL Server インスタンスがインストールされている環境で SQL Server IaaS Agent 拡張機能をインストールすることは可能ですか？
複数の SQL Server インスタンスがある場合は既定のインスタンス (MSSQLSERVER) のみが拡張機能にサポートされます。具体的にサポートされる環境は以下となります。
- 1 つの既定のインスタンスのみがある環境。
- 複数のインスタンスがある場合は、Azure portal の拡張機能によって既定のインスタンスのみがサポートおよび管理されます。既定のインスタンスがなく名前付きインスタンスが複数ある環境はサポートされていません。
- インストールされているインスタンスが 1 つのみの場合は、1 つの名前付きインスタンスがサポートされます。
VM を削除すると SQL 仮想マシンリソースも削除されますか？
対象の SQL Server がインストールされた VM が削除されると、SQL 仮想マシンリソースも削除されます。

// ご参考) Azure VM 上の SQL Server についてよく寄せられる質問 (FAQ) - Azure SQL | Microsoft Learn
https://learn.microsoft.com/ja-jp/azure/azure-sql/virtual-machines/windows/frequently-asked-questions-faq?view=azuresql

※本情報の内容（添付文書、リンク先などを含む）は、作成日時点でのものであり、予告なく変更される場合があります。

------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Hello, this is the SQL Server Support Team.

In this article, we will explain the SQL IaaS Agent Extension and the SQL Virtual Machines resource on Azure.

What is the SQL Server IaaS Agent extension?

The SQL Server IaaS Agent Extension is a feature that enables you to manage and operate SQL Server installed on Azure VMs directly from the Azure Portal.
When an Azure VM with SQL Server installed is registered with the "SQL Server IaaS Agent Extension" on Azure, a corresponding "SQL Virtual Machine" resource is created in the Azure Portal. This allows you to access a management interface for SQL Server licensing and other settings.

//What is the SQL Server IaaS Agent extension?
What is the SQL Server IaaS Agent extension? (Windows) - SQL Server on Azure VMs | Microsoft Learn

Benefits of Registering the extension

By using the SQL Server IaaS Agent Extension, you can enable various management features. Some of the key features are introduced below.
By default, when a VM is registered with the SQL Server IaaS Agent Extension, only the basic functionality (such as the "SQL Server License" setting in the SQL Virtual Machine management) is enabled.

When you enable any of the features listed in the Microsoft documentation that states "Requires SQL IaaS Agent extension.", the extension service is installed on the Azure VM.

//Automate management with the Windows SQL Server IaaS Agent extension - Feature benefits
https://learn.microsoft.com/ja-jp/azure/azure-sql/virtual-machines/windows/sql-server-iaas-agent-extension-automate-management?view=azuresql&tabs=azure-portal#feature-benefits

Key Features

The features provided by the SQL IaaS Agent Extension are summarized in the documentation above. Here are some of the most notable ones.
For more details, please refer to the linked documentation.

1. Automated backup

Automated Backup automatically configures Managed Backup to Microsoft Azure for all existing and new databases.

//Automated Backup for SQL Server 2014 virtual machines (Resource Manager)
Automated Backup for SQL Server 2014 Azure virtual machines - SQL Server on Azure VMs | Microsoft Learn

//Automated Backup for SQL Server on Azure VMs
Automated Backup for SQL Server 2016 and later - SQL Server on Azure VMs | Microsoft Learn

2. Configure tempdb

You can configure tempdb storage directly from the Azure Portal.

//Manage SQL Server VM using Azure Portal - Storage | Microsoft Learn
https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/manage-sql-vm-portal?view=azuresql#storage

3. Microsoft Entra authentication

You can use Microsoft Entra ID for authentication to SQL Server.

//Enable Microsoft Entra authentication for SQL Server on Azure VMs
Enable Microsoft Entra authentication - SQL Server on Azure VMs | Microsoft Learn

FAQ

Q: Is the SQL Server IaaS Agent Extension a paid feature?
A: No, there are no additional costs for using the SQL Server IaaS Agent Extension.

Q: How can I register with the SQL Server IaaS Agent Extension?
A: You can register manually using Azure PowerShell or Azure CLI or enable automatic registration at the subscription level via the Azure Portal. Registration may also occur during maintenance by Microsoft.

//Automatic registration with SQL IaaS Agent extension
Automatic registration with SQL IaaS Agent extension - SQL Server on Azure VMs | Microsoft Learn

//Register Windows SQL Server VM with SQL IaaS Agent extension
Register with SQL IaaS Agent Extension (Windows) - SQL Server on Azure VMs | Microsoft Learn

Q: Can I install the SQL Server IaaS Agent Extension in an environment with multiple SQL Server instances?
A: The SQL IaaS Agent extension supports the following environments:

One default instance.
If there are multiple instances, only the default instance is supported and managed by the extension in the Azure portal. Environments with multiple named instances without a default instance aren't supported.
One named instance, if it's the only installed instance.

Q: If I delete the VM, will the SQL Virtual Machine resource also be deleted?
A: Yes, if the VM with SQL Server installed is deleted, the corresponding SQL Virtual Machine resource will also be removed.

//Frequently asked questions for SQL Server on Azure VMs
Frequently asked questions (FAQ) for SQL Server on Azure VMs - Azure SQL | Microsoft Learn

Note: The content of this article (including attachments and linked pages) is current as of the time of writing and may be subject to change without notice.

更新プログラム適用後、bcp.exe の出力メッセージが英語になる

KeiichiroTani — Mon, 18 Aug 2025 14:05:53 GMT

English follows Japanese

こんにちは。SQL Server サポートチームです。

事象

日本語版の SQL Server をインストールし、利用している環境で、SQL Server へ更新プログラムを適用後、bcp.exe の出力メッセージが英語になる場合があります。

接続先インスタンスを誤った場合の例:

本事象は、ODBCドライバーの修正が含まれた更新プログラムを適用する場合、更新プログラムに含まれるODBCドライバーのインストーラーは各言語版ではなく英語版での提供となり、更新プログラムを適用時にODBCドライバーの日本語リソースファイルが削除されるために発生します。

対処策

SQL Server の更新プログラムを適用後、日本語リソースファイルを再配置します。再配置手順は以下の通りです。

ODBC ドライバー（17.10.6.1）を例としてご説明します。

日本語リソースファイルを再配置方法：

1) 以下のサイトにアクセスし、リリース番号 17.10.6.1 ODBC ドライバー(日本語) をダウンロードします。
ODBC Driver for SQL Server のダウンロード - ODBC Driver for SQL Server | Microsoft Learn

2) ダウンロードしたファイル(msodbcsql.msi) を任意のフォルダーに配置します。

なお、後述の手順はC:\temp フォルダーにインストールしたインストーラーを配置した場合の例です。

3) 管理者権限でコマンドプロンプトを起動し、下記のコマンドを実行し、ダウンロードしたインストーラーを展開します。

　　msiexec /a "C:\temp\msodbcsql.msi" /qb targetdir="C:\temp\msodbcsql_msi"

4) 展開したフォルダーにある msodbcsqlr17.rll を次の配置先のフォルダーにコピーします。

※ 配置先のフォルダー C:\Windows\System32\1041 が存在しない場合は、フォルダーを作成したうえで msodbcsqlr17.rll をコピーします。

　　コピー元: C:\temp\msodbcsql_msi\Windows\System32\1041

　　配置先: C:\Windows\System32\1041

5) bcp.exe の出力メッセージが日本語となるか確認します。

ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー

Hello, this is the SQL Server Support Team.

Issue

In environments where the Japanese version of SQL Server is installed and used, after applying an update to SQL Server, the output messages from bcp.exe may appear in English.

Example when connecting to the wrong instance:

This issue occurs when the update includes a fix for the ODBC driver. The installer for the ODBC driver included in the update is provided in English rather than in each localized language version. As a result, the Japanese resource file for the ODBC driver is deleted during the update process.

Resolution

After applying the SQL Server update, relocate the Japanese resource file.

The steps below explain how to do this using ODBC Driver version 17.10.6.1 as an example.

Steps to relocate the Japanese resource file:

1) Access the following site and download the ODBC Driver version 17.10.6.1 (Japanese version):
Download ODBC Driver for SQL Server - ODBC Driver for SQL Server | Microsoft Learn

2) Place the downloaded file (msodbcsql.msi) in any folder.

The following steps assume the installer is placed in the C:\temp folder.

3) Open Command Prompt with administrator privileges and run the following command to extract the installer:

msiexec /a "C:\temp\msodbcsql.msi" /qb targetdir="C:\temp\msodbcsql_msi"

4) Copy the msodbcsqlr17.rll file from the extracted folder to the following destination folder:

If the destination folder C:\Windows\System32\1041 does not exist, create it before copying the file.

Source : C:\temp\msodbcsql_msi\Windows\System32\1041

Destination : C:\Windows\System32\1041

5) Confirm that the output messages from bcp.exe are displayed in Japanese.

How to artificially create a non-yielding scheduler scenario using WinDbg

Joseph_Pilov — Tue, 01 Jul 2025 19:17:35 GMT

This article demonstrates how you can create a non-yielding scheduler scenario at will so you can examine the various diagnostic tools for non-yielding schedulers - error log, memory dump, nonyield_copiedstack_ring_buffer_recorded in system_health Xevents.

Do not do this on a production server as you will freeze your production workload. This is designed for learning and better understanding of how SQL Server works and can be troubleshot.

If you already have WinDbg or WinDbgX installed and symbols configured, skip to step 6 [Attach WinDbg to a SQL Server process]
Download and install WinDbg via Windows SDK - Windows app development | Microsoft Developer . Choose the Download the Installer button.
When you start the installation uncheck everything except Debugging Tools for Windows and press Install
Start Windbg
Define a symbol path so you can debug with call stacks that are human-readable
1. File -> Symbol File Path
2. Paste srv*c:\PublicSymbols*https://msdl.microsoft.com/download/symbols
3. Click OK
4. File -> Save Workspace
Attach WinDbg to a SQL Server process
1. File -> Attach to process
2. Chose Sort -> By executable
3. Locate your SQLServr.exe by expanding the properties of each process to identify the one you want
4. Select the process and click OK
Run a debugger command that causes the public symbols for many of the loaded modules to be downloaded in your local symbols folder. For example, the kc command will display the call stacks of all threads in SQL Server. It may take awhile to complete because Windbg is downloading symbol files locally in your defined symbol path. Wait for the debugger to return to the prompt

~*kc100
Now let the SQL Server process continue running by pressing typing g followed by Enter
Connect to SQL Server using SSMS or Sqlcmd and execute a very long-running query. Here is an example that works:

SELECT COUNT_BIG (*) FROM sys.messages a, sys.messages b, sys.messages c
After the query has run for more than 2-3 seconds, go back the WinDbg and break into the debug session. Press either CTRL+Break or Alt+Del. This will place you back to a WinDbg prompt awaiting commands
Let's re-run the command to display the call stacks for all threads. This time the command should complete faster than it did earlier, because most symbol files should already be downloaded.
~*kc100
Scroll to the top of the output and using CTRL+F, search for CSQLSource::Execute. You should find (at least) one thread that has this in the call stack.
Once you locate that thread, look at its WinDbg thread ID. For example, it could be 93 or 112. Here is an example where it's 69:
Now, let's freeze this thread on purpose inside WinDbg, in order to simulate a stalled, non-yielding scenario. This thread has actively executing code and thus likely using a SQL Scheduler. If the thread is frozen artificially, SQL Server Scheduler Monitor will consider it a non-yielding thread because it is not changing state and is not releasing control of the scheduler it was running on.
1. To freeze your thread, use the ~Tf by where T is your identified thread id ("f" is for freeze)
  
  ~Tf
2. Then hit Enter
3. Now press g and Enter
Wait for a minute or two (a quick coffee break)
Now open the C:\Program Files\Microsoft SQL Server\MSSQLXXXXXX\MSSQL\Log\Errorlog and look for an entry like this one:

2025-06-06 15:03:55.77 Server * ******************************************************************************* 2025-06-06 15:03:55.77 Server * 2025-06-06 15:03:55.77 Server * BEGIN STACK DUMP: 2025-06-06 15:03:55.77 Server * 06/06/25 15:03:55 spid 5144 2025-06-06 15:03:55.77 Server * 2025-06-06 15:03:55.77 Server * Non-yielding Scheduler 2025-06-06 15:03:55.77 Server * 2025-06-06 15:03:55.77 Server * ******************************************************************************* 2025-06-06 15:03:55.77 Server CImageHelper::DoMiniDump entered. Dump attempts: 1. Exception record exists... 2025-06-06 15:03:55.77 Server Stack Signature for the dump is 0x00000000000003D6qd
You have successfully simulated a Non-yielding scheduler
To unfreeze your thread, use the ~Tu by replacing T with your identified thread id ("u" is for unfreeze), then hit Enter. In this example:

~69u
To detach Windbg from SQL Server type the quit+detach (qd) command. After that you can close Windbg

qd
If you examine the lastes system_health XEL in your \Log folder you can also find the nonyield_copiedstack_ring_buffer_recorded matching the non-yielding scheduler event and also extract the call stack from there. You can use this call stack with SQLCallStackResolver/README.md at main · microsoft/SQLCallStackResolver tool to identify the call stack. (this screenshot is from another debug session so ignore timestamps)
After SQL Server 2022 another diagnostic capability: RING_BUFFER_NONYIELD_COPIEDSTACK in the sys.dm_os_ring_buffers. When the non-yielding scheduler issue occurs, you can query using this query:

SELECT * FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = 'RING_BUFFER_NONYIELD_COPIEDSTACK'

The output may look like this, particularly if you let the non-yielding issue persist over 5 minutes or so.

Happy debugging!

Windows Server 2025 にインストールしたSQL Server の更新プログラムを削除する方法

Masafumi_Hori — Thu, 22 May 2025 07:19:04 GMT

こんにちは。SQL Server サポートチームです。

事象

Windows Server 2025 にインストールしたSQL Server に対して適用した更新プログラムが、Uninstall Updates(更新プログラムをアンインストール) 画面に表示されません。

// Microsoft SQL Browser とMicrosoft SQL Server VSS Writer は表示されますが、SQL Server は表示されない

対処策

SQL Server の更新プログラムをアンインストールする必要がある場合は、以下の方法でアンインストールをお願いします。

SQL Server 2022 CU19(KB5054531) を例としてご説明します。

※注意事項※

レジストリエディターまたは別の方法を使用してレジストリを誤って変更すると、深刻な問題が発生することがあります。

最悪の場合、オペレーティングシステムの再インストールが必要になることがあります。

マイクロソフトは、レジストリの変更により発生した問題に関しては、一切責任を負わないものとします。

そのため、十分に注意のうえ、作業ください。

SQL Server の更新プログラム(KB5054531) のアンインストール方法：

1) スタートボタン -> ファイル名を指定して実行から「regedit.exe」と入力し、「OK」をクリックします。

2) レジストリエディターが起動したら、以下のレジストリキーを開きます。

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\KB5054531

3) UninstallString をダブルクリックして表示される画面から、[値のデータ] に記載の内容をコピーします。

4) コマンドプロンプトを管理者で起動し、手順3 でコピーしたコマンドを実行します。

5) 更新プログラムをアンインストールする画面が表示されるため、指示に従い更新プログラムのアンインストールを進めます。

以下のように[状態] が全て[成功] となっていれば、SQL Server の更新プログラムをアンインストールすることができたと判断できます。

6) コマンドプロンプトとレジストリエディターの画面を閉じます。

You may see high CPU usage after enabling the Log Shipping with Always ON on multiple databases.

haci — Tue, 13 May 2025 10:54:36 GMT

Symptom

It is possible to reproduce this scenario with 10 Availability Groups and 5 databases per AG.

For all 50 databases SQL Server Log Shipping is configured. Idea is to have a separate copy of these databases which follow the production with a certain delay (this is an intended delay due to business needs)

Observed behavior is whenever Log Shipping backup jobs are triggered, the CPU is peaking on Log Shipping Principal Server. (If you keep the default behavior and they all triggered at the same time)

Perfmon showing the peak on % Processor Time.

Task Manager showing the high CPU usage of cluster.exe , sqlserver.exe (other contributors are sqlagent.exe and logship.exe (multiple instances ) )

To understand this further WPR was captured from this environment. Investigations showed this usage is mainly in Kernel Time and caused by a Cluster API call made by SQL Server.

sqlmin.dll!HadrWsfcUtil::OpenClusterResourceKey sqlmin.dll!HadrAvailabilityGroupStore::Retrieve sqlmin.dll!HadrAvailabilityGroup::Refresh sqlmin.dll!HadrAvailabilityGroup::HadrAvailabilityGroup sqlmin.dll!HadrAvailabilityGroupObjectModel::GetAvailabilityGroupByResourceId sqlmin.dll!HadrAvailabilityGroupObjectModel::GetAvailabilityGroupByName sqlmin.dll!DmHadrInternalAgStatesTable::GetAgConfiguration sqlmin.dll!DmHadrInternalAgStatesTable::InternalGetRow sqlmin.dll!CQScanTVFStreamNew::GetRow sqlmin.dll!CQScanLightProfileNew::GetRow sqlmin.dll!CQScanFilterNew::GetRowHelper sqlmin.dll!CQScanLightProfileNew::GetRow sqlmin.dll!CQueryScan::GetRow sqllang.dll!CXStmtQuery::ErsqExecuteQuery sqllang.dll!CXStmtSelect::XretExecute sqllang.dll!CMsqlExecContext::ExecuteStmts<1,1> sqllang.dll!CMsqlExecContext::FExecute sqllang.dll!CSQLSource::Execute sqllang.dll!CSQLObject::ExecuteFunction sqllang.dll!CUdfExecInfo::InvokeTSQLScalarUDF sqllang.dll!UDFInvokeImpl sqltses.dll!CEsExec::GeneralEval4 sqlmin.dll!CQScanProjectNew::EvalExprs sqlmin.dll!CQScanProjectNew::GetRow sqlmin.dll!CQScanLightProfileNew::GetRow sqlmin.dll!CQueryScan::GetRow sqllang.dll!CXStmtQuery::ErsqExecuteQuery sqllang.dll!CXStmtSelect::XretExecute sqllang.dll!CMsqlExecContext::ExecuteStmts<1,1> sqllang.dll!CMsqlExecContext::FExecute sqllang.dll!CSQLSource::Execute sqllang.dll!ExecuteSql sqllang.dll!CSpecProc::ExecuteSpecial sqllang.dll!CSpecProc::Execute sqllang.dll!process_request sqllang.dll!process_commands_internal sqllang.dll!process_messages sqldk.dll!SOS_Task::Param::Execute sqldk.dll!SOS_Scheduler::RunTask sqldk.dll!SOS_Scheduler::ProcessTasks sqldk.dll!SchedulerManager::WorkerEntryPoint sqldk.dll!SystemThreadDispatcher::ProcessWorker sqldk.dll!SchedulerManager::ThreadEntryPoint kernel32.dll!BaseThreadInitThunk ntdll.dll!RtlUserThreadStart [Root]

This is because log shipping is calling system function sys.fn_hadr_backup_is_preferred_replica for those backups so it can gather AG related backup preferences and act accordingly.

This system function is using certain system views which are built on Cluster API calls in the background to fetch this data (AG role ownership) from Cluster database.

Cause

Concurrent and repetitive calls to this API can cause this high CPU usage.

Resolution

This is due to live nature of those parameters and mitigation is to be differentiating the backup times to minimize that concurrency or distribute AG Primaries to multiple replicas.

SQL Server の更新プログラム GDR, CU, Service Pack の違いと適用可否

Mizuho_Kitao — Thu, 17 Apr 2025 23:54:25 GMT

こんにちは、 SQL Server サポートです。

今回は、SQL Server の GDR, CU, Service Pack の違いと適用可否について紹介します。

GDR、CU、Service Pack の違い

SQL Server の更新プログラムには主に GDR, CU, Service Pack の3種類があります。

GDR (General Distribution Release)

主にセキュリティ関連の修正を提供します。

そのバージョンのSQL Server の以前のGDR のすべての修正を含みます。

例えば、CU も GDR も適用していない SQL Server 2019 で、SQL Server 2019 向けの GDR5 を適用すると、GDR1～GDR4の修正も適用されます。

そのため、GDR1 → GDR2 → GDR3 →・・・のように段階的に適用する必要はありません。

CU (Cumulative Update)

機能改善やバグ修正を含む更新を提供します。

そのバージョンのSQL Server の以前のCU や以前のGDR のすべての修正を含みます。

例えば、CU も GDR も適用していない SQL Server 2019 で、SQL Server 2019 向けの CU5 を適用すると、CU1～CU4の修正も適用されます。

そのため、CU1 → CU2 → CU3 →・・・のように段階的に適用する必要はありません。

Service Pack

大規模な更新を提供し、複数の修正や新機能をまとめて提供します。

そのバージョンのSQL Server の以前のService Pack や以前のCU、以前のGDR のすべての修正を含みます。

そのため、例えばどの Service Pack も適用していない SQL Server 2016 で、Service Pack 3 を適用したい場合、 Service Pack1 → Service Pack2 → Service Pack3 のように段階的に適用する必要はありません。

ただし、どの Service Pack も適用していない SQL Server 2016 で Service Pack 3用の CU や GDR を適用したい場合は、Service Pack3 → 目的の CU や GDR のように適用する必要があります。

なお、Service Pack は SQL Server 2016 以前で提供されていました。

シンプルで予測可能なメインストリームサービスライフサイクルを採用したため、SQL Server 2017 以降ではService Pack は存在せず、CU と GDR のみとなります。

GDR、 CU、 Service Pack の適用可否

・GDR を適用済みの SQL Server には、より新しい GDR か Service Pack か CU が適用できます。

・CU を適用済みの SQL Server には、より新しい CU か Service Pack が適用できます。

・Service Pack が適用済みのSQL Server で、かつ CU も GDR も適用していない場合は、その Service Pack 用の CU またはその Service Pack 用の GDR を適用できます。

各バージョンの SQL Server の最新の更新プログラムは下記から確認できます。

// SQL Server の最新の更新プログラムとバージョン履歴 - SQL Server | Microsoft Learn

https://learn.microsoft.com/ja-jp/troubleshoot/sql/releases/download-and-install-latest-updates

※参考情報

// Announcing the Modern Servicing Model for SQL Server | Microsoft Community Hub

https://techcommunity.microsoft.com/blog/sqlserver/announcing-the-modern-servicing-model-for-sql-server/385594

// SQL Server 累積的更新プログラム (CU) のインストールに関する一般的な問題のトラブルシューティング - SQL Server | Microsoft Learn

https://learn.microsoft.com/ja-jp/troubleshoot/sql/database-engine/install/windows/sqlserver-patching-issues#cumulative-update-and-service-pack-installation-information

// SQL Server のサービスモデル - SQL Server | Microsoft Learn

https://learn.microsoft.com/ja-jp/troubleshoot/sql/releases/servicing-models-sql-server

※本情報の内容（添付文書、リンク先などを含む）は、作成日時点でのものであり、予告なく変更される場合があります。

Kerberos Authentication flow

Arslanov — Sun, 09 Mar 2025 04:31:35 GMT

Video version: Kerberos Authentication flow

Whenever you login with Windows Authentication to the sever like SQL Server, in many cases, Kerberos is used as a main authentication type.

In short, Kerberos is a secure method for verifying the identity of users and services in a network to authenticate users.

So, suppose, Kerberos authentication started to fail and your application cannot login? You started to panic and want to troubleshoot as soon as possible! But you do not know how to do that because you do not understand how this authentication works internally. In this case, if you know how Kerberos functions internally, it becomes much easier for you to troubleshoot this issue.

The below is the Kerberos authentication flow at high level:

1. Whenever client tries to login to the server, it should first do Name resolution. If there is no DNS cache, it will connect domain controller and bring IP address for the server DNS name.

2. Afterwards, there will be three-way TCP handshake with the server.

3. Following TCP handshake, servers negotiate on protocol types of authentications: NTLM or KERBEROS. Often Kerberos is chosen as this is new protocol with more performance efficiency.

4. After Kerberos is chosen, Client will do TCP handshake on TCP port 88.

5. Then, client will send AS-REQ packet to KDC center in domain controller. AS-REQ (Authentication Service Request) is the initial message sent by a client to the Key Distribution Center (KDC) in Domain controller to get TGT. This message includes the client's principal name (username) and may include pre-authentication data such as password. If the username is correct and password is valid, domain controller sends AS RESPONSE. This response includes TGT key and session key. Here is the important point: TGT key is encrypted with password of KRBTGT account and session key is encrypted with client's password. If you go to your domain controller, you will see this KRBGT account. TGT is encrypted with this account’s password.

When the client receives the TGT, it decrypts the session key with password and this session key is placed in memory along with the TGT. Going forward the account’s password is no longer required. When the client makes subsequent ticket requests it will present the TGT and creates a new authentication using the session key and the system timestamp.

6. After getting TGT and session key, client makes TGS request presenting TGT and service principal name (SPNs) of the service the client wants to access.

7. After getting TGS-REQ, domain controller decrypts TGT ticket, validates the user and SPNs. If the SPNs are correctly registered and user credentials, TGT ticket are valid, domain controller responds with TGS-REP which includes the service ticket and a session key. The service ticket is encrypted with the service's secret key, while the session key is encrypted with the client's session key. Here, important point is service ticket is encrypted with the password of service account attached to the server and session key is encrypted by the client’s session key. Therefore, client cannot decrypt service ticket.

8. Finally, by using this service ticket, client makes AP request to the server. And AP-REP is received. This AP-REP is often not mandatory and does not happen depending on the service type. With this, KERBEROS authentication finishes.

Authentication flow in network traces

1. TCP-handshake

2. First, as we learn AS REQUEST is sent. One important point here: first AS-REQ fails with PRE-AUTH REQUIRED error because client did not send pre-authorization, or did not send the appropriate type of pre-authorization, to receive a ticket. The client will retry with the appropriate kind of pre-authorization (the KDC returns the pre-authentication type in the error). Many Kerberos implementations will start off without preauthenticated data and only add it in a subsequent request when it sees this error. This is expected and you should ignore this.

3. Afterwards, client again sends AS-REQUEST with appropriate pre-authorization.

4. Domain controller is responding with AS-REP and providing encrypted TGT ticket and session key.

5. By using this session key and TGT, client is making TGS request. It also includes SPN names.

6. Finally, domain controller responds TGS response and includes service key. By using this service key, client makes authentication request.

About SQL Server Error 13542

cleng-ms — Fri, 14 Feb 2025 04:32:16 GMT

Recently we have received a problem report about the following error:

Msg 13542, Level 16, State 0, Line 42

ADD PERIOD FOR SYSTEM_TIME on table 'xxx' failed because there are open records with start of period set to a value in the future.

I would like to share the cause of the problem and the solution here to help DBAs experiencing the same problem.

Background:

You can add versioning data to an existing non-temporal table with data populated to make it a temporal table. The document below contains a sample script in the "Add versioning to non-temporal tables" section for this purpose.

Create a system-versioned temporal table - SQL Server | Microsoft Learn

Note that in the sample script, the column for "ROW START" column the data type is set to DATETIME2 as follows.

ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_InsurancePolicy_ValidFrom DEFAULT SYSUTCDATETIME()

If you change DATETIME2 to DATETIME2(x), by specifying a precision x (range is 0 - 7) for the floating-point part, the aforementioned error may occur when adding the value of the ValidFrom column for existing data.

Cause:

When adding the value of SYSUTCDATETIME() as the default ValidFrom value for existing data, because of the precision specified with the DATATIME2(x), SQL Server needs to round the time to the nearest value with that precision. This can be either "round up" or "round down", depending whether the last digit within the precision is larger than 4 or not. In the case of "round up", the result is a time value in the future.

Workaround:

Change
DEFAULT SYSUTCDATETIME()
to
DEFAULT DATEADD(SECOND,-1, SYSUTCDATETIME())
so that the time is not in the future even in the case of "round up".

ValidFrom DATETIME2(0) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_InsurancePolicy_ValidFrom DEFAULT DATEADD(SECOND,-1, SYSUTCDATETIME())

Documentation for DATETIME2:
datetime2 (Transact-SQL) - SQL Server | Microsoft Learn

SQL Server がインストールされていないAzure VM において、「Failed to find SQL instance to target」を含むメッセージが記録される

Masafumi_Hori — Mon, 16 Sep 2024 06:47:09 GMT

こんにちは。SQL Server サポートチームです。

事象

SQL Server がインストールされていないAzure VM において、Windows イベントログのアプリケーションログに以下のメッセージが記録される場合があります。

　-- ソース

　Microsoft SQL Server IaaS Agent Setup

　-- メッセージ

　Failed to find SQL instance to target. Skipping 'NT Service\\SQLIaaSExtensionQuery' removal from sql logins.

メッセージの原因

SQL IaaS Agent 拡張機能への自動登録機能を有効化しているサブスクリプションにおいては、Azure VM に対して SQL Server がインストールされているかどうかを検出するジョブが毎月実行されます。

また、自動登録機能が有効化されているかどうかに関わらず、弊社の計画メンテナンスによって、SQL Server がインストールされているかどうかを検出するジョブが実行されます。

したがって、上述のジョブによりAzure VMにSQL Serverがインストールされていないことを検出したことが発生原因となります。

※計画メンテナンスのタイミングは不定期です。

SQL IaaS Agent 拡張機能への自動登録の有効化状況については、Azure Portal から以下の手順で確認が可能です。

　１．Azure portal にサインインします。

　２．[SQL 仮想マシン] リソースページに移動します。

　３．[SQL Server VM の自動登録](Automatic SQL Server VM registration) を選択して、 [SQL Server VM の自動登録](Automatic SQL Server VM registration) ページを開きます。

　４．登録済みの場合は以下のように表示されます。

SQL IaaS Agent 拡張機能への自動登録の詳細につきましては、下記の公開情報をご確認ください。

　SQL IaaS Agent 拡張機能への自動登録

　https://learn.microsoft.com/ja-jp/azure/azure-sql/virtual-machines/windows/sql-agent-extension-automatic-registration-all-vms?view=azuresql&tabs=azure-cli

影響

SQL ServerがインストールされていないAzure VMにて記録されたものであれば、本メッセージによる影響はありません。

対処策

本メッセージはジョブの出力結果として記録されるため、出力を止めることはできません。

監視ツールなどで監視されている場合は、必要に応じて監視対象から除外してください。

101 of Troubleshooting SQL Server on Linux

Infant_Arockiaraj — Fri, 19 Jul 2024 15:36:18 GMT

Based on our extensive experience with customers using SQL on Linux, we have compiled a guide outlining fundamental troubleshooting steps and available tools to assist in resolving SQL on Linux issues. This guide aims to make it easier for SQL DBAs who have primarily worked on Windows operating systems over the years.

In this article, we will normalize three checks in Linux that we typically perform in Windows to start troubleshooting issues with SQL Server:

System logs
SQL Server logs
Task Manager

Note: The screenshots in the below examples are taken from RHEL and Ubuntu machines, and while the Linux flavors are different, the commands are similar in both environments.

System logs:

It contains the log messages that the system processes and applications, and these messages are written as plain text log files.

The rsyslog service keeps various log files in the /var/log directory. You can open these files using native commands such as tail,head,more,less,cat, and so forth, depending on what you are looking for.

In RHEL and SLES, they are called messages, while in Ubuntu, you would find them as syslog.

RHEL and SLES: The primary system log file is located at /var/log/messages.

Ubuntu: The main system log file is /var/log/syslog.

Log files and journals are crucial to a system administrator's work. They reveal a great deal of information about a system and are instrumental during troubleshooting and auditing.

For example, in RHEL, to display boot and other kernel messages, view /var/log/messages:

[server]$ cat /var/log/messages

Use grep and other filtering tools to gather more specific events from a file. You can also use tail to view files as they are updated.

[server]$ tail -f /var/log/messages

Check the /var/log/secure file to view users and their activities:

[server]$ tail -f /var/log/secure

Similarly in Ubuntu, we have/var/log/syslog

What does the syslog or messages file contain? how do we read them?

These log files contain events and messages generated by the kernel, applications, and users that log into the system.

The logs are written in the below format.

The timestamp indicates the time when a log entry was created in the format MMM dd HH:mm:ss. Notice that this format does not include a year.
Hostname is the host or system that originally create the message.
Application is the application that created the message.
Message contains the actual details of an event.

Since the message/syslog could contain thousands/millions of lines as log entries, using 'cat' command alone might not be the right choice at times. I suggest using grep for filtering text through the files.

Using tail -f command allows you to read the current log file in real time. You may combine it with 'grep' to filter on desired text.

An alternate method to validate the system events is via the journald

Systemd-journald :

journald is a component of systemd responsible for handling logging. It captures logs, records them, and makes them easy to find. Unlike traditional syslog implementations, journald offers features like structured logging, indexing for fast search, access control, and signed messages.

The systemd-journald service does not keep separate files, as rsyslog does. The idea is to avoid checking different files for issues. Systemd-journald saves the events and messages in a binary format that cannot be read with a text editor. You can query the journal with the journalctl command.

journald stores logs in memory (RAM) without persistent storage (by default), while the Traditional syslog (e.g., /var/log/messages) persists log data to flat files.

To show all event messages, use:

[server]$ journalctl

To view journal entries for today, use:

[server]$ journalctl --since today

To check for messages related to the sql server service for the past hour, you can run:

journalctl --unit mssql-server.service --since "1 hour ago"

SQL Server Error logs:

The error log contains informational messages, warnings, and information about critical events. The error log also contains information about user-generated messages and auditing information such as logon events (success and failure).In Linux the default SQL Errorlog location is /var/opt/mssql/log

(Keep in mind that you will require superuser permissions to traverse these folders and logs.)

This is also the default location for XEvents, dump files and trace files.

Comparing Error and System Logs Output

You can use both the SQL Server error log and the system logs to identify the cause of problems. For example, while monitoring the SQL Server error log, you may encounter error messages that do not contain cause information. By comparing the dates and times for events between these logs, you can narrow the list of probable causes.
Consider the following scenario: We receive an alert indicating that the application is unable to connect to SQL following a maintenance activity over the weekend. We log into the server to verify if SQL Server is operational.

Upon inspection, we find that the SQL process is not running. We then check the status and attempt to start the SQL Server to see if it comes online.

On restarting SQL Server, we observe the following information in the error logs.

Next, we need to examine the system event logs, specifically the messages file in RHEL & SLES or the syslog file in Ubuntu, where events are recorded.We can use the following command to retrieve service logs from the recent boot:

journalctl --unit mssql-server.service --boot --no-pager

By validating the messages or syslog file within the same timeframe, we gather additional information.

From the logs, we identify a permission issue on a specific folder or file that is preventing SQL Server from starting. Granting the necessary permissions should resolve the issue.

Task Manager:

Where is the Task Manager in Linux? There are command-line utilities that provide similar information to what we see in Windows. We will explore the top and htop utilities

top

The first line of numbers on the dashboard includes the time, how long your computer has been running, the number of people logged in, and what the load average has been for the past one, five, and 15 minutes. The second line shows the number of tasks and their states: running, stopped, sleeping, or zombie.The next 3 lines describe CPU, Memory utilization, Swap Memory of the server.
The column details of the process are tabulated as below.

To get into the details of a particular process, (In our case SQL Server) we can use the PID to get the further details of the SQL and the tasks that SQL is currently running.
To get the child PID of SQLServer, use this short command and run the top command on the pid.

pidof sqlservr | cut -d' ' -f1

top -p (pid output of the above command)

We can also get them into single command as shown below,

top -p $(pidof sqlservr | cut -d' ' -f1)

We see the utilization of various tasks within SQL Server, that are consuming the resources.

Another utility that provides similar information is htop. The key difference is that htop offers a more user-friendly experience with its use of colors and graphs, compared to the top command.

While top and htop provide valuable information, there are additional command-line tools such as vmstat, System Activity Information (sar), iostat, and others. I suggest running these commands on your test machines and monitoring the output to become familiar with checking performance metrics on a Linux server.
References:

SLES: https://documentation.suse.com/sles/12-SP5/html/SLES-all/cha-util.html#

RHEL:https://docs.redhat.com/en/documentation/red_hat_enterprise_linux/7/html/performance_tuning_guide/sect-red_hat_enterprise_linux-performance_tuning_guide-performance_monitoring_tools-built_in_command_line_tools

Ubuntu: https://manpages.ubuntu.com/manpages/jammy/man1/top.1.html#2.%20summary%20display

I hope this serves as a helpful introduction to basic troubleshooting for SQL on Linux.

Effectively troubleshoot latency in SQL Server Transactional replication: Part 2

Arslanov — Wed, 17 Jul 2024 05:54:04 GMT

Let us continue our troubleshooting by checking threads in this part.

Step 4.1. Troubleshoot latency in Log Reader agent’s reader thread

Firstly, define the level of reader thread latency by running below query in Publisher server.
sp_replcounters GO
The above shows reader thread replicating on average 115 transactions per second and more than 7.5mln transactions are waiting to be replicated to the distribution database. On average, transactions are waiting 134880secs to be replicated, which is high latency.
Run below in publisher server and find session id of Log Reader’ reader thread:SELECT SessionId = s.session_id, App = ISNULL(s.program_name, N'') FROM sys.dm_exec_sessions s WHERE s.program_name LIKE '%LogReader%'
Place the session id to the below Event session and create event session. Run the session for about 5mins:CREATE EVENT SESSION [LogReaderMonitor] ON SERVER ADD EVENT sqlos.wait_completed( ACTION(package0.callstack) WHERE ([sqlserver].[session_id]=(123))), ---Change session id here ADD EVENT sqlos.wait_info_external( ACTION(package0.callstack) WHERE (([opcode]=('End')) AND ([sqlserver].[session_id]=(123)))), ---Change session id here ADD EVENT sqlserver.rpc_completed( ACTION(package0.callstack) WHERE ([sqlserver].[session_id]=(123))) ---Change session id here ADD TARGET package0.event_file(SET filename=N'C:\Temp\logreader_reader_track',max_file_size=(256),max_rollover_files=(5)) WITH (MAX_MEMORY=8192 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF) GO
Investigate the event file. For example, below you can confirm the activities with the same GUID with sequence ids. As you can see, initially, we are spending time for memory allocation and then sp_replcmds is finishing after 789 microseconds.
Note: duration of wait_info_external is in milliseconds while rpc_completed is in microseconds.

If wait time is high compared to CPU time, check wait type and troubleshoot accordingly. For example, on the above example we faced MEMORY_ALLOCATION_EXT wait_type but duration is 0. So, we are not waiting.

If CPU time is higher, this means log thread is running but latency is being observed because you have high load. High load can be caused by several causes:

Large batch of replicated transactions: large batch of transactions are the main cause of latency in reader thread performance. Check number of commands and transactions in agent statistics from verbose logs we obtained in Step 3.1.b. If the number of commands is significantly high compared to the number of transactions, it is possible that large transactions are being replicated. For example, as below:

If Reader Latency is caused by large number of pending commands, waiting for the Log Reader to catch up may be the best short-term solution. Long-term options include replicating batches during non-peak time.

Large number of non-replication transactions: A transaction log with a high percentage of non-replicated transaction will cause latency as the Log Reader scans over transaction to be ignored. You can check whether this problem exists by looking at Log Reader agent history we checked in Step 3.1.a. For example, in the below log reader history, we can see more than 5mln rows are being scanned but only 142 rows have been marked for replication.
In this case, ensure constant transaction log truncation and try to perform maintenance activities offline.

High number of VLFs: A large number of Virtual Log Files (VLFs) can contribute to long running read times. For the number of VLFs, execute the following command. Counts in 100K+ may be contributing to Log Reader Reader-Thread performance problems.
SELECT COUNT (DISTINCT vlf_sequence_number) FROM sys.dm_db_log_info ( PublisherDBID )

Step 4.2. Troubleshoot latency in Log Reader agent’s writer thread

By using log reader’s history log (refer to Step 3.1.a), you can get last transaction sequence number and delivery rate, latency information. If you do not observe latency in reader thread (Step 4.1), this means the latency rate is mainly by writer thread:

You can use below command to check transaction (xact_seqno) at where we are currently:
-- Get publisher db id USE distribution GO SELECT * FROM dbo.MSpublisher_databases -- Get commands we are at SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED GO BEGIN TRAN USE distribution GO EXEC Sp_browsereplcmds @xact_seqno_start = 'xact_seqno', @xact_seqno_end = 'xact_seqno', @publisher_database_id = PUBLISHERDB_ID COMMIT TRAN GO
Run below in publisher server and find session id of Log Reader’ writer thread:
SELECT SessionId = s.session_id, App = ISNULL(s.program_name, N'') FROM sys.dm_exec_sessions s WHERE s.program_name LIKE '%LogReader%'
Check whether any blocking happening with this session:
sp_who2
Then run the below query in distributor server by changing the session id to log reader session id:
CREATE EVENT SESSION [logreader_writer_track] ON SERVER ADD EVENT sqlos.wait_completed( ACTION(package0.callstack) WHERE ([sqlserver].[session_id]=(64))), -- Change session id to log reader writer session id ADD EVENT sqlos.wait_info_external( ACTION(package0.callstack) WHERE (([opcode]=('End')) AND ([sqlserver].[session_id]=(64)))), -- Change session id to log reader writer session id ADD EVENT sqlserver.sp_statement_completed( ACTION(package0.event_sequence,sqlserver.plan_handle,sqlserver.session_id,sqlserver.transaction_id) WHERE ([sqlserver].[session_id]=(64))) -- Change session id to log reader writer session id ADD TARGET package0.event_file(SET filename=N'C:\Temp\logreader_writer_track',max_file_size=(256),max_rollover_files=(5)) WITH (MAX_MEMORY=8192 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
Investigate the collected event logs. For example, below you can confirm the activities with the same GUID with sequence ids. Each of this activity is writer threads attempt to write replication logs to distribution database. As you can see, we spent very little time (nearly 0) initially for MEMORY_ALLOCATION_EXT, then select statement is finishing after 39 microseconds.

Compare wait time (duration – cpu_time) and cpu_time. If wait time is high compared to CPU time, check wait_type and troubleshoot accordingly. For example, above we face MEMORY_ALLOCATION_EXT wait_type. If CPU time is high, you can investigate the execution plan by using corresponding plan_handle for time consuming query which you can get from above event logs:
SELECT * FROM sys.dm_exec_query_plan(PLAN_HANDLE)

Step 4.3. Troubleshoot latency in Distribution agent’s reader thread

To find the session id for Distribution agent, you need to find if it is a Push or Pull Subscription. In the case of push subscription, run below command in distributor server. In the case of Pull subscription, run below command in subscriber server.
SELECT SessionId = s.session_id, App = ISNULL(s.program_name, N'') FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id) WHERE (select text from sys.dm_exec_sql_text(c.most_recent_sql_handle)) LIKE '%sp_MSget_repl_command%'
Check whether any blocking happening with this session:
sp_who2
Then run the below query in distributor server by changing the session id to distribution agent session id:
CREATE EVENT SESSION [distributor_writer_track] ON SERVER ADD EVENT sqlos.wait_completed( ACTION(package0.callstack) WHERE ([sqlserver].[session_id]=(64))), -- Change session id to dist agent session id ADD EVENT sqlos.wait_info_external( ACTION(package0.callstack) WHERE (([opcode]=('End')) AND ([sqlserver].[session_id]=(64)))), -- Change session id to dist agent session id ADD EVENT sqlserver.sp_statement_completed( ACTION(package0.event_sequence,sqlserver.plan_handle,sqlserver.session_id,sqlserver.transaction_id) WHERE ([sqlserver].[session_id]=(64))) -- Change session id to dist agent session id ADD TARGET package0.event_file(SET filename=N'C:\Temp\distributor_reader_track',max_file_size=(256),max_rollover_files=(5)) WITH (MAX_MEMORY=8192 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
Investigate the collected event logs. For example, below you can confirm the activities with the same GUID with sequence ids. Each of this activity is reader threads attempt to read replication logs. As you can see, we spent very little time (nearly 0) initially for MEMORY_ALLOCATION_EXT, then select statement is finishing after 29 microseconds.
Compare wait time (duration – cpu_time) and cpu_time. If wait time is high compared to CPU time, check wait_type and troubleshoot accordingly. For example, above we face MEMORY_ALLOCATION_EXT wait_type. If CPU time is high, you can investigate the execution plan by using corresponding plan_handle for time consuming query which you can get from above event logs:
SELECT * FROM sys.dm_exec_query_plan(PLAN_HANDLE)

High CPU time can often mean there is a high load which can be caused by large batch of replicated transactions. You can compare the number of commands and transactions by using the below query.

SELECT count(c.xact_seqno) as CommandCount, count(DISTINCT t.xact_seqno) as TransactionCount FROM MSrepl_commands c with (nolock) LEFT JOIN msrepl_transactions t with (nolock) on t.publisher_database_id = c.publisher_database_id and t.xact_seqno = c.xact_seqno WHERE c.publisher_database_id = 1 --Change to target database id here

For the past days’ statistics, you can leverage below command:

USE distribution select t.publisher_database_id, t.xact_seqno, max(t.entry_time) as EntryTime, count(c.xact_seqno) as CommandCount, count(DISTINCT t.xact_seqno) as TransactionCount into #results FROM MSrepl_commands c with (nolock) LEFT JOIN msrepl_transactions t with (nolock) on t.publisher_database_id = c.publisher_database_id and t.xact_seqno = c.xact_seqno GROUP BY t.publisher_database_id, t.xact_seqno SELECT publisher_database_id ,datepart(year, EntryTime) as Year ,datepart(month, EntryTime) as Month ,datepart(day, EntryTime) as Day ,datepart(hh, EntryTime) as Hour ,sum(CommandCount) as CommandCountPerTimeUnit ,sum(TransactionCount) as TransactionCountPerTimeUnit FROM #results GROUP BY publisher_database_id ,datepart(year, EntryTime) ,datepart(month, EntryTime) ,datepart(day, EntryTime) ,datepart(hh, EntryTime) ORDER BY publisher_database_id, Month, Day, Hour

As you see, I am executing one command per transaction making TransactionCount nearly equal to CommandCount.

Step 4.4. Troubleshoot latency in Distribution agent’s writer thread

Find the session id and App name for Distribution agent by inserting your publication name to WHERE clause below:
SELECT SessionId = s.session_id, App = ISNULL(s.program_name, N'') FROM sys.dm_exec_sessions s WHERE s.program_name LIKE '%publish%' GO
Check whether there is blocking for the above session id(s):
sp_who2
Create event session by inserting app name:
CREATE EVENT SESSION [distributor_writer_track] ON SERVER ADD EVENT sqlos.wait_completed( ACTION(package0.callstack,sqlserver.session_id,sqlserver.sql_text) WHERE ([sqlserver].[client_app_name]=N'SQLVM4-TRANSACR_AdventureWorksLT_test_table_pub' AND [package0].[greater_than_uint64]([duration],(0)))), ADD EVENT sqlos.wait_info_external( ACTION(package0.callstack,sqlserver.session_id,sqlserver.sql_text) WHERE ([sqlserver].[client_app_name]=N'SQLVM4-TRANSACR_AdventureWorksLT_test_table_pub' AND [package0].[greater_than_uint64]([duration],(0)))), ADD EVENT sqlserver.sp_statement_completed( ACTION(package0.event_sequence,sqlserver.plan_handle,sqlserver.session_id,sqlserver.transaction_id) WHERE ([sqlserver].[client_app_name]=N'SQLVM4-TRANSACR_AdventureWorksLT_test_table_pub')) ADD TARGET package0.event_file(SET filename=N'C:\Temp\distributor_writer_track',max_file_size=(5),max_rollover_files=(5)) WITH (MAX_MEMORY=8192 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF) GO
Investigate the collected event logs. For example, below you can confirm the activities with the same GUID with sequence ids as statement levels. Can you find any high duration at any of the statements?

Compare wait time (duration – cpu_time) and cpu_time. If wait time is high compared to CPU time, check wait_type and troubleshoot accordingly. For example, above we face NETWORK_IO wait_type. If CPU time is high, you can investigate the execution plan by using corresponding plan_handle for time consuming query which you can get from above event logs:

SELECT * FROM sys.dm_exec_query_plan(PLAN_HANDLE)

Supervisor: Collin Benkler, Sr EE for SQL Server in Microsoft

Effectively troubleshoot latency in SQL Server Transactional replication: Part 1

Arslanov — Wed, 17 Jul 2024 05:53:05 GMT

High level transactional replication architecture

The initial stage of transactional replication is initializing the subscriber. Although this can be done via backup, the typical approach generating a snapshot by the Snapshot Agent and storing it in the snapshot folder. It’s C:\Program Files\Microsoft SQL Server\<INST>\MSSQL\ReplData by default and configurable. Then, the Distribution Agent transfers the snapshot to the subscriber.

Afterwards, incremental changes in the published database are tracked and replicated to subscriber database. This replication process is done in three phases:

Transactions are marked "for replication" in the transaction log.
The Log Reader Agent reader thread scans through the transaction log using sp_replcmds and looks for transactions that are marked "for replication." These transactions are then saved to the distribution database by the Log Reader agent writer thread using sp_MSadd_replcmds.
The Distribution Agent reader thread scans through the distribution database using sp_MSget_repl_commands. Then, by using the distribution writer thread, this agent connects to the subscriber to apply those changes to the subscriber using sp_MSupd..., sp_MSins..., and sp_Msdel_* (where the “*” denotes the schema and object name of the published article)..

Figure 1. Transactional Replication architecture that shows the locations of each thread and agent in the case of Remote distributor and pull subscription case

Troubleshooting steps

The following graph shows the process we use to troubleshoot. We troubleshoot by dividing the process into two parts.

Step 1. Get information about “Big Picture”

Before you dive into solving any issue, you need to fully understand the type of environment you have as there might have been changes you are unaware of. An easy way to do that is to run script SQLServer/Script Replication Topology at master · sqlserver-parikh/SQLServer (github.com) which gives output like below.

Step 2. Get tracer tokens

After confirming the environment, insert tracer tokens and identify where we are stuck. Tokens can be inserted via Replication Monitor:

For historical tracer token results, you can run below query in distributor and compare with current results, being the last row the last result:

USE Distribution SELECT p.publication_id, p.publication, agent_id, Datediff(s,t.publisher_commit,t.distributor_commit) as 'Time To Dist (sec)', Datediff(s,t.distributor_commit,h.subscriber_commit) as 'Time To Sub (sec)' FROM MStracer_tokens t JOIN MStracer_history h ON t.tracer_id = h.parent_tracer_id JOIN MSpublications p ON p.publication_id = t.publication_id

NOTE –

“distribution” is the default for the distribution database. Be sure to change this if you configured a different name for the distribution database. Additionally, this history is cleaned up any time replication upgrade scripts are executed, there is a change to the distributor configuration, sp_MShistory_cleanup (depending on retention duration specified), or sp_MSdelete_tracer_history is executed (again, depending on parameters used).

If you observe latency or “Pending” status in “Publisher to Distributor”, the issue is with Log Reader agent (refer to Step 3.1). If the latency is seen in “Distributor to Subscriber” as the screenshot above, the issue is with Distribution agent (refer to Step 3.2).

Step 3.1. Troubleshoot latency in Log Reader agent

Check agent history table for any errors by specifically paying attention to comments and error text columns:
USE distribution SELECT a.name AS agent_name, CASE [runstatus] WHEN 1 THEN 'Start' WHEN 2 THEN 'Succeed' WHEN 3 THEN 'In progress' WHEN 4 THEN 'Idle' WHEN 5 THEN 'Retry' WHEN 6 THEN 'Fail' END AS Status ,[start_time] ,h.[time] -- The time the message is logged. ,[duration] --The duration, in seconds, of the message session. ,[comments] ,h.[xact_seqno] -- The last processed transaction sequence number. ,[delivery_time] -- The time first transaction is delivered. ,[delivered_transactions] --The total number of transactions delivered in the session. ,[delivered_commands] -- The total number of commands delivered in the session. ,[average_commands] -- The average number of commands delivered in the session. ,[delivery_rate] -- The average delivered commands per second. ,[delivery_latency] -- The latency between the command entering the published database and being entered into the distribution database. In milliseconds. ,[error_id] -- The ID of the error in the MSrepl_error system table. ,e.error_text -- error text FROM [distribution].[dbo].[MSlogreader_history] h JOIN MSlogreader_agents a ON a.id = h.agent_id LEFT JOIN MSrepl_errors e ON e.id = h.error_id ORDER BY h.time DESC
Furthermore, 5-min interval performance statistics have been added to the history table.

If stats state=1, both reader and writer thread of Log Reader agent are performing as expected. If state=2, writer thread is taking a long time to write changes to distribution database. In this case, you should investigate, writer thread (Step 4.2). State=3 means the reader thread is taking a long time scanning the replicated changes from the transaction log and this thread should be investigated (Step 4.1). For example, below, the writer thread is causing latency as reader thread waited for it for 300 seconds to free queue buffer for new replicated data.
Ref: Statistics for Log Reader and Distribution agents - SQL Server | Microsoft Learn
Sometimes the history table is not enough to resolve latency issues. In this case, you should enable verbose logging for detailed logs (-Output C:\Temp\OUTPUTFILE.txt -Outputverboselevel 3 ). https://learn.microsoft.com/en-US/sql/relational-databases/replication/troubleshoot-tran-repl-errors?view=sql-server-ver16#enable-verbose-logging-on-any-agent
You can investigate the verbose detailed logs for any errors. Particularly pay attention to "Status" logs.
Furthermore, verbose logging provides 5-min interval Log Reader agent statistics as below. Check the Fetch time (reader thread performance) and Write time (writer thread performance) for any latency.

If you cannot find any error logs but you detect latencies in either reader or writer thread, go to Step 4 and check the corresponding thread. For example, in the above example, we detected high latencies in Fetch time compared to Write time. So, the issue is probably with reader thread (refer to Step 4.1 in Part 2).

Step 3.2. Troubleshoot latency in Distribution agent

Check Distribution agent history for any errors by specifically paying attention to comments and error text: USE distribution SELECT a.name AS agent_name, CASE [runstatus] WHEN 1 THEN 'Start' WHEN 2 THEN 'Succeed' WHEN 3 THEN 'In progress' WHEN 4 THEN 'Idle' WHEN 5 THEN 'Retry' WHEN 6 THEN 'Fail' END AS Status ,[start_time] ,h.[time] -- The time the message is logged. ,[duration] --The duration, in seconds, of the message session. ,[comments] ,h.[xact_seqno] -- The last processed transaction sequence number. ,[current_delivery_rate] -- The average number of commands delivered per second since the last history entry. ,[current_delivery_latency] --The latency between the command entering the distribution database and being applied to the Subscriber since the last history entry. In milliseconds. ,[delivered_transactions] --The total number of transactions delivered in the session. ,[delivered_commands] -- The total number of commands delivered in the session. ,[average_commands] -- The average number of commands delivered in the session. ,[delivery_rate] -- The average delivered commands per second. ,[delivery_latency] -- The latency between the command entering the distribution database and being applied to the Subscriber. In milliseconds. ,[total_delivered_commands] -- The total number of commands delivered since the subscription was created. ,[error_id] -- The ID of the error in the MSrepl_error system table. ,e.error_text -- error text FROM MSdistribution_history h JOIN MSdistribution_agents a ON a.id = h.agent_id LEFT JOIN MSrepl_errors e ON e.id = h.error_id ORDER BY h.time DESCFurthermore, 5-min interval performance statistics have been added to the history table.
If stats state=1, both reader and writer thread of Log Reader agent are performing as expected. If state=2, writer thread is taking a long time to write changes to distribution database. In this case, you should investigate, writer thread (Step 4.2). State=3 means the reader thread is taking a long time scanning the replicated changes from the transaction log and this thread should be investigated (Step 4.1). For example, below, the writer thread is causing latency as reader thread waited for it for 300 seconds to free queue buffer for new replicated data.

Ref: Statistics for Log Reader and Distribution agents - SQL Server | Microsoft Learn
Sometimes the history table is not enough to resolve latency issues. In this case, you should enable verbose logging for detailed logs (-Output C:\Temp\OUTPUTFILE.txt -Outputverboselevel 3 ). https://learn.microsoft.com/en-US/sql/relational-databases/replication/troubleshoot-tran-repl-errors?view=sql-server-ver16#enable-verbose-logging-on-any-agentYou can investigate the verbose detailed logs for any errors. Particularly pay attention to "Status" logs.

Furthermore, verbose logging provides distribution agent statistics as below. Check the Fetch time (reader thread performance) and Write time (writer thread performance) for any latency.

If you cannot find any error logs but you detect latencies in either reader or writer thread, go to Step 4 and check the corresponding thread. For example, in the above example, we detected high latencies in Reader thread compared to Writer thread. Therefore, the issue is with reader thread (Step 4.3). Let us continue doing the next steps in Part 2!!

Supervisor: Collin Benkler, Sr. EE for SQL Server in Microsoft.

SQL Server 2022 分散型可用性グループにおける同期失敗

Katsuya_Ito — Mon, 16 Sep 2024 06:51:48 GMT

こんにちは。SQL Server サポートチームです。

今回は、分散型可用性グループにおける同期が突然失敗する事象についてご紹介します。

事象

SQL Server 2022 で分散型可用性グループを構成し FILESTREAM を使用している環境において、正常に稼働していた分散型可用性グループが突如同期に失敗し、同期の状態が NOT_HEALTHY になります。

<事象発生構成>

・SQL Server 2022

・分散型可用性グループ

・FILESTREAM

原因

前提として、FILESTREAM の機能はログの順序性が重要になっています。

SQL Server 2022 より、パフォーマンス向上を目的に分散型可用性グループ間で、下記の弊社公開情報に記載している通り複数の TCP 接続を使用できるようになりました。

SQL Server 2022 の新機能により複数の TCP セッションからログを転送することで、FILESTREAM の内部的なログの順序がずれてしまうことで、同期に異常が発生することが原因です。

本事象についてはSQL Server 2022 の動作上の制限になります。

SQL Server 2022 (16.x) の新機能

- 可用性

https://learn.microsoft.com/ja-jp/sql/sql-server/what-s-new-in-sql-server-2022#availability

*****

分散型可用性グループ - tcp 待機時間が長いリモートリンク間のネットワーク帯域幅使用率を向上させるために、複数の TCP 接続を使用するようになりました。

*****

対処策

本事象の対処策は、トレースフラグ 5597 を設定することです。

トレースフラグ 5597 は、SQL Server 2022の新機能(分散型可用性グループにおける複数TCP使用) を無効するのみで、以前のバージョンと同等な動作となり、他への影響はありません。

変更手順

トレースフラグ 5597 の設定方法は、以下の通りです。

トレースフラグ設定方法

--------------------------------

1) SQL Server Configuration Manager (構成マネージャー)を起動します。

2) SQL Serverのサービス-> SQL Server (MSSQLSERVER もしくはインスタンス名) を右クリックし、プロパティを開きます。

3) [起動時のパラメーター] タブの[起動時のパラメーターの指定] ボックスに下記の値を入力し、[追加] をクリックします。

-T5597

5) [OK]をクリックし、設定を確定します。設定は、次回SQL Server サービス起動後に有効になります。

How to perform unattended uninstall for SQL Server Express

IdaliaL — Tue, 25 Jun 2024 16:22:32 GMT

Recently, I received a question about unattended uninstall for SQL Server Express edition. This article describes how to perform this task.

We need to take in consideration before to proceed

User that performs the process must be a local administrator with permissions to log on as a service. You can review more information about required permissions here.
If the machine has the minimum required amount of physical memory, increase the size of the page file to two times the amount of physical memory. Insufficient virtual memory can result in an incomplete removal of SQL Server.
On a system with multiple instances of SQL Server, the SQL Server browser service is uninstalled only once the last instance of SQL Server is removed. The SQL Server Browser service can be removed manually from Programs and Features in the Control Panel.
Uninstalling SQL Server deletes tempdb data files that were added during the install process. Files with tempdb_mssql_*.ndf name pattern are deleted if they exist in the system database directory.
Backup your databases.

Uninstall process

Open Command Prompt with admin rights
Navigate to the below directory

C:\Program Files\Microsoft SQL Server\160\Setup Bootstrap\MyInstanceName

Please note that 160 version is the major build version of SQL Server.

https://docs.microsoft.com/en-us/sql/database-engine/install-windows/view-and-read-sql-server-setup-log-files?view=sql-server-ver15

C:\>cd C:\Program Files\Microsoft SQL Server\160\Setup Bootstrap\SQL2022
Run the below command
C:\Program Files\Microsoft SQL Server\160\Setup Bootstrap\SQL2022>setup.exe /Q /ACTION=uninstall /INSTANCENAME=SQLEXPRESS /FEATURES=SQLENGINE

For more information on uninstall options, see

https://learn.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-from-the-command-prompt?view=sql-server-ver16#Uninstall

Hope this helps!

Idalia - SQL Server CSS

Migration sql server from sql 2017 to sql 2022 with Minimal Down time

Yohei_Kawajiri — Fri, 14 Jun 2024 08:57:13 GMT

Below is our suggestion for the migration activity:

Always on High Availability
DB Mirroring
Logshipping

SQL Always on High Availability :

It is possible to configure a SQL Server Always On availability group with a primary replica running on SQL Server 2017 and a secondary replica running on SQL Server 2022, but there are important considerations and limitations to keep in mind:

Backward Compatibility: SQL Server supports having replicas on different versions, but the primary replica must be on an older version than or equal to the secondary replicas. Therefore, having SQL Server 2017 as the primary and SQL Server 2022 as the secondary is valid.
Database Upgrade Path: When you decide to upgrade the primary replica to a newer version, you need to follow a specific upgrade path to ensure minimal downtime and data integrity. Typically, this involves:

Adding the newer version as a secondary replica.
Failing over to the secondary replica running the newer version (making it the new primary).
Upgrading the former primary to the newer version.
Adding it back to the availability group.

Feature Compatibility: Some features available in SQL Server 2022 might not be fully supported or behave differently when the primary replica is running on SQL Server 2017. It's essential to test the behavior of your applications and workloads thoroughly in this mixed-version environment.
Support and Documentation: Always refer to the official Microsoft documentation for the most accurate and up-to-date information about version compatibility and supported configurations. Microsoft's official [documentation on Always On availability groups](https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/always-on-availability-groups-sql-server?view=sql-server-ver15) is a good place to start.
Licensing and Maintenance: Ensure that your licensing agreements and maintenance plans support such a configuration. Running different versions might have implications for your support agreements with Microsoft.

Here is a high-level example of setting up such a configuration:

Install SQL Server 2017 on the primary server and configure it as the primary replica.
Install SQL Server 2022 on the secondary server and configure it as the secondary replica.
Create the availability group on SQL Server 2017 and add the database(s) to the group.
Add the SQL Server 2022 instance as a secondary replica to the availability group.
Configure synchronization and verify that the secondary replica is properly synchronized with the primary replica.

Example Steps for Adding SQL Server 2022 as a Secondary Replica

On the Primary Server (SQL Server 2017):

ALTER AVAILABILITY GROUP [YourAGName] ADD REPLICA ON 'SQL2022ServerName' WITH (ENDPOINT_URL = 'TCP://SQL2022ServerName:5022', AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL);

On the Secondary Server (SQL Server 2022):

ALTER DATABASE [YourDatabaseName] SET HADR AVAILABILITY GROUP = YourAGName;

Join the Secondary Replica to the Availability Group:

ALTER AVAILABILITY GROUP [YourAGName] JOIN;

Start Data Synchronization:

ALTER DATABASE [YourDatabaseName] SET HADR RESUME;

Always test such configurations in a non-production environment to ensure that everything works as expected and to understand any potential issues or performance implications.

https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/upgrading-always-on-availability-group-replica-instances?view=sql-server-ver16

DB Mirroring :

Using database mirroring to migrate a SQL Server database from SQL Server 2017 to SQL Server 2022 is a viable strategy. Database mirroring provides a way to maintain a synchronized copy of your database on the new server. Here are the detailed steps to perform the migration:

Step-by-Step Migration Process

Prepare the Environment

Install SQL Server 2022: Set up SQL Server 2022 on your new server where you want to migrate your database.
Network Configuration: Ensure that the network between the old (SQL Server 2017) and the new server (SQL Server 2022) is properly configured and both servers can communicate with each other.

Backup the Primary Database

Full Backup:

BACKUP DATABASE YourDatabaseName TO DISK = 'C:\Backup\YourDatabaseName_Full.bak';

Transaction Log Backup:

BACKUP LOG YourDatabaseName TO DISK = 'C:\Backup\YourDatabaseName_Log.trn';

Restore the Backup on the New Server

Copy the backup files to the new server.
Restore the Full Backup:

RESTORE DATABASE YourDatabaseName FROM DISK = 'C:\Backup\YourDatabaseName_Full.bak' WITH NORECOVERY;

Restore the Transaction Log Backup:

RESTORE LOG YourDatabaseName FROM DISK = 'C:\Backup\YourDatabaseName_Log.trn' WITH NORECOVERY;

Configure Database Mirroring

On the Principal (SQL Server 2017):

ALTER DATABASE YourDatabaseName SET PARTNER = 'TCP://SQL2022ServerName:5022';

On the Mirror (SQL Server 2022):

ALTER DATABASE YourDatabaseName SET PARTNER = 'TCP://SQL2017ServerName:5022';

Security Configuration: Ensure that the database mirroring endpoints are configured correctly and that the service accounts have the necessary permissions.

Monitor the Synchronization

Verify that the mirroring session is established and that the databases are synchronizing. You can use the following query to check the status:

SELECT db_name(database_id) AS DatabaseName, mirroring_state_desc, mirroring_role_desc, mirroring_partner_name, mirroring_partner_instance FROM sys.database_mirroring;

Failover to the New Server (SQL Server 2022)

Once the database is fully synchronized, you can perform a manual failover to make the SQL Server 2022 instance the principal server.

ALTER DATABASE YourDatabaseName SET PARTNER FAILOVER;

Update Applications and Services

Point your applications and services to the new SQL Server 2022 instance.
Update connection strings to reflect the new server name or IP address.

Remove Mirroring (Optional)

If you no longer need mirroring after the migration, you can remove the mirroring configuration.

-- On the Principal (now SQL Server 2022) ALTER DATABASE YourDatabaseName SET PARTNER OFF; -- On the Mirror (SQL Server 2017) ALTER DATABASE YourDatabaseName SET PARTNER OFF;

Considerations and Best Practices

Compatibility: Ensure that your database and applications are compatible with SQL Server 2022. Test in a non-production environment before migrating.
Downtime: Plan for a maintenance window, as there might be a brief downtime during the failover.
Backup and Restore: Always have a recent backup before starting the migration.

Using database mirroring can provide a smooth transition with minimal downtime, ensuring that your data remains consistent throughout the migration process.

https://learn.microsoft.com/en-us/sql/database-engine/database-mirroring/database-mirroring-sql-server?view=sql-server-ver16

Log Shippig :

Using log shipping to upgrade from SQL Server 2017 to SQL Server 2022 involves setting up log shipping between the two servers, synchronizing them, and then performing a cutover. Here are the steps to perform the migration using log shipping:

Step-by-Step Migration Process

Prepare the Environment

Install SQL Server 2022: Set up SQL Server 2022 on your new server.
Network Configuration: Ensure that the network between the old (SQL Server 2017) and the new server (SQL Server 2022) is properly configured and both servers can communicate with each other.

Configure Log Shipping

Full Backup of the Primary Database (SQL Server 2017):

BACKUP DATABASE YourDatabaseName TO DISK = 'C:\Backup\YourDatabaseName_Full.bak';

Copy the Full Backup to the new server (SQL Server 2022).
Restore the Full Backup on the New Server (SQL Server 2022):

RESTORE DATABASE YourDatabaseName FROM DISK = 'C:\Backup\YourDatabaseName_Full.bak' WITH NORECOVERY;

Enable Log Shipping on the Primary Database (SQL Server 2017):

Right-click the database in SQL Server Management Studio (SSMS), go to "Properties," and select "Transaction Log Shipping."
Check "Enable this as a primary database in a log shipping configuration."
Configure the backup settings (backup path, schedule).

Configure the Secondary Server (SQL Server 2022):

Specify the new server as the secondary.
Set the path to the backup folder where transaction log backups will be copied.
Configure the restore settings to restore the logs with the `STANDBY` option (so the database can be read-only for verification purposes) or `NORECOVERY` (if you want to keep it in a restoring state).

Start Log Shipping

Initial Transaction Log Backup:

BACKUP LOG YourDatabaseName TO DISK = 'C:\Backup\YourDatabaseName_Log1.trn';

Copy and Restore the Log Backup on SQL Server 2022:

RESTORE LOG YourDatabaseName FROM DISK = 'C:\Backup\YourDatabaseName_Log1.trn' WITH NORECOVERY;

Schedule Regular Log Backups: Configure SQL Server Agent jobs to take regular transaction log backups on the primary and copy them to the secondary server.

Monitor Log Shipping

- Monitor the log shipping status using the Log Shipping Monitor or SQL Server Management Studio to ensure that the transaction logs are being backed up, copied, and restored correctly.

Perform the Cutover to SQL Server 2022

Stop All Transactions on the Primary Database (SQL Server 2017): Ensure no new transactions are occurring on the primary database.

ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Take a Final Log Backup on the Primary (SQL Server 2017):

BACKUP LOG YourDatabaseName TO DISK = 'C:\Backup\YourDatabaseName_LogFinal.trn';

Copy and Restore the Final Log Backup on the Secondary (SQL Server 2022):

RESTORE LOG YourDatabaseName FROM DISK = 'C:\Backup\YourDatabaseName_LogFinal.trn' WITH RECOVERY;

Bring the New Database Online:

ALTER DATABASE YourDatabaseName SET MULTI_USER;

Update Applications and Services

Point your applications and services to the new SQL Server 2022 instance.
Update connection strings to reflect the new server name or IP address.

Considerations and Best Practices

Testing: Thoroughly test the log shipping configuration and the final cutover process in a non-production environment before doing it in production.
Downtime: Plan for a maintenance window during the cutover process as there will be some downtime.
Backup and Restore: Always have recent backups before starting the migration.
Log Shipping Monitor: Use the Log Shipping Monitor to ensure that all log shipping processes are running smoothly.

Log shipping for the upgrade provides a way to minimize downtime and ensure data consistency throughout the migration process.

https://learn.microsoft.com/en-us/sql/database-engine/log-shipping/configure-log-shipping-sql-server?view=sql-server-ver16

Action	Description
sql_text	The exact T-SQL command that was run.
client_app_name	The name of the application (like SSMS or a .NET app) that sent the request.
client_hostname	The computer name where the request came from.
client_pid	The process ID of the client application.
username	The SQL Server login or user who ran the query.
session_id	The session ID (SPID) on SQL Server.

アクション	説明
sql_text	実行された T-SQL コマンドの内容
client_app_name	リクエスト元のアプリケーション名
client_hostname	リクエスト元のコンピューター名
client_pid	クライアントアプリケーションのプロセス ID
username	実行ユーザーの SQL Server ログイン名
session_id	SQL Server 上のセッション ID（SPID）

SQL Server Support Blog articles

Caution When Using a Database in Single User Mode

Troubleshooting SQL Server Backup Failures to Azure Blob Storage

1. Backup Failures Due to Page Blob Size Limit (1 TB):-

Recommended Resolution :-

2. Block Blob Limit Reached During Backup :-

Required SAS Permissions :

4. Operating System Error 50 – “The Request is Not Supported”:-

Troubleshooting Replication Performance Issues

SQL Server Database Corruption: Causes, Detection, and some details behind DBCC CHECKDB

SQL Server Database Corruption: Causes, Detection, and some details behind DBCC CHECKDB

Why ACID Matters in Corruption Scenarios

Common Causes of SQL Server Database Corruption

How DBCC CHECKDB Works Under the Hood

Performance Tuning Tips for DBCC CHECKDB

Sample Error Messages and Interpretation

Prevention and Recovery Best Practices

Key Takeaways

Critical Note on Repair Options

How to Capture Running Queries in SQL Server

🔍 What is Extended Events (xEvents)?

🛠 Step-by-Step Instructions

❓ FAQ

📘 SQL Server で実行中のクエリをキャプチャする方法

🛠 拡張イベント ログの収集方法

✅ ステップ1：拡張イベント セッションを作成する対象のインスタンス上で、下記クエリを sysadmin のような拡張イベントセッションの作成に必要な権限を有するアカウントにて実行します。

✅ ステップ2：拡張イベントの開始以下のクエリを実行することで、拡張イベントの収集を開始できます。

🔍 結果（収集データ）の確認方法

✅ ステップ3：拡張イベントの停止以下のクエリを実行することで、拡張イベントの収集を停止できます。

🗂 保存先について：

SQL Server IaaS Agent 拡張機能の概要 (What is the Windows SQL Server IaaS Agent extension?)

SQL Server IaaS Agent 拡張機能とは

拡張機能に登録する利点

代表的な機能

FAQ

What is the SQL Server IaaS Agent extension?

Benefits of Registering the extension

Key Features

FAQ

更新プログラム適用後、bcp.exe の出力メッセージが英語になる

事象

対処策

Issue

Resolution

How to artificially create a non-yielding scheduler scenario using WinDbg

Windows Server 2025 にインストールしたSQL Server の更新プログラムを削除する方法

You may see high CPU usage after enabling the Log Shipping with Always ON on multiple databases.

Symptom

Cause

Resolution

SQL Server の更新プログラム GDR, CU, Service Pack の違いと適用可否

GDR、CU、Service Pack の違い

GDR (General Distribution Release)

CU (Cumulative Update)

Service Pack

GDR、 CU、 Service Pack の適用可否

Kerberos Authentication flow

About SQL Server Error 13542

SQL Server がインストールされていないAzure VM において、「Failed to find SQL instance to target」を含むメッセージが記録される

101 of Troubleshooting SQL Server on Linux

SQL Server Error logs:

Comparing Error and System Logs Output

Task Manager:

Effectively troubleshoot latency in SQL Server Transactional replication: Part 2

Step 4.1. Troubleshoot latency in Log Reader agent’s reader thread

Step 4.2. Troubleshoot latency in Log Reader agent’s writer thread

Step 4.3. Troubleshoot latency in Distribution agent’s reader thread

Step 4.4. Troubleshoot latency in Distribution agent’s writer thread

Effectively troubleshoot latency in SQL Server Transactional replication: Part 1

High level transactional replication architecture

Figure 1. Transactional Replication architecture that shows the locations of each thread and agent in the case of Remote distributor and pull subscription case

Troubleshooting steps

Step 1. Get information about “Big Picture”

Step 2. Get tracer tokens

Step 3.1. Troubleshoot latency in Log Reader agent

Step 3.2. Troubleshoot latency in Distribution agent

SQL Server 2022 分散型可用性グループにおける同期失敗

本事象についてはSQL Server 2022 の動作上の制限になります。

How to perform unattended uninstall for SQL Server Express

Migration sql server from sql 2017 to sql 2022 with Minimal Down time

🛠 拡張イベントログの収集方法

✅ ステップ1：拡張イベントセッションを作成する
対象のインスタンス上で、下記クエリを sysadmin のような拡張イベントセッションの作成に必要な権限を有するアカウントにて実行します。

✅ ステップ2：拡張イベントの開始
以下のクエリを実行することで、拡張イベントの収集を開始できます。

✅ ステップ3：拡張イベントの停止
以下のクエリを実行することで、拡張イベントの収集を停止できます。