Archive for the ‘SQL Express’ Category

How to create maintenance jobs for SQL Server instances running on SQL Server Express Edition?

SQL Server Expression edition has the best pricing model of all editions of SQL Server- it is free! But, there are catches. It has the limitations of lack of the SQL Server Agent service, 10GB database size limit, and 1GB max memory. To see a full list of features supported, you can visit http://msdn.microsoft.com/en-us/library/cc645993(v=sql.105).aspx.

While it’s rare to see SQL Server Express Edition installation on enterprise production systems, they do happen. As a DBA, you will need to ensure the server maintenance jobs are accounted for and enabled for the SQL Server Express instances. But, without SQL Server Agent, how can you accomplish such task? Have no fear, my friends. It is not Mission Impossible.


Here’s an overview:

  1. Create the required stored procedures.
  2. Test your SQLCMD commands that you wish to execute and then save it as a batch file.
  3. Schedule the batch file to run using Task Scheduler.

Implementation steps:

  1. In this example, we will test cycling the SQL Server Error Log using SQLCMD.  Open the Command Prompt and then test running the following command:
    sqlcmd -S <i>SERVERNAME</i> -Q "EXEC master.sys.sp_cycle_errorlog"

  2. If step 1 is successful, copy and paste the command into a text file and then save it as a batch file. Eg.: CyclingSQLErrorLog.bat.

  3. Open Task Scheduler (Windows -> Administrative Tools -> Task Scheduler).

    Task Scheduler


  4. On the Actions Pane and under Task Scheduler, click “Create Basic Task…”
    Create Basic Task

  5. Name the task and then click on the “Next” button:
    Name the Task

  6. On the “Trigger” section, set the schedule and then click the “Next” button to continue:
    Set the Schedule

  7. On the “Actions” section, select the default option “Start a program” and then click the “Next” button to continue:
    Action

  8. Click on the “Browse” button and then navigate to the location where you had saved the batch file. Select the batch file and then click the button “Open” to select it. Click the “Next” button to continue.
    Browse to select the batch file

  9. On the Finish section, review the options you had selected and click the “Finish” button to complete.

  10. On the Active Tasks section, you will find the new task you had just created. Double-click to open it.
    Active Tasks

  11. On the Action Pane located on the left hand side, click on the “Properties” link:
    Properties
  12. On the task scheduler properties dialog box and on the “General” tab, click on the “Change User or Group…” button to specify the dedicated domain user with permission to run the scheduled task:
    Change Users or Group
  13. You can test running the task by right-click it and select “Run”:
    Run the scheduled task

Note:

  1. In the above example, we have only tested running the simple task to cycle the SQL Server Error Log. The same concept can apply to database backup, index maintenance, and update statistics maintenance jobs.

  2. To view all the options for SQLCMD, go to http://msdn.microsoft.com/en-us/library/ms162773.aspx.


REFERENCES:
Features Supported by the Editions of SQL Server 2008 R2
http://msdn.microsoft.com/en-us/library/cc645993(v=sql.105).aspx

How to schedule and automate backups of SQL Server databases in SQL Server Express
http://support.microsoft.com/kb/2019698

Schedule a Task
http://technet.microsoft.com/en-us/library/cc748993.aspx