Menu & Search

Running Scheduled Tasks in SQL Server Express

Running Scheduled Tasks in SQL Server Express

Running scheduled tasks in SQL Server Express has to be done differently due to the limitations of the Edition. As well as having a maximum database size of 10GB and 1GB consumable RAM, Express does include the SQL Server Agent. So, If you need a task to run on a time-based schedule, you’ll have to look outside of SQL Server.

We can schedule our jobs using the Windows Task Scheduler, which will run a PowerShell or DOS script that calls the sqlcmd.

The DOS approach is explained within the following steps:
# Install sqlcmd and locate files
# Create .bat sqlcmd script
# Create Scheduled Task SQLCMD (.BAT) Script

The .bat script for this post will execute Ola Hallengren’s Index Maintenance, which I’ll set to run weekly on the MS SQL Express instance. Ola’s index maintenance stored procedure is assumed to be on the SQL Server for this task.

Locate and Install Sqlcmd

The sqlcmd utility is installed with SSMS, or you can download it separately for Windows/Linux environments – MS Docs: Download and install sqlcmd

If you already have SSMS installed, you can find the directory of sqlcmd with the help of this Microsoft Documentation Page – File Locations for Default and Named Instances of SQL Server (search ‘sqlcmd’ after selecting your SQL Version when on the page).

My sqlcmd.exe and bcp.exe clients are in:
-> C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Bin

sqlcmd Location

The only thing we need for now is to have it installed on the same machine we’re running the script from.

I wrote another blog post on sqlcmd if you would like more information and examples of executing SQL commands with sqlcmd.exe.

Create .BAT Script SQLCMD Script

Below you’ll see -Q being used and everything within the command is within quotes. The -o parameter is also being used to forward output messages to a text file.

sqlcmd -Q "EXEC master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_updatestats'; EXEC dbo.IndexOptimize @Databases = 'ALL_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 30, @FragmentationLevel2 = 50, @pagecountlevel = 1000" -o "c:\logs\index_maintenance.txt"

Copy this into a notepad and save it as a .bat file, in any folder.

sqlcmd .bat index maintenance
example .bat file for sqlcmd (notepad++)
sqlcmd bat file
example .bat file for sqlcmd (windows explorer)

I’m going to create a new folder for the output log file. This is specific to the index_maintenance solution I’m applying here.


Next, I’m running the index_maintenance.bat file to verify it’s working as expected, and by viewing the output log file.

c:\logs index maintenance
SQL Server Index Maintenance Log File

Now we have our script ready and tested, we can proceed to the next step and schedule the execution of the script.

Create Scheduled Task SQLCMD (.BAT) Script

We have our script file ready, and we know where SQLCMD is. It is time for us to schedule the script as a Task in Windows Task Scheduler. This should be a more familiar area for Windows Admins.

1. Open Task Scheduler.

Open Task Scheduler

2. Right-click and select Create New Task as shown below.

Task Scheduler Create New Task

3. Within the General tab of the prompted window, change the following:
–> Name / Description, more information the better for visibility.
–> The user account that the task runs as to a new local service account I created for this. Remember, the chosen account requires Logon As Batch and MSSQL permissions (sysadmin for quickness here).
–> Run whether the user is logged on or not.

Task Scheduler New Task General Tab

4. Click the Triggers tab and hit New to set the schedule.

Task Scheduler New Task Trigger Tab

The schedule below is set to run every Sunday at 02:00. Set this up to your desired days/times and click OK once done.

Task Scheduler Set Schedule

5. Click on the Actions tab and click New to add our script as the action.

Task Scheduler New Task Action Tab

This is where we add our .bat script file location. Browse to the folder you saved your script and click OK when after selecting the file.

Task Scheduler New Task Add .bat File

6. Click on the Settings tab and review options. The “Stop task if it runs for longer than” setting is of particular note as the default is 3 days.

Task Scheduler New Task Set Max Runtime

7. Now that we have reviewed all tabs within the Create Task window, click OK to create the task.

You will be prompted to enter a username and password if the task is set to use an AD service account.

Task Scheduler New Task Password Prompt

This Scheduled Task has been created and is awaiting its next execution, which is early Sunday morning.

Task Scheduler sqlcmd

If we run this now we can watch our indexes being rebuilt in SQL Server by running sp_who, sp_who2 or sp_whoisactive from SSMS.

Your script might not be running for long enough to verify this way. This maintenance solution script run time can vary as it depends on the level of index fragmentation on all SQL Server databases. It’s been run 64 times on my test databases and now only taking a second to complete.

We can also verify the SQLCMD script ran successfully by checking the Scheduled Task History and log output.

Index Maintenance Scheduled Task Verification