Skip to content

Instantly share code, notes, and snippets.

In SQL 2016, there is a feature to allow effective Backup Compression for databases protected by TDE.
There is however a caution that this feature should not be used when the database has VLFs which are
larger than 4GB. This short script can help identify if any of your databases would be affected.
DECLARE @dbname SYSNAME = 'foo';
--TODO: Add switch to only show noncompliance
--TODO: Add switch to only evaluate databases with TDE active
stummsft / Set-SqlTlsCertificate.ps1
Created April 5, 2019 00:00
Programmatically set the TLS certificate to be used by a given SQL Server Instance
function Set-SqlTlsCertificate {
param (
[Alias("CN", "MachineName")]
[String[]]$ComputerName = "localhost",
* Creates an XEvent session utilizing the 'Trace' event added to SQL 2016
* This event will capture the protocol negotiation details of all TLS connections
* This allows you to capture how many connections are being made and with what
* protocol version and cipher.
* This information can be used to predict the impact of disabling a given
* protocol version or cipher suite before taking such actions.
CREATE OR ALTER PROCEDURE usp_get_tls_connection_stats
* This function will return all plan_handles that include a reference to
* a given object. This can help with targeted purges of execution plans.
CREATE OR ALTER FUNCTION find_query_plans_by_object (
@database SYSNAME,
@schema SYSNAME,
@table SYSNAME
stummsft / version_store_sentry.sql
Last active June 17, 2019 22:50
Identify sessions causing version store retention
* This script will identify which transactions are causing excessive growth of the version store.
* It will output a list of how much version store retention each transaction is responsible
* This retention is not "exclusive" to that single transaction and overlaps with others
* if there are multiple long-lived transactions.
--Meaningless magic numbers. Replace with thresholds meaningful to your system!
DECLARE @version_store_size_total_threshold BIGINT = 25 * POWER(2.0, 30); --GB
DECLARE @version_store_size_per_db_threshold BIGINT = 10 * POWER(2.0, 30); --GB
stummsft / long_running_snapshot_tran_info.sql
Last active March 5, 2019 16:16
Gather info about long running snapshot transactions
This query grabs relevant information about active long-running snapshot transactions.
This can help identify what is preventing cleanup of tempdb version store records.
This can also help identify what is preventing long truncation in an Availability Group topology.
DECLARE @old_transaction_cutoff_minutes INT = 60;
stummsft / get_server_tls_certificate.sql
Created February 15, 2019 22:32
Scrape the currently loaded SQL Server TLS certificate from the available error logs
CREATE PROCEDURE [dbo].[get_server_tls_certificate]
@maximum_results INT = 1
DECLARE @error_log_count INT = 0;
CREATE TABLE #error_log (
stummsft / azure-pricing-managedinstance-IO-estimate.sql
Created October 11, 2018 21:21
Estimate the price of moving a database to Azure SQL Managed Instance
This script is intended to help estimate the expected pricing for a database to be migrated to an Azure SQL Managed Instance.
This only captures the I/O activity directly associated with user queries. Background processes are different between
Managed Instances and SQL Server on-premises, so this number should not be treated as an exact prediction.
Most workloads have periodic ups and downs at least weekly, and sample periods shorter than one month
or especially one week are much more likely to be estimated from a highly-skewed sample period.
If you are using preview pricing, GA, or have any other factor affecting the pricing, update the parameter below.
stummsft / furthest-allocation.sql
Created October 1, 2018 22:36
Which table is blocking a shrinkfile operation
This script returns the further-allocated page per index per file in the database
This may be useful in reclaiming diskspace after a major data purge or migration operation
If a shrinkfile operation is not proceeding in a timely fashion or is being blocked,
this will help you target specific indexes to be rebuilt or migrated to a different filegroup
to help the space reclamation proceed.