The AG failed over and the new primary is healthy, but apps time out or reach the old node. Check listener resource state, DNS records and TTL, MultiSubnetFailover, cluster permissions and cloud load balancer probes, then restore connections quickly.
Understand SIMPLE vs FULL recovery, take full, differential and transaction log backups with T-SQL and PowerShell, check that backups are usable, and practise a restore to a new database name before you ever need one.
Recognise parameter-sensitive plans, prove it with Query Store and plan cache data, and pick the right fix. Parameter Sensitive Plan optimization in SQL Server 2022, OPTIMIZE FOR, RECOMPILE, plan forcing and Query Store hints, with the trade-offs of each.
An AG secondary lags minutes or hours behind, reports on the readable secondary show old data, and failover RTO is at risk. Separate send queue from redo queue, find a blocked redo thread and fix I/O, workload and redo settings.
New connections time out and the server looks hung while CPU is low. Confirm worker thread exhaustion, connect through the Dedicated Admin Connection, find the blocking chain or parallelism storm, and fix it without blindly raising max worker threads.
Since the September 2026 SQL Server updates, querying sys.dm_exec_requests while a database is recovering (after a restart or failover) can raise an access violation and write a dump. Monitoring tools trigger it most. Choose between trace flag 4696, pausing those queries during recovery, or removing the update.
After the September and October 2026 SQL Server updates, linked servers that use MSDASQL with a provider string can fail with Msg 7416 - access denied because no login mapping exists. A stricter connection validation check is the cause. Check the login mappings and provider string, and look for the fix in the later builds.
Route ApplicationIntent=ReadOnly connections through the AG listener to readable secondaries, load-balance them, test with sqlcmd and fix the usual reasons reads still land on the primary.
Microsoft's 13 October 2026 updates for SQL Server 2017-2025 fix an elevation of privilege flaw (CVE-2026-96956) among 22 CVEs in the SQL Server 2025 update. Two known issues carried over from September affect MSDASQL linked servers and sys.dm_exec_requests during recovery. Check them, pick the right package, and patch availability groups in the right order.
After restoring a database to another server, users can't log in because database users point to login SIDs that don't exist there. Find orphaned users, re-map them with ALTER USER, and copy logins with their original SIDs to prevent it.
Which msdb table holds what for SQL Server Agent, how they join, and ready-to-use queries for the questions you'll ask most. Jobs with steps, schedules per job, running jobs, last outcome per step, operators and alerts.
Build a job health report in Power BI on top of a central Agent history store. Covers the data model, DAX measures for success rate and duration change, the pages worth building, refresh through a gateway and the read-only permissions it needs.
Create SQL Server Agent jobs from a single re-runnable T-SQL script with variables for name, category, owner, steps, schedule and notification. It's safe to run again, works well in source control, and gives you the same standards on every server.
SQL Server security drifts when each instance is configured by hand. Define a baseline covering authentication, least-privilege roles, encryption in transit and at rest, auditing, surface area and patching, apply it with scripts or policy, and check every instance against it regularly.
Backup schedules are often copied from a template instead of from what the business can lose and how long it can wait. Agree RPO and RTO per database tier, derive full, differential and log schedules, store copies off-server and immutable, protect against ransomware, and prove restores regularly.
Enterprise edition costs much more than Standard, and many workloads don't need it. List the features and scale limits your workload depends on, compare them with Standard's capabilities in your version, consider alternatives such as basic availability groups or Azure SQL, and confirm with licensing specialists before deciding.
SQL Server performance on Azure VMs often comes down to storage choices: disk types, host caching, striping, and the VM's own throughput limits. Size from a measured baseline, separate data, log and tempdb, choose Premium SSD v2 or Premium SSD with correct caching, use local SSD for tempdb, and check VM-level caps.
The basics of running WinSCP's command-line client from a SQL Server Agent CmdExec step without it hanging, prompting or failing silently. Pin the host key, use key authentication, write a session log and let WinSCP's exit code fail the step.
Walk through the New Job dialog in SQL Server Management Studio page by page, with the choices that matter for production. Owner, category, step flow, retries, output, schedule and notifications. Then script the job out so it can be rebuilt.
Test emails from Database Mail arrive, but job failure and alert notifications never do. The usual reason is that SQL Server Agent isn't set to use Database Mail, or wasn't restarted after it was. Fix it in SSMS or T-SQL, then verify the whole path.
A dependable file-transfer job. A PowerShell step uses the WinSCP .NET assembly to download new files, verify them, archive them on the remote side, record each file in a control table and fail cleanly with a clear message when anything goes wrong.
Collect jobs, owners, schedules, last outcomes and notification settings from all your SQL Server instances with dbatools, export to CSV or a central table, and flag common problems. Includes handling of unreachable servers and a scheduled version.
Jobs that call sp_start_job create dependencies nobody sees in SSMS. Find them by searching step commands, alerts that run jobs and procedures that start jobs, then draw the chain before you disable, move or reschedule anything.
Copy SQL Server Agent history from msdb into a central reporting database with an incremental, watermark-based load before Agent purges it. Covers a simple star schema, the load procedure, collecting from many servers and the queries it makes possible.
Find out what is filling msdb, from job history, backup history, Database Mail logs or job step logs, and set up one clean-up job that purges each of them by age in batches. Also covers the indexes that keep purging fast.
Find jobs with no failure notification and fix them all with one script. Also covers keeping new jobs covered automatically, writing failures to the event log for monitoring tools, and making sure the emails actually leave the server.
Temporary jobs, such as an overnight index rebuild, a one-time data fix or a migration step, pile up and confuse people for years. Make them delete themselves after running, give recurring temporary jobs an end date, and sweep up the ones that slipped through.
Slow backups overrun maintenance windows, and slow restores stretch outages. Measure throughput, turn on compression, stripe to several files or URLs, tune BUFFERCOUNT and MAXTRANSFERSIZE, speed up restores with instant file initialization, and test restore time regularly against the RTO.
Row and page compression shrink data on disk and in memory, and columnstore indexes make large analytical scans far faster. Estimate savings first, choose page or row compression by update pattern, use columnstore for large fact and history tables, and watch CPU and maintenance impact.
Nightly rebuilds of every fragmented index burn I/O, bloat the log and slow AG replicas, often for little gain on modern storage. Focus on statistics freshness, rebuild only where fragmentation actually hurts scans, use resumable and online options, and measure the maintenance window before and after.
Too few indexes slow reads; too many slow every write and waste memory. Review missing-index suggestions critically, find indexes that are written but never read, merge duplicates and overlapping indexes, and validate each change against Query Store before and after.
Default parallelism settings let small queries go parallel and big queries take every core, causing CXPACKET/CXCONSUMER waits and unpredictable performance. Set MAXDOP from the NUMA layout, raise cost threshold for parallelism from the default, use database-scoped and query-level overrides where needed, and confirm with wait stats.
Wait statistics show what SQL Server spends time waiting on, which points you to storage, CPU, memory, locking or log throughput. Snapshot waits over a busy period, filter out benign waits, map the top waits to causes and fixes, and re-measure after each change.
Three ways to stop a SQL Server Agent job from running, and what each one does to manual runs, shared schedules and other jobs. Includes T-SQL for each, a check for jobs left disabled by accident, and a habit that stops them being forgotten.
Agent has no built-in dependency between jobs, so people chain them with sp_start_job or guess at schedule gaps. Instead, keep dependencies in a table and add a gate step that checks predecessors succeeded for today's batch. Each job can then wait, skip or fail, and the whole chain is visible in one query.
Set up an SSIS job step that runs under a dedicated domain account instead of the Agent service account. Create the credential and proxy, grant the SSIS subsystem, give the right people use of the proxy, configure the step and troubleshoot the usual failures.
Schedule a Python script with SQL Server Agent the right way. Use a dedicated virtual environment, call it from a CmdExec or PowerShell step, run it under a proxy, pass parameters with Agent tokens and make failures fail the step.
Job history cuts step messages short, often just before the useful part. Compare the output options SQL Server Agent offers per step (output file, log to table, include in history) and set them in SSMS or T-SQL so failures can be diagnosed the first time.
The only sysadmin login was removed, sa is disabled and its password lost, or the DBA group was deleted from AD. A local Windows administrator can start SQL Server in single-user mode, connect as sysadmin and add a proper admin back. Here are the steps, the traps and the clean-up.
A TDE-encrypted database won't restore or attach on another server (error 33111), or the original server was rebuilt. Find the certificate backup, recreate the master key and certificate, restore, and set up certificate backups so this never depends on luck again.
A database shows RECOVERY_PENDING or SUSPECT after a restart or storage problem. Work out which it is and why from the error log. Then bring it online by fixing files and permissions, restoring from backup, or, only as a last resort, using emergency mode repair.
The SQL Server service starts and stops at once, or won't start at all. Find the real error in ERRORLOG, then fix the common causes. Covers a missing tempdb path, damaged or missing master files, account and permission problems, port conflicts and bad startup parameters, using minimal-configuration and single-user starts.
A first tour of SQL Server Agent for people new to SQL Server operations. What each building block does, how they fit together, and a first useful automated task built end to end.
Short, practical answers to the SQL Server Agent questions that come up again and again. Where jobs are stored, who they run as, why a job didn't run, time zones, Express edition, Azure options, and more.
SQL Server Agent fails to start with error 5 "Access is denied" or stops straight after starting. Work through the service account, folder and registry permissions, the sysadmin login, and the Configuration Manager change that fixes most cases.
sp_start_job returns at once, so an SSIS package that calls it carries on before the job has done anything. Build a reusable procedure that starts a job, waits for it with a timeout, and returns its outcome, then call it from an Execute SQL Task. Covers permissions too.
Before patching, a migration cut-over or a DR test you often need all jobs stopped. Save the current enabled state, disable everything, and afterwards restore exactly that state, so jobs that were already disabled stay disabled.
SQL Server Agent notifies per job, not per step, and if a step is set to continue on failure, the job reports success and nobody hears about it. Three patterns for alerting on individual step failures while the rest of the job keeps running.
Agent only alerts on failure, so a job stuck for six hours looks fine until morning. Build a check that compares each running job with its own history and emails when it runs well beyond its normal duration, with sensible thresholds and an exclusion list.
A step-by-step check for SQL Server Agent jobs that didn't start when expected. Covers enabled flags, next run times, overlapping runs, daylight saving, Agent downtime, schedule end dates and frequency settings that don't mean what people think.
Create Agent alerts for high-severity errors and the I/O errors that warn of corruption, with sensible delays so you aren't flooded. One script sets them all up, then you can test them safely and know what to do when one fires.
sysjobhistory stores run_date as 20261025, run_time as 23015 and run_duration as 13542 (1 hour 35 minutes 42 seconds). Here are reliable T-SQL conversions to datetime, seconds and hh:mm:ss, plus end times and a reusable view.
Live SQL Server Agent status without opening Job Activity Monitor. Which jobs are running, which step each is on, what its session is waiting for, what runs next, and whether Agent itself is up. Also how to give these views to people who aren't sysadmin.
Build a readable inventory of every SQL Server Agent job on an instance from msdb. Owner, category, enabled state, schedules, step count, subsystems, notification settings and last outcome, in one result set you can paste into documentation.
SQL Server Agent records that a job changed but not who changed it or what changed. Use a database audit specification on msdb's job procedures to capture who did it, keep script snapshots to capture what changed, and know the limits of triggers on msdb tables.
The client always sees state 1. Pull the real state from the SQL Server error log, map it to the cause (bad password, disabled login, missing database, Windows-only mode) and fix it.
A step-by-step runbook for failed SQL Server Agent jobs. Find every failure and the step that broke, read the full error from history, output files and the Agent log, match it to the common causes (owner, permissions, proxies, timeouts, blocking, disk, Agent itself), fix and rerun safely, and stop it recurring.
Ready-to-run msdb queries and procedure calls for everyday SQL Server Agent work. Inventory every job with its schedule and last outcome, enable or disable jobs in bulk, change owners and categories, script jobs out for source control, copy them between servers and remove the ones nobody needs.
What to check in the first days after moving SQL Server to Azure or AWS. Covers data validation, statistics and index maintenance, raising compatibility level safely with Query Store, common performance gaps in the cloud, and monitoring set-up.
If SQL Server Agent can't reach the operator on an alert, the notification is lost unless a fail-safe operator is set. This guide sets up Database Mail, enables it for Agent, creates team operators and the fail-safe operator, and tests the whole chain end to end.
Move self-managed SQL Server on EC2 to Amazon RDS to hand over patching, backups and Multi-AZ HA. Covers the readiness check against RDS limits, sizing from CloudWatch data, migration by native backup or DMS, replacing instance-level features, and decommissioning.
A FinOps playbook for SQL estates already in Azure. Covers finding idle and oversized databases, moving spiky databases to serverless, consolidating into elastic pools or instance pools, reservations, Azure Hybrid Benefit hygiene, and dev/test savings.
The decisions and settings that separate a well-run Azure SQL Database from a fragile one. Covers choosing the service tier, networking and identity, encryption and auditing, Query Store and automatic tuning, retry logic, backups and geo-DR, monitoring, maintenance, and cost control, with az CLI and T-SQL for each item.
Move a growing Azure SQL Database from General Purpose or Business Critical to Hyperscale for up to 128 TB, fast backups and restores, and read scale-out. Covers fit checks, the online conversion, reverse migration window, replicas, and cost.
The second step after a lift-and-shift. Move databases from SQL Server on Azure VMs to Azure SQL MI to stop patching, clustering and backup work. Covers the business case, readiness checks, picking MI link or LRS, network design inside Azure, and decommissioning the VMs.
Job categories are a free, built-in way to group SQL Server Agent jobs by team, application or purpose. Create a simple category scheme, assign jobs in bulk, use categories in reports, bulk enable and disable, and notifications, and avoid the built-in categories that other features depend on.
A practical map of the on-premises SQL Server features that block or complicate moving to Azure SQL Managed Instance or Azure SQL Database, with the replacement for each and example code.
Move databases from Amazon RDS for SQL Server to Azure SQL MI, for example after a consolidation decision or an acquisition. Covers native backups from RDS to S3, copying to Azure Blob Storage with azcopy, restoring with Log Replay Service, TDE and identity considerations, and cutover.
SQL Server licences are often the biggest line in an AWS database bill. Covers downgrading Enterprise to Standard where safe, Optimize CPUs on EC2, memory-rich instance types, consolidating instances, Savings Plans and RIs, and when refactoring to Babelfish pays off.
A routine for keeping SQL Server Agent healthy. Daily checks for failures, long runs and disabled jobs, a weekly review of owners, notifications and history, and monthly housekeeping and testing. Each check comes with the query or dbatools command, and links to the deep-dive posts in this series.
Start SQL Agent jobs from SSMS, T-SQL, another job, PowerShell, sqlcmd, schedules, Agent alerts or an application. Includes how to start at a specific step, wait for a job to finish (sp_start_job doesn't wait), and give non-admins permission to run only the jobs they need.
Replace ETL jobs and reporting replicas with Fabric mirroring, which continuously copies SQL tables into OneLake as Delta tables. Covers the supported sources, prerequisites, setup, what it costs, what isn't replicated, and how reporting changes.
Most monitoring platforms already read the Windows Application log. Configure every SQL Server Agent job to write an event on failure, write your own custom events from job steps, find the events with PowerShell, and forward them to Azure Monitor, SCOM or a SIEM.
Use an Always On availability group to move Tier 1 SQL Server workloads to Azure with seconds of downtime. Covers network and cluster design across sites, adding an Azure VM replica (or a distributed AG), using it for DR first, and the planned failover that completes the migration.
Plan the two areas that delay most SQL Server moves to Azure. Covers mapping Windows logins and groups to Microsoft Entra ID, service accounts to managed identities, MI Windows authentication with Kerberos, private endpoints vs VNet-injected MI, DNS and firewall design.
Many SQL Server apps rely on Windows logins. Design Active Directory for SQL Server on AWS with AWS Managed Microsoft AD and a trust to on-premises AD, join RDS for SQL Server and EC2 to it, create Windows logins, and test Kerberos.
Unexpected restarts and failovers often go unnoticed until users complain. Set up an Agent job that emails on every service start with the node, start time and reason clues; add availability group role-change alerts; and check cluster and Windows events to find out why it happened.
CmdExec, PowerShell and SSIS job steps run as the SQL Agent service account unless you give them a proxy. Create a credential and a proxy, grant it to the right subsystems and logins, use it in a step, and troubleshoot the access errors you'll meet. Includes an audit query for every proxy on the server.
Rehost SQL Server on EC2 with full control. Covers instance and EBS design, three move methods (AWS Application Migration Service for whole servers, backup/restore via S3, and an AG replica over Direct Connect), licensing choices, and HA after the move.
Rehost SQL Server on Azure Virtual Machines with full compatibility. Covers building the VM correctly, the three common data move methods (backup to URL, log shipping, adding an AG replica), registering with the SQL IaaS Agent extension, and the cutover.
SQL Server Agent publishes its own Windows performance counters for jobs, steps and alerts. Find them, collect them with Get-Counter and logman, and alert on failed jobs and success rate. Then go further and measure the CPU, reads and duration each running job step uses through DMVs.
Move databases to Azure SQL MI from native backups in Blob Storage using the Log Replay Service. Covers backup requirements, uploading with azcopy, continuous vs autocomplete mode, monitoring, the final cutover and common errors.
Use AWS Database Migration Service to copy SQL Server data to RDS or EC2 and keep it in sync with change data capture until cutover. Covers source prerequisites (MS-CDC or replication), schema-first approach, task settings for LOBs, validation, monitoring latency and cutover.
Lift existing SSIS packages into Azure Data Factory with the Azure-SSIS Integration Runtime. Covers SSISDB on Azure SQL MI or Database, package deployment, connecting to on-premises sources, custom components, scheduling and cost control, plus when to rebuild instead.
Decide where SQL Server Integration Services and Reporting Services run after an AWS migration. Covers SSIS and SSRS as RDS options, their limits, running them on EC2 instead, migrating the SSIS catalog and report server content, and modern alternatives.
Move databases to Amazon RDS for SQL Server using .bak files in S3. Covers the option group and IAM role, uploading backups, full + differential + log restores with NORECOVERY for small cutover windows, task monitoring and post-restore steps.
Move a database into Azure SQL Database. Covers fixing compatibility blockers first, choosing between Azure Database Migration Service (offline), BACPAC and transactional replication (minimal downtime), and the post-move settings that matter.
Use the Managed Instance link, which is built on distributed availability groups, to replicate on-premises databases to Azure SQL MI in near real time, cut over with a planned failover in seconds, and keep the option to fail back with SQL Server 2022.
How msdb stores job and step history, and the queries that answer real questions. What failed overnight and why, how long each job really takes, which jobs are getting slower, which step is the bottleneck, and what is running right now.
Use Babelfish for Aurora PostgreSQL to move SQL Server applications off SQL Server licences with fewer code changes. Covers Babelfish Compass assessment, migration modes, creating the cluster, moving schema and data, connecting apps over TDS, and testing gaps.
How to schedule SSIS packages deployed to the SSIS catalog (SSISDB) with SQL Server Agent. Covers the SSIS job step and its options, environments for per-server settings, running under a proxy, 32-bit runtime, starting executions from T-SQL, and the SSISDB queries that show why a package failed.
Size Azure SQL, RDS and cloud VMs from real data rather than server specs. Covers which counters to collect and for how long, how to read CPU, memory, IOPS, throughput and log rate, and how to map them to service tiers and instance families.
The starting point for a SQL Server move to the cloud. Covers the 6R options for each database, every Azure and AWS landing target compared side by side, a decision flow, and how to group an estate into migration waves.
Confirm the damage with DBCC CHECKDB, find out what is corrupt, and recover with an index rebuild or a page restore before you ever reach for REPAIR_ALLOW_DATA_LOSS.
One page of tested T-SQL and PowerShell for daily SQL Server work. Covers instance and database info, sessions and blocking, waits and top queries, indexes and statistics, space and tempdb, backups and restores, Always On, Agent jobs, security and dbatools one-liners.
Use Azure Arc as the first step of a SQL Server transformation. Connect on-premises and other-cloud instances to Azure for inventory, best-practices assessment, Defender, Entra ID authentication, pay-as-you-go licensing and ESUs, and migration assessment.
Build a complete inventory of SQL Server instances, databases, features and dependencies, then assess compatibility with each target. Covers the tools for Azure and AWS, the T-SQL queries to fill the gaps, and how to turn the results into migration waves.
How SQL Server licences carry over to Azure and AWS. Covers Azure Hybrid Benefit and the 180-day dual-use right, pay-as-you-go versus reserved capacity, AWS license-included versus BYOL with License Mobility or Dedicated Hosts, and edition and core optimisation.
A target-neutral cutover runbook for moving a SQL Server database to Azure or AWS. Covers the T-minus checklist, freezing writes, final sync, switching connections, validation, go/no-go points and a tested rollback path.
SQL Server Agent has three fixed database roles in msdb that let people create, view or operate jobs without sysadmin. What each role can and can't do, how to grant them to groups, how they interact with job ownership and proxies, and how to review who has Agent access.
Job ownership decides whether a job can run at all and under which security context its steps execute. Why jobs owned by personal logins fail when people leave, which owner to choose, how ownership affects T-SQL steps versus proxies, and scripts to find and fix risky owners across the estate.
Rebuild a shared-storage SQL Server Failover Cluster Instance on EC2 using Amazon FSx for Windows File Server (SMB) or FSx for NetApp ONTAP (iSCSI) across two Availability Zones. Covers when FCI beats an AG, cluster networking on AWS, storage choice, and migration steps.
A practical operating standard for SQL Server Agent. Covers job naming and categories, ownership and security, step design with retries and logging, notifications and alerts, history retention, source control and change management, so that jobs stay understandable and fail loudly instead of silently.
SQL Agent stores schedules as bitmasks and packed integers. This post decodes freq_type, freq_interval, relative intervals and sub-day settings into plain English, and builds a report of every job, its schedules and next run time. Use it for on-call run books, overlap checks and maintenance-window planning.
RDS Custom gives a managed SQL Server with OS and sysadmin access for apps that can't run on standard RDS. Covers when to choose it, prerequisites, the shared responsibility model, migrating with native backups, automation pause for customisations, and HA.
A disabled job fails silently, because it never runs. Three ways to catch enable and disable changes on SQL Server Agent jobs, with complete scripts. A low-risk polling job that compares a snapshot, an msdb trigger for instant email, and SQL Server Audit to record exactly who made the change.
SQL Server Agent keeps only 1,000 history rows by default, 100 per job, so busy servers lose history within days. Three ways to keep what you need. Raise the row limits, switch to time-based purging, or archive history to a table, with scripts and msdb sizing advice.
Check replica and database state with DMVs, interpret send and redo queues, and resume or reseed a database that is NOT SYNCHRONIZING or SUSPENDED.
Use Query Store to find a query whose plan changed, compare the old and new plans, force the good plan as an immediate fix, and address the root cause.
Restore full, differential and log backups side by side to the moment before a bad DELETE or UPDATE, then copy the lost rows back, without overwriting the production database.
Choose and build a SQL Server DR option for a second site or region. Covers an asynchronous AG replica, distributed availability groups, and log shipping, with RPO/RTO, network needs and a forced-failover runbook.
Use log_reuse_wait_desc to see why the log can't be truncated, fix the specific cause (missing log backups, long transactions, replication, AG), and shrink only when it's safe.
Choose between an FCI and an AG for SQL Server high availability. Compares what each protects, storage, licensing and failover behaviour, then walks through building a two-replica synchronous AG with a listener and checking its health.
Build a production standalone SQL Server instance properly. Covers volume layout, service accounts, setup options, tempdb, memory, MAXDOP, cost threshold, instant file initialisation, maintenance jobs and backups.
Identify which sessions and objects are filling tempdb, recognise PAGELATCH allocation contention, and configure tempdb files correctly.
Find the head blocker in seconds, pull deadlock graphs from the system_health session, and pick the right long-term fix, from indexes to RCSI.
No posts in this category yet.