 Welcome

Welcome to Db Maint, the powerful and easy way to maintain your SQL Server installations.


Quick Start

 Db Maint

Quick Start

Use this guide to get started with Db Maint.

  1. Create Database Group(s) if desired.

Database group gives you the possibility to define similar job for several databases.

Please refer to the Database Groups section for more details.

  2. Create Job(s).

A job defines what to perform for the database(s).

Please refer to the Jobs section for more details.

  3. Create backup device(s) if needed.

The backup device defines the physical location for a backup. This can also be done with SQL Enterprise Manager.

Please refer to the Backup Devices section for more details.

  4. Create e-mail operators if desired.

Requires that you have configured SQL Server for e-mail support.

This can also be done with SQL Enterprise Manager.

Please refer to the Email Operators section for more details.

  5. Create task(s).

The task defines when to perform job(s).

Recommendation: Do not use the same backup device for more than one task.

Please refer to the Tasks section for more details.

  6. Test your task to make sure that the backup is performed.

This can be done from the Backup Devices dialog, show contents.

You might also want to verify that the e-mail is sent.


What is Db Maint?

 Overview

What is Db Maint?

Db Maint is a product that helps you maintain Microsoft SQL Server installations.

It consists of two programs: DbMaint.exe (the "client") and DbM.exe (the "server").

DbMaint.exe is a graphical program used to define what you want to perform for each maintenance execution.

DbM.exe reads those definitions and executes the various commands. A scheduler, for instance SQL Executive or SQL Server Agent, starts DbM.


Why Db Maint?

 Overview

Why Db Maint?

Ease of use

Our goal was to make Db Maint as easy to use as possible, but still powerful and flexible enough to fit most installations.

Edit defined Jobs and Tasks

Information about jobs and are stored in tables in SQL Server (msdb). You can open previously defined jobs and tasks and edit them.

Flexible and extensible task scheduling

A job can perform any of the predefined commands. You can also extend it with your own stored procedures, all in the same job definition. The stored procedure does not have to follow a certain pattern. If any messages are returned from the procedure execution, it will be logged.

The ability to define stored procedures along with your job definition gives you virtually endless functionality. Some examples:

  • Correcting identity values. A sample stored procedure (CheckId.sql) is available on the Db Maint web site (www.dbmaint.com).

  • Performing Bulk Copy (before or after execution).

  • Other batch processing.

Serial execution of tasks

All SQL Server installations need to maintain several databases (since you definitely should include your system databases). This poses some problems when using Database Maintenance Wizard/SQLMaint. SQLMaint is scheduled per database. To maintain several databases, you need to define several tasks, and estimate when the first task has finished, so you can schedule the next one, etc.

With Db Maint, one task contains several jobs, where one job define a certain database (or group of databases). All jobs within a task are executed serially.

Advanced logging

Your consistency checks are quite useless if you are not prompted when something has gone wrong in the database.

Db Maint can present its log in the Db Maint application log window, NT's Eventlog, through an email and to an HTML file. The log includes all messages from the commands, including SQL Servers error number so that you (or your support provider) have as much details about the situation as possible. All messages are categorized as INFORMATION, WARNING or ERROR.

Reporting of server and database configuration

Full report is available in Db Maint. The report can be exported to a text file or to Microsoft Excel. This is valuable for documentation purposes as well as for troubleshooting. A report can also be generated regularly though a Db Maint task.

Performance logging and presentation

Vital performance counters can be continuously logged. The logging is non-intrusive. Performance information is stored in a table and the format is very compact (one year of information, logged once an hour takes about 6MB of storage). This information is valuable for trend analysis and capacity planning. Db Maint client includes a graphical presentation of collected performance data. This information can also be exported to other tools, such as Microsoft Excel. Since the information is stored in a table, other tools can be used for analysis as well.

Grouping of databases

You will probably find that you have several databases for which you want to define similar maintenance routines. The Database Group functionality allows you to group several databases and add them as a group to a job.

Support for "All Databases"

Some installations have many databases. It would be a tedious task to define a job for each database. With the "All" support, you simply define one job for all databases and a task for that job. That is all you have to do. This will also include databases that are created after that job was created.

When using Database Groups or "all", msdb is run last

This is done automatically and is important since msdb contains information required to restore your databases easily. This information is stored in history tables and SQL Enterprise Manager reads those tables during restore. If msdb were not the last database in your backup schedule, you would risk restoring an unnecessarily old version of your database.

Note that this is per Job. If you have scheduled several Jobs within a Task, a later Job might backup a database after the Job which backuped msdb was run.

Database structure generation

(Relevant for 6.5 only)

When loading a SQL Server backup (dump), you need to create the database first. It should be created with the same data and log allocation structure.

Db Maint saves this structure with each backup and automatically presents the necessary CREATE DATABASE and ALTER DATABASE commands to re-create the database in a correct manner. Since this information is stored with each run, you can re-create a database correctly even if you expanded it after that backup was taken.

Db Maint uses predefined Backup Devices (Dump Devices)

This simplifies the task of checking that the backup devices really contain the dumps that they should.

Support for backup generations (to disk)

You can store generations of your backup files to disk. This can be very useful when you combine SQL Server backup to disk and machine backup to tape. This is a common scenario.

The archived backup files are also created as backup devices so that you can view them in SQL Enterprise Manager and in Db Maint. SQL Servers backuphistory tables will also be modified (6.5 only) if desired to point to the archived backup devices. This is to enable you to perform an automatic restore from within SQL Enterprise Manager.

The archived backup file name (and backup device name) is appended with a timestamp (yyyymmddhh) reflecting when they were archived.


System Requirements

 Overview

System Requirements

Client:

Windows NT 4.0 service pack 3 or later, x86.

Windows 95 or Windows 98.

SQL Server client installed (SQL-DMO).

In order to view Db Maint Books Online, you need Internet Explorer version 4.01, sp1.

Server:

Windows NT 4.0 service pack 3 or later, x86.

SQL Server 6.5. or 7.0

For more information regarding the SQL Server installation, see the System Requirements section in Server Installation .


Disclaimer

 Overview

Disclaimer

In no event shall Lan Assistans & Konsulting AB, Tibor Karaszi or Börje Carlsson be liable for damage of any kind. This includes loss of data, loss of profits, business interruption or other pecuniary loss arising directly or indirectly. Any liability of the seller will be exclusively limited to replacement of the product or refund of purchase price.


Trademarks

 Overview

Trademarks

Microsoft, Microsoft SQL Server and Windows are registered trademarks of Microsoft Corporation.


Client Installation

 Installation

Client Installation

Please check system requirements before installation. Before running Db Maint, make sure that the Db Maint server installation is completed on those servers that you wish to manage.

Before you begin

Before you start the Db Maint installation, make sure that you have the SQL Server Client installed. Check that you can run SQL Enterprise Manager on the client, which verifies the presence of SQL-DMO.

Installation

Run setup.exe from the subdirectory \X86\SQL65 on the installation CD.


Server Installation

 Installation

Server Installation

Before Installation

It is a good practice to backup all databases as well as the SQL Sever NT machine before installing any type of software. Also make sure that you have an updated Emergency Repair Disk.

System Requirements

Db Maint requires that the following extended stored procedures are available to sa for execution (they are available by default):

master..xp_cmdshell

master..xp_sendmail Only for email support

