It’s 2AM. The NOC is calling.

“We’re getting alarms. Everything on Server X is running slow.”

Your brain wakes up faster than your body.
CPU?
Blocking?
Storage latency?

Probably best to check the backups.

Not because backups are bad.
But because backups are heavy.

What This Query Is Actually Doing

The query (linked below) reads from sys.dm_exec_requests — which is SQL Server’s live view of what is currently executing right now.

But instead of showing everything, it filters down specifically to:

BACKUP DATABASE
BACKUP LOG
RESTORE DATABASE
RESTORE LOG

In other words:

“Show me if SQL Server is currently backing something up or restoring something.”

It then shows:

Which session is doing the work
Which database is affected
When it started
How far through it is (percent_complete)
When SQL Server estimates it will finish

At 2AM, that’s all you need.

You’re not tuning queries yet.
You’re not redesigning indexes.

You’re answering one simple question:

“Is this slowness just a backup smashing the storage?”

Why Backups Make Things Feel Slow

Backups generate serious I/O.

They:

Read large chunks of data files sequentially
Write large backup files to disk or network storage
Use CPU (especially if compression is enabled)
Compete for throughput on shared storage arrays

If your:

Data files
TempDB
Log files
Backup target

…are all sitting on the same underlying storage, congratulations — you’ve built yourself a 2AM slowdown generator.

Backups don’t block like bad queries do.
They just quietly consume everything.

Why We Use Full, Differential & Transaction Log Backups

Backups aren’t just about safety.

They’re about balancing:

Recovery Time (RTO)
Data Loss (RPO)
Storage footprint
Performance impact
Operational sanity

If you only ever do full backups, you’re either:

Burning storage
Burning backup windows
Or restoring painfully slowly

A proper strategy prevents nightmares.

Full Backups — The Foundation

A Full backup captures the entire database at a point in time.

This is your baseline.
Everything builds on this.

Without a recent Full:

Restores take forever
You lose more data than you expected
Your recovery chain gets messy

Full backups are stability.

But they’re heavy.

Differential Backups — The Smart Middle Ground

A Differential backup captures only what has changed since the last Full.

That means:

Smaller backup size
Faster backups
Faster restores

Restore chain: Full → Latest Differential

Done.

Differentials stop you having to run massive full backups every night.
They reduce storage pressure.
They reduce I/O pressure.

They’re a storage sanity tool.

Transaction Log Backups — The Nightmare Preventer

This is the big one.

In FULL recovery mode, log backups:

Capture all transactions since the last log backup
Allow point-in-time restore
Truncate the transaction log (mark space reusable)

If you don’t run log backups:

Your log file grows.
And grows.
And grows.
Until disk fills.
Then the database stops.

That’s not theory.
That’s a classic 2AM incident.

With regular log backups (every 5–15 minutes typically), you get:

Minimal data loss
Controlled log growth
Ability to restore to “just before the bad deploy”

That’s the difference between:

“We lost a day’s data.”

and

“We lost three minutes.”

Sometimes The Right Move Is To Do Nothing

One of the hardest lessons as a DBA:

Not every 2AM alarm requires intervention.

If a Full backup is running and:

It’s progressing normally
I/O is high but not failing
There’s no blocking chain forming
The system is responsive, just slower

…then the safest move is often to let it finish.

Killing a Full backup halfway through:

Wastes all I/O already consumed
Leaves you without a usable backup file
Forces you to restart the job later
Can extend the pain window

Full backups are heavy by design.
They read the entire database.

If you stop them at 85%, you’ve gained nothing — you’ve just delayed completion.

A controlled slowdown is better than no backup at all.

Backups protect you from disasters.

Interrupting them casually is how you create one.

When You Should Intervene

There are situations where you act.

You consider pausing, cancelling, or rescheduling Full backups when:

The storage subsystem is collapsing
You’re breaching a hard business SLA
Critical transactional workload is being blocked
You’re saturating cloud IOPS limits
Backup windows were badly designed and overlap peak trading hours

If Full backups routinely hurt production, the problem isn’t the backup.

It’s:

Maintenance window design
Storage layout
IOPS provisioning
Or lack of backup offloading strategy

Fix the architecture.
Don’t fight the symptom at 2AM.

The Real DBA View

When the NOC calls at 2AM, this query isn’t about curiosity.

It’s about triage.

You’re checking:

Is a backup running?
Which database?
How long has it been going?
Is it nearly finished?
Is it a restore someone forgot to tell us about?

If the backup is at 92% and finishing in 3 minutes, you calm everyone down.

If it’s at 3% and targeting the largest database on shared storage, you start planning.

Good backup strategy prevents:

Storage blowouts
Runaway log files
Data loss disasters
Restore horror stories

But it can absolutely create performance pressure if not scheduled and architected correctly.

Final Thought

Backups are protection.

But protection has weight.

At 2AM, your job isn’t to panic.

It’s to look at the live workload, understand what SQL Server is actually doing, and decide:

Is this an emergency…
or just a very busy safety net doing its job?

Active Requests Script

    Loading…

Gareth Winterman