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.

A scheduler starts DbM. For more information about scheduling, see More about Scheduling.

DbM uses SQL-DMO. It uses a “Client-Requested Trusted Connection” (the log on is performed using an “NT Authentication” also called a “Windows login”), so the account that your scheduler uses must be mapped to sa (or an account with sysadmin privileges in 7.0 and later versions).

Command-line switches

Note: the output from command-line switches is found in the NT Event Log, Applications. As of Db Maint 3.0, a server name is used as the second switch. This is necessary because SQL Server 2000 supports several instances, one default instance and up to 15 named instances.

Syntax:

dbm.exe /?|/v|nn [Server Name[\Instance Name]]

/? Or /v

Displays available command-line switches.

nn

Where nn is a number (exemplified as 6 in below example). This is the ID for the task to execute.

[Server Name[\Instance Name]]

This is the SQL Server to connect to. The Db Maint server only supports local SQL Servers (dbm.exe must be installed on the SQL Server machine). The instance name part should only be specified for SQL Server 2000 and later, for non-default instances. Server name must be specified, unless the /? Or /v switch are used.

An invocation of dbm.exe (as executed by the scheduler) might look like:

C:\Program Files\Db Maint 3.00\dbm.exe 6 SRVR\INSTANCE2

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 or OSQL through xp_cmdshell

The SQL-DMO method used is CommandShellWithResults.

You might find it odd to let SQL Server execute ISQL/OSQL 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/OSQL 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 result sets, sometimes as error messages, and sometimes as informational messages.

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

This is also an advantage for user defined TSQL commands. The commands do not have to adhere to any particular rule. If they return errors, result sets or messages, it will be logged.