Db Maint also needs ISQL.EXE in the same path as SQLSERVR.EXE, which is there by default.

Installation

Make sure that you have restarted the machine after you installed SQL Server.

Run setup.exe from the subdirectory \X86\SQL65 on the installation CD.

After the setup program was executed, start the program “Db Maint – System Tables Administration” from the Db Maint program group. This program creates the Db Maint system tables.

The Db Maint client will notify you if you log in to a SQL Server where the Db Maint system tables are missing.

Alternative server installation

You might not want to install the whole Db Maint package on a server, or you might want to install the server components from some other machine. Follow these simple steps:

  1. Create or identify a directory on the server machine.

  2. Make sure that you are using a machine, on which you have installed both Db Maint server and client (this is a choice in the Db Maint installation program). This machine does not necessarily have to have SQL Server installed. All you need is the file dbm.exe.

  3. Copy the following files to the server machine (you might have to use “Find” in explorer to locate the files.: DBM.EXE, MSVBVM60.DLL, OLEAUT32.DLL, OLEPRO32.DLL, STDOLE2.TLB, ASYCFILT.DLL

  4. Run the Db Maint configuration program and log on to the server. Create the Db Maint tables, and make sure that you specify the path to dbm.exe.

System tables

Db Maint installs a number of system tables and stored procedures. If a table already exists with the same name, and was created by some other application, Db Maint will not run.

msdb..dbmsysdbgroups

msdb..dbmsysdiskspacewarningdisks

msdb..dbmsysjobs

msdb..dbmsyslog

msdb..dbmsysrun

msdb..dbmsysserverprop

msdb..dbmsystasks

msdb..dbmsystasksdetails (Only for 6.5)

msdb..dbmsysusages


Db Maint Configuration

 Installation

Db Maint Configuration

Welcome to the Db Maint Configuration program. This program creates, upgrades and removed the Db Maint system tables. Please refer to the Db Maint documentation for information on table and stored procedure names.

Introduction

When you login to a SQL Server at startup, a check will be performed to see if the Db Maint system tables exists in the msdb database. You will be presented a status in the textbox, describing if each table exists, and what version(s) of Db Maint the table is compatible with

The program will enable/disable choices, depending on the status of the current Db Maint system tables.

Select the desired option and press OK.

Unattended execution

Db Maint configuration can create tables in unattended mode. This is useful for scripting installation. The program accepts one command line switch: /P<password>, where password is the password for sa. The switch is case sensitive and the password must be entered immediate after the letter P. Example (assuming password is “Hello”):

DbmConf /PHello

DbmConf will log successful creation of the Db Maint tables to the NT Eventlog (with a source of “VBRuntime”).

If anything goes wrong during unattended execution (tables already exists, wrong password etc.), a message box will be presented even though the program is executed in unattended mode.

Check Version

If you logoff a SQL Server and logon to some other SQL Server, press “Check Version”, and Db Maint will perform a version check and enable/disable the radio buttons accordingly.

Drop old Db Maint stored procedures.

This option is only visible if you are upgrading from Db Maint 1.5. It will drop stored procedures that 1.5 were using. Db Maint does not install any stored procedures. You will be presented with a message box, which lists the name of the stored procedures that will be dropped.

Truncate Db Maint log.

This option will be presented if you are upgrading from Db Maint 1.5, and if Db Maint finds that you might be low on space in your transaction log.

OK

This is the button that actually performs the creation, upgrade or delete of the Db Maint system tables.

Create Db Maint tables (no Db Maint tables found)

This option will only be enabled if no Db Maint tables were found. Select this when you are installing Db Maint on an SQL Server for the first time.

Upgrade Db Maint tables from 1.5 to 2.0.

You will not loose your Db Maint configuration. It is strongly recommended that you run the Db Maint client and verify Server Properties, Job and Task settings, though.

Recreate tables.

This option will drop all the Db Maint tables and create them again. This is useful if you want to “scratch” your Db Maint installation.

Drop all Db Maint tables.

All Db Maint tables will be deleted from the msdb database.

Location of dbm.exe on server

Make sure that this textbox specifies the location of dbm.exe on the SQL Server machine. You can also configure this setting from the Db Maint client (Server Properties). You have the option to browse the tables, the file list dialog presents the files on the SQL Server machine.

Status

This textbox presents the status of the Db Maint tables in the msdb database.


Db Maint Files

 Installation

Db Maint Files

Dbmaint.exe

This is the client program.

Dbmaint.hlp

The help file in Winhelp4 format (.HLP).

Dbmaint.cnt

Contents file for the help file.

Dbmaint.chm

The help file in html-help format (.CHM).

Dbm.exe (server installation only)

The server program.

Dbmaint.inf

Quick setup configuration file.

Dbmconf.exe

This program creates, upgrades and removes the Db Maint system tables.

Dbm.htm (server installation only)

Only used so that a shortcut can be created to the Db Maint html log file.

Uninst.isu

Uninstallation file

Text files

Readme.txt

License.txt

Support files for quick setup

ExcludeFromAllGroup.sql

Perfcrea.sql

Sp_checkident.sql

Sp_warn_if_full_db.sql

Support files

Mfc42.dll

Msvcrt.dll

Dbmaint.dll

Perform.dll

Asycfilt.dll

Ctl3d32.dll

Comcat.dll

Oleaut32.dll

Olepro32.dll

Msvbvm60.dll

Comctl32.ocx

Msmask32.ocx

Comct232.ocx

Ssdatb32.ocx

Mschrt20.ocx

Mscomclt.ocx

Stdole2.tlb


Overview

 Using Db Maint

Overview

Db Maint is designed to be easy to use, yet powerful. To work efficiently with Db Maint, we recommend that you spend a few minutes to learn about basic terminology before diving into the product.

Job

A job defines certain actions to be performed against one or several database(s). These actions can include:

  • Consistency checks (DBCC's)

  • Backup

  • Reorganization/Defragmentation

  • Update Statistics

  • And almost anything else, with the support of Stored Procedures

You can also define that the job should terminate if an error is encountered.

Task

A task defines what job(s) to run and scheduling for that job(s). A task can include several jobs. You also specify where each jobs backup is to be taken (the backup device).

You have the option to send Email depending on the outcome of the job (Success and/or Fail).

The task is basically a superset of a task/job specified in SQL Enterprise Manager. Task information is stored in the same table as SQL Enterprise Manager tasks/jobs, but also in additional tables. You should therefore not remove a task created with Db Maint from SQL Enterprise Manager.

Scheduling

The scheduling is designed to work with SQL Executive (SQL Server Agent in version 7). SQL Executive is a service that (amongst other) can start execution of an EXE file (CmdExec).

This means that SQL Executive must be started on your SQL Server in order for your tasks to be executed.

However, you can also use a 3:rd party scheduler as long as you start execution of DbM.exe at the desired time. In order to avoid the task being executed twice, you can either disable the task or stop the SQLExecutive service. To use a 3:rd party scheduler, look up the task definition in SQL Enterprise Manager, and use the command line specified in the 3:rd party scheduler.

Database Groups

Database Groups lets you create a named group of several databases for which you want to define similar maintenance routines.

When you create a job, instead of specifying one database, you can specify a database group. All tasks relating to this group will perform its operations against all databases in the group.

Similarly, you can, for a Job, specify <all> databases, which quite simply mean that all databases will be included. This also includes databases that do not yet exist at that moment.

Note that msdb is always processed as the last database per Job. This is in order to include all other databases backup history in the msdb backup.


SQL Server Versions

 Using Db Maint

SQL Server Versions

Not all Db Maint features are available in all versions of SQL Server. Below is a list of the functionality supported, based on SQL Server version.

Version 6.5

Service pack 3 and 4

Full functionality

Released version, service pack 1 and 2

Maintain original free space when rebuilding indexes is not supported. Db Maint imposes this limitation, due to a known bug in SQL Server (which was fixed in sp3) which might corrupt the OrigFillFactor column in sysindexes when using a fillfactor of 0 with DBCC DBREINDEX.

Version 6.0

Db Maint 2.0 does not support SQL Server version 6.0. Please contact LAN Assistans & Konsulting AB if that support is desired. You can send an email through the Web site (www.dbmaint.com).

Version reference:

To determine your version, open a query window and execute the following SQL statement:

SELECT @@VERSION

7.0

Release 7.00.623

6.5

Release 6.50.201

Service pack 1 6.50.213

Service pack 2 6.50.240

Service pack 3 6.50.258

Service pack 4 6.50.281

Service pack 5 6.50.214 (avoid this due to a bug related to restore)

Service pack 5a 6.50.215

6.0

Release 6.00.121

Service pack 1 6.00.124

Service pack 2 6.00.139

Service pack 3 6.00.151


The Main Window

 Using Db Maint, Working with…

The Main Window

Task/Job Pane

This is the left region of the main window. Here you see the tasks and jobs that are created.

The top level contains the tasks. A task is never listed more than once.

The second level contains the jobs. The job is listed under each task that includes the Job. If the job is not included in any task, It is listed under "Job(s) with no associated task(s)".

The easiest way to work with tasks and jobs is by using the secondary mouse-button ("right-clicking") and choose from the context menu.

Add a new Task:

Right-click on the top level, press the Add Task toolbar button, or choose "New Task" from the Configure menu. This will present the Task Settings dialog.

Modify an existing Task:

Right click on an existing task and choose "Modify Task". This will present the Task Settings dialog.

Run Task:

Right-click on an existing task and choose "Run Task" to execute selected task.

Running Tasks:

Right-click on the top level or press the Running Task toolbar button. This will give you a list of all task(s) currently running.

Rename Task:

Right-click on an existing task and choose "Rename Task" to rename selected task.

Add a new Job:

Right-click on the second level, press the Add Job toolbar button, or choose "New Job" from the Configure menu. This will present the Job Definition dialog.

Modify an existing Job:

Right click on an existing task and choose "Modify Job". This will present the Job Definition dialog.

Rename Job:

Right click on an existing job and choose "Rename Job".

Db Maint Log Pane

This is the right region of the main window and presents the Db Maint log. Only the outcome of the task is shown here. To view all logged messages for a task, double click on a log row, which will present the Log Details dialog. The type column shows the most severe message produced by the task execution.

Alter the sorting order for the log pane:

From the View menu, select the desired sort order.

You can also click in the header of the log table to define sort order. The chosen sorting order is indicated by a plus (+) sign for descending order or a minus (-) sign for ascending order.

Define what to view from the errorlog:

From the View menu, select what you want to see, error, warning and/or information.

Note that error, warning and information are not mutually exclusive, you can choose more than one.

View error details:

Double-click on an error message to view the Log Details dialog.


Quick setup

 Using Db Maint, Working with…

Quick setup

Db Maint ships with a number of predefined maintenance routines. These are specified in the file dbmaint.inf. You add your own maintenance routines to this file. This feature makes it easy to deploy the same routine to a number of servers.

There are comments in the file, describing the various sections.

The following pre defined routine serves as an example of what can be achieved with the quick setup feature.

Database daily, transaction log every 4 hour

One database group with the name NoLogBackup is created. This includes the master, model and msdb databases.

A script is executed, which excludes the master, model, tempdb, pubs, northwind and msdb databases from the <all> group.

Two backup devices are created (UserDbBackup and SystemDbBackup).

Two stored procedures are created in the master database (sp_checkident and sp_warn_if_full_db).

Two jobs are created, one for system databases and one for user databases (WithLogBackup and NoLogBackup).

Two tasks are created, which performs database backup once a day, and transaction log backup every 4:th hour.


Login

 Using Db Maint, Working with…

Login

Both the client and the server need to login as sa (or an account with sysadmin privileges in version 7).

Client login

SQL Server

Name of the SQL Server.

LoginId

The loginId, must be sa (or an account with system administrator privileges for version 7)

Password

The password.

Use trusted connection

You can use this if your NT account system administrator privileges in SQL Server. This mapping is done through SQL Security Manager in version 6.5. By default, the Administrator group is mapped to sa.

Server Login:

The server uses a trusted connection. Since SQLExecutive/SQLServerAgent starts the server, it has system administrator privileges by default. If SQLExecutive/SQLServerAgent is using an NT user account, the account needs system administrator privileges as well.

If you are using a 3:rd party scheduler, make sure that the scheduler runs under an NT account that system administrator privileges.


Jobs

 Using Db Maint, Working with…

Jobs

The Job Definition dialog is used to define new jobs and to modify existing jobs.

Job Name

This is read-only for an existing job. An existing job can only be renamed in the main window.

For a new job, enter the job name here.

Database

Choose the database that you want to include in the job. You can also choose a Database Group , which will include all databases in the group for that job or <All>, which means that all databases will be included in the job.

  1. Pre-processing

Enter a name of a stored procedure to be run first. If the job contains several databases, this stored procedure will be run for each database.

This stored procedure should either exist in the database(s), or it should be a global stored procedure (created in master and prefixed with sp_). Maximum length for the procedure name is 30 characters.

  2. Data Verification, Check:

Choose which consistency checks to be run.

It is advisable to run as a complete consistency check as your time-window allows. This is so that you can catch possible inconsistencies as early as possible, and act accordingly.

See SQL Server Books Online for details about each DBCC command.

Data Linkage

DBCC CHECKDB, which checks that data and index pages are correctly linked.

Index linkage

Check this if you want the DBCC CHECKDB command to also check index linkage. If you have a tight time window, you can define a task to do index checking only once a week, for instance.

Data allocation:

DBCC NEWALLOC, which checks data and index pages against corresponding extent structures.

Index linkage:

Check this if you want the DBCC NEWALLOC command to also check index linkage. If you have a tight time window, you can define, for instance, a task to do index checking only once a week.

Text/Image allocation

DBCC TEXTALL, which checks the allocation of text and image columns.

System Tables

DBCC CHECKCATALOG, which checks consistency in the system tables.

Note:

Db Maint disables checkboxes that represent statements that are already covered. In version 7, for instance, you do not need to run CHECKALLOC if you run CHECKDB.

Run procedure after check

Enter a name of a stored procedure that is to be run after the DBCC section. This stored procedure should either exist in the database(s), or be a global stored procedure (created in master and prefixed with sp_). Maximum length for the procedure name is 30 characters.

Terminate on error

This will stop the execution if an error was encountered. Other databases within the job and other jobs within the task will still be executed.

  3. Backup

Do Backup

Check this if you want to perform backup for the database(s) included in this job.

Append on error

INIT (overwrite) will not be performed for a backup device if any integrity problems was found in the Data Verification section.

Run procedure after backup

Enter a name of a stored procedure that is to be run after the backup section. This stored procedure should either exist in the database(s), or be a global stored procedure (created in master and prefixed with sp_). Maximum length for the procedure name is 30 characters.

Terminate on error

This will stop the execution if an error was encountered. Other databases within the job and other jobs within the task will still be executed.

  4. Optimizations

Reorganize data

Runs DBCC DBREINDEX. Rebuilds all indexes for all tables in the database. This makes the leaf level of the index tree more continuous

Maintain original free space (service pack 3 and higher for 6.5) leaves the same amount of space in the leaf level of each index tree as when the index was originally built.

Change free space to __ % will leave specified amount of free space in the leaf level of the index tree.

Update index statistics

Runs UPDATE STATISTICS, which updates the distribution page for each index. SQL Server uses the distribution page to find an optimal execution path for your queries. This is not available if you choose Reorganize data, since DBCC DBREINDEX updates the distribution page.

Recalculate space usage

Runs DBCC UPDATEUSAGE, which updates information in the sysindexes table. This information is used by the various space usage reporting commands.

Run procedure after optimizations

Enter a name of a stored procedure that is to be run after the optimization section. This stored procedure should either exist in the database(s), or be a global stored procedure (created in master and prefixed with sp_). Maximum length for the procedure name is 30 characters.

Terminate on error

This will stop the execution if an error was encountered. Other databases within the job and other jobs within the task will still be executed.

  5. Post-processing

Enter a name of a stored procedure to be run last. If the Job contains several databases, this stored procedure will be run for each database.

This stored procedure should either exist in the database(s), or be a global stored procedure (created in master and prefixed with sp_). Maximum length for the procedure name is 30 characters.


Tasks

 Using Db Maint, Working with…

Tasks

The Task Settings dialog is used to define new tasks and modify existing tasks.

Task name

This is read-only for an existing task. An existing task can only be renamed in the main window.

For a new task, enter the task name here.

Scheduling frame

Note that a transaction log backup will always be performed before the database backup, if possible. The transaction log backup destination will be the archive backup device; if you have specified that backup generations will be saved. Otherwise, it will be the backup device chosen for the task.

Start time

This is the time when the task will start its execution.

End time

This is only enabled for jobs scheduled as Hourly. The start and end time specifies the time interval for which the task will be executed. You might want to exclude nighttime, for instance, if you are running batch operations during night.

Weekly

Choose this if you want the task to be executed once a week. The task will be started on the specified day and start time defines the time for the task to be executed. If any of the included jobs includes backup, database backup will be performed. The performed backup is described in the Backup frame.

Daily

The task will be executed once a day, at the specified start time.

Choose, in the backup frame, whether you want database backup to be performed each day, or database backup once a week with transaction log backup all other days.

Hourly

The task will be performed several times per day, as specified in the every __ hour box. The first execution is specified in the Start time box, and the last in the End time box.

Backup Frame

The top section of this frame presents information and options for the backup, as described in the Scheduling section above.

Save __ generations and Path

Affects only backup made to disk. A copy of the backup file will be taken before the backup is performed. This is only done at database backup, not log.

Specify the path where to the backup is to be copied. If you specify a directory on some other machine than where SQL Server is executing, enter an UNC name and make sure that the MSSQLServer service has read/write permissions to that share/directory. You cannot specify an UNC name if SQL Server runs under the LocalSystem account.

The backup file will retain its original name, but a timestamp will be appended to the name. The time stamp is in the format yyyymmddhh, hence the limitation of 20 characters when working with backup devices in Db Maint.

If chosen in the Server Properties dialog, the backup history tables in the msdb database dialog will be altered to point to the archived backup device (6.5 only). This is to enable the automatic restore dialog in SQL Enterprise Manager to find the backup device.

Ignore all job options except backup at...

Only backup will be performed for the included jobs when a transaction log backup is performed. Check this if you do not want DBCCs and reorgs to be performed too frequently.

Overwrite sub frame

Specifies that the backup will be taken with the WITH INIT option, which first erases the previous backups on that backup device.

Every database backup

Overwrites on each database backup occasion. It is vital that you somehow keep copies of the file/tape if you would need to restore from an older backup.

Never

Will never perform overwrite. Choose this if you have another task, which performs overwrite or performs the same operation yourself (either by moving the file to another media, or by rotating tapes).

Last <weekday> every month

Performs overwrite once a month, at a database backup execution. The weekday is chosen automatically if you backup the database weekly.

If you backup the database daily or hourly, choose the desired day.

Job selection frame

Available job(s)

Lists the jobs defined with Db Maint, which are not members of this task.

To add a job to this task, highlight the job and select the right-arrow button, or double-click on the job. The job will disappear from the Available jobs dialog and appear the Included job(s) list.

To rearrange the order, in which the tasks are processed, highlight a job and press the arrow up or down button.

Choose a backup device for each task by positioning the cursor over the Backup Device column and single click. This is the destination for the backup. For jobs that do not include backup, you will be presented an informational message and you are not allowed to choose a backup device for that job.

Options frame

Enabled

Uncheck this if you do not want SQL Executive to execute your task. This could be useful if you want to keep your task definition but disable it for a period. This will not have any effect if you use a 3:rd party scheduler.

Disconnects all users

A KILL command will be issued for all connections in each database that the task affects.

Unload tape

Affects only backup taken to tape. This will unload (eject) the tape after backup is performed.

Check diskspace

Check this if you want Db Maint to check that there is an amount of disk space available. Configure the amount of space available and the disks in the Server Properties dialog. If the configured amount of disk space is not available, a warning message will be written to the Db Maint log.

Check backupstatus

If you check this, Db Maint will check if a backup has been taken within a configured amount of days. Configure this amount of days in the Server Properties dialog. If backup has not been taken within the configured amount of days, a warning message will be written to the Db Maint log.

Collect performance information

If you check this, Db Maint will collect and store performance information each time the task is executed. Generally, you only want one task to collect performance information. This is not available unless you have configured a database for performance information to be stored in. The collected performance statistics is viewed with the Performance Viewer dialog.

Server Report

Check this if you want a file containing the server configuration where the task is executed. Configure the file name and location in the Server Properties dialog.

Log options sub frame

Specify if Db Maint should log messages to the various locations. Specify properties for the log destinations and if the log destination should be available in the Server Properties dialog.


Database Groups

 Using Db Maint, Working with…

Database Groups

Database groups allows you to group a number of databases and include that group in a job specification. This can be useful if you want to perform the same maintenance routine for several databases.

Database Group

Choose an existing group if you want to change the members in the groups.

Included databases and Available databases

Use these two lists to add/remove members to/from the group.

Save

Saves the edited group.

Save As

Saves the group specification and lets you specify a name for the group.

Delete group

Deletes the group name chosen in the Database Group dropdown box.


Backup Devices

 Using Db Maint, Working with…

Backup Devices

A backup device is the destination for a database or a log backup. It is basically a pointer to a file or an installed tape device.

Available devices

This is a list of the installed backup devices.

Show contents

Populates the "Contents on backup device" list. This is a list of dumps included on this backup device. Reading the backup device (LOAD HEADERONLY) assembles the list. This can take a while for a tape backup device. Please refer to the LOAD HEADERONLY command in Books Online for explanation of the columns in the list.

Note: SQL Server 6.5 Service pack 2 introduced a bug when reporting size for each member in the backup device. The first members' size is reported as the second members' size, the second members' size is reported as the third members' size etc. The last members' size is not reported at all (the size can be found in SQL Servers errorlog or the Db Maint log).

Check Space on Hard Drive

Displays available space on the hard drive where the backup device is residing. This is only available for local disk backup devices.

Remove file when deleting backupdevice

Check this if you want the physical file to be removed when deleting a backup device.

New

Displays the New Backup Device dialog.

Delete

Deletes the selected backup device.

Note that Db Maint limits the device name to 20 characters in order for a timestamp to be appended when creating backup generations.


New Backup Device

 Using Db Maint, Working with…

New Backup Device

Name

Enter the name for the new backup device. This name must adhere to SQL Servers rules for identifiers. An additional limit is maximum 20 characters in order to be able to append a timestamp when creating backup generations.

Backup devices can also be administered from SQL Enterprise Manager. If you use SQL Enterprise Manager to add backup devices, do not use more than 20 characters for the device name.

Location

Enter the physical name for the backup device. This can be:

  • A local path

  • An UNC name. The MSSQLServer service must have read/write permissions to the share and directory. You cannot specify an UNC name if SQL Server runs under the LocalSystem account.

  • Name of an installed tape device.

Type

Specifies the type: disk or tape.

Skip Headers (tape only)

Specifies that ANSI tape labels should be ignored (SKIP). See SQL Server Books Online, sp_addumpdevice, for further discussion.


Backuphistory

 Using Db Maint, Working with…

Backuphistory

Most recent backup (from backup history tables)

This table presents the most recent database and transaction log backup taken. The information is drawn from the backup history tables in the msdb database, so the backup might have been overwritten

Backup device information

This is what the backup devices (files or tapes) contain. By default, only the files are presented. To display tape(s) as well, press the Include information for tape devices button.

Export options

You can export this information to a file. Check what information you want to export and press the Export button.

Include information for tape devices

Contents on tape devices will not be shown unless you press this button. This is because it can take a while to read the backup header form the tape(s).


Server Configuration

 Using Db Maint, Working with…

Server Configuration

This dialog provides a comprehensive report of the server configuration. We recommend that you save this report so it is available for crash recovery and trouble shooting purposes. It is advisable to create a report after each time SQL Server or a database is reconfigured. You can also specify that a task will create a file each execution with this information.

General Configuration tab

This is general server configuration information: hardware, operating system, SQL Server options chosen at install time, SQL Server options that are configurable.

Server Parameters tab

This is the server parameters. They are set with the sp_configure procedure or SQL Enterprise Manager, Server, Configure, Configuration.

Database configuration Tab

General database configuration information. Choose the desired database from the Database drop-down box.

Export options frame

The information can be exported to a text file. Choose what information to include in the report.


Performance Viewer

 Using Db Maint, Working with…

Performance Viewer

This dialog presents the performance statistics information saved by the Db Maint server (dbm.exe). In order to have any performance statistics, you must:

  1. Select a database in the Performance Configuration dialog.

  2. Create a task including Collect performance information, which actually collects the performance statistics. You find information about the counters in the Performance Counters section.

Information presented here is saved statistics. Use SQL Performance Monitor if you want to look at the current statistics.

Log start date and Log end date

These are the earliest and latest date that statistics has been collected. This is the available time-window.

View average over period

An average will be displays for the selected period. For instance, you want to see the daily load for a certain period.

Select the counters to display, the period, and what average (day, week, month or year) you want to display. The x-axis resolution is automatically selected for you.

View interval

Choose the selected period.

Choose if you want to view by hour, date, month or year.

Press Calculate period, which calculates the amount of time each x-axis represents. For instance, if you have a long period, the diagram will be very cluttered if you want to have each x-axis to represent one hour. The amount of time that each x-axis represents is also dependent on how you set x-axis resolution. The value shown for each time is the average for all the measurements during the period. If no measurement was done during the period, a value of 0 will be presented.

Counter selection frame

Choose the counters that you want the diagram to present. Do not choose too many counters, it will take a long time to draw the graph and the graph will be cluttered.

Exclude frame

You can exclude a range of hours for selected days. You might, for instance, want to exclude the timespan for which batch processing occurs.

Graph option frame

Select the type of diagram to display.

You can also set the number of measure point to display (x-axis resolution).

Copy button

Copies the chart to the clipboard. The chart is copied as both tabular text information as well as the graph (as a Windows meta file).

It is up to the destination application to decide what (text or graph) to paste. Some applications have a paste special feature, which allow you control if you want to paste text or graph.


Performance Configuration

 Using Db Maint, Working with…

Performance Configuration

The configuration of performance logging is necessary if you want to collect performance information.

Database

Specify in which database you want to store performance information. Before you enter this dialog you must create a database (Using the CREATE DATABASE statement or SQL Enterprise Manager). The maximum length for the database name is 30 characters. The size of the database depends on how often you want to collect data. A database size of 20 MB and a transaction log of 5 MB is enough to begin with.

The database chosen should not be a system database or used by other applications. We recommend that you create a database solely for this purpose.

Create tables in selected database

Installs performance collection table on the server. Before Db Maint can start collection performance information, you have to include performance collection in one task. A recommended interval for the task is between once per hour and once per day.

Drop tables

Removes performance collection tables from the server.


Performance Counters

 Using Db Maint, Working with…

Performance Counters

Note:

Db Maint clears all counters each time it is run. The performance collection Task should be scheduled regularly, so that the accumulative counters has been measured for the same amount of time. Not all counter are available for all versions of Db Maint.

I/O Statistics (IOSTATS)

Log Flush Requests (Accumulative)

Number of requests to flush the log to disk.

Log Logical Page IO (Accumulative)

Logical page I/O for transaction log.

Log Physical IO (Accumulative)

Physical I/O for transaction log.

Log Flush Average (Average)

Log flush requests per physical write.

Log Logical IO Average (Average)

Logical pages written per physical write for transaction log.

Batch Writes (Accumulative)

The number of times multiple pages were written to disk in a batch.

Batch Average Size (Average)

The average number of pages written in a batch.

Batch Max Size (Maximum)

The maximum number of simultaneous outstanding physical I/Os.

Page Reads (Accumulative)

The number of 2K pages read from disk (physical reads).

Reads Outstanding (Current)

The number of read requests issued to the operating system that has not been completed.

Writes Outstanding (Current)

The number of write requests issued to the operating system that has not completed.

Transactions (Accumulative)

The number of Transact-SQL batches executed. This value is useful in computing ratios involving log write numbers.

Transactions/Log Write (Average)

The number of transaction records written to disk during each physical log write.

Cache Statistics (LRUSTATS)

Cache Hit Ratio (Average)

The percentage of times a data page was found in the cache.

Free Page Scan (Avg)

The average number of buffer pages that had to be scanned in order to find a free page.

Free Page Scan (Max)

The maximum number of buffer pages that had to be scanned in order to find a free page.

Min Free Buffers (Configured)

The lazy writer will attempt to maintain at least this number of buffers on the free list.

Cache Size (Current)

The total number of pages in the cache.

Free Buffers (Current)

The number of buffers currently on the free list.

Network Statistics (NETSTATS)

Network Reads (Accumulative)

The total number of reads from the network.

Network Writes (Accumulative)

The total number of writes to the network.

Max Command Queue Length (Maximum)

The maximum number of client requests waiting to be handled by the SQL Server worker threads.

Worker Threads (Current)

The current number of worker threads servicing the command queue.

Max Worker Threads (Maximum)

The highest number of worker threads that serviced the command queue since the network application was started or since the statistics were cleared.

Read Ahead Statistics (RASTATS)

RA Pages Found in Cache (Accumulative)

How many pages the RA Manager found already in the cache when trying to perform scans.

RA Pages Placed in Cache (Accumulative)

How many pages were brought into the cache by the RA Manager.

RA Physical IO (Accumulative)

How many 16K reads were done by the RA Manager.

Used Slots (Current)

How many RA slots are being used by active queries. Note that a single query may use multiple RA slots.


Email Operators

 Using Db Maint, Working with…

Email Operators

This is a basic dialog for defining SQL Server email operators. A more advanced dialog is found in SQL Enterprise Manager.

Defined email operators

This is a list of the defined email operators. The friendly name as well as the email address is shown.

Name:

A friendly name that you use to address this operator within SQL Enterprise Manager and Db Maint.

Email address:

The actual email address for this email operator.

Test

A test message will be sent to the selected email operator.


Server Properties

 Using Db Maint, Working with…

Server Properties

Server properties define the behavior of the server, regardless of which client is using it.

Location of dbm.exe on server

Enter the path on the SQL Server machine where Db Maint is installed. Do not enter the file name itself (dbm.exe). A check will be made that the path is valid.

Location of Server report file on server

Enter the path where the server report file should be created. There is an option in the task dialog to create a server report on each execution. This information can come in handy in case you have to reinstall SQL server and in troubleshooting situations.

Max number of backup device generations

This is the maximum number of device generations allowed. The number to use is defined in each task, in order not to over-specify; you can set a general maximum. If you lower this value and you have tasks that are defined with a higher value, you will get a warning presented with the option to change those tasks.

Log Settings

Max No of rows in Db Maint log

This is the maximum number of rows in msdb..dbmsyslog table. Each time dbm.exe is executing a task; it will truncate that log to this number.

Enable SNMP (SNMP support id only available for 6.5)

Check this if you want to be able to specify that a task can generate an SNMP trap at execution time.

Log to HTML

Check this if you want to be able to specify that a task can generate an HTML file with task information at execution time.

Path to HTML-file (from server)

This is where the HTML file should be created. You can specify an UNC name (possibly on a directory published by a Web server). The MSSQLServer service needs write permissions to that directory. Do not specify an UNC name if SQL Server runs under the LocalSystem account.

Printer name

Enter the name of the printer, if any, that Db Maint should be able to log to. This printer must be configured on the SQL Server machine, using the same NT account that the MSSQLServer service is using.

Master log server name

Enter the name of the server name that Db Maint should log to. This option gives you the ability to log information to a central server, which makes supervising several servers easier. SQLExecutive/SQLServerAgent must be able to log on to that server with a trusted connection.

Email operator

Specify the name of the email operator that emails should be sent to.

System backup tables

Allow Db Maint to modify msdb..sysbackupdetail

When backup generations are created, the sysbackupdetail table will become "out of sync". This has the effect that automatic restore from SQL Enterprise Manager will not find those backup devices. Check this if you want DbM.exe to modify sysbackupdetail when creating archives so that it points to the archived backup device.

Truncate tables to __ rows (0 = do not truncate)

SQL Server keeps backuphistory in the sysbackuphistory and sysbackupdetail tables. SQL Server never truncates these tables. In order to avoid filling the msdb database, you have an option to let DbM.exe truncate those tables, saving only the amount of history (in days) that you choose.

The tables will be truncated on each Db Maint task execution.

Specify 0 if you do not want DbM to truncate these tables.

Disk space tracking

Specify the minimum amount of disk space to be available, and the disks to check. DbM.exe will check and generate a warning unless this amount of disk space is available. You must choose Check diskspace in the tasks for this check to occur.

Database backup tracking

Specify the number of days that may have elapsed, without a backup is performed for a database, before Db Maint generates a warning message in the log. You must choose Check backupstatus in the tasks for this check to occur.

Exclude databases from "all" group.

If a job is defined over the <all> group, all databases that exist at the execution time will be included in the job. You might want to exclude some databases from the <all> group.

Exclude databases from transaction backup warning

Db Maint will generate a warning message if you try to perform a transaction log backup for a database and the transaction log backup cannot be performed. This warning is useful, since the database option Truncate log on checkpoint or Select into/bulkcopy might be set for some database by mistake.

Choose the databases for which you do not want such a warning to be generated.

For 6.5, you probably want to include the master and model database in this group.


Client Properties

 Using Db Maint, Working with…

Client Properties

These settings control what the Db Maint client is to display in its log pane in the main window. This information is stored in the client computer registry and is user-dependent.

Display last __ days

Db Maint will only display the specified number of days in the log pane.

Display last __ rows

Db Maint will only display the specified number of rows in the log pane.

Available Tasks and Exclude tasks from view

You can choose to exclude tasks from the Db Maint log window. This is useful for tasks that are run often, but are not important (for instance a task that collects performance data each hour).

Login timeout

Specify timeout (in seconds) before Db Maint presents an error message at connect time, if a server is not found. You might want to increase the value if you have a slow connection to the SQL Server machine.


Log Options

 Using Db Maint, Working with…

Log Options

Delete log frame

Delete entire log

This option will delete all rows in the Db Maint log.

Delete entries older than __ days

This will delete all entries older than the specified number of days

Delete all tasks named __

Chose the task name for which you will delete all rows.

Delete all tasks of error type __

Chose the type (INFORMATION, WARNING, ERROR) for which you want to delete all rows.

To perform the delete, press the Delete button.

Note that the Db Maint server (dbm.exe) can also truncate the log at each execution. This is configurable in the Server Properties dialog.

Export log frame

You can choose to export the Db Maint log to a text file. All rows in the table (msdb..dbmsyslog) will be exported to the text file.


Log Details

 Using Db Maint, Working with…

Log Details

The log details dialog presents information of a selected log entry.

Database

The database for which the message was generated.

Starttime

The time when the message was generated.

Jobname

The name of the job for which the message was inserted.

Command

The SQL command which generated the message.

Errortype

INFORMATION, WARNING or ERROR. For messages generated by SQL Server this classifications is based on the same as SQL Server uses:

Severitylevel < 15 will be written as INFORMATION

Severitylevel = 15 will be written as WARNING

Severitylevel < 15 will be written as ERROR

Severity

The SQL Server severity level generated (if any).

Error

The SQL Server error number generated (if any).

Error desc

The actual SQL Server error message (if any). Note that only the first 255 characters are stored.

Export options

You can export this information to a text file. This is useful for problem determination purposes. This information can be save and sent to your support provider, if necessary. Choose what to be included in the report and press the Export button.

View DB Structure (only for 6.5)

Presents the data and log allocation structure at the time he task was run. Each run will save the structure to the msdb..dbmsysusages table. The structure is presented as a series of CREATE DATABASE, ALTER DATABASE and sp_logdevice commands.

This information is useful when you have to recreate a database in order to load a backup.

Choose the applicable database in the list and press the Database structure button.


Backup

 More about database maintenance

Backup

Note: please refer to SQL Server Books Online for more information regarding this topic.

The reason for taking backup will not be discussed here: it is obvious.

Backup schemes

Basically, all backup schemes looks pretty much the same with variations depending on economics, amount of data and how much the daily usage of the database can be disturbed. Backup of SQL Server is online, but performance during backup will suffer (improvements were made with 7.0).

Take backup as often as possible (sorry for stating the obvious). This includes both database and log backups.

Backing up to disk, using disk generations (daily or weekly)

When the database backup occurs, the old backup device will be copied to another name (both logical and physical) with a timestamp appended. Make sure to pick up those files (along with the active backup device) and copy them to another media (disk or tape).

This is a good solution if you can spare the disk space and want to do a full backup of your machine with some NT backup software.

If the task include transaction log backup, a log backup will be taken to the archive, after the archive is created. This is to achieve Point-in-time recovery .

Backing up to disk, without disk generations

Database backup will be taken at the specified time, as will the log. It is important to copy the files to another media (disk or tape).

Backing up to tape (backup generations not supported)

Make sure that you have proper rotation of the tapes. You should also handle INIT of your tapes. How you do this depends on how you rotate your tapes. You have the option to INIT (overwrite) on each database backup, once a month, or never.

If you never perform INIT, re-using a tape will eventually fill it up.

Log handling during backup

If your task includes log backups and databases for which the log cannot be backup separately, Db Maint will perform a database backup on each log occasion, and present a warning in the log.

Db Maint tries to check if it cannot backup the log separately:

  • Log is not on separate database device (6.5 only).

  • Database option "trunc. log on chkpt" is on

  • Database option "select into/bulkcopy" is on

There are situations, which cannot be detected by Db Maint:

  • Non-logged operation has occurred since last database backup, but "select into/bulkcopy" has been turned off again.

  • A database backup was never performed.

If any of the later situations occur during a log schedule, Db Maint tries to perform a log backup and an error message will be presented in the log.

Backing up to multiple backup devices in the same task

Each backup device will be handled separately, in the same manner as if it was the only backup device in the task.

Mixing backup media in the same task

You can mix both disk and tape backup devices in the same task. Generations will still be used (if specified) for the disk devices.

Backing up multiple databases to the same backup device in the same task

If it is time for an INIT, it will only be performed for the first backup that references that backup device. Otherwise, the last one would overwrite all previous.


Restore

 More about database maintenance

Restore

Note: please refer to SQL Server Books Online for more information regarding this topic.

There is no restore functionality in Db Maint, and the reason is simple: SQL Enterprise Manager has excellent restore functions.

Overview of restore

To restore a database backup, you use the LOAD/BACKUP DATABASE command. To restore a log backup, use the LOAD TRANSACTION/RESTORE LOG command. The log backups have to be restored in the same sequence as they were created.

If you perform a non-logged operation, you cannot continue to perform log backups. You must set the database option "truncate log on checkpoint" to be able to perform a non-logged operation, so dbo has control when database backups should to be taken.

Restore in SQL Enterprise Manager

SQL Enterprise Managers Backup/Restore dialog helps to perform restore. It does so by reading the backup history tables in the msdb database.

The dialog presents a number of database and transaction backups, and defaults to the most recent database and transaction log backups.

If you choose Restore Now, the tables will be read and the restores will be executed, in sequence.

Note, however, that the backup tables do not necessarily match what's on the actual backup device(s). If you have truncated (by mistake, possibly) a backup device, the corresponding backup will still be presented in this dialog.

Db Maint will automatically update the backup history tables (if chosen in the Server properties dialog) when it creates backup generations. This is so the corresponding entries still points to the correct backup device. So you can still use the SQL Enterprise Manager Restore dialog to restore from archived backup files. Even if you archive them on a tape they will be useful, as long as you restore the files from tape to the correct directory.


Consistency Check

 More about database maintenance

Consistency Check

Note: please refer to SQL Server Books Online for more information regarding this topic.

Consistency check should be performed periodically against your data. If, for some reason, some inconsistency occurs, you would want to know it as soon as possible. Worst case would be to restore the most recent clean backup.

Unfortunately, there have been installations where a corruption was found in the database, but since no consistency checks have ever been run, no clean backup existed. The end to this is generally to try to get the data out from the database (by BCP or other tool) to salvage as much data as possible.

Db Maint will perform the various consistency check commands, DBCC's, (if chosen in the job) and it will log all messages returned from the commands.

It is vital that you act upon an error or warning message as soon as possible. It might not be a problem, but it is best to make sure. See If an error occurs for tips.


Optimizations

 More about database maintenance

Optimizations

Note: please refer to SQL Server Books Online for more information regarding this topic.

Performance in a Relational Database Management System, like Microsoft SQL Server, is a large topic. It involves issues such as:

  • The database design (logical and physical)

  • Query formulation

  • Indexing

  • Tuning

There exist a number of papers, books and courses regarding performance. Db Maint has a section in the job dialog that performs a very small amount of what can be done to achieve good performance.

Re-building indexes

An index is sorted in a semi-physical sequence. This applies to the actual data pages for clustered indexes.

When the table (or index) is created, SQL Server allocates an extent (8 pages) for the table. This extent is continuous on the database device. As the table grows, new extents are allocated (not necessarily next to the old one, see SQL Server Books Online for more details).

If an insert has to be made in sequence, the page chain still has to be in order. This applies at the data level if a clustered index exists, and for all non-clustered indexes. A new page has to be allocated, but the new page might be on another extent. So when scanning the table, a lot of extent switches have to be made. This increases the disk head movement and is also makes the Read-ahead process less efficient.

When rows are deleted (and updated), gaps are left on the pages, and eventually pages are de-allocated. These pages will be reused, but still not necessarily in sequence.

Whether this is a major issue or not depends on the indexing scheme and the read and update pattern against the table.

By rebuilding the index, the page chain is re-sorted to make it physically continuous again.

By specifying that some free spaces are to be left on each page, future inserts can still be made in physical continuos order (no page-split). SQL Server will not maintain this free space, so rebuilding indexes with a fillfactor regularly might be a good idea.

See SQL Server Books Online, DBCC SHOWCONTIG for information regarding how to check "fragmentation" level for a table/index.

Update index statistics

SQL Servers optimizer depends on statistics when it tries to calculate good access paths for your queries. This statistics is stored in each index' Distribution Page.

The distribution page is not continuously updated in version 6.5. In version 7, statistics will be updated at query execution time, if the optimizer find that the statistics are old, but SQL Server will not scan the entire table to build the statistics..

There is no need to update statistics if you re-built indexes.


Managing the Transaction Log

 More about database maintenance

Managing the Transaction Log

Overview

Note: please refer to SQL Server Books Online for more information regarding this topic.

All changes made to a database are recorded in the transaction log.

This information is used:

  • In the event a Rollback has to be performed.

  • Building the logical Inserted and Deleted tables, which are presented in triggers.

  • The replication process.

  • To take incremental backup of the database (DUMP TRANSACTION/BACKUP LOG).

The log would fill up after a while if it were not truncated. The error message from SQL Server version 6.5 when there is no more space in a database is:

1105

Can not allocate space for object '%.*s' in database '%.*s' because the '%.*s' segment is full. If you ran out of space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment.

If the object name is syslogs, then the transaction log is full. If you have not separated the log, this simply means that the database is full because all page allocations go to the same device(s).

Do not use sp_extendsegment to extend the segment. This should only be used if you have created user defined segments, and for user-created tables.

The error message for version 7 is:

The log file for database '%.*ls' is full. Back up the transaction log for the database to free up some log space.

Preventing a full transaction log.

Regardless of which method you choose to truncate the log, SQL Server will not truncate past the first open transaction (active "commands") or past transactions in the log that still hasn't been replicated to the Distribution database (when using replication). You can check if you have open transaction with the DBCC OPENTRAN command.

The "Trunc. log on chkpt." database option

When this option is set, you cannot perform transaction log backups.

SQL Server will truncate the log each time the checkpoint process wakes up, which is once a minute.

Perform continuous backup of the transaction log

This is the preferred method of truncating the transaction log. Since you perform incremental backup, you risk losing less data in a crash situation

Batch operations

It is not unusual for the log to fill up during batch operations. If possible, perform DUMP TRANSACTION/BACKUP LOG within your batch and/or use non-logged operations (fast BCP).

To recover from a full transaction log

If the log is 100% full you cannot truncate it by backing it up, since the backup has to be recorded in the transaction log. For version 6.5, use:

DUMP TRANSACTION <dbname> WITH NO_LOG

And for version 7:

BACKUP LOG <dbname> WITH NO_LOG

Since you cannot continue to perform transaction log backup after this command has been executed, you should perform a database backup.


In an error occurs

 More about database maintenance

If an error occurs

Below follows a few tips of what to do if, for instance, DBCC reports some sort of data corruption problem.

The tips should not be processed in any particular order. The action plan is always depending on the error etc. Do not perform the action unless you are confident with what you are doing. Some tips can make things worse in some situations.

Record the error number, severity level and the message string

This is found in Db Maints’ various logs.

Search for the error number in Books Online

It is very easy to search in SQL Server Books Online for a particular error. Just enter the error number in the query window.

The recommendations from SQL Server Books Online are generally the best recommendations to follow.

Search for the error number on Microsoft’s Knowledge Base

This is found on Microsoft’s Web and on Microsoft’s TechNet CD.

Contact your support provider

It is likely that they have a good recommendation for you. Even if the call will cost you money, it will still be inexpensive compared to losing all data (or loading an unnecessarily old backup).

Ask for tips on the Newsgroups

Msnews.microsoft.com is Microsoft’s news server and it has a number of SQL Server related discussions areas.

It is likely that you will receive good advice here. Remember to always be as specific as possible regarding your problem when asking for advice.

Note: All advice is not good advice. Remember that you (probably) do not know the person who advises you. That person does not know about your installation, or might quite simply give you bad advice. Always verify advice you get from the newsgroup.

Drop the index

If the problem only occurs in an index, you might get rid of it by simply dropping the index (and re-creating it).

Try to "get the data out"

If you have, for instance, a linkage problem in a table, you might be able to save the data in the table. You can try to use INSERT with a subselect, SELECT INTO, BCP or even Transfer Manager to copy the data to another table/database.

Stop and restart SQL Server

You should only do this if suggested by Books Online.

Some errors are "transient errors"; i.e. they only occur in the cache, not on disk.

Turn of the "Suspect Flag", or turn on "Emergency Mode"

This is useful if the database is inaccessible, and you want to get to the data so you can use BCP or Transfer Management Interface to copy out the data.

This is not a way to repair the database. By turning of the suspect flag, you will only get rid of the symptom, not the cause. SQL Server (probably) turned on the suspect flag for some good reason.


The Client

 Technical Details

The Client

DBMAINT.EXE

The Clients task is to collect information from the user and update the Db Maint tables in the msdb database.

It uses SQL-DMO (an OLE based object model) as the programming interface.

Command-line switches

/?

Displays available command-line switches.

All other strings are used as default server name in the Login dialog.


The Server

 Technical Details

The Server

DBM.EXE

The server's task is to read the Db Maint tables in msdb and execute the various specified commands.

The information given here is provided to give advanced users a better understanding of the way Dbm works, which can help in troubleshooting.

Dbm is started by a scheduler. For more information about scheduling, see More about Scheduling .

Dbm uses SQL-DMO. It uses a Client-Requested Trusted Connection, so the account that your scheduler uses must be mapped to sa (or an account with sysadmin privileges in 7.0).

Command-line switches

Note: the output from command-line switches is found in the NT Event Log, Applications.

/? Or /v

Displays available command-line switches.

nn

Where nn is a number. This is the ID for the task to execute.

Command execution

With SQL-DMO, there are several options for executing commands. Dbm uses two ways:

The ability to execute a SQL Query through SQL-DMO

Methods used are ExecuteImmediate and ExecuteWithResults.

This is used for basic queries, where we anticipate that not very much can go wrong, like reading the various tables in msdb.

Using ISQL through xp_cmdshell

The SQL-DMO method used is CommandShellWithResults.

You might find it odd to let SQL Server execute ISQL with a query, but we have basically two reasons for doing it:

  1. Stability: If something goes wrong during the execution of a command, only ISQL will be affected, not Dbm.

  2. Logging: SQL-DMO does not provide the functionality to retrieve all messages and error numbers that a command can return.

DBCC commands, for instance, can find several inconsistencies in a database and will return all of them. Sometimes they are returned as resultsets, sometimes as error messages, and sometimes as informational messages.

By using ISQL, we can catch them all and log the returned information in a consistent way.

This is also an advantage when using stored procedures. The procedure does not have to adhere to any particular rule. If it returns errors, resultsets or messages, it will be logged.


More about Scheduling

 Technical Details

More about Scheduling

Dbm is quite simply started at the time specified by the task. It reads the specified command-line argument, which specifies the task id to execute.

Using a 3:rd party scheduler

If you want to use some other scheduler than SQLExecutive/SQLServerAgent:

  1. Extract the taskid. The taskid is presented in the task dialog.

  2. Schedule the task with your scheduler. Make sure that you specify the same scheduling interval that SQLExecutive has. Enter the taskid as the (sole) command-line switch. Example:

DBM 37

  3. Disable the execution made by SQL Executive/SQLServerAgent. Either make sure that SQLExecutive/SQLServerAgent is not started, or clear the Enable checkbox for the task in SQL Enterprise Manager or Db Maint.


Directory and File browser dialog

Depending on what you are about to specify, this dialog can have the file list enabled or not. If you are about to specify a directory, the file list is disabled. If you are requested a file, it is enabled.

Note: The dialog read the directory structure on the SQL Server machine. This is done through the hidden shares C$, D$. These shares must exist in order for this dialog to work.


Job_Description

 Job

A job defines what actions to perform, backup, reorganizations, consistency checks etc.


New Database Group

Specify the name that you want this group to be saved as.


Point-in-time recovery_Description

 Point-in-time recovery

Point-in-time allows you to specify a date and time when the transaction log restore should stop. This is useful if you know that some error was introduced at a certain time and let you load up until that time.


Task_Description

 Task

The task is the unit of scheduling/execution. It includes one or several job(s), backup attributes, etc.