Skip to main content
System StatusContact Support
VersionOne Community

Configuring and Maintaining On-Premise Databases


Before You Begin

Work with your SQLServer DBA to review and consider implementing the following recommendations.  See SQLServer 2005 Books Online for more information about the concepts discussed.

If you’re using SQLServer 2005 Express Edition, you’ll need to have the SQLServer 2005 Management Studio Express to easily modify server and database settings via its’ GUI.

Recovery Model

By default, SQLServer will create new databases with the Recovery Mode set to ‘Full’. If your DBA team uses this as a standard, does log backups in addition to database backups, and has an effective backup management and recovery process, then you are probably fine in this regard.  If, however, you don’t have this support/infrastructure/process in place, and your database’s recovery model is set to ‘Full’, it’s likely that your transaction log file is growing continuously, and begun to seriously impact performance. 

If this is the case, you should consider:

  • Setting the application database recovery model to "Simple":

    1. In SQLServer Mgmt Studio, right-click on the database and choose Properties. 

    2. Select the Options page, change the Recovery Model drop-down to Simple, and then click OK.

  • Shrinking the transaction log:

    1. Right-click on the database, choose Tasks > Shrink > Files

    2. On the Shrink File dialog, choose File Type: Log.

    3. Click OK.

  • Creating a daily (or more frequent) backup schedule, retaining a rolling week or so of backups (other maintenance tasks will be discussed separately):

    • If you are using SQLExpress, you won’t have SQLServer Agent, so will need to use the Task Scheduler to execute cmd/bat files that launch the command-line utility.

    • See Related Links for more information on Transact-SQL BACKUP command syntax.

To learn more about SQLServer 2005 backup and restore strategies to make sure you’re comfortable with this approach, see SQLServer 2005 Books Online.

Database Maintenance

SQLServer databases need recurring maintenance to ensure optimal performance.  It’s recommended that the following database maintenance tasks execute once per week (on a weekend night, presumably, in this order).

  • Rebuild Indexes

  • Update Statistics

This will eliminate all index fragmentation, then refresh the query optimizer’s statistics, ensuring that SQLServer’s query plans are built on the most up-to-date information.

If you have a large user population making heavy use of the application, you may want to schedule an overnight database maintenance task that includes (in this order):

  • Reorganize Indexes

  • Update Statistics

In this case, reorganizing will repair minor index fragmentation prior to updating stats.

Additionally, as was mentioned above, you should backup your database at least once/day, depending on the backup and recovery strategy you employ.

SQLExpress and Database Maintenance

Unfortunately, SQLExpress, the freely licensed version of SQLServer does not come with SQLServer Agent, the subsystem used for scheduling and managing recurring database/server tasks.  Therefore, if you want to have those automatically scheduled, you’ll need to use a combination of the window scheduled task utility with cmd/bat files executing SQLServer’s command-line utility against a Transact-SQL script.

For example, to create a scheduled task for a nightly database backup:

  1. Create a sql script named ‘backup.sql’ with the following content (modifying paths/names and BACKUP DATABASE options as necessary) *:

    BACKUP DATABASE [VersionOne] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\VersionOne.bak' WITH NOFORMAT, INIT, SKIP, NAME = N'VersionOne-Full Database Backup'

  2. Then, create a file named ‘exec backup.cmd’ with this content (replacing LOCALHOST\SQLEXPRESS with whatever your server\instance names are):

    sqlcmd -S LOCALHOST\SQLEXPRESS -i backup.sql -o output.txt

  3. Create a scheduled task to execute ‘exec backup.cmd’ each night.

Note that if you want to keep a rolling number of days of backups, simply extend the batch file to delete files older than n days, and rename the newly created backups with a date included.

Use this same approach to create automated maintenance for the other recommended tasks. See SQLServer Books Online for the Transact-SQL commands and syntax.

* a shortcut for getting the t-sql syntax for certain tasks is to use Mgmt Studio to start a task, then click the ‘Script’ button in the middle of the top toolbar of the task dialog to see the sql that Mgmt Studio was going to run for that task.

  • Was this article helpful?