SQL Server DBA Scripts
Copy and paste scripts from the toolkit I use as a working SQL Server DBA, free and open source as dba-tools. Every script link lands on the code.
New here? Start with the Complete Guide
195 SQL scripts182 read-only45 in the health checkFree, open source, no sign-up
Go-To Scripts
The ones I reach for first. Each link lands on the script, ready to copy.
Get-ActiveSessionsPerformance: what is running and who is blockingGet-UserPermissionsAuditSecurity: one login’s access across the instanceGet-DiskSpaceStorage: free and used space per database volumeGet-DatabaseFreeSpaceSummaryStorage: tightest databases first, capped filesGet-IndexFragmentationMaintenance: most fragmented indexes, rankedGet-AvailabilityGroupLatencyHigh Availability: how far behind each replica isGet-BackupCoverageBackup: coverage per database, with a status flagGet-BackupRestoreProgressBackup: percent done and estimated finish time
The Script Areas
Each area in its colour, with its pillars and the scripts to start with. See how it all connects on the Site Map.
Server & Configuration
What this server is, and how it’s installed and patched.
Pillars: Server Inventory, SQL Server Installation
Start with: Patch Level, Config Score, OS and Hardware Info, Configuration Snapshot
Storage & Capacity
Disk, database and filegroup free space, plus growth forecasting.
Start with: Disk Space, Free Space Summary, Database Sizes, Growth Events, Growth Forecast
Backup & Recovery
Coverage, integrity, encryption, and restore readiness.
Start with: Backup Coverage, Backup and Restore Progress, Restore Time Estimate, Restore History, Backup Chain Integrity
High Availability
Always On, mirroring, failover clustering, and replication.
Start with: AG Latency, AG Replica State, AG Failover Readiness, Replication Status, Log Shipping Status
Performance & Troubleshooting
Blocking, deadlocks, wait stats, missing indexes, live tracing.
Pillars: Query, Blocking and Locking, Extended Events Tracing, Collectors
Start with: Active Sessions, Blocking Chains, Wait Statistics, Missing Indexes, Top CPU Queries
Security
Logins, permissions, sysadmin review, orphaned users.
Start with: Login Permissions, Permissions and Role Membership, Sysadmin Members, Certificates and TDE, Fix Orphaned Users
Maintenance & Automation
SQL Agent jobs, index maintenance, generated maintenance jobs.
Pillars: SQL Agent and Jobs, Index Maintenance, SQL Server Maintenance Job Framework
Start with: Index Fragmentation, Agent Job Failures, Maintenance Job Status, SQL Agent Job Overview
Migration & Deployment
Compatibility checks, deprecated features, DDL generators.
Pillar: SQL Server Migration Script Generators
Start with: Migration Risk, Orphaned Users, Fix Orphaned Users
How Deep Do You Need to Go?
Stopping at the first one is a perfectly good answer.
📋One answer, now
Copy/Paste First
195 SQL scripts, 182 of them read-only, documented across 130 pages here. Open one, paste it into SSMS, run it. No install, no parameters, no magic variables.
🩺Review a whole server
Health Check
45 diagnostic scripts run in a single command, saved as CSVs, then a rules review flags CRITICAL / WARNING / INFO findings.
🤖Help me understand
AI Assessment
An AI reads the whole collection, correlates findings into root causes, and writes a prioritized report.
Clone it, run one command
Setup, for the second and thirdA single script needs nothing installed. The collection and the assessment run from the repo, and that is the whole of it. Read-only by default, and every script header states the permissions it needs and what it touches.
git clone https://github.com/peterwhyte-lgtm/dba-tools
cd dba-tools
.\Initialize-Environment.ps1🤖 The Health Check, Reviewed by AI
The flagship workflow. A collection runs 45 diagnostic scripts against an instance (backups, waits, blocking, memory, disk, security surface area) and saves every result as a CSV. A rules review turns those into deterministic findings. Then the AI assessment reads the whole collection and does what fixed thresholds can’t: it correlates findings across the outputs into root causes and writes a prioritized report with evidence and a fix for each issue.
Nothing is sent to any AI until you run the assessment step yourself. See a real assessment, end to end →
A 30-second run: terminal command, web UI output, then the same script in SSMS.
Where You Run It
1
Copy and paste it
Take a script from the post that explains it, or from the repo, and paste it into SSMS. Self-contained and read-only, so it runs as-is.
Needs any query window
2
Run it from the repo
Clone the toolkit and call scripts by name. Output lands as CSV you can keep and compare, across as many servers as you like.
Needs PowerShell
3
Start the Web UI
Launch the local dashboard and work from it: health scorecard, drill-downs, AI reports and incident triage. Runs entirely on your machine.
Needs PowerShell, then a browser
🛠
The tool you will actually be sitting in
Whichever route you pick, most of this ends up in SQL Server Management Studio. If yours is out of date, missing something you expected, or was never set up the way a DBA wants it, the SSMS complete guide covers installing and updating it, the settings worth changing, and the shortcuts that save real time.
🔌
Also new
Give your AI assistant the same reference, as tools it can call
The assessment above hands an AI one server’s output. The sqldba MCP server hands it the whole library instead: every error write-up, wait type, build and support date, and all 244 scripts with the safety class on each one. Your assistant looks the answer up and cites the page rather than recalling it.
Free, MIT licensed, runs locally, and it has no database connection at all.
Grab the Toolkit
Free and open source. Every script came from a real situation: an incident, a migration window, or a routine check that needed to be fast and safe.
Script Finder
Every script on the site, searchable. Type what is actually going wrong, or filter by area.
What do you need right now?
Showing all 130 scripts
Get Blocking SessionsBlocking is one of the most common causes of a SQL Server that suddenly looks frozenPerformanceGet Blocking ChainsA single blocked session is usually easy to spotPerformanceGet Deadlock SummaryRecent deadlocks pulled from the system_health session, with no trace to set up firstPerformanceGet Open TransactionsOpen transactions by age, with the session and the SQL it is running or last ranPerformanceGet Temp DB HotspotsWhich sessions are using the most TempDB right now, for contention and spill triageStorageGet Last DBCC CHECKDBWhen each database last had a clean DBCC CHECKDB, so you know how stale your corruption checks areMaintenanceGet Statistics HealthQuery plans are only as good as the statistics SQL Server uses to build themPerformanceGet Worker Threads and Active SessionsThere is a particular kind of SQL Server incident where the server is up, CPU looks fine, and yet new connections hang or time outPerformanceGenerate Collector AlertsEvery collector in this pillar writes evidence somewhere, blocking chains, deadlocks, wait stats, VLF counts, database growth, TempDB pressureMaintenanceGet Implicit ConversionsImplicit conversion warnings in the plan cache, the ones turning index seeks into scansPerformanceGet Database IO UsageWhen a SQL Server is struggling and the usual suspects (CPU, memory, blocking) come back clean, the next question is always the same: which database is hammering the disks?PerformanceGet Query Store Top QueriesQuery Store is SQL Server’s built-in flight recorder for query performance: every query’s plan, runtime stats, and history, retained and queryable without any extra tracing setupPerformanceGet Long-Running QueriesWhen users report the application is slow, the first question is always the same: what is actually running right now?PerformanceGet Query Store StatusQuery Store state per database: off, full, read-only or healthy, with capture mode and fillPerformanceGet Wait StatisticsEvery time a SQL Server session has to wait for something, a lock, a page from disk, a CPU slice, a memory grant, the engine records what it waited on and for how longPerformanceGet Database Sizes and Free SpaceDatabase size checks are one of the first things I review when assessing a SQL Server environmentStorageGet Transaction Log Size and UsageKeeping an eye on transaction log usage is one of the simplest ways to prevent unexpected database outagesStorageGet Autogrowth HistorySQL Server autogrowth is one of those settings that is often ignored until it becomes a production problemStorageGet Database Growth Risk and ForecastHow close each database is to its size limit, and at the current growth rate, when it gets thereStorageGet Database Free Space SummaryAllocated, used and free space for every online database, split into data and log, tightest database firstStorageGet Log Reuse WaitsWhy each transaction log cannot truncate, with recovery model, log size and last log backupStorageGet Disk Space on SQL ServerFree and used space on every volume that holds SQL Server data or log filesStorageGet Filegroup SpaceUsed and free space per filegroup, lowest free percentage first, for the filegroup that is full when the database is notStorageGet SQL Server Database File DetailsSQL Server database files are one of those areas where small configuration issues can become large operational problemsStorageGet VLF CountsVirtual Log File (VLF) count is one of those SQL Server health checks that is often overlookedStorageGet Backup Restore ProgressA large restore has been running for twenty minutesBackupsGet Backup CoverageMost production SQL Server environments have backup jobs configuredBackupsGenerate Backup and Restore ScriptsWriting a one-off BACKUP DATABASE or RESTORE DATABASE command for a single database is trivialBackupsGet Backup Restore Duration EstimatePlanning a maintenance window is a guessing game if you don't know how long your own backups actually takeBackupsGet Backup Chain IntegrityYou can have a full backup from last night and still not be able to restore to 2pm todayBackupsGet Database Backup HistoryMost backup checks answer one question: what's the latest backupBackupsGet Recovery Model AuditEach database's recovery model against its real backup posture, flagging FULL with no log backupsBackupsGet Permissions and Role MembershipServer and database role members plus explicit grants and denies, who can do what in one passSecurityGet Edition Feature UsageEdition downgrades, Enterprise to Standard, Standard to Web, look simple until you discover a feature that only Enterprise supports has quietly been in use the whole timeServer & ConfigGet Linked ServersEvery linked server with its login mappings, and whether each one still connectsServer & ConfigGet Login and Job InventoryLogins by type and status, and Agent jobs with their owners, for a migration or access reviewSecurityGet Sysadmin MembersThe sysadmin fixed server role provides unrestricted access to a SQL Server instanceSecurityGet Backup Encryption StatusTDE (Transparent Data Encryption) and backup encryption are two different things that get confused constantlyBackupsGet XE Session ActivityStanding up a trace session is the easy partPerformanceGet Orphaned UsersDatabase users whose SID matches no server login, the usual leftover after a restore or migrationSecurityGet Login Security AuditSQL logins with policy or expiration off, failed login patterns, lockouts and last activitySecurityGet Version Upgrade ReadinessCompatibility levels, deprecated features in use and sizing, in one pre-upgrade readiness summaryMigrationGet Active XE SessionsExtended Events sessions have a way of quietly accumulating on a busy instancePerformanceGet User Permissions AuditMost permissions checks answer a broad question: who has sysadmin, what roles exist, what's granted whereSecurityGet Migration Login Audit and Post-Migration ValidationEvery login that needs migrating with its risk, then a source and target check that it all arrivedMigrationGet Audit Specifications, DDL Triggers, and Proxy CredentialsSome security infrastructure isn’t misconfigured, it just doesn’t existSecurityGet Certificates, Keys, and TDE StatusCertificates created for TDE, backup encryption, or an Availability Group encrypted endpoint get created once and then quietly forgottenSecurityGet Database Mail and xp_cmdshell Configurationxp_cmdshell lets any login with EXECUTE permission on it run arbitrary operating system commands from inside SQL ServerSecurityGet Replication StatusEvery publication and subscription from the distribution database, with type and statusHigh AvailabilityGet AG Failover Readiness and Readable Secondary UsageWhether a failover would succeed right now, with RPO and RTO estimates, plus readable secondary routingHigh AvailabilityGet Last Node BlipOn a Failover Cluster Instance (FCI), every node blip, planned or not, causes SQL Server to restart on whichever node picks it upHigh AvailabilityGet Mirroring Endpoint Health and StatusDatabase Mirroring has been deprecated since SQL Server 2012, Always On Availability Groups are the supported replacement for any new deploymentHigh AvailabilityGet Replication Agent StatusLog Reader and Distribution Agent latency, errors and undistributed commands over the last 24 hoursHigh AvailabilityGet Database InventoryOne row per database with state, recovery model and compatibility level, for migration planningServer & ConfigGet Instance Configuration SnapshotEvery sp_configure setting captured in one result set, for a baseline and for change trackingServer & ConfigGet OS and Hardware InfoOS version, CPU, memory and SQL Server uptime in one rowServer & ConfigGet Database SummaryOne row per database: state, recovery model, log reuse wait, file sizes, backup currency and flagsServer & ConfigGet Patch LevelVersion, edition, build and Cumulative Update level, for patch tracking across an estateServer & ConfigGet Instance Configuration ScoreAbout twenty key configuration checks scored PASS, WARN or FAIL, with a recommended action for eachServer & ConfigGet Version and EditionVersion and edition are two of the most basic facts about a SQL Server instance, and two of the easiest to get wrong when you're relying on memory or an out-of-date wiki pageServer & ConfigGet Missing IndexesEvery time SQL Server builds an execution plan that could have been improved with a better index, it makes a note of itMaintenanceGet Maintenance Job StatusA maintenance job that’s silently failing is one of the more dangerous states a SQL Server instance can be in, because everything looks normal from the outsideMaintenanceGet Index FragmentationIndex fragmentation is one of those topics where the received wisdom, "rebuild your indexes every weekend", causes almost as many problems as it solvesMaintenanceGet Unused IndexesEvery non-clustered index has to be maintained by SQL Server on every INSERT, UPDATE, and DELETE against the tableMaintenanceGet HeapsA surprising amount of SQL Server performance pain comes from tables that were never given a clustered indexMaintenanceGet SQL Agent Job Failure SummaryAgent job failures from the last seven days with readable timestamps and the error messageMaintenanceGet Duplicate IndexesExact duplicate and left-prefix overlapping indexes across every user database, with usage stats so the unused ones come firstMaintenanceGet Index Design IssuesMost index problems aren’t about a single bad index, they’re about a table’s index design as a whole drifting out of shape over timeMaintenanceGet Index Fragmentation Across DatabasesIndex fragmentation across every user database in one result set, worst first, for maintenance planningMaintenanceGet SQL Agent Job OverviewMost SQL Server instances end up with a handful of Agent jobs that quietly run backups, index maintenance, and monitoring collectors in the backgroundMaintenanceGenerate Database Integrity and Housekeeping JobsGenerates the Agent jobs for weekly CHECKDB, msdb history cleanup and error log cycling, as DDL you review before you run itMaintenanceGenerate Backup JobsGenerates the Agent jobs for daily full backups, log backups every 15 minutes and backup file cleanup, as DDL you review before you run itMaintenanceGenerate Index Maintenance JobsGenerates an Agent job that rebuilds or reorganizes fragmented indexes across every online user database, online where the edition allowsMaintenanceGet Agent Alerts and OperatorsAgent alerts and operators with the severity 19 to 25 gaps, plus whether Database Mail can actually deliver the notificationMaintenanceGet Migration Risk AssessmentPre-migration risk scan: HIGH, MEDIUM and INFO findings for compatibility, database settings, linked server dependencies and sizingMigrationGenerate Login ScriptCREATE LOGIN DDL for every non-system login with SIDs and password hashes preserved, so restored databases have no orphaned usersMigrationGenerate Restore With Move ScriptRESTORE DATABASE WITH MOVE statements from each database’s latest full backup, relocating every file to the target’s foldersMigrationGenerate User Mapping ScriptMoving a SQL Server workload to a new instance means recreating every database user, custom role, and role membership, unless you script itMigrationGenerate Agent Job Scriptsp_add_job DDL to recreate every Agent job, with its steps and schedules, on the target serverMigrationGenerate Linked Server ScriptMoving a SQL Server workload to a new instance means recreating every linked server and its login mappings, unless you script itMigrationGet Lock Escalation, Contention Analysis, and Blocking Chains with PlanBlocking chains with the cached plan for each session, plus lock escalation and contention totalsPerformanceGet Trace Flags and Resource Governor ConfigurationActive global and session trace flags, what each one does and whether it still matters, plus the Resource Governor configurationOtherGet Database Snapshot InventoryEvery database snapshot with its source, age and real sparse file size on disk, so forgotten snapshots show before they fill a volumeStorageGet Index Usage StatsSeeks, scans, lookups and updates for every index across all user databasesMaintenanceGet Backup Size TrendNobody watches backup file sizes month to month, they just watch that the backup succeededOtherGet Query Performance Deep-DivePlan cache health, execution time variance, memory grant spills and the slowest stored proceduresPerformanceGet Last Restore HistoryEvery DBA can point to a backup scheduleOtherGet CPU Topology and OS Configuration ChecksCPU and NUMA layout, scheduler summary, Lock Pages in Memory and Instant File Initialization checksServer & ConfigGet Recent Error Log EntriesThe SQL Server error log records almost everything worth knowing about an instance's recent health, and almost nobody reads it until they're already troubleshooting somethingOtherGet CDC and Change TrackingWhich databases have CDC or Change Tracking on, with retention, cleanup settings and latency, before they stall or bloat the logOtherGet Memory Configuration and UsageMax Server Memory is the single most important memory setting on any SQL Server instance, and it's astonishing how often it's left unconfiguredOtherGet Suspect Pages and Integrity ChecksAnything recorded in msdb suspect_pages, and whether each database is ready for an integrity checkBackupsGet Table SizesLargest tables across every online user database by data plus index size, the first read on an instance you have just inheritedOtherGet Top CPU QueriesThe top 20 CPU-consuming queries from the plan cache, with execution counts and timingsOtherGet Collation Conflicts and Cross-Database DependenciesDatabases whose collation differs from the server’s, and the cross-database references that break when one of them movesOtherGet Database HealthEvery user database’s state, recovery model, log reuse wait, auto settings and data and log size in one passStorageGet Error Log PatternsThe current error log grouped by category: memory pressure, login failures, IO errors, autogrowthPerformanceGet Job Schedules and Duration TrendsEnabled jobs with their schedules and next run, and which jobs are running longer than their averageMaintenanceGet MAXDOP ConfigurationMax Degree of Parallelism is one of the first things worth checking on any server, and one of the most commonly wrongOtherGet Services InformationA SQL Server instance is really a handful of Windows services working together, and it is surprisingly common for one of them to be quietly misconfiguredOtherGet TempDB ConfigurationTempDB is shared by every database on the instance, and it’s usually the first thing to show contention under load, long before any user database doesOtherGet Active Sessions and RequestsWhat is running right now: wait type, blocker, CPU, reads, elapsed time and the current statementPerformanceGet Schema Change HistoryRecent CREATE, ALTER and DROP statements from the default trace, answering what changed on this server after an incidentServer & ConfigGet Active Connections by DatabaseSession count, active requests, open transactions and blocked sessions per database, the check before you take one offlineOtherGet Compression CandidatesRow and page compression trade CPU for storage and I/O, and that trade is worth making on some tables and not othersOtherGet Query Store Regressions and Forced PlansQueries running slower today than their seven-day average, and forced plans that are failingPerformanceGet Service Broker Health and Database Mail QueueService Broker conversation endpoints by state, transmission queue depth and activation, plus the Database Mail queue, across every user databaseOtherGet TempDB Usage and File BalanceTempDB file sizes and free space, and whether the files match in size, growth and countStorageCheck AG Replica Role and Synchronization StateWhether every replica is healthy, synchronizing and actually ready to take overHigh AvailabilityCheck Always On Availability Group LatencyHow far behind each secondary really is, in seconds and in log bytesHigh AvailabilityCollect Blocking and Deadlock EventsA standing Agent job that captures blocking chains and deadlocks while you sleepMaintenanceCollect Capacity and TempDB BaselinesAgent collectors that build database growth and TempDB history over timeMaintenanceCollect Health and Configuration BaselinesAgent collectors that record instance config and health so drift becomes visibleMaintenanceCollect Performance BaselinesWait stats, storage IO and Query Store snapshots collected on a schedule for trendsMaintenanceCreate Decommission Audit SessionAn Extended Events session that proves whether anything still uses a server before you retire itPerformanceCreate Login Activity SessionAn Extended Events session recording who actually connects, and from wherePerformanceCreate SP Execution SessionAn Extended Events session capturing stored procedure execution the plan cache missesPerformanceRemove XE SessionStop and drop an Extended Events session cleanly when the investigation is overPerformanceFix Orphaned UsersRepairs the orphaned database users a restore or migration leaves behindMigrationInstall and Configure SQL ServerUnattended install, then the sensible configuration defaults applied straight afterServer & ConfigInstall and Update SSMS via PowerShellInstall or update SQL Server Management Studio from PowerShell, SSMS 21 and later includedServer & ConfigPatch SQL Server and Check StatusCheck patch status, preview the update, then apply the CU across one server or a fleetServer & ConfigPre-Install and Post-Install ChecksWill this build actually run here, and did the install land correctlyServer & ConfigUninstall SQL ServerRemove an instance cleanly, and know exactly what gets left behindServer & ConfigUninstall SSMS via PowerShellRemove SQL Server Management Studio from PowerShell, all versionsServer & ConfigRun a Full SQL Server Health CheckCollect 45 scripts, review the findings against rules, then get an AI assessmentMaintenanceGet Login Migration ParityOne fingerprint row per login on source and target, so a migrated login that differs stands outMigrationEnable or Disable All SQL Agent JobsReport, save, disable and restore the enabled state of every Agent job around a maintenance windowMaintenanceGet Log Shipping StatusBackup, copy and restore ages against their thresholds, showing which log shipping step is behindHigh AvailabilityGet Identity Column UsageHow close each identity column is to the top of its data type, before inserts start failingStorage
Nothing matches that. Try a broader word. The search
covers the script name, what it does, and its area.
- Site Map, how the pillars, areas and scripts connect
- DBA Quick Links, the scripts reached for most, with a filter
- The Complete Guide, how the scripts are organised and how to pick between them