SQL Server
Articles in the SQL Server category (191).
Articles in the SQL Server category (191).
- AG: availability group status queries — T-SQL queries listing availability groups, their primary and secondary replicas, their databases and settings, and a condensed availability group dashboard.
- AG: check that the instance is the primary replica — T-SQL block that prints PRIMARY NODE on the primary replica of an availability group and raises an error elsewhere, usable as a guard.
- AG: database not synchronizing (suspend and resume) — SUSPEND/RESUME HADR statements to restart data movement for a database in an availability group, and removing a database with SET HADR OFF.
- AG: failover cluster timeout settings — WSFC network thresholds, availability group lease, session and health check timeouts, with the PowerShell and T-SQL commands to read and set them.
- AG: read-only routing list query — T-SQL query listing, per replica, the read-only replicas, their routing URL and priority in an availability group.
- Always On: changing the HADR endpoint and replica ENDPOINT_URL — Recreate the Hadr_endpoint and update the ENDPOINT_URL of the replicas of a SQL Server availability group.
- Always On: cluster timeouts, lease/health check and node quarantine — Relax WSFC heartbeat settings and availability group timeouts, and handle a quarantined node.
- Analyse SQL Server wait statistics — Query that ranks SQL Server wait types from sys.dm_os_wait_stats, filtering benign waits, and how to reset the counters.
- Audit database permissions per user — List the permissions granted directly or through roles to SQL and Windows users in a database, and those of the public role.
- Audit logins, logouts and failed logins with Extended Events — Extended Events sessions that record SQL Server logins, logouts and failed logins to .xel files.
- Availability group counters, events and health-check queries — Replica counters, AlwaysOn_health events and health queries for WSFC, groups, replicas, databases and listeners.
- Availability group role of each database and sync state — Show for each database whether it is replicated (primary or secondary) and list the synchronization state of the local replica's databases.
- Avamar: SQL Server availability group databases not visible — Enable show_db_in_availability_group in avsql.cmd to list availability group databases.
- Avoid scientific notation when converting numbers to text — Convert numbers to text without scientific notation in T-SQL, using a DECIMAL cast or the STR function.
- Azure SQL Database: database copy and the General Purpose to Hyperscale limit — Use CREATE DATABASE AS COPY OF, monitor the copy, and understand error 45557 when targeting Hyperscale.
- Azure SQL Database: logins, Entra ID users and roles — Create logins, Microsoft Entra ID users and roles in Azure SQL Database, then audit permissions.
- Azure SQL: connecting with Invoke-Sqlcmd via managed identity or service principal — Examples of connecting to Azure SQL Database with Invoke-Sqlcmd and sqlcmd, using managed identity, service principal or interactive Azure AD.
- Azure SQL: database and elastic pool size queries (T-SQL) — T-SQL queries to list databases per elastic pool and measure used, allocated and free storage.
- Azure SQL: elastic pool databases and storage used — T-SQL queries listing databases per elastic pool and tracking their size with sys.resource_stats.
- Backup fails with error 59 on a network disk: raise SessTimeout — Fix SQL Server backup errors 3202 and 3013 (operating system error 59 on FlushFileBuffers) by raising the LanmanWorkstation SessTimeout registry value.
- Capture SQL Server errors with an Extended Events session — Create an Extended Events session that records SQL Server errors above severity 10 to an event file, and purge old .xel files.
- Change the default data and log paths of SQL Server — Change the default folders for new data and log files of a SQL Server instance with xp_instance_regwrite.
- Check database file sizes, free space and autogrowth — T-SQL query that shows, for each data and log file of the current database, the size, the used and free space, and the autogrowth setting.
- Check database, data file and transaction log sizes — T-SQL queries for the size of each database, its data and log files, the free space in each file, the log space usage and the last full and log backups.
- Check the isolation level of a session — T-SQL query that returns the isolation level of the current session from sys.dm_exec_sessions.
- Check the latest full and log backups — msdb queries to show the last full and log backup per database, and the recent backup history.
- Check the Lock Pages in Memory privilege — Check from T-SQL whether the SQL Server service account holds the SeLockMemoryPrivilege right, using xp_cmdshell.
- Checklist: SQL Server Always On deployment on VMware — Prerequisites to validate before deploying an Always On availability group on a VMware failover cluster: AD, network, accounts, storage and instance.
- Clear SQL Server caches with DBCC commands — DBCC commands to clear the plan cache, system caches, clean buffers and authentication caches, with their use cases.
- Clone a SQL Server login with its permissions — Generate a script that clones a login, its roles, users and database permissions using five stored procedures.
- Convert a UK-style date string to DATETIME — T-SQL scalar function that converts a dd/mm/yy date string into a DATETIME value, with day/month swap and two-digit year handling.
- Copy an Azure SQL Database with wait and retry — T-SQL script that starts CREATE DATABASE AS COPY OF and tracks the copy through sys.dm_operation_status with timeout and retries.
- Create, drop and revert a database snapshot — T-SQL commands to create a database snapshot, drop it, and revert a database from it, with the cautions for each step.
- Data warehouse design principles on SQL Server — Star schema, surrogate keys, columnstore indexes, compression, SSIS practices, CDC and temporal tables.
- Database inventory: status, files, sizes, last backup and options — A T-SQL query listing, for each database, its state, files and sizes, recovery model, compatibility level, last backup and options.
- Database scoped configuration: MAXDOP, cardinality and sniffing — ALTER DATABASE SCOPED CONFIGURATION settings (MAXDOP, cardinality estimator, parameter sniffing, query optimizer fixes) with their default values for SQL Server 2014 to 2019.
- Detect the number of VLFs in a transaction log — Query that counts the virtual log files (VLFs) and active VLFs of each database, to spot an excessive VLF count.
- Diagnose failed connections to Azure SQL and SQL Server — Two queries to list failed connections: sys.event_log on Azure SQL Database and the connectivity ring buffer.
- Diagnose latch and spinlock contention — Query wait statistics and spinlock statistics to find latch and spinlock contention in SQL Server.
- Diagnose THREADPOOL waits and connection errors with ring buffers — Use sys.dm_os_sys_info, THREADPOOL waits and the connectivity and exception ring buffers to diagnose worker thread exhaustion and login failures in SQL Server.
- Disable constraints and delete all data from a database — Disable foreign keys and triggers with sp_MSforeachtable, empty the tables, then re-enable everything.
- Disconnect all users and set a database to single-user — ALTER DATABASE statement that disconnects all other users at once by setting the database to SINGLE_USER with ROLLBACK IMMEDIATE.
- Dynamic SQL in T-SQL with sp_executesql — Dynamic SQL patterns: parameters, output values, procedure calls, cursors and WITH RESULT SETS.
- Entra ID group membership and SQL permissions audit queries — List a user's groups and group members with PowerShell, then audit database users, roles and permissions in SQL.
- Error 1807: CREATE DATABASE fails on an exclusive lock on model — Find and end the session that holds the exclusive lock on the model database when CREATE DATABASE fails with error 1807.
- Find a table or object across all databases in SQL Server — Search for a table or object name in every database of a SQL Server instance using sp_MSforeachdb.
- Find and fix orphaned SQL Server database users — Queries to find orphaned database users, users mapped to an expired certificate, and commands to map them to a login.
- Find blocking sessions on Azure SQL — Find sessions, running requests and blocking on Azure SQL with DMVs, and use sp_WhoIsActive.
- Find inactive nodes in a SQL Server availability group cluster — PowerShell script listing failover cluster nodes that own no availability group.
- Find LOB and binary columns in a SQL Server database — List the user tables that contain text, ntext, image, varbinary or binary columns, using the system catalog views.
- Find modules that reference an object and databases that hold a table — T-SQL queries to find procedures, views and functions that reference an object, and the databases that hold a table.
- Find the history of AlwaysOn failovers — Read the failover cluster event log, the current primary replica and the SQL Server error log to rebuild the failover history of an availability group.
- Find the oldest query plans in the cache — List the ten oldest query plans still in the cache with their execution statistics.
- Fix the CImageHelper symbol file load error — Fix the SQL Server error CImageHelper::Init() Failed Load of symbol file by giving the full Binn path in the sqlctr.ini and sqlagtctr.ini files.
- Generate KILL statements for the sessions of one database — Build KILL statements for every session connected to a SQL Server database, review them, run them and check the sessions still connected.
- Generating a date table with a recursive CTE in SQL Server — T-SQL queries that generate one row per day, month or week for a time dimension.
- Get the SQL Server instance start time — Three T-SQL queries to get the last start time of a SQL Server instance, directly or through indirect indications.
- Import and export an Azure SQL BACPAC with SqlPackage (authentication methods) — SqlPackage commands to export and import a BACPAC on Azure SQL or Managed Instance: interactive sign-in, access token, managed identity.
- Insert explicit values into an IDENTITY column — Use SET IDENTITY_INSERT to write a value into an identity column, then turn the option off again.
- Install SQL Server 2019 on RHEL 8 — Install SQL Server 2019 on RHEL 8: directory layout, Python 2 prerequisites, Microsoft repository and default backup directory.
- Installing SQL Server 2019 on Ubuntu 20.04 — Steps to install SQL Server 2019 on Ubuntu 20.04 from the Microsoft repository, then check the service and connect.
- Instance, listener and availability group information queries — Collection of T-SQL queries to check AG listeners, the listening port, service accounts, server properties and availability group replicas.
- Kill low-priority blocking sessions tagged with CONTEXT_INFO — T-SQL script that finds blocking sessions tagged with CONTEXT_INFO and prints or runs the KILL statements, in print-only mode by default.
- Linked server from SQL Server to Oracle with MSDAORA — Create a SQL Server linked server to an Oracle database with the MSDAORA OLE DB provider and map a local login to an Oracle user.
- List AlwaysOn availability groups, replicas and listener — DMV query showing, for each AlwaysOn availability group, its listener and the state of each replica.
- List and configure SQL Server startup stored procedures — How to flag a stored procedure to run when SQL Server starts with sp_procoption, and how to list these procedures.
- List availability groups and the local replica role — T-SQL query listing the availability groups of an instance with the current primary replica and the role of the local replica.
- List connections with their authentication scheme — Query showing the current connections with their authentication scheme (Kerberos, NTLM or SQL), transport and client address.
- List failed logins from the error log over seven days — Count SQL Server failed logon attempts from the last seven days using the error log archives.
- List indexes and their columns — T-SQL query that lists the indexes of a database's tables and views, with their columns, type and uniqueness.
- List server logins by type — List SQL logins, Windows logins and Windows groups of a SQL Server instance from sys.server_principals.
- List table columns with INFORMATION_SCHEMA — T-SQL query on INFORMATION_SCHEMA.COLUMNS that returns the type, length, nullability, identity flag and default value of a SQL Server table's columns.
- List the constraints and unique indexes of a database — One query to list the primary, unique, foreign key, check and default constraints of every user table and view.
- List the database users mapped to a login — Find every database where a SQL Server login has a user, with its default schema and database roles.
- List the members of the sysadmin role — Query listing the logins that belong to the fixed server role sysadmin, with their disabled status.
- List the most expensive cached statements from query stats — Query sys.dm_exec_query_stats and sys.dm_exec_sql_text to list cached statements and stored procedures by logical reads, with execution counts and calls per second.
- Load the SqlServer or SQLPS module in PowerShell — Check whether the SQL Server module is loaded in PowerShell, then load it (SqlServer first, SQLPS as fallback).
- Logon trigger limiting the number of sessions per login — Refuse a new SQL Server logon when a login has reached its session limit, with the limit read from a table.
- Logon trigger with an IP address blacklist — Refuse SQL Server logons from client addresses listed in a table, and log the refused attempts.
- Logon trigger with an IP address whitelist — Allow SQL Server logons only from the addresses listed in a table in the master database.
- Looping over rows with a T-SQL cursor — Declare, open, fetch and close a simple T-SQL cursor, with the loop driven by @@FETCH_STATUS.
- Max worker threads and SQL Agent jobs waiting for a worker thread — How to compute the default max worker threads, check their usage with DMVs, and size the worker threads an availability group needs.
- Measure the plan cache used by single-use plans — Calculate the memory used by single-use ad hoc and prepared plans, and their share of the buffer pool.
- Missing index suggestions from the plan cache — List the missing index suggestions the SQL Server optimizer stored in cached plans, with the matching CREATE INDEX statement.
- Monitor active sessions and AG sync lag with a SQL Agent job — Monitoring pattern: a SQL Server Agent job records the synchronization lag of Always On databases and the running requests into dedicated tables.
- Open SQL Server Configuration Manager by version — Command to open SQL Server Configuration Manager, by SQL Server version.
- Physical memory and memory used by SQL Server — Two T-SQL queries that return the physical memory of the server and the memory used by SQL Server, in gigabytes.
- PowerShell: export a SQL Server blob to files — PowerShell script that exports SQL Server binary columns to files in chunks, using GetBytes and a FileStream.
- PowerShell: import the SqlServer or SQLPS module — Load the SqlServer module when available, otherwise fall back to the older SQLPS module.
- Prerequisites checklist for SQL Server Always On servers — Checklist of points to settle (AD, network, accounts, storage, instance) before building SQL Server 2022 servers for an availability group.
- Querying XML event data with XQuery in SQL Server — Use XQuery value() expressions in T-SQL to filter and extract fields from XML event data stored in a table.
- Read the SQL Server default trace events — Query that reads the SQL Server default trace with sys.fn_trace_gettable and shows the most recent system events.
- Recent stored procedure execution statistics — List stored procedures by most recent execution with their average elapsed time, from sys.dm_exec_procedure_stats.
- Recover a SUSPECT database — Steps to recover a SUSPECT database: EMERGENCY mode, CHECKDB, single-user mode, repair, and back to multi-user.
- Recover a suspect database with DBCC CHECKDB repair options — Put a suspect SQL Server database in emergency mode, run the DBCC CHECKDB repair options in single-user mode, then return it to multi-user mode.
- Recover from a blocking logon trigger — List server-level triggers, connect through the DAC, and disable or remove a logon trigger that blocks access.
- Recreate a SQL login with the same SID on Always On replicas — Generate a CREATE LOGIN with hashed password and identical SID to avoid orphaned users after an availability group failover.
- Reduce the VLF count of a transaction log — Count the VLFs of each SQL Server database, inspect one log and reduce its VLF count with a log backup, truncation and resize.
- Run an Agent job step only on the primary replica — Several T-SQL variants so that a SQL Server Agent job step runs only on the primary replica of an availability group.
- Sargable predicates: rewrite filters to use an index — Sargability rules and common SQL Server filter rewrites so that the filters can use an index.
- Search a string in every column of every user table in SQL Server — T-SQL script that searches a string in the text and numeric columns of all user tables in a database.
- Search for text in stored procedures and functions — Query that searches the definitions of stored procedures and functions for a text string, using INFORMATION_SCHEMA.ROUTINES.
- Shrink a transaction log file — Steps to find why a transaction log cannot be reused, back up the log, shrink the log file and shrink tempdb, with the scripts and their cautions.
- Shrink the tempdb data files — List the tempdb files, then generate the DBCC SHRINKFILE commands that reduce them to 8 GB, with a warning about clearing the plan cache.
- Simulate a cascading delete before removing rows in SQL Server — T-SQL procedure that generates the DELETE statements for the child tables of a parent row and counts the affected rows, without deleting anything.
- Size of tables and indexes per object — Query listing the total, used and data size of every index and heap in the current database, from the allocation units.
- SMO PowerShell: change compatibility level and database owner — Change CompatibilityLevel and SetOwner on a SQL Server database with SMO, then Alter().
- SQL Server 2005: un-cluster an instance or retire a cluster node — Convert a clustered SQL Server 2005 instance to standalone, retire or replace a node, and uninstall after the Windows cluster was broken.
- SQL Server 2019 cluster: install checklist, Kerberos SPNs and AD permissions — What to check before and after installing a SQL Server 2019 cluster, setspn commands and troubleshooting cluster permissions in Active Directory.
- SQL Server activity monitoring (Guardium): pre-deployment questions — Checklist of questions to ask the vendor before deploying a database audit and monitoring tool on SQL Server.
- SQL Server Agent: run a PowerShell script from a job — Two ways to run a PowerShell script in a SQL Server Agent job step, with error handling.
- SQL Server AlwaysOn: WSFC (Windows) or Pacemaker (Linux) — Comparison of an AlwaysOn cluster on Windows Server Failover Cluster and an AlwaysOn cluster on Pacemaker on Linux.
- SQL Server Audit: Registry key of the Security event log source — Registry key and values of the SQL Server Audit event source for the Security log.
- SQL Server backup and restore history queries — Collection of msdb queries to check the last backups and restores, backup age, the version compatibility of a backup file and Azure SQL Database backups.
- SQL Server backup and restore with IBM Tivoli Storage Manager (TDP) — Configure the TSM OS and Data Protection for SQL Server agents, schedule backups and restore a database to another server.
- SQL Server backup and restore with IBM TSM (tdpsqlc) — IBM Tivoli Storage Manager tdpsqlc commands to back up, query and restore SQL Server databases.
- SQL Server databases overview: status, sizes, backups and options — Two queries that summarize the databases of a SQL Server instance (status, files, sizes, recovery model, last backup, options) and the volumes that host the files.
- SQL Server IDENTITY: read, insert and reseed values — Compare @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT(), insert an explicit IDENTITY value and reseed with DBCC CHECKIDENT.
- SQL Server instance settings to check — Checklist of SQL Server instance settings (parallelism, memory, backups, plan cache) to review on a new or existing server.
- SQL Server LocalDB: start and inspect an instance with sqllocaldb — sqllocaldb commands to show the state, start a LocalDB instance and connect to (localdb)\MSSQLLocalDB.
- SQL Server performance counters to monitor — Perfmon counters and DMVs to monitor the SQL Server engine, SQL Server Agent, Analysis Services, Reporting Services and Service Broker.
- SQL Server permission queries — List database permissions, effective permissions of a user, and the principals that can start a SQL Server Agent job.
- SQL Server process memory with sys.dm_os_process_memory — Query sys.dm_os_process_memory to follow the memory used, reserved and committed by the SQL Server process.
- SQL Server Resource Governor: pools, workload groups and classifier function — Configure Resource Governor with three resource pools, three workload groups and a login-based classifier function.
- SQL Server session and request status values — Meaning of the Dormant, Running, Runnable, Suspended, Pending and other status values in the session and request views.
- SQL Server table compression: choose the method and estimate the savings — Choosing a SQL Server compression method (ROW, PAGE, columnstore), listing compressed tables and estimating the savings before compressing.
- SQL Server trace flags for performance: reference — Enable, list and persist SQL Server trace flags, with a table of common flags and their caveats.
- SQL Server: add data files to tempdb and move them — Generate the statements that add data files to tempdb, and the statements that move its files to another folder.
- SQL Server: Agent alerts and security audit to the Application log — SQL Server Agent alerts on critical errors and severities 16 to 25, notifications, and a server audit to the Application log.
- SQL Server: allow xp_cmdshell to a non-sysadmin login — Enable xp_cmdshell, grant EXECUTE and configure the proxy account for a login without sysadmin rights.
- SQL Server: batched delete and update with commit in a loop — T-SQL patterns to purge or modify large tables in batches, with commit, pause and progress monitoring.
- SQL Server: catalog collation of contained databases (Latin1_General_100_CI_AS_KS_WS_SC) — Name and meaning of the default catalog collation of SQL Server contained databases.
- SQL Server: change the owner of all databases to sa — List the owner of each database and generate the ALTER AUTHORIZATION statements to the sa login.
- SQL Server: check index fragmentation — Two queries that list index fragmentation in the current database: a quick read, then a sampled read.
- SQL Server: check process memory, locked pages and VAS — Query on sys.dm_os_process_memory showing physical memory, locked pages and virtual address space used by SQL Server.
- SQL Server: configure and troubleshoot Database Mail — Queries to inspect, configure, grant, test and troubleshoot Database Mail and its use by SQL Server Agent.
- SQL Server: connection timeouts in a multi-subnet availability group — Avoid listener connection timeouts with MultiSubnetFailover.
- SQL Server: contained database collation and collation conflicts — The fixed Latin1_General_100_CI_AS_WS_KS_SC collation of contained databases and how to avoid conflicts.
- SQL Server: copy logins, roles and permissions between nodes — Stored procedure to sync logins (with SID and hash), server roles and permissions between two SQL Server instances.
- SQL Server: create and check the Service Broker endpoint — Create the Service Broker TCP endpoint (port 4022) and check its state through sys.tcp_endpoints.
- SQL Server: current sessions and running requests — Three DMV queries to see connected sessions, their last request and the progress of running requests.
- SQL Server: cycle the error log and the Agent error log — Close the current error log and start a new one with sp_cycle_errorlog and sp_cycle_agent_errorlog.
- SQL Server: date literals in ODBC canonical format — Write language-independent dates, times and timestamps using the {d}, {t} and {ts} syntax.
- SQL Server: error 4014 (network input stream) and TCP Chimney/RSS — netsh steps to disable TCP Chimney and RSS on a Windows host, as a response to SQL Server error 4014 on the network input stream.
- SQL Server: export a binary column to a file with bcp — Extract a document stored in a binary column to a file with bcp queryout.
- SQL Server: find long-running transactions and requests — DMV queries to find the transactions that hold the log and the longest-running requests.
- SQL Server: find who created or dropped a database — Use the default trace to find the login and host that created or dropped a database.
- SQL Server: fix the Configuration Manager WMI error with mofcomp — Recompile the SQL Server WMI provider MOF file with mofcomp.
- SQL Server: generate a Database Mail configuration script — T-SQL script that extracts profiles, accounts and SMTP servers from msdb to generate a Database Mail recreation script.
- SQL Server: generate blocking with an open transaction — Open a transaction that keeps its locks for 50 minutes, to test blocking and its monitoring.
- SQL Server: generate CPU load for a test — Two T-SQL blocks that keep a session busy for 30 or 60 seconds, to test CPU monitoring.
- SQL Server: generate test data in two related tables — Create two tables linked by a foreign key and fill them with random data, for performance tests.
- SQL Server: high-frequency queries from the plan cache — Rank the cached statements by execution frequency, with their average elapsed time and plan.
- SQL Server: I/O statistics per database and per drive — Measure reads and writes per database and per drive, file latency, and the most expensive read queries.
- SQL Server: impersonation with EXECUTE AS USER — Switch user context with EXECUTE AS, check the identity and generate a permission script across all databases.
- SQL Server: kill the sessions of a database — Generate KILL statements for the sessions of a database and force the disconnection with ROLLBACK IMMEDIATE.
- SQL Server: last seek, scan, lookup and update per table — List, for each table of the current database, the last access by lookup, scan, seek or update.
- SQL Server: list data files and filegroups — List the files of an instance or a database, with the free space per filegroup and per volume.
- SQL Server: list indexes with their columns and type — List the indexes of the user objects with their key columns, type and uniqueness.
- SQL Server: list schemas and their owners in all databases — List the schemas of all databases with their owner, and the objects of a schema.
- SQL Server: manual availability group failover with Set-AGClusterFailover — How the Set-AGClusterFailover PowerShell function works and how to use it (synchronous, forced and DR failover of an Always On group).
- SQL Server: map session IDs to OS thread IDs — Map each SQL Server session ID (SPID) to the operating system threads that run its tasks.
- SQL Server: missing index suggestions for one table — List the optimizer's missing index suggestions for one table, with the creation script and an improvement measure.
- SQL Server: new cluster design checklist — Checklist to prepare a SQL Server failover cluster or availability group: AD, network, accounts, storage, instance.
- SQL Server: recover a database without its LDF file — Emergency procedure to rebuild a SQL Server database from its MDF file only, using EMERGENCY mode and DBCC CHECKDB.
- SQL Server: script logins, roles and permissions to another instance — List logins, server roles and permissions, and generate the CREATE LOGIN, role and user statements to recreate them.
- SQL Server: script the users, roles and permissions of a database — Copy a database's security to another by generating a T-SQL script with the usp_get_object_permissions procedure.
- SQL Server: search a column name in all databases — Search for a column by name pattern in all databases of an instance with sp_MSforeachdb.
- SQL Server: sessions consuming tempdb space — Identify the running requests that allocate the most tempdb pages, for user objects as well as internal objects.
- SQL Server: set all databases to compatibility level 150 — Generate the ALTER DATABASE statements that set user databases to compatibility level 150.
- SQL Server: shrink tempdb after clearing the caches — Clear caches with DBCC, then shrink tempdb files with DBCC SHRINKFILE.
- SQL Server: shrink tempdb without restarting the instance — Shrink tempdb files with DBCC SHRINKFILE without a restart, with a link to the reference article.
- SQL Server: sizing max server memory from installed RAM — Sizing rule, reference values and performance counters to set max server memory on a standalone SQL Server instance.
- SQL Server: SMO error "Cannot convert value Version140" (CompatibilityLevel) — PowerShell error when setting compatibility level 140 through SMO: cause and workarounds.
- SQL Server: SQLMorningHealthChecks daily instance health checks — Overview of the SQLMorningHealthChecks GitHub project that automates daily SQL Server instance health checks.
- SQL Server: start after a failed update with trace flag 902 — Start SQL Server with -T902 to skip upgrade scripts, and cycle the error log.
- SQL Server: TLS certificate with PowerShell and certutil — Create, check, convert and import a TLS certificate for SQL Server on Windows.
- SQL Server: top statements by logical I/O and CPU — List the cached statements that cost the most logical reads, logical writes or CPU time.
- SQL Server: usage statistics of each index — List, for each index of the user tables, the number of reads (seeks, scans, lookups) and writes, with the row count of the table.
- SQL Server: use a pipe-delimited list in an IN clause with REPLACE — Turn an a|b|c string into an IN ('a','b','c') list using REPLACE and dynamic SQL.
- SQL Server: version number to release name mapping — Lookup table between major version numbers (8.0 to 16.0) and SQL Server 2000 to 2022 releases.
- SSMS does not start: resetting the configuration key — Registry procedure to get SQL Server Management Studio starting again after a startup failure.
- SSMS: reset SQL Server Management Studio with /selfreg /setup — Command to re-register SSMS components and rerun its initial setup.
- T-SQL stored procedure template with transaction handling — Reusable T-SQL stored procedure skeleton with nested transaction handling, savepoint and rollback logic in TRY/CATCH.
- Top wait types with running percentage — List the wait types that make up 95% of SQL Server waiting time, from sys.dm_os_wait_stats.
- Troubleshoot an availability group failover — Cluster log components, sources to check and Microsoft references to diagnose an unexpected Always On failover.
- Troubleshoot CPU and slow queries with DMVs — DMV queries to find CPU usage, the most expensive queries, active sessions and Azure SQL operation status.
- Useful PowerShell commands for a Windows failover cluster and SQL Server — Cheat sheet of PowerShell and cluster.exe commands to manage groups, resources, disks and logs of a Windows failover cluster hosting SQL Server.
- VM backups and SQL Server: the VSS Writer role — How a virtual machine backup affects SQL Server databases, depending on the state of the SQL Server VSS Writer service.
- Why a SELECT waits on row locks held by an INSERT or UPDATE — Understand why a SELECT is blocked by an INSERT or UPDATE, compare READPAST, NOLOCK and RCSI, and find the session holding the lock.
- WSFC: heartbeat delays and ping-loss thresholds — FailoverClusters PowerShell commands to set the same-site and cross-site heartbeats and the ping-loss thresholds of a WSFC cluster hosting an availability group.