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

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.

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.

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.

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

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.

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

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.

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

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.

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.

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.

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

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.