Mssql
Articles in this category.
- 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.
- 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.
- 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.
- 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.
- Backup Status / Current process and all processes
- 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, 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 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 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.
- 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.
- 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 Guid Sid
- 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.
- 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.
- 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 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 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 blocking sessions on Azure SQL — Find sessions, running requests and blocking on Azure SQL with DMVs, and use sp_WhoIsActive.
- 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.
- 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.
- 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.
- 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: import the SqlServer or SQLPS module — Load the SqlServer module when available, otherwise fall back to the older SQLPS module.
- 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.
- 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 All database
- 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: 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: 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 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: 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: current sessions and running requests — Three DMV queries to see connected sessions, their last request and the progress of running requests.
- 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: 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: 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: 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 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: 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: 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: 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 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: 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: 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 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: 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 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: 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.
- SSMS does not start: resetting the configuration key — Registry procedure to get SQL Server Management Studio starting again after a startup failure.
- The SQL Server Network Interface library could not register the Service Principal Name (SPN)
- 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.
- 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.
- 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.