SQL Server DBA Scripts & Tools
dba-tools is the toolkit I use as a working SQL Server DBA, free and open on GitHub. Copy/paste scripts for the day-to-day, a one-command health check, and an AI assessment that reviews the whole server with you.
Quick access
What do you need right now?
What’s Inside
Eight areas, each one a hub with the scripts and the write-ups behind them.
Server & ConfigurationWhat this server is, and how it’s installed and patched.24 scripts
Storage & CapacityDisk, database and filegroup free space, plus growth forecasting.16 scripts
Backup & RecoveryCoverage, integrity, encryption, and restore readiness.13 scripts
High AvailabilityAlways On, mirroring, failover clustering, and replication.9 scripts
Performance & TroubleshootingBlocking, deadlocks, wait stats, missing indexes, live tracing.32 scripts
SecurityLogins, permissions, sysadmin review, orphaned users.18 scripts
Maintenance & AutomationSQL Agent jobs, index maintenance, generated maintenance jobs.22 scripts
Migration & DeploymentCompatibility checks, deprecated features, DDL generators.9 scripts
How Deep Do You Need to Go?
Stopping at the first one is a perfectly good answer.
📋One answer, now
Copy/Paste First
184 SQL scripts, 177 of them read-only, documented across 145 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 233 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.
Script Articles
Every script on the site, searchable. Type what is actually going wrong, or filter by area.
Showing all 127 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 SummaryA deadlock is SQL Server’s way of resolving a situation it can’t otherwise escape: two transactions each holding a lock the other one needs, waiting on each other forever unless…PerformanceGet Open TransactionsAn open transaction that never commits or rolls back is one of the quietest ways SQL Server gets into trouble: it holds locks other sessions wait on, it stops the transaction lo…PerformanceGet Temp DB HotspotsTemp DB is shared by every database and every session on the instance: temp tables, table variables, sort and hash spills, version store for snapshot isolation, all of it lands …PerformanceGet Last DBCC CHECKDBDBCC CHECKDB is the only thing that actually confirms a database’s data is structurally sound; backups only prove a database can be restored, not that what gets restored is unda…PerformanceGet 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 pressurePerformanceGet Implicit ConversionsAn implicit conversion is SQL Server quietly changing a data type to compare two values that don’t match, and it’s one of the few performance problems that will not throw an err…PerformanceGet 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 is the single most useful diagnostic feature SQL Server has shipped in the last decade, a built-in, per-database history of every query’s plans and runtime stats, no…PerformanceGet 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 Forecast“How close is this database to its configured size limit?” and “at its current growth rate, when will it actually get there?” are two different questions, and answering them nee…StorageGet Database Free Space SummaryDatabase Files Detail gives you file-level detailStorageGet Log Reuse WaitsA transaction log that won’t stop growing, or a log backup job that suddenly can’t keep up, always comes back to the same underlying question: what is this database’s log actual…StorageGet Disk Space on SQL ServerA SQL Server that runs out of disk stops taking writes, and depending on which volume filled up, that can mean suspended databases, a frozen transaction log, or failed backups a…StorageGet Filegroup SpaceDatabase Free Space Summary tells you a database has plenty of room overallStorageGet 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 AuditA database’s recovery model is a promise about what kind of restore is possible, FULL and BULK_LOGGED promise point-in-time recovery, backed by log backups; SIMPLE promises none…BackupsGet Permissions and Role MembershipSysadmin Members answers the single highest-stakes question: who has unrestricted accessSecurityGet 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 timeSecurityGet Linked ServersLinked servers are the kind of configuration that gets set up once, for one integration, and then forgotten until a migration or an incident forces someone to ask what’s actuall…SecurityGet Login and Job InventoryPermissions and Role Membership and SQL Agent and Jobs already cover the deep-dive on access and job healthSecurityGet 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 constantlySecurityGet XE Session ActivityStanding up a trace session is the easy partSecurityGet Orphaned UsersAn orphaned user is a database user whose SID no longer matches any server login, usually left behind after a database restore or attach on a different server, a migration, or a…SecurityGet Login Security AuditPermissions and Role Membership covers who can do whatSecurityGet Version Upgrade ReadinessMigration Risk Assessment covers per-database risk findings when moving to different hardware or infrastructureSecurityGet Active XE SessionsExtended Events sessions have a way of quietly accumulating on a busy instanceSecurityGet 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 ValidationVersion Upgrade Readiness and Migration Risk Assessment cover getting readySecurityGet 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 StatusTransactional replication runs quietly in the background for years without incident, right up until a subscriber falls behind or a publication stops distributing changes, and by…High AvailabilityGet AG Failover Readiness and Readable Secondary UsageAG Replica Role and Synchronization State tells you the current health of every replicaHigh 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 StatusReplication Status tells you what’s published and subscribedHigh AvailabilityGet Database InventoryBefore you migrate, upgrade, or consolidate anything, you need one honest answer per database: is it actually online, what recovery model is it running, and is its compatibility…Server & ConfigGet Instance Configuration SnapshotEvery sp_configure setting on an instance tells a small story: a value someone changed deliberately, a value nobody's touched since install, or a value that's been changed but i…Server & ConfigGet OS and Hardware InfoBefore you can reason about performance, capacity, or whether a setting makes sense, you need the basics: how much CPU and memory the box actually has, what OS it’s running, and…Server & ConfigGet Database SummaryChecking every database on an instance one at a time, state here, recovery model there, backup age in a third window, doesn’t scale, and it’s exactly the kind of routine check t…Server & ConfigGet Patch LevelVersion and Edition answers "what is this one instance running" as a quick single-server snapshotServer & ConfigGet Instance Configuration ScoreGet Instance Configuration Snapshot gives you every raw sp_configure setting; useful, but it doesn't tell you which of those hundred-plus rows actually matterServer & 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 SummaryGet SQL Agent Job Overview answers “what’s the current state of every job.” This script answers a narrower, more urgent question: “what actually failed recently, and why.” Job h…MaintenanceGet Duplicate IndexesIndexes accumulate the same way clutter does: one developer adds an index to fix a slow query, a colleague adds a near-identical one three months later because the first one was…MaintenanceGet 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 checks are usually written for one database at a time, which is fine until you inherit an instance with forty databases and no idea which of them has been sk…MaintenanceGet 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 JobsIf you're already running Ola Hallengren's maintenance solution, this won't replace it, but the approach here might still be useful: a small, readable T-SQL generator that build…MaintenanceGenerate Backup JobsIf you're already running Ola Hallengren's maintenance solution, this won't replace it, but the approach here might still be useful: a small, readable T-SQL generator that build…MaintenanceGenerate Index Maintenance JobsIf you're already running Ola Hallengren's maintenance solution, this won't replace it, but the approach here might still be useful: a small, readable T-SQL generator that build…MaintenanceGet Agent Alerts and OperatorsA SQL Server can raise a severity 19-25 error, the fatal, “something is genuinely broken” tier, corruption, out-of-resource conditions, hardware faults, and if there’s no alert …MaintenanceGet Migration Risk AssessmentEvery migration has a list of things that will cause a problem if nobody checks for them first: a database in an Availability Group that needs coordinated removal, a linked serv…MigrationGenerate Login ScriptMoving a SQL Server workload to a new instance means recreating every login, unless you script it, and doing it wrong means restored databases end up with orphaned users the mom…MigrationGenerate Restore With Move ScriptMoving a SQL Server workload to a new instance where the target's drive layout doesn't match the source means every RESTORE DATABASE needs a WITH MOVE clause per file, unless yo…MigrationGenerate 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 ScriptMoving a SQL Server workload to a new instance means recreating every SQL Agent job, unless you script it, and getting the order wrong (steps before the job header, schedule bef…MigrationGenerate 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 shows what’s blocked right nowOtherGet Trace Flags and Resource Governor ConfigurationTrace flags and Resource Governor are both instance-wide behavior changes that don’t show up in sp_configure at a glance, don’t appear in the database, and can sit silently in p…OtherGet Database Snapshot InventoryA database snapshot is created for one specific reason, a pre-deployment rollback point, a reporting-consistency workaround, a test before a risky change, and then it just sits …OtherGet Index Usage StatsUnused Indexes already filters straight to drop candidates in one databaseOtherGet Backup Size TrendNobody watches backup file sizes month to month, they just watch that the backup succeededOtherGet Query Performance Deep-DiveTop CPU Queries ranks by raw resource consumptionOtherGet Last Restore HistoryEvery DBA can point to a backup scheduleOtherGet CPU Topology and OS Configuration ChecksOS and Hardware Info tells you what hardware existsOtherGet 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 TrackingChange Data Capture and Change Tracking answer a similar question, "what changed and when," for two different audiences, ETL/replication consumers for CDC, application-level con…OtherGet 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 ChecksLast DBCC CHECKDB tells you how stale your corruption checks areOtherGet Table SizesWalk onto a new SQL Server instance and the first honest question is usually "where's all the space going." Not at the database level, sys.master_files answers that in seconds, …OtherGet Top CPU Queries“Which query is causing this” has more than one right answer, because a query can be expensive in different ways: it can burn CPU, it can generate huge I/O, or it can just habit…OtherGet Collation Conflicts and Cross-Database DependenciesTwo checks that rarely matter until the exact moment they do: does this database’s collation match the server’s (and every other database it might join against), and does anythi…OtherGet Database HealthBefore diving into anything deeper, a health check should start with the basics: is every database actually online, is the recovery model what it's supposed to be, and is anythi…OtherGet Error Log PatternsThe SQL Server error log is where the instance tells you what’s wrong, but on a busy server it’s also where hundreds of routine startup, backup, and checkpoint messages bury the…OtherGet Job Schedules and Duration TrendsSQL Agent Job Failure Summary tells you what brokeOtherGet 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 RequestsWorker Threads and Active Sessions gives you the aggregate health number: how close the worker thread pool is to exhaustionOtherGet Extended Events SessionsExtended Events sessions are easy to lose track ofOtherGet Schema Change HistoryThat question comes up after almost every unexpected behavior change: a query that used to work now errors, a report that used to return data now doesn’t, a job that used to suc…OtherGet Active Connections by DatabaseTaking a database offline, whether for a decommission, a restore, or a maintenance operation that needs exclusive access, starts with the same question: is anyone actually using…OtherGet 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 PlansQuery Store Top Queries tells you what’s expensive right nowOtherGet Service Broker Health and Database Mail QueueService Broker and Database Mail are both messaging infrastructure, and both are easy to forget are even active, since SQL Server uses Service Broker internally for things like …OtherGet TempDB Usage and File BalanceTempDB Configuration and TempDB Hotspots already cover configuration baseline and allocation-contention diagnosisOtherCheck 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 assessmentMaintenance
Nothing matches that. Try a broader word. The search
covers the script name, what it does, and its area.
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.
Major pillars
Find a script
Health check & tooling
Script areas
Latest posts
- Set the Default Database for a SQL Server Login
- SQL Server High CPU: What to Check First, in Order
- Cannot Connect to SQL Server: The Checks in the Order That Finds It
- Cumulative Wait Stats Lie: Measuring Waits Over an Interval
- SQL Server Differential vs Log Backup: Which One Restores What, and How the Chain Breaks
- SQL Server MAXDOP and Cost Threshold: What To Set, and Why 5 Is Not a Recommendation
Browse by topic
- Backups & Recovery (17)
- DBA Scripts (150)
- High Availability (HA) (12)
- Installation & Configuration (45)
- Maintenance (24)
- Migration & Upgrades (14)
- Monitoring (9)
- Performance Tuning (37)
- Security (Encryption & Permissions) (19)
- Storage & Capacity (14)
- T-SQL Fundamentals (11)
- Troubleshooting (65)
- Wait Types (235)