SQL Server IaaS Agent 拡張機能の概要 (What is the Windows SQL Server IaaS Agent extension?)
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.
SQL Server がインストール済みのAzure VM をデプロイする際、サーバーの照合順序を変更すると、デプロイに失敗する場合があります
こんにちは。日本マイクロソフト SQL Server サポートチームです。 SQL Server がインストール済みのAzure VM をデプロイする際、サーバーの照合順序を変更することができます。 しかし、サーバーの照合順序を変更してデプロイした場合、デプロイに失敗することがあります。 今回は、デプロイに失敗する原因と対処について説明します。 また、デプロイする際にサーバーの照合順序を変更する必要がない場合があるため、デプロイする前にご確認いただきたいこととして、まとめました。 なお、このブログの内容は2022 年 11 月時点の情報となります。 本件については、既知の問題として認識しており対応を検討しておりますため、対応に進捗がある場合には、こちらも更新します。 ●対象の画面 SQL Server がインストール済みのAzure VM をデプロイする際、下図のように[SQL Server の設定] タブから[SQL インスタンスの設定の変更] をクリックすることで、サーバーの照合順序を変更することができます。 図．仮想マシンの作成(ご参考) ●サーバーの照合順序を変更してデプロイに失敗する原因 サーバーの照合順序を変更する際、SQL Server をシングル ユーザー モードで起動します。 その間、他のセッションからSQL Server へ接続することができず、SQL仮想マシン リソースの作成に失敗する場合があります。 ●サーバーの照合順序を変更してデプロイに失敗した際の対処策 タイミングイシューとなるため、デプロイに失敗したリソースを削除し、再度、新規に照合順序を指定してデプロイしてください。 なお、弊社としても、既知の問題として認識しており、修正を検討しております。 他方、修正には時間がかかることが予想されるため、ブログとして掲載しました。 ●デプロイする前にご確認いただきたいこと 日本語版のSQL Server を利用する予定の場合、SQL Server がインストール済みのAzure VM をデプロイする際にサーバーの照合順序を変更する必要がありません。 Azure 上で用意されているイメージを使用してSQL Server がインストール済みのAzure VM をデプロイした場合、OS ならびにSQL Server は英語の状態でデプロイされます。 そのため、Azure Portal に作成されるSQL 仮想マシンという名前のリソースを削除したうえで、Azure VM にインストールされている英語版のSQL Server をアンインストールし、日本語版のSQL Server をインストールする必要があります。 つきましては、サーバーの照合順序を変更せずにSQL Server がインストール済みのAzure VM をデプロイし、以下のブログに記載の方法で日本語版のSQL Server をインストールしてください。 ◆ SQL Server 2012 、2014 、2016 、2017 および2019 の日本語化手順は以下のブログをご確認ください。 Azure 仮想マシン上に作成した SQL Server の日本語化手順（SQL Server 2012 , 2014 対応手順） Azure 仮想マシン上に作成した SQL Server の日本語化手順（SQL Server 2016 対応手順 ） Azure 仮想マシン上に作成した SQL Server の日本語化手順（Windows OS 版 SQL Server 2017 対応手順） Azure 仮想マシン上に作成した SQL Server の日本語化手順（Windows OS 版 SQL Server 2019 対応手順） また、英語版のままで使用を継続される場合、SQL VM をデプロイ後に手動で、サーバー照合順序を変更することも可能ですので、併せてご検討ください。 サーバーの照合順序の設定または変更 https://learn.microsoft.com/ja-jp/sql/relational-databases/collations/set-or-change-the-server-collation?view=sql-server-ver16 以上です。SQL Server がインストールされていないAzure VM において、「Failed to find SQL instance to target」を含むメッセージが記録される
こんにちは。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にて記録されたものであれば、本メッセージによる影響はありません。 対処策 本メッセージはジョブの出力結果として記録されるため、出力を止めることはできません。 監視ツールなどで監視されている場合は、必要に応じて監視対象から除外してください。SQL Backup using Azure File Share
SQL Server limits the maximum backup size supported using a page blob to 1 TB. The maximum backup size supported using block blobs is limited to approximately 200 GB (50,000 blocks * 4 MB MAXTRANSFERSIZE). In order to get over the above limitation, we can opt for Azure File Share. SQL Backup is more than 12TiB, you can choose to backup using Azure File Share Standard file shares can span up to 100 TiB, however this feature is not enabled by default. If you need a file share that is larger than 5 TiB, you will need to enable the large file share feature for your storage account. Premium file shares can span up to 100 TiB without any special setting, however premium file shares are provisioned, rather than pay as you go like standard file shares. This means that provisioning file share much larger than what you need will increase the total cost of storage. In local and zone redundant storage accounts, Azure file shares can span up to 100 TiB, however in geo- and geo-zone redundant storage accounts, Azure file shares can span only up to 5 TiB. Prerequisites Storage Account Azure PowerShell Storage Module https://www.powershellgallery.com/packages/Az.Storage/3.7.0 Steps 1. Storage Account with Premium FileShare Enable Large File Share 2. Register for the SMB Multichannel preview with the following commands. Connect-AzAccount # Setting your active subscription to the one you want to register for the preview. # Replace the <subscription-id> placeholder with your subscription id. $context = Get-AzSubscription -SubscriptionId <your-subscription-id> Set-AzContext $context Register-AzProviderFeature -FeatureName AllowSMBMultichannel -ProviderNamespace Microsoft.Storage Register-AzResourceProvider -ProviderNamespace Microsoft.Storage You can also verify if the feature registration is complete Get-AzProviderFeature -FeatureName AllowSMBMultichannel -ProviderNamespace Microsoft.Storage 3. Enable SMB Multichannel Once you have created a File Storage account, you can follow the instructions to update SMB Multichannel settings for your storage account Note: If the SMB Multichannel option is not visible under File share settings or you get a failed to update setting error while updating the configuration, please make sure that your subscription is registered, and your account is in one of the supported regions with supported account type and replication. 4. Create a file share You can set max capacity up to 100TB 5. Connect to FileShare from Window either using Active Directory or Storage Account Connecting to a share using the storage account key is only appropriate for admin access. But mounting the Azure file share with the Active Directory identity of the user is preferred. $connectTestResult = Test-NetConnection -ComputerName testsmbfileshare.file.core.windows.net -Port 445 if ($connectTestResult.TcpTestSucceeded) { # Save the password so the drive will persist on reboot cmd.exe /C "cmdkey /add:`"testsmbfileshare.file.core.windows.net`" /user:`"localhost\testsmbfileshare`" /pass:`"SxbRsNuwc1*******/8lk1TyUkqC+2+************==`"" # Mount the drive New-PSDrive -Name Z -PSProvider FileSystem -Root "\\testsmbfileshare.file.core.windows.net\sqlbackup" -Persist } else { Write-Error -Message "Unable to reach the Azure storage account via port 445. Check to make sure your organization or ISP is not blocking port 445, or use Azure P2S VPN, Azure S2S VPN, or Express Route to tunnel SMB traffic over a different port." } Copy this script and run this in PowerShell to map this as a network drive locally. This script will check to see if this storage account is accessible via TCP port 445, which is the port SMB uses. If port 445 is available, your Azure file share will be persistently mounted. Note: The script will only work on Windows Server 2012 and above Once we had the above script is executed, we could see the Z drive as network Drive in My computer / This PC 6. On the SQL Server you need to first enable XP_cmdshell so we can configure backups to this file share. Enable the Advance SQL Configuration EXECUTE sp_configure 'show advanced options', 1; GO -- To update the currently configured value for advanced options. RECONFIGURE; GO -- To enable the feature. EXECUTE sp_configure 'xp_cmdshell', 1; GO -- To update the currently configured value for this feature. RECONFIGURE; GO We must mount the Z Drive in SQL server and opt for it to be available for backups. Therefore, we map it using the script below xp_cmdshell 'net use Z: \\testsmbfileshare.file.core.windows.net\sqlbackup /u:localhost\testsmbfileshare SxbRsNuwc1*******/8lk1TyUkqC+2+************==`' Get the storage account, username and access key from Step 5 7. Backup the database now to the file share subdirectory using the below command BACKUP DATABASE [AdventureWorks2019] TO DISK = 'Z:\AdventureWorks2019.bak' with stats = 5 Reference (optional) SMB file shares in Azure Files | Microsoft Docs Create an Azure file share - Azure Files | Microsoft Docs https://technet.microsoft.com/en-us/library/dn435916(v=sql.120).aspx#limitations
TSQL Scripts for Troubleshooting Common Issues with SQL Server
This blog provides TSQL samples for troubleshooting common SQL Server issues as listed below. You can modify the parameters (i.e. database name, table name, keyword name, duration, etc.) based on customized environment settings and requirements. (Test these scripts before implementing in PROD environment. Please be aware of all potential risks for implementing the script in your PROD environment.) 1. Performance Troubleshooting(blocking, high CPU, memory, idle, query execution) 2. Deadlock and Database Object ID mapping 3. HA (Clustered SQL Server) 4. Backup & Transaction Log Related Issue 5. Query Store (QDS) 6. Database Encryption (TDE) 7. Tool (Profiler trace and X-event) Performance Troubleshooting ========================= (blocking, high CPU, memory, idle, query execution) 1.List all active sessions and its queries that contain your target table name or specific TSQL structure (input your target key word) SELECT sqltext.TEXT, req.session_id, req.status, req.command, req.cpu_time,req.database_id, req.total_elapsed_time FROM sys.dm_exec_requests req CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext where sqltext.text like '%Your Target Key Word%' 2.List all sleeping user sessions that have been idle for over 15 minutes with detailed queries (You can customize the session’s status and idle time) SELECT CURRENT_TIMESTAMP as currenttime, datediff(minute,last_batch,GETDATE()) as 'idletime_in_minute' ,sp.status,sp.spid,sp.login_time,sp.program_name,sp.hostprocess,sp.loginame,text FROM sys.sysprocesses sp CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) AS QT where sp.status = 'sleeping' and datediff(minute,last_batch,GETDATE()) >15 and spid>50 3.List top 10 high CPU queries that currently running in this SQL instance SELECT s.session_id,r.status,r.blocking_session_id 'Blk by',r.wait_type,wait_resource,r.wait_time / (1000 * 60) 'Wait M',r.cpu_time,r.logical_reads,r.reads,r.writes,r.total_elapsed_time / (1000 * 60) 'Elaps M',Substring(st.TEXT,(r.statement_start_offset / 2) + 1, ((CASE r.statement_end_offset WHEN -1 THEN Datalength(st.TEXT) ELSE r.statement_end_offset END - r.statement_start_offset) / 2) + 1) AS statement_text, Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' + Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text,r.command,s.login_name, s.host_name,s.program_name,s.last_request_end_time,s.login_time, r.open_transaction_count FROM sys.dm_exec_sessions AS s JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st WHERE r.session_id != @@SPID ORDER BY r.cpu_time desc 4. List top 10 high memory usage queries that currently running in this SQL instance SELECT mg.session_id,mg.granted_memory_kb,mg.requested_memory_kb,mg.ideal_memory_kb,mg.request_time,mg.grant_time,mg.query_cost,mg.dop,st.[TEXT],qp.query_plan FROM sys.dm_exec_query_memory_grants AS mg CROSS APPLY sys.dm_exec_sql_text(mg.plan_handle) AS st CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp ORDER BY mg.required_memory_kb DESC 5. List detailed memory usage for each memory clerk DBCC MEMORYSTATUS 6. List memory usage for plan cache and its maximum size based on current setting. By removing the WHERE condition, you will get a full list for all memory cache clerks’ information select name, type, buckets_count from sys.dm_os_memory_cache_hash_tables where name IN ( 'SQL Plans' , 'Object Plans' , 'Bound Trees' ,'Extended Stored Procedures') select name, type, pages_kb, entries_count from sys.dm_os_memory_cache_counters where name IN ( 'SQL Plans' , 'Object Plans' , 'Bound Trees' ,'Extended Stored Procedures') 7. List progressive (on-going) execution plan for a specific session (Starting with SQL 2016) For SQL 2016 and 2017, please first run below TSQL in the query session of which the execution plan you wish to extract later set statistics profile on Run below query to extract the on-going execution plan for your target session (input the SPID in the bracket) SELECT * FROM sys.dm_exec_query_statistics_xml(59); 8.List all block header queries that currently detected in this SQL instance declare @blocker varchar(100),@sql varchar(100) print convert(varchar(20), getdate(),120) select distinct blocked into #blocker from sysprocesses where blocked <> 0 DECLARE blocker CURSOR FOR select spid from sysprocesses where spid in (select * from #blocker) and blocked = 0 OPEN blocker FETCH NEXT FROM blocker INTO @blocker WHILE @@FETCH_STATUS = 0 BEGIN set @sql='DBCC inputbuffer(' + @blocker + ')' execute (@sql) set @sql= 'select * from sysprocesses where spid=' + @blocker execute (@sql) FETCH NEXT FROM blocker INTO @blocker END CLOSE blocker DEALLOCATE blocker drop table #blocker 9.Kill all sleeping sessions that has been idle over 1 hour (You can customize the idle duration) DECLARE user_spid INT DECLARE CurSPID CURSOR FAST_FORWARD FOR SELECT SPID FROM master.dbo.sysprocesses (NOLOCK) WHERE spid>50 AND status='sleeping' -- only sleeping threads AND DATEDIFF(HOUR,last_batch,GETDATE())>=1 -- thread sleeping for 1 hours AND spid<>@@spid -- ignore current spid OPEN CurSPID FETCH NEXT FROM CurSPID INTO user_spid WHILE (@@FETCH_STATUS=0) BEGIN PRINT 'Killing '+CONVERT(VARCHAR,@user_spid) EXEC('KILL '+@user_spid) FETCH NEXT FROM CurSPID INTO user_spid END CLOSE CurSPID DEALLOCATE CurSPID GO 10.Kill all block headers. This script will continuously scan every 5 seconds for all block header sessions and kill all block headers Use master go while 1=1 Begin declare @blocker varchar(100),@sql varchar(100) print convert(varchar(20), getdate(),120) select distinct blocked into #blocker from sysprocesses where blocked <> 0 DECLARE blocker CURSOR FOR select spid from sysprocesses where spid in (select * from #blocker) and status='sleeping' OPEN blocker FETCH NEXT FROM blocker INTO @blocker WHILE @@FETCH_STATUS = 0 BEGIN set @sql='DBCC inputbuffer(' + @blocker + ')' execute (@sql) set @sql= 'kill ' + @blocker execute (@sql) FETCH NEXT FROM blocker INTO @blocker END CLOSE blocker DEALLOCATE blocker drop table #blocker waitfor delay '0:0:05' End Deadlock and Database Object ID mapping =================================== Below are 3 examples to map the issued object based on the ID to its source database, index or schema. 1. Key type wait resource waitresource=KEY: 6:12345678990 (987654321a9b) database_id = 6 hobt_id = 12345678990 hash value = (987654321a9b) We can use below TSQL to check the database name based on DATABASE ID select db_name(6) Use below TSQL to check the schema, object, and index details related to this key USE DatabaseName GO SELECT sc.name as schema_name, so.name as object_name, si.name as index_name FROM sys.partitions AS p JOIN sys.objects as so on p.object_id=so.object_id JOIN sys.indexes as si on p.index_id=si.index_id and p.object_id=si.object_id JOIN sys.schemas AS sc on so.schema_id=sc.schema_id WHERE hobt_id = 12345678990 2. Object Wait resource type waitresource=OBJECT: 6:1234567890:4 database_id = 6 Object ID = 1234567890 We can use below TSQL to check the specific object name based on the waitresource details select OBJECT_NAME(1234567890,6) 3. Page Wait Resource Type waitresource=“PAGE: 6:3:70133 ” = Database_Id : FileId : PageNumber database_id=6 data_file_id = 3 page_number = 70133 HA (Clustered SQL Server) =================== List the latest error encountered by local AG replica for connection timeout select r.replica_server_name, r.endpoint_url, rs.connected_state_desc, rs.last_connect_error_description, rs.last_connect_error_number, rs.last_connect_error_timestamp from sys.dm_hadr_availability_replica_states rs join sys.availability_replicas r on rs.replica_id=r.replica_id where rs.is_local=1 List the current redo rate, redo queue size, log send rate, log send queue size for current AG replica SELECT CURRENT_TIMESTAMP as currenttime,drs.last_commit_time,ar.replica_server_name, adc.database_name, ag.name AS ag_name, drs.is_local, drs.is_primary_replica, drs.log_send_queue_size, drs.log_send_rate, drs.redo_queue_size, drs.redo_rate FROM sys.dm_hadr_database_replica_states AS drs INNER JOIN sys.availability_databases_cluster AS adc ON drs.group_id = adc.group_id AND drs.group_database_id = adc.group_database_id INNER JOIN sys.availability_groups AS ag ON ag.group_id = drs.group_id INNER JOIN sys.availability_replicas AS ar ON drs.group_id = ar.group_id AND drs.replica_id = ar.replica_id Restart endpoint Check SQL AG endpoint name SELECT * FROM sys.endpoints Restart AG endpoint on this local replica ALTER ENDPOINT <Your AG Endpoint Name> STATE=STOPPED ALTER ENDPOINT <Your AG Endpoint Name> STATE=STARTED Backup & Transaction Log Related Issue ================================ List all backup history and backup file details for the past 7 days (You can modify the date) SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date, msdb.dbo.backupset.expiration_date, CASE msdb..backupset.type WHEN 'D' THEN 'Database' WHEN 'L' THEN 'Log' END AS backup_type, msdb.dbo.backupset.backup_size, msdb.dbo.backupmediafamily.logical_device_name, msdb.dbo.backupmediafamily.physical_device_name, msdb.dbo.backupset.name AS backupset_name, msdb.dbo.backupset.description FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) ORDER BY msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_finish_date List all databases’ data and log file path and logical name USE master; SELECT name 'Logical Name', physical_name 'File Location' FROM sys.master_files; List each database’ transaction log size usage and space DBCC SQLPERF(LOGSPACE) List VLF size and counts for all databases as well as detailed size for your target database select * from sys.dm_db_log_info(5); /*input here your DB ID to get detailed size for existing VLFs on this database*/ SELECT [name], s.database_id, COUNT(l.database_id) AS 'VLF Count', SUM(vlf_size_mb) AS 'VLF Size (MB)', SUM(CAST(vlf_active AS INT)) AS 'Active VLF', SUM(vlf_active*vlf_size_mb) AS 'Active VLF Size (MB)', COUNT(l.database_id)-SUM(CAST(vlf_active AS INT)) AS 'In-active VLF', SUM(vlf_size_mb)-SUM(vlf_active*vlf_size_mb) AS 'In-active VLF Size (MB)' FROM sys.databases s CROSS APPLY sys.dm_db_log_info(s.database_id) l GROUP BY [name], s.database_id ORDER BY 'VLF Count' DESC GO For the purpose of shrinking transaction log file, check the log_reuse_wait_desc type for all database select name,database_id,log_reuse_wait, log_reuse_wait_desc from sys.databases Please refer this link for how to fix each of the log_reuse_wait_desc type when it is not “NOTHING” https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql?view=sql-server-ver15 Database Corruption Issue Force repair of database with REPAIR_ALLOW_DATA_LOSS option (data loss expected and sometimes can cause more damage. Please refrain from using this method unless as for a last resort. More details for fixing database corruption, please refer this link https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql?view=sql-server-ver15) Alter database YourDBName set single_user with rollback immediate; DBCC CHECKDB('YourDBName', REPAIR_ALLOW_DATA_LOSS); Alter database YourDBName set multi_user with rollback immediate; Query Store (QDS) ================= 1.List a database’s query store status select * from sys.database_query_store_options 2. List all databases that have query store configured select name as 'DATABASE NAME', CASE is_query_store_on when 1 then 'ENABLED' else 'OTHER' END AS 'QUERY STORE STATE' from sys.databases where is_query_store_on = 1 order by 1 ; Database Encryption (TDE) ====================== Check if the DMK exist in master database USE master GO SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##' Check if the certificate is created in master database. A certificate is equivalent to an asymmetric key USE master GO select * from sys.certificates Check if the database is encrypted/progress (encryption_state = 3 encrypted; =2 in progress) USE master GO SELECT db_name(database_id) [TDE Encrypted DB Name], c.name as CertName, encryptor_thumbprint , dek.* FROM sys.dm_database_encryption_keys dek INNER JOIN sys.certificates c on dek.encryptor_thumbprint = c.thumbprint Check if the DMK in the master database is now encrypted by the SMK (is_master_key_encrypted_by_server = 1) select is_master_key_encrypted_by_server, * from sys.databases where database_id = db_id(N'master') Tool (Profiler trace and X-event) ======================= Import SQL Profiler trace into SQL Server database tables USE DatabaseName GO select * into Sample from fn_trace_gettable('c:\trace\YourFolderToStoreTheTrace\YourTraceFile.trc',default) where eventclass in (10, 12) For event class ID, check the list in this link https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-trace-setevent-transact-sql?view=sql-server-ver15 Use XELoader to process X-event and import the data into SQL database table for data analysis purpose. XELoader can be downloaded from this opensource link https://github.com/suresh-kandoth/XELoader Use CMD script to load the X-event files into SQL Server database Sample script: C:\XELoader\6.2>XELoader.exe -D"C:\Users\yixin\Desktop\xeloader\xevent" -SYourServerName\InstanceName -dYourDBName Use TSQL query to check aggregated data result Sample TSQL: SELECT sum(c_duration) as SUM_DURAION,sum(c_signal_duration) as SUM_SIGNAL_DURATION,c_wait_type from [xel].[wait_info] group by c_wait_type order by SUM_DURAION desc DISCLAIMER : THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.GMSA is used for SQL service and it fails to start
GMSA is used for SQL service and its failing to start with error “the request failed, or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details”. In this post we would like to explain one of the interesting issues that we encountered where SQL Server uses a gMSA as its service account. Troubleshooting: When we tried to start SQL server using GMSA account, we found the SQL Server could not start due to timeout. One reason could be that the service account is not properly set or could not be authenticated with domain controllers. When we checked Windows Services applet (Services.msc) we found that it was in “Starting” state. SQL Server Services running under the context of a gMSA service account, gMSA service relies on KDS service, however the "Microsoft Key Distribution Service" service is not started on the domain controller. Here is an actual explanation about how GMSA account needs Microsoft Key Distribution Service is . During startup, Windows enumerates all automatic services and tries to start them. When Windows tries to start a service that is configured to use a group Managed Service Account (gMSA), the Service Control Manager (SCM) tries to log on by using the account information for the service. The logon request is sent to the Local Security Authority process (lsass.exe, LSASS) that is running on the computer. LSASS receives the request. While handling the request, LSASS tries to do a Lightweight Directory Access Protocol (LDAP) search for the msDS-ManagedPassword attribute. When the LDAP request is performed on a domain controller, the LDAP query can be sent back to the local server, where it is handled by a different thread in LSASS, which is the same process that issued the query. The LDAP server thread calls in to the Microsoft Key Distribution Service Provider (kdscli.dll), where it tries to find server components: Microsoft Key Distribution Service (KdsSvc), RPC endpoint from the RPC endpoint mapper (EPM). Because the KdsSvc service is set to be triggered as soon as one of these RPC queries occurs, the service should start (in theory). However, because the SCM is currently blocked from starting a service and it can only start one service at a time, KdsSvc never gets started, and SCM hangs. From domain controller side, we observed the issue of starting the KDS service. When manually starting KDS service, we can see following error: C:\>net start kdssvc The Microsoft Key Distribution Service service is starting. The Microsoft Key Distribution Service service could not be started. A system error has occurred. System error 1064 has occurred. An exception occurred in the service when handling the control request Also, we observed that Domain Controller is in Computer Container instead of Domain Controllers OU. Cause: This issue occurs because KDS assumes that the Domain Controllers are in the Domain Controllers OU instead of other OUs or Computer Container. We moved the Domain Controller (DC) back to Domain Controllers OU, then started the KDS service. C:\>net start kdssvc The Microsoft Key Distribution Service service is starting. The Microsoft Key Distribution Service service was started successfully. SQL service is now able to start with both service accounts. Issue has been resolved. Please refer: https://support.microsoft.com/en-za/help/4294429/service-using-gmsa-account-doesn-t-start-on-windows-server-2012-r2-dc Resolution: Move Domain Controller (DC) back to a Domain Controller OU and start KDS service. Recommendation: From AD perspective, we always recommend not to move DCs out of domain controllers OU, because default Domain Controller has many different user rights assigned. If you move it to other OUs or Container this may cause unexpected errors. When the service is up and running, we can move the DC out to different OUs, and this won’t cause the issue. However, later when machine is rebooted, this service will not start correctly due to the described behavior. Please refer: https://support.microsoft.com/en-us/help/3094486/kds-doesn-t-start-or-kds-root-key-isn-t-created-in-windows-server-2012 Failback Option: The failback option for the SQL Server service is to use the NT Service\MSSQLSERVER account. Author: Saniya Samreen – ARR Support Engineer, SQL Server on Azure VM Microsoft Reviewer: Joseph Pilov – Escalation Engineer, SQL Server, Microsoft
