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.
Windows Task Scheduler is where I’d steer to, running a PowerShell or DOS script which utilises sqlcmd.
The .bat script for this post will execute Ola Hallengren’s Index Maintenance, which I’ll set to run weekly on the Express instance of SQL. Ola’s index maintenance stored proc is assumed to be on the SQL Server for this task.
The sqlcmd utility is installed with SSMS, or you can download separate Windows & Linux clients.
If you already have SSMS installed, you can find the directory of sqlcmd using this Microsoft documentation link – search ‘sqlcmd’ after selecting your SQL Version when on the page.
The only thing we need for now is to have it installed on the same machine we’re running the script from… But have a look at my other post for more information on sqlcmd if of interest.
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 notepad and save as a .bat file. Also note, it’ll break if you try format the code.
Next create a new folder for the log output file.
Double-click and run the index_maintenance.bat file, and we should quickly be able to see it working by viewing the logfile.
Open Task Scheduler.
Within here, right-click and select Create New Task…
Within the General tab of the prompted window, I’m changing the following;
# Name / Description, more information the better for visibility.
# The user account who 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 user is logged on or not.
Click into the Triggers tab and hit New.. to set the schedule.
I’m configuring to run every Sunday at 02:00.
Next up is the Actions tab.
Hit New… and add our .bat file as the start script.
Over to the last note-worthy tab, Settings. Stop the task if it runs longer than you’re comfortable with.
When ready, click OK to create the task and enter user password.
The scheduled task has been created and is awaiting it’s next run early Sunday morning.
If we run this now we can watch it working by running sp_whoisactive on the SQL Server – you should see stats being refreshed or indexes being rebuilt. For me it takes under a second to run as my indexes have been refreshed 64 times today… but I can verify by deleting the logfile and watch it be recreated, and also by looking at timestamps in the log file.