Author: gb_four

Automating SQL Server Express tasks
SQL Server Express edition doesn’t include SQL Agent, and even on editions that do, there are times when scheduling a query at the operating-system level is simply the more practical option (DBA won’t let you access SQL Agent, for example).

Windows Task Scheduler paired with SQLCMD fills that gap: it lets you run a .sql script on a recurring schedule, using either Windows Integrated Security or a SQL login, with no dependency on the SQL Server Agent service.

This post walks through setting up a scheduled, unattended query execution using a batch file, SQLCMD, and Task Scheduler.

Before you automate anything destructive: if the task you’re scheduling includes DELETE, UPDATE, DROP, or any other destructive operation, make sure you have a backup first. Test the logic with a SELECT in place of the DELETE (or wrap it in BEGIN TRANSACTION / ROLLBACK TRANSACTION) to validate the results before you let it run unattended. An unattended job that runs every 15 minutes will happily repeat a mistake just as reliably as it repeats a success.

Step 1: Create the batch file and the query file

Start by creating two files in a folder of your choosing (permissions allowing):
1. A Windows batch file that calls SQLCMD.
2. The actual query that SQLCMD will execute.
Here’s an example of what each file contains.

exec_deletes.cmd
```
@echo off
sqlcmd -S ServerName\InstanceName -E -i "C:\SQLDEV\sqlcmd\YourQuery.sql" -o "C:\SQLDEV\sqlcmd\Output_%date:~-4,4%%date:~-10,2%%date:~-7,2%.log"
```
In this command, the -E switch tells SQLCMD to connect using Integrated Security — meaning the Windows user account configured in Task Scheduler (see Step 2) needs adequate permissions in the target database. You can also connect with the -U and -P switches to use a standard SQL Server login, but that approach exposes the username and password in plain text, so Integrated Security is the safer default.

YourQuery.sql
```
USE [database]

DELETE FROM [table] WHERE OperationID = 0 AND DateTimeColumn < DATEADD(DAY, -15, GETUTCDATE());
```
Step 2: Create a new task in Task Scheduler

Open Windows Task Scheduler and create a new task.

Step 3: Configure the General tab

On the General tab, there are two settings worth calling out (highlighted below):

Change User or Group — enter a valid Windows login with the appropriate privileges in the SQL Server database to execute the query, as noted in Step 1.

Run whether user is logged in or not — select this option for unattended execution, unless you specifically want the task to run only while you’re logged in to the server.

Step 4: Configure the Triggers tab

Go to the Triggers tab and click New to set up the execution schedule.

In the New Trigger window, set the schedule — again, the key settings are highlighted below:

In this example, the task runs On a schedule, Daily, Recur every 1 days, with Repeat task every 15 minutes — Indefinitely. Adjust these values to fit your own requirements.

It’s also worth setting the Stop task if it runs longer than option based on how long you expect the query to take — this prevents overlapping runs if a job ever hangs.

Make sure the trigger is Enabled, then click OK.

Step 5: Configure the Actions tab

Move to the Actions tab and click New.

Set the action to Start a program, and point it at the batch file created in Step 1 (highlighted below).

Click OK to save the new action.

Step 6: Review Conditions and Settings, then save

Review the Conditions and Settings tabs — in most cases, the defaults are fine and the options are self-explanatory (power conditions, network availability, retry behavior, and so on).

Once you’re happy with the configuration, save the task.

Wrapping up

That’s the whole setup: a batch file that calls SQLCMD against a .sql script, and a Task Scheduler job that runs it on whatever cadence you need. It’s a lightweight way to automate query execution outside of SQL Server Agent —useful for Express edition, or simply when you’d rather keep the scheduling at the Windows level.

As with any automated, unattended process touching production data: back it up first, test with a non-destructive SELECT or a transaction you can roll back, and only then let the schedule run on its own.
August 28, 2026
TempDB: Which Size Will It Be After a Restart?
Recently I had to demonstrate/clarify this exact situation to a company I’ve been working with.

A rogue query filled up the entire disk with TempDB activity. The query crashed and rolled back. TempDB was still huge — roughly 1TB across 16 files. We know TempDB is recreated every time SQL Server restarts, so… that should fix the disk space issue pretty quickly, right?

There’s nuance.

What size will the files actually be once TempDB is re-created and SQL Server is running again? Will they reset to the original size defined at installation — 8MB, in my case?

Confirming the starting point

I confirmed the starting sizes using sys.master_files and sys.database_files:
```
SELECT * FROM sys.master_files WHERE database_id = 2
```
```
USE tempdb
GO
SELECT * FROM sys.database_files
```
And sp_spaceused:

So far, all three sources agree on the database and file sizes.

Filling up TempDB on purpose

Now let’s run something deliberately wasteful to grow TempDB:
```
SELECT s1.* INTO #HOLD_JUNK
FROM sys.all_objects AS s1
CROSS JOIN sys.all_objects AS s2
CROSS JOIN sys.all_objects AS s3
```
I had to stop the query after a minute or two, as my TempDB files had already reached 2GB each.

Notice the difference in the “size” column between sys.master_files (top) and sys.database_files:

The lower result set accurately reflects the new TempDB size.

Shrinking it back down — sort of

Since the query was cancelled, the space in TempDB was deallocated, and I should be able to reduce the file size. Let’s shrink 1GB off the first file, tempdev:
```
USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev', 992)
GO
```
SHRINKFILE started taking too long because of blocking. So I did what a lot of us do under pressure: restarted the SQL instance to “fix” the tempdb problem.

So what size will tempdev come back at?

These are the actual files right before SQL Server was stopped:

Stop SQL Server. Start SQL Server.

Interesting:

Let’s check the DMVs again:

How do you like them apples?

TempDB did not come back at the original 8MB. It came back at the size recorded in sys.master_files at the time of shutdown — not the size defined at installation, and not the size after the (interrupted) shrink.

Can I go back? Kind of.

The quick fix — just restarting the instance — is no longer available. SHRINKFILE has to be run manually, preferably in small chunks, until the desired file sizes are reached:
```
USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev', 8)
GO
```
Takeaway

TempDB is recreated on every restart, but “recreated” doesn’t mean “reset to installation defaults.” Each file comes back at the size SQL Server had last recorded for it in sys.master_files. If you let TempDB balloon and then restart before shrinking it back down, you’re stuck doing the shrink manually — in small increments — rather than getting a free reset.
August 6, 2026