Managing the Enterprise

Updated : July 19, 2001

Enterprise Manager is the primary tool you'll use to manage database servers. Other tools you may want to use to manage local and remote servers are SQL Server Service Manager, Performance Monitor, and Event Viewer. You'll use Service Manager to manage SQL-related services, such as MSSQLServer and SQLServerAgent. You'll use Performance Monitor to track SQL Server activity and performance. You'll use Event Viewer to examine events generated by SQL Server, which can provide helpful details on problems. This chapter covers Enterprise Manager and Service Manager. For details on Performance Monitor and Event Viewer, see Chapter 10.

Note: SQL Server 2000 restricts permissions for the Mssql directory (either Mssql or mssql$instancename). Only the local Administrators group and the user account specified to start the SQL Server service instance have access to the Mssql folder and its subdirectories.

On This Page

Starting, Stopping, and Pausing SQL Server
Using SQL Server Enterprise Manager
Managing SQL Server Groups
Managing Servers
Starting, Stopping, and Configuring SQL Server Agent
Starting, Stopping, and Configuring the Microsoft Distributed Transaction Coordinator
Installing and Configuring Full-Text Search
Managing SQL Mail and SQL Server Agent Mail
Managing Server Activity

Starting, Stopping, and Pausing SQL Server

SQL Server has two modes of operation. It can run as a command-line application (SQLSERVR.EXE) or as a service. You'll use the command-line application when you need to troubleshoot problems or modify configuration settings in single-user mode. Other than that, the normal technique is to run SQL Server as a service.

Managing SQL Server Service with Service Manager

SQL Server Service Manager is the primary tool you'll use to manage SQL Server services. Most database administrators prefer SQL Server Service Manager to the alternatives because you can manage both local and remote servers. You can also configure these services to start automatically. Start SQL Server Service Manager by double-clicking the SQL Server shortcut on the taskbar or by clicking Start, then Programs, then Microsoft SQL Server, and finally Service Manager. This opens the dialog box shown in Figure 3-1.

Cc917619.ppc0301(en-us,TechNet.10).gif

Figure 3-1: SQL Server Service Manager makes it easy to manage services on local and remote systems.

You can manage the SQL Server service with Service Manager by completing the following steps:

  1. Use the Server drop-down list box to select an available server. Or type the server name. For database server instances, type the server name and the instance name separated by a backslash, such as Server1\Engineering1.

  2. On the Services drop-down list box, select SQL Server. Service Manager automatically selects the correct instance of the SQL Server service, which is MSSQLServer or MSSQL$instancename.

  3. The status of the currently selected service is shown on the status bar. In Figure 3-1, for example, SQL Server is running on the selected server instance.

    Use the available options to work with the service as follows:

    • Select Refresh Services to update the service state information.

    • Select Start/Continue to start the service.

    • Select Pause to temporarily stop the service.

    • Select Stop to stop the service.

    • Select Auto-Start Service When OS Starts in order to have SQL Server automatically start when the system boots.

Note: On Microsoft Windows 95 and Windows 98, Service Manager can only be used to manage local resources. The reason for this is that these "services" are actually running as executables on Windows 95 and Windows 98.

Managing SQL Server Service from the Command Line

You can start, stop, and pause SQL Server as you would any other service. On a local system, you type the necessary command at a standard command prompt. On a remote system, you could connect to the system using Telnet and then issue the necessary command. With Windows 2000 Server, you could also establish a remote Terminal Server session to the server and access the command console remotely. To manage the Default database server instance, the commands you use are

  • NET START MSSQLSERVER Starts SQL Server as a service

  • NET STOP MSSQLSERVER Stops SQL Server when running as a service

  • NET PAUSE MSSQLSERVER Pauses SQL Server when running as a service

  • NET CONTINUE MSSQLSERVER Resumes SQL Server when running as a service

To manage named instances of SQL Server, you use the following commands:

  • NET START MSSQL$ instancename Starts SQL Server as a service where instancename is the actual name of the database server instance

  • NET STOP MSSQL$ instancename Stops SQL Server when running as a service where instancename is the actual name of the database server instance

  • NET PAUSE MSSQL$ instancename Pauses SQL Server when running as a service where instancename is the actual name of the database server instance

  • NET CONTINUE MSSQL$ instancename Resumes SQL Server when running as a service where instancename is the actual name of the database server instance

Note: If you choose not to install the default instance of SQL Server during the initial setup and instead create a new named instance as the initial SQL Server instance, you will not be able to use the NET command to manage services from the command line.

Managing the SQL Server Command-Line Executable

The SQL Server command-line executable (SQLSERVR.EXE) provides an alternative to the SQL Server service. You must run SQLSERVR.EXE from the Binn directory that corresponds to the instance of the SQL Server database engine that you want to start. For the default instance, the utility is located in \mssql\Binn. For named instances, the utility is located in \mssql$instancename\Binn.

When SQL Server is installed on a local system, you start SQL Server by changing to the directory for the instance of SQL Server you want to start and then typing

sqlservr at the command line. On a remote system, you connect to the system by using Telnet, changing to the appropriate directory, and then issuing the startup command. In Windows 2000 Server Editions, you can also establish a remote Terminal Server session and access the command line remotely. Either way, SQL Server reads the default startup parameters from the registry and starts execution.

You can also enter startup parameters and switches that override the default settings. The available parameters were summarized in Table 2-1 in Chapter 2. You can still connect Enterprise Manager to the server (though when you do, it may incorrectly report that it's starting the SQL Server service).

To stop an instance of SQL Server started from the command line, complete the following steps:

  1. Press Ctrl+C to break into the execution stream.

  2. When prompted, press N to stop SQL Server.

Using SQL Server Enterprise Manager

With its graphical point-and-click interface, Enterprise Manager makes server, database, and resource management a snap. Using Enterprise Manager, you can manage both local and remote server instances by establishing a connection to SQL Server and then administering its resources. If you've disabled remote server connections to a particular server, however, you can only work with the server locally (by logging in to the system at the keyboard or by establishing a remote Terminal Server session in Windows 2000 and then running the local management tools) or through a Telnet session.

Getting Started with Enterprise Manager

To run Enterprise Manager, choose Start, then Programs, then Microsoft SQL Server, and finally Enterprise Manager. As shown in Figure 3-2, Enterprise Manager presents information in a directory tree structure. The left pane is called the Console Root, and it can be used to work your way down through successive levels of server hierarchy, including

  • Microsoft SQL Servers The top level of the SQL Server hierarchy

  • SQL Server Groups Groupings of related SQL Server instances as defined by you

  • SQL Servers Entries for individual SQL Server instances and their related resource folders

The right pane shows resources you can work with, such as databases or logons. If you have the Taskpad enabled, the right pane also shows tasks you can perform. (To enable or disable the Taskpad, select a SQL Server in the Console Root and then from the View menu, choose the Taskpad option.)

Cc917619.ppc0302(en-us,TechNet.10).gif

Figure 3-2: Enterprise Manager is a Microsoft Management Console (MMC) snap-in. In many cases, you can add Enterprise Manager to an MMC you already use by choosing Add/Remove Snap-In from the Console menu and then following the prompts.

Managing SQL Server Groups

You use SQL Server groups to organize sets of SQL servers. You define these server groups, and you can organize them by function, department, or any other criteria. Creating a server group is easy. You can even create subgroups within a group if you want to. If you make a mistake, you can delete groups as well.

Creating a Server Group

You can create a server group or a subgroup by completing the following steps:

  1. In Enterprise Manager's Console Root (the left pane), right-click Microsoft SQL Servers or any group-level entry.

  2. From the shortcut menu, choose New SQL Server Group. This displays the dialog box shown in Figure 3-3.

  3. In the Server Groups dialog box, type a name for the new group in the Name field.

  4. Top-level groups follow the Microsoft SQL Servers entry in the console hierarchy. Create a top-level group by selecting the Top Level Group option button.

    Cc917619.ppc0303(en-us,TechNet.10).gif

    Figure 3-3: Use the Server Groups dialog box to create new server groups or subgroups.

  5. Subgroups are organized under their primary group, such as Engineering SQL Servers. Create a subgroup by clicking the Sub-Group Of option button and then choosing the primary group from the list provided.

  6. Click OK.

Deleting a Server Group

You can delete a group or subgroup by completing the following steps:

  1. In Enterprise Manager's Console Root, click the plus sign (+) next to the group or subgroup you want to delete. If the group has servers registered in it, move them to a different group, as described in the section of this chapter entitled "Moving a Server to a New Group."

  2. Select the group or subgroup entry.

  3. Press Delete.

Adding SQL Servers to a Group

When you register a SQL Server for use with Enterprise Manager, you can choose which group you want the server to be in. You can even create a new group specifically for this server. The next section covers server registration.

Managing Servers

Servers and databases are the primary resources you manage in Enterprise Manager. Click a server group in the Console Root and the right pane shows you which servers are available for this group. If the server you want to work with isn't

shown, you'll need to register it. After that, you can connect to the server to work with it and disconnect when you're finished. You can start the registration process using any of the following techniques:

  • From the Action menu, choose New SQL Server Registration while Microsoft SQL Servers or any group-level entry is selected in the Console Root.

  • Right-click any server or group-level entry in Enterprise Manager's Console Root and then choose New SQL Server Registration.

  • Choose the Wizards option from the Tools menu and then double-click the Registry Server Wizard entry.

If you've opted not to use the Registry Server Wizard, you'll go straight to the manual registration process. Otherwise you'll use a wizard to register the server.

Registering a Server with a Wizard

The Register SQL Server Wizard makes server registration a point-and-click process. You can register multiple servers at the same time, create new groups for these servers, and more. Just complete the following steps:

  1. After you start the registration process, you'll see the Register SQL Server Wizard dialog box shown in Figure 3-4. As shown, the first dialog box window provides an introduction, so click Next to continue.

  2. Use the Available Servers list to select the SQL servers you want to register and then click Add to add the server to the Added Servers list. These servers should all have the same authentication account or logon account, or both. Click Next to continue.

    Cc917619.ppc0304(en-us,TechNet.10).gif

    Figure 3-4: The Register SQL Server Wizard makes it easy to register multiple servers. But if you don't like using wizards, select From Now On, I Want To Perform This Task Without Using A Wizard.

    Tip Sometimes it takes a while for servers to broadcast their names over the network. If the server you want to use isn't listed, just type the name directly in the Available Servers field and then click Add. For Default database server instances, type the server name. For named instances, type the server name and the instance name separated by a backslash, such as Server1\SQLEngineering.

  3. Select the authentication mode you want to use, either Windows authentication or SQL Server authentication. With Windows authentication, you use the current domain logon for authentication. With SQL Server authentication, you use a separate SQL Server logon ID and password. Click Next to continue.

  4. If you're using SQL Server authentication, the next screen asks you to specify how to log on to SQL Server. For automatic login, choose Login Automatically Using My SQL Server Account Information and then type a SQL Server logon ID and password. For manual login with each connection, choose Prompt. For The SQL Server Account Information When Connecting. Click Next to continue.

  5. Select an existing server group or create a new server group for the server or servers you're registering. Click Next to continue.

  6. The wizard lists the server or servers you're registering. Click Finish to complete the process and begin registration.

  7. The Register SQL Server Message dialog box appears and tells you the status of each server. If a server failed to register, click Properties and edit the registration properties for the server. To stop the registration of a server that isn't responsive, click Stop. To complete the process, click Close.

Registering a Server Manually

If you don't want to use the wizard, you can manually register each server that you want to work with individually. To do this, complete the following steps:

  1. In Enterprise Manager's Console Root, right-click any server or group-level entry and then choose New SQL Server Registration. If the Wizard dialog box comes up, click From Now On, I Want To Perform This Task Without Using A Wizard. Click Next. This displays the Registered SQL Server Properties dialog box shown in Figure 3-5.

  2. In the Server field, type the name of the server instance you want to register. If you're unsure of the server name, use the build button (…) to display the Select Server dialog box. This dialog box shows SQL Server instances that are active on the network.

  3. Select the authentication mode you want to use, either Windows authentication or SQL Server authentication. With Windows authentication, you use the current domain logon for authentication. With SQL Server authentication, you use a separate SQL Server logon ID and password.

    Cc917619.ppc0305(en-us,TechNet.10).gif

    Figure 3-5: Manual registration puts everything you need in one convenient location, but you can't simultaneously register multiple servers.

  4. If you're using SQL Server authentication, type a SQL Server logon ID and password. Or select Always Prompt For Login Name And Password.

  5. Using the Server Group drop-down list box, select an existing server group for the server or click the build button (…) to create a new group.

  6. To display the server state in the console, select Display SQL Server State In Console. Otherwise, clear this check box and you won't see the start, stop, or pause symbols in the server icon.

    Tip The server state is continually refreshed using remote procedure calls (RPCs). To cut down on network traffic, you may want to disable this feature. In addition, the server state may not be displayed when you're running Enterprise Manager on Windows 95 and Windows 98. Windows 95 and Windows 98 don't have the necessary Windows NT networking facilities.

  7. To display system databases and system objects, select Show System Databases And System Objects. If you clear this option, system databases and objects are hidden, and you can display them only by editing the registration properties. The system databases are master, msdb, model, and tempdb.

    Tip In Enterprise Manager you can edit the registration properties by right-clicking the server name and selecting Edit SQL Server Registration Properties.

  8. To automatically start the SQL Server service when connecting, select Automatically Start SQL Server When Connecting.

    Tip Clearing this option may prevent the accidental starting of SQL Server by other administrators when you're working on the server system. This is recommended. After all, you don't want someone to accidentally start the server when you're shutting it down or performing some other type of maintenance.

  9. Click OK to start the registration process. If Enterprise Manager is unable to connect to the server, you'll see an error dialog box. Choose Yes to register the server regardless of the error. Choose No to modify the registration properties and try again.

Editing Registration Properties

You can change the registration properties at any time by right-clicking the SQL Server entry in Enterprise Manager's Console Root and then selecting Edit SQL Server Registration Properties. Afterward, use the Registered SQL Server Properties dialog box as described in the "Registering a Server Manually" section of this chapter. The only property you can't change is the server name.

Moving a Server to a New Group

To move the server to a new group, complete the following steps:

  1. Right-click the SQL Server entry in Enterprise Manager's Console Root and then from the shortcut menu, choose Edit SQL Server Registration Properties.

  2. In the Registered SQL Server Properties dialog box, use the Server Group drop-down list box to select a new group.

  3. Click OK.

Deleting a Server Registration

If you change a server name or remove a server, you may want to delete the server registration in Enterprise Manager so that Enterprise Manager no longer tries to connect to a server that can't be accessed. Right-click the server entry in the Console Root and then select Delete SQL Server Registration. When prompted to confirm the action, click Yes.

Connecting to a Server

Once you've registered a server, connecting to it is easy. Right-click the server entry in Enterprise Manager's Console Root and then from the shortcut menu, choose Connect. Or double-click the server entry to establish a connection and display the server's properties dialog box.

Note: Enterprise Manager connects to other SQL servers through RPC. If you've disabled remote access to a server, however, you won't be able to connect to that server in Enterprise Manager.

Disconnecting from a Server

When you're finished working with a server, you may want to disconnect from it. This cuts down on the back-and-forth RPCs to the server. To disconnect, right-click the server's entry in Enterprise Manager and then from the shortcut menu, choose Disconnect.

Starting, Stopping, and Configuring SQL Server Agent

SQL Server Agent runs as a service and is used to schedule jobs, alerts, and other automated tasks. Once you schedule automated tasks, you'll usually want SQL Server Agent to start automatically when the system starts. This ensures that these tasks are performed as expected. Using SQL Server Service Manager, you can control the related service (SQLServerAgent or SQLAgent$instancename) just as you do the SQL Server service. For details, see the section of this chapter entitled "Managing SQL Server Service with Service Manager."

To configure SQL Server Agent, you'll use Enterprise Manager. Although Chapter 12 covers the agent configuration in detail, the basic steps are as follows:

  1. Start Enterprise Manager and then in the left pane (Console Root) click the plus sign (+) next to the server on which you want to manage SQL Server Agent.

  2. Click the Management folder.

  3. Right-click the SQL Server Agent icon and then from the shortcut menu, choose Properties. You can now configure SQL Server Agent.

  4. The SQL Server Agent shortcut menu also lets you start and stop the SQL Server Agent service. Click Start to start the service or click Stop to stop the service.

Starting, Stopping, and Configuring the Microsoft Distributed Transaction Coordinator

Microsoft Distributed Transaction Coordinator (MS DTC) is a transaction manager that allows client applications to work with multiple sources of data in one transaction.

When a distributed transaction spans two or more servers, the servers coordinate the management of the transaction using MS DTC. When a distributed transaction spans multiple databases on a single server, SQL Server manages the transaction internally.

SQL Server applications can call MS DTC directly to start an explicit distributed transaction. Distributed transactions can also be started implicitly by

  • Calling stored procedures on remote servers running SQL Server

  • Updating data on multiple OLE DB data sources

  • Enlisting remote servers in a transaction

If you work with transactions under any of the previously listed scenarios, you'll want DTC to be running on the server and you'll probably also want DTC to start automatically when the server starts. As with SQL Server itself, DTC runs as a service. This service is named Distributed Transaction Coordinator. Unlike the SQL Server service, only one instance of the MS DTC service runs on a computer regardless of how many database server instances are available. This means all instances of SQL Server running on a computer use the same transaction coordinator.

As you learned in the section of this chapter entitled "Managing SQL Server Service with Service Manager," you can control the DTC service with Service Manager. You can also start and stop DTC in Enterprise Manager by completing the following steps:

  1. Start Enterprise Manager and then in the left pane (Console Root) click the plus sign (+) next to the server on which you want to manage DTC.

  2. Click the Support Services folder.

  3. Right-click Distributed Transaction Coordinator and then click Start to start the service or click Stop to stop the service.

Full-text search allows extensive word searches of textual data and is an additional component you can add to the SQL Server installation. (For details, see the section of Chapter 2 entitled "Adding Components.") Once you install the full-text search component, you can manage full-text searches as described in the following sections.

Using Full-Text Searches

As far as administration goes, full-text searches depend on three factors:

  • That you install the necessary components

  • That you start the related service, which is called the Microsoft Search service

  • That you create a full-text catalog for the database

When you create a full-text catalog, the search service creates full-text indexes of textual data contained in the database. Afterward, the service manages the indexes and provides the primary mechanism for examining the data they contain. As with MS DTC, only one instance of the search service runs on a computer regardless of how many database server instances are available. This means that all instances of SQL Server running on a computer use the same Microsoft Search service.

Note: In Enterprise Manager's Support Services folder, the Microsoft Search service is referred to as Full-Text Search. Don't let the terminology confuse you. These services are one and the same, and the correct name is Microsoft Search service.

The concept of a full-text index may be a bit different than you're used to. In SQL Server, a full-text index stores information about keywords and their location within a specific column. These text indexes are created for each table in the database, and groups of indexes are contained in catalogs, which are stored separately from the databases to which they belong.

Furthermore, full-text indexes are defined on base tables and not on views, system tables, or temporary tables. Indexes are populated with key values that have information about the significant words in a table, such as the column they're in and their location in the column. In Transact-SQL you can test rows against a full-text search condition using contains and freetext. You can also return the set of rows that match a full-text search condition using the functions containstable and freetexttable.

Starting, Stopping, and Configuring the Microsoft Search Service

Microsoft Search is a service that performs the necessary management and search tasks for full-text indexes and their related catalogs. Again, these indexes and catalogs are created automatically when you start and stop the search service. Using SQL Server Service Manager, you can control the search service just as you do the SQL Server service. For details, see the section of this chapter entitled "Managing SQL Server Service with Service Manager." If SQL Server uses full-text searches, you'll want to make sure that Microsoft Search starts automatically when the system boots.

Locating Files Used for Full-Text Searches

Catalogs are stored separately from the databases themselves, and although you don't have control over the where's and how's, you still should know where the related information is stored. To examine current catalog information, complete the following steps:

  1. Start Enterprise Manager and then in the left pane (Console Root) click the plus sign (+) next to the server using full-text search.

  2. Click the Support Services folder.

  3. Right-click the Full-Text Search icon and then from the shortcut menu, choose Properties. This opens the Full-Text Search Service Properties dialog box shown in Figure 3-6.

    Cc917619.ppc0306(en-us,TechNet.10).gif

    Figure 3-6: The Full-Text Search Service Properties dialog box shows where important catalog files and logs are located.

    The General tab provides the following information:

    • The default location of catalog files

    • The location of temporary files used with full-text searching

    • The location of error logs

Managing Catalogs

Each database that you want to search must have its own full-text catalog. When you create a catalog, you set a schedule for populating the catalog on a regular basis or you elect to manually populate the catalog as necessary. Populating the

catalog updates the full-text indexes for the catalog and ensures that the search results are accurate. SQL Server supports two methods for populating catalogs:

  • Full population The search service builds index entries for all rows in all the tables covered by the full-text catalog. In most cases you perform a full population only when you create a catalog or need to refresh the entire contents of a catalog.

  • Incremental population The search service only changes index entries for rows that have been added, deleted, or modified after the last population. Note that you can perform incremental population only on tables that have a timestamp column. If the table doesn't have a timestamp column, full populations are always performed.

Creating a catalog is only one part of the indexing process. After you create the catalog, you must select individual tables for indexing and associate these with the catalog. You also need to specify the individual table columns that should be indexed. Periodically, you may also need to clean up old catalogs.

Creating Catalogs

You need catalogs to perform full-text searches of databases. A single database can have multiple catalogs associated with it, and you can use these catalogs to perform different types of searches. For example, in a customer database, you could create one catalog for searching company contact information and another for searching account history.

You create a catalog for a database by completing the following steps:

  1. Start Enterprise Manager and then in the left pane (Console Root) click the plus sign (+) next to the server you want to examine.

  2. Right-click the database you want to catalog, point to New, and then select New Full-Text Catalog. This displays the New Full-Text Catalog Properties dialog box.

  3. Type a descriptive name for the catalog in the Name field.

  4. The Location field shows the default location of the catalog. You can change this location by typing a new folder path or clicking the location button (. . .) to chose a folder path.

  5. If you want to automatically update the indexes in the catalog, click New Catalog Schedule on the Schedules tab and then follow the remaining steps. Otherwise, click OK and skip the remaining steps.

  6. As shown in Figure 3-7, type a name for the job used to schedule the indexing.

    Tip In most cases, you'll want to create two jobs. Create a one-time job to perform a full population of the catalog and then create a recurring job to perform an incremental population. SQL Server Agent runs scheduled tasks. The agent identifies scheduled tasks by the unique job name you specify. You'll learn more about SQL Server Agent in Chapter 12.

    Cc917619.ppc0307(en-us,TechNet.10).gif

    Figure 3-7: Use the New Full-Text Indexing Catalog Schedule dialog box to create one-time or recurring jobs that populate the catalog.

  7. On the Job Type panel, select Full Population or Incremental Population as appropriate for the type of job you want to create.

  8. Use the options of the Schedule Frequency panel to determine when the job runs. One-time jobs run at a specific date and time. Recurring jobs run daily, weekly, or monthly at a specific date and time.

  9. Click OK twice.

Enabling Indexing of Tables and Columns

You can enable indexing of a table by completing the following steps:

  1. Start Enterprise Manager and then in the left pane (Console Root) click the plus sign (+) next to the server you want to examine.

  2. Click the plus sign (+) next to the database you want to work with and then click Tables.

  3. In the details pane, right-click the table you want to index, point to Full-Text Index Table, and then select Define Full-Text Indexing On A Table. This starts the Full-Text Indexing Wizard.

  4. Click Next and then select a unique index for the table. The index is used as a unique constraint on a single column in the table and can be used in table joins. If the table doesn't have a unique index you'll need to exit the wizard, create an index, and then restart this process.

  5. As shown in Figure 3-8, select the character or image-based columns that you want to index. Each column can be set with a language constraint that identifies the natural language of the column. With text and binary data, you can also specify a document type. Click Next.

    Cc917619.ppc0308(en-us,TechNet.10).gif

    Figure 3-8: Use the Full-Text Indexing Wizard to define how a table should be indexed. You'll need to select a unique index key and columns for indexing.

  6. Use the Select Full-Text Catalog selection menu to choose an existing catalog. Or select Create A New Catalog and then specify a name and location for the catalog.

  7. Click Next. You can now select or create population schedules for the catalog. You can also select or create a population schedule for the currently selected table.

    Real World In most cases, you'll want to create schedules for populating an entire catalog rather than an individual table. Still, there are times when populating individual tables makes sense, especially if the contents of a particular table change frequently and contents of other tables change rarely.

  8. Click Next and then click Finish. Enterprise Manager defines the full-text index for the table but doesn't populate the index. You must populate the index manually or create a schedule for performing this task.

Editing Indexing of Tables and Columns

To change the indexing settings of a table, complete the following steps:

  1. Start Enterprise Manager and then in the left pane (Console Root) click the plus sign (+) next to the server you want to examine.

  2. Click the plus sign (+) next to the database you want to work with and then click Tables.

  3. In the details pane, right-click the table you want to work with, point to Full-Text Index Table, and then select Edit Full-Text Indexing. This starts the Full-Text Indexing Wizard, which you can use to change the previous indexing definition.

Removing Full-Text Indexing from Tables

If you decide that you don't want to index a table any more, you can remove full-text indexing by completing the following steps:

  1. Start Enterprise Manager and then in the left pane (Console Root) click the plus sign (+) next to the server you want to examine.

  2. Click the plus sign (+) next to the database you want to work with and then click Tables.

  3. In the details pane, right-click the table you want to work with, point to Full-Text Index Table, and then select Remove Full-Text Indexing. When prompted to confirm the action, click Yes.

Populating Catalogs

Once you select the tables and columns you want to index, you can populate the catalog manually or set a schedule that instructs SQL Server Agent to run a one-time or recurring job that populates the catalog. You can populate catalogs at the database level or at the table level. In most cases, you'll want to create schedules for populating an entire catalog rather than an individual table. Still, there are times when populating individual tables makes sense, especially if the contents of a particular table change frequently and the contents of other tables change rarely.

To manually populate a catalog with text for all tables selected for indexing, complete the following steps:

  1. Start Enterprise Manager and then in the left pane (Console Root) click the plus sign (+) next to the server you want to examine.

  2. Click the plus sign (+) next to the database you want to work with and then click Full-Text Catalog.

  3. In the details pane, right-click the catalog you want to work with and then select Start Full Population or Start Incremental Population as appropriate. If you later need to stop the population, right-click the catalog and then select Stop Population.

To set a schedule for populating a catalog with text for all tables selected for indexing, complete the following steps:

  1. Start Enterprise Manager and then in the left pane (Console Root) click the plus sign (+) next to the server you want to examine.

  2. Click the plus sign (+) next to the database you want to work with and then click Full-Text Catalog.

  3. In the details pane, right-click the catalog you want to work with and then select Schedules.

  4. Use the Full-Text Indexing Schedules dialog box to set a schedule for populating the catalog.

  5. Click OK.

To manually populate a catalog with text for a single table, complete the following steps:

  1. Start Enterprise Manager and then in the left pane (Console Root) click the plus sign (+) next to the server you want to examine.

  2. Click the plus sign (+) next to the database you want to work with and then click Tables.

  3. In the details pane, right-click the table you want to work with, point to Full-Text Index Table and then select Start Full Population or Start Incremental Population as appropriate. If you later need to stop the population, right-click the table, point to Full-Text Index Table, and then select Stop Population.

To set a schedule for populating a catalog with text for a single table, complete the following steps:

  1. Start Enterprise Manager and then in the left pane (Console Root) click the plus sign (+) next to the server you want to examine.

  2. Click the plus sign (+) next to the database you want to work with and then click Tables.

  3. In the details pane, right-click the table you want to work with, point to Full-Text Index Table, and then select Schedules.

  4. Use the Full-Text Indexing Schedules dialog box to set a schedule for populating the table.

  5. Click OK.

Rebuilding Current Catalogs

When you make frequent changes to a database, catalogs can sometimes get out of sync with the contents of a database. Over an extended period of time, catalogs can also grow quite large. To resync the catalog with the contents of the database or to squeeze out extra space in the catalog, you'll need to rebuild the catalog.

You can rebuild catalogs individually or you can rebuild all the catalogs used by a database. To rebuild a single catalog, complete the following steps:

  1. Start Enterprise Manager and then in the left pane (Console Root) click the plus sign (+) next to the server you want to examine.

  2. Click the plus sign (+) next to the database you want to work with and then click Full-Text Catalog.

  3. In the details pane, right-click the catalog you want to work with and then select Rebuild Catalog.

To rebuild all catalogs associated with a database, complete the following steps:

  1. Start Enterprise Manager and then in the left pane (Console Root) click the plus sign (+) next to the server you want to examine.

  2. Click the plus sign (+) next to the database you want to work with. Right-click Full-Text Catalog and then select Rebuild All Catalogs. When prompted to confirm the action, click Yes.

Caution: Rebuilding catalogs can be time and resource intensive. In a production environment you should rebuild catalogs only during off-peak hours.

Cleaning Up Old Catalogs

Although the full-text search components do a good job of maintaining indexes and cleaning up after themselves, you'll want to keep an eye on the number and size of catalog files. You'll also want to regularly clean up old catalogs. You do this by completing the following steps:

  1. Start Enterprise Manager and then in the left pane (Console Root) click the plus sign (+) next to the server using full-text search.

  2. Click the Support Services folder.

  3. Right-click the Full-Text Search icon and then from the shortcut menu, choose Cleanup Catalogs.

  4. When prompted, choose Yes to start the cleanup. The cleanup may take a few minutes, depending on the size of the catalogs.

Removing Catalogs

To remove a single catalog, complete the following steps:

  1. Start Enterprise Manager and then in the left pane (Console Root) click the plus sign (+) next to the server you want to examine.

  2. Click the plus sign (+) next to the database you want to work with and then click Full-Text Catalog.

  3. In the details pane, right-click the catalog you want to delete and then select Delete. When prompted to confirm the action, click Yes.

To remove all catalogs associated with a database, complete the following steps:

  1. Start Enterprise Manager and then in the left pane (Console Root) click the plus sign (+) next to the server you want to examine.

  2. Click the plus sign (+) next to the database you want to work with. Right-click Full-Text Catalog and then select Remove All Catalogs. When prompted to confirm the action, click Yes.

Managing SQL Mail and SQL Server Agent Mail

SQL Mail and SQL Server Agent Mail are core facilities of SQL Server. You use SQL Mail to trigger stored procedures and return result sets by e-mail. You use SQL Server Agent Mail to send e-mail and pager notifications. Both facilities rely on a Messaging Application Programming Interface (MAPI)-compliant e-mail service. SQL Mail runs through the SQL Server service. SQL Server Agent Mail, on the other hand, runs through the SQL Server Agent service. The sections that follow explain how you configure and test the mail client needed by SQL Mail and SQL Server Agent Mail and show you how to configure SQL Mail and SQL Server Agent Mail.

Setting Up SQL Server as a Mail Client

SQL Mail and SQL Server Agent Mail can use your existing e-mail infrastructure to send and receive messages as long as your e-mail server is MAPI compliant and you configure SQL Server as a valid mail client. The easiest mail service to configure a client for is Microsoft Exchange. If you're running Exchange, you must

  1. Set up an Exchange mailbox for SQL Mail and SQL Server Agent Mail. Or, if you prefer, set up separate mailboxes for these facilities.

    Tip By default, the SQL Mail service runs in the same security context as SQL Server service. Because of this, you must configure the Exchange mailbox for a domain account that can run the SQL Server service. SQL Server Agent Mail runs through the SQL Server Agent service and, likewise, you must configure the Exchange mailbox for a domain account that can run the SQL Server Agent service.

  2. Install compatible mail client software on the server, such as Microsoft Outlook. The key components of the client used by SQL Mail and SQL Server Agent Mail are the MAPI extensions. Watch out, because you can modify these extensions accidentally when you install service packs, e-mail applications, or other applications that use e-mail.

  3. Set up a mail profile for each account using the Mail utility on the Control Panel. You should configure the profiles to point to the Mail Exchange Server and the Exchange mailbox.

Tip Be sure to use a profile name that's easy to identify as the SQL Mail or SQL Server Agent Mail profile, or both. This will make it easier for others to work with the profiles and will help prevent accidental deletion.

Testing the Mail Installation

Once you configure SQL Mail as a mail client(s), you should test the installation by completing the following steps:

  1. Log on to the system running SQL Server using a domain account set up for SQL Server. Remember that each database server instance has a different SQL Server service.

  2. Start the mail client using the new mail profile.

  3. Test the configuration by sending a message addressed to the mailbox for SQL Mail.

  4. If the message doesn't show up in the mail client's inbox, you may have improperly configured SQL Mail. Take a close look at each step of the configuration and then repeat this test.

  5. Repeat this process to test SQL Server Agent Mail. Remember that each database server instance has a different SQL Server Agent service.

Configuring SQL Mail

After you determine that SQL Server can send and receive mail with the mail client, you're ready to complete the configuration. You need to tell SQL Server about the profile. You do this by completing the following steps:

  1. Start Enterprise Manager and then in the left pane (Console Root) click the plus sign (+) next to the server using SQL Mail.

  2. Click the Support Services folder.

  3. Right-click the SQL Mail icon and then from the shortcut menu, choose Properties. This opens the dialog box shown in Figure 3-9.

    Cc917619.ppc0309(en-us,TechNet.10).gif

    Figure 3-9: In the SQL Mail Configuration dialog box, type the profile name in the field provided and then click Test.

  4. Type the profile name in the field provided or use the drop-down list box to select any of the available profiles on the server. If you want to test the configuration, click Test.

  5. Click OK.

Configuring SQL Server Agent Mail

Next, you need to tell SQL Server about the profile for SQL Server Agent Mail. You do this by completing the following steps:

  1. In Enterprise Manager, access the Management folder on the server using SQL Server Agent.

  2. Right-click the SQL Server Agent icon in the left pane and then from the shortcut menu, choose Properties.

  3. Type the profile name in the Mail Profile field or use the drop-down list box to select any of the available profiles on the server. If you want to test the configuration, click Test.

  4. Click OK.

Managing Server Activity

As a database administrator, it's your job to make sure that SQL Server runs smoothly. One of the ways you do this is by actively monitoring the server. By monitoring server activity, you can

  • Keep track of user connections and locks

  • View processes and commands that active users are running

  • Check the status of locks on processes and objects

  • See blocked or blocking transactions

  • Ensure that processes complete successfully and detect errors if they don't

When problems arise, you can send a message to a user who is running a process or even terminate a process, if necessary.

Note: For more coverage of monitoring SQL Server, see Chapter 10. There you'll learn how to use Performance Monitor and SQL Server Profiler to keep track of SQL Server activity, performance, and errors.

Examining Process Information

Process information provides detailed information about the status of processes, current user connections, and other server activity. You can view process information by completing the following steps:

  1. Start Enterprise Manager and then in the left pane (Console Root) click the plus sign (+) next to the server you want to examine.

  2. Click the Management folder and then select Current Activity. The Current Activity entry shows a date and time when the activity snapshot was taken.

  3. Choose the Process Info entry in the Console Root or double-click Process Info in the right pane. You should see a summary of process activity similar to that shown in Figure 3-10.

  4. Process information isn't updated automatically. To refresh it, right-click Current Activity and from the shortcut menu, choose Refresh.

    Cc917619.ppc0310(en-us,TechNet.10).gif

    Figure 3-10: Process Info provides a detailed look at processes and the user connections running them.

Initially, processes are sorted by process ID, but you can order them by any of the available information categories summarized in Table 3-1. Click a category header to sort processes based on that category. Click a category header a second time to do a reverse sort on the category.

Table 3-1 Process Information Used in Database Administration

Category

Description

Process ID

Server process ID of the current user process.

Context_ID

Identifies the database context in which the process was started.

User

User running the process by SQL Server ID or domain account, depending on the authentication technique used.

Database

The database being used. Some server processes are started before the master database is brought online, and these have no database context.

Status

The status of the process, which is usually runnable, sleeping, or background. A runnable process is active. A sleeping process is waiting for input or a lock. A background process is running in the background and periodically performing tasks.

Open Transactions

The number of open transactions.

Command

The command being executed or the last command executed.

Application

The application connecting to the server and running the process, such as SQL Server Query Analyzer.

Wait Time

The elapsed wait time in milliseconds.

Wait Type

Specifies whether the process is waiting or not waiting.

Wait Resource

The resource the process is waiting for (if any).

CPU

The amount of processor time being used by the process.

Physical I/O

The physical input/output used by the process.

Memory Usage

The amount of memory the process is using in KB.

Login Time

When the connection was established.

Last Batch Time

When the last command was executed using the connection.

Host

The host from which the connection originated.

Network Library

The network library used to establish the connection.

Network Address

The network address for the connection.

Blocked By

The number of connections blocking this process.

Blocking

The number of connections waiting for this process to finish.

Tracking Locks by Process ID and Object

Locks can be tracked by process ID and object. Either technique provides the same information, it's just presented in a different way. You can view locks by process ID or object by completing the following steps:

  1. Start Enterprise Manager and then in the left pane (Console Root) click the plus sign (+) next to the server you want to examine.

  2. Click the Management folder and then select Current Activity. The Current Activity entry shows a date and time when the activity snapshot was taken.

  3. With processes, you can view a summary of all objects the process is locking. Click Locks/Process ID and then select the process you want to examine, such as SPID.

  4. With objects, you can see a list of all processes with locks on the object. Click Locks/Object and then select the database object you want to examine, such as master.

  5. Lock statistics aren't updated automatically, and you'll occasionally have to refresh the view. To do this, right-click Current Activity and from the shortcut menu, choose Refresh.

Although locks by process ID and locks by object are presented in a slightly different manner, the information contained in the listings is almost identical. With process ID, you see a list of objects the process has a lock on. With objects, you see a list of processes that have locks on the object. Beyond this, you'll also see information on the type, status, and mode of the lock as well as the lock owner, the resource being locked, and the index being locked (if applicable)—all of which are summarized in Table 3-2.

Table 3-2 Lock-Related Information Used in Database Administration

Category

Type

Description

Lock Type

RID

Row identifier. Used to lock a single row within a table.

 

KEY

A row lock within an index. Used to protect key ranges.

 

PAG

A lock on a data or index page.

 

EXT

A lock on a contiguous group of eight data or index pages.

 

TAB

A lock on an entire table, including all data and indexes.

 

DB

A lock on a database.

Lock Mode

S

Shared; used for read-only operations, such as a select statement.

 

U

Update; used when reading/locking an updateable resource. Prevents some deadlock situations.

 

X

Exclusive; allows only one session to update the data. Used with the modification operations, such as INSERT, DELETE, and UPDATE.

 

Intent

Used to establish a lock hierarchy.

 

SchS

Schema stability; used when checking a table's schema.

 

Sch-M

Schema modification; used when modifying a table's schema.

 

BU

Bulk update; used when bulk-copying data.

Status

GRANT

The lock was obtained.

 

WAIT

The lock is blocked by another process.

 

CNVT

The lock is being converted—that is, it's held in one mode but waiting to acquire a stronger lock mode.

Owner

curs

The lock owner is a cursor.

 

sess

The lock owner is a user session.

 

xact

The lock owner is a transaction.

Index

Index Name

The index associated with the designated resource. With clustered indexes, you'll see a table name instead.

Resource

RID

A row identifier for a locked row within a table; listed by <file id>:<page>:<row id>/, where row id is a row within the specified page.

 

KEY

A key shown as a hexadecimal number and used internally by SQL Server.

 

PAG

A page number; listed by <file id>:<page>, where file id is the file id in the sysfiles table and page is the logical page number within that file.

 

EXT

The first page number in the extent being locked; listed by <file id>:<page>.

 

TAB

Table identifier.

 

DB

Database identifier.

Troubleshooting Deadlocks and Blocking Connections

Two common problems you may see are deadlocks and blocking connections. Deadlocks and blocking connections can occur in just about any database environment, especially when lots of users are making connections to databases.

  • Deadlocks occur when two users have locks on separate objects and each wants a lock on the other's object. Each user waits for the other to release the lock and this doesn't happen.

  • Blocking connections occur when one connection holds a lock and a second connection wants a conflicting lock type. This forces the second connection either to wait or to block the first.

Both deadlocks and blocking connections can degrade server performance.

Although SQL Server can detect and correct deadlock and blocking situations, you can help speed up this process by identifying potential problems and taking action, if necessary. Process information can tell you when deadlocks or blocking occur. You'll want to examine these columns: Wait Time, Wait Type, Wait Resource, Blocking, and Blocked By. When you have a deadlock or blocking situation, you'll want to take a closer look at the locks on the objects that are causing problems, and you can do this in the manner described earlier in the "Tracking Locks by Process ID and Object" section of this chapter. You may also want to stop the offending processes, and you do this by following the steps described in the section of this chapter entitled "Killing Server Processes."

Tracking Command Execution in SQL Server

Sometimes you'll want to track the commands that users are executing, and you can do this by using the Current Activity resource viewer. The steps you take are the following:

  1. Start Enterprise Manager and then in the left pane (Console Root) click the plus sign (+) next to the server you want to examine.

  2. Click the Management folder and then select Current Activity. The Current Activity entry shows a date and time when the activity snapshot was taken.

    Tip If the snapshot is old, you can refresh the snapshot by right-clicking Current Activity and choosing Refresh from the shortcut menu.

  3. Select Process Info. The entries in the User column can help you track user sessions and the processes they are using.

  4. Double-click a process to display the dialog box shown in Figure 3-11. This dialog box shows the last command executed by the user.

  5. To track commands being executed by the user, click Refresh.

  6. To kill the process, click Kill Process. Then, when prompted, choose Yes.

  7. To send a message to the user running the process, click Send Message.

    Cc917619.ppc0311(en-us,TechNet.10).gif

    Figure 3-11: The Process Details dialog box shows the last command or batch executed, as well as the user logon ID, process ID, and originating host.

Sending Messages to Users Based on Connections

While you're examining processes and server activity, you may want to send messages to users based on connections. To do this, complete the following steps:

  1. Start Enterprise Manager and then in the left pane (Console Root) click the plus sign (+) next to the server you want to examine.

  2. Click the Management folder and then select Current Activity. The Current Activity entry shows a date and time when the activity snapshot was taken.

  3. Select Process Info and then right-click a process being used by a user to whom you want to send a message.

  4. From the shortcut menu, choose Send Message (see Figure 3-12).

    Cc917619.ppc0312(en-us,TechNet.10).gif

    Figure 3-12: Use the Send Message dialog box to send a message using the Messenger service. If you want to send a message to a different host, choose the Using Hostname option button and then type the new host name.

  5. In the Message area, type the text of the message.

  6. Select a destination using the option buttons provided. You can send a message to the user or to the user's computer.

  7. Click Send to send the message.

Note: When you send messages to users, you use the Windows Alerter and Messenger services and not SQL Mail. These services must be running on SQL Server and on the user's system. A good resource on Windows services and service management is Microsoft Windows 2000 Server Administrator's Pocket Consultant (Microsoft Press, 2000).

Killing Server Processes

You may need to stop processes that are blocking connections or are using up CPU time. To do this, complete the following steps:

  1. Start Enterprise Manager and then in the left pane (Console Root) click the plus sign (+) next to the server you want to examine.

  2. Click the Management folder and then select Current Activity. The Current Activity entry shows a date and time when the activity snapshot was taken.

  3. Select Process Info and then right-click the process you want to stop.

    Note: When you start SQL Server, the system starts the process IDs 1 to 6. Don't kill these processes. If you're concerned about them, stop and restart the MSSQLServer service instead of trying to kill these processes.

  4. From the shortcut menu, choose Kill Process. Then, when prompted, click Yes.

Link
Click to order