Server Management

The Management folder contains subfolders for the SQL Server agent, backup devices, current activity, database maintenance plans, and error logs. Each of these plays a key role in managing your server.

SQL Server Agent

The SQL Agent is the "quarterback" when it comes to maintenance tasks. It handles the automation of SQL Server operations. General tasks such as starting and stopping the agent can be accessed from the right-click pop-up menu, as can configuration through the Properties dialog box. Figure 4.12 displays the Agent Properties dialog box.

Figure 4.12. Configuring the SQL Server Agent.


Subfolders for alerts, operators, and jobs exist to store and manage these respective tasks. As with other folders, the right-click menu is used to manage these specific objects. Chapter 18, "SQL Server Scheduling and Notification," provides full details on the agent's functionality.


Backup devices can be created and accessed from the Backup folder. Expanding the Management folder in EM and selecting Backup displays the backup devices in the Details pane. Right-clicking the Backup folder presents you with the options to create a new backup device or to back up a database. Double-clicking any of the backup devices in the Details pane brings up the Properties window for the device, shown in Figure 4.13, from which you can obtain file information for the device, and display the backups that the device contains. Chapter 16 covers this topic in detail.

Figure 4.13. Displaying Backup Device properties.


Current Activity

The Current Activity folder allows you to access detailed information on processes, locks by process id, and locks by object. Note that the information provided in this folder is "point in time" information, so a refresh is required to display any changes. Clicking on any of the displayed locks or processes allows you the option of sending a message to the owner of the process, or killing the process if necessary. One of the Current Activity folder's features is its ability to display blocking and blocked processes, as displayed in Figure 4.14.

Figure 4.14. Displaying a blocking process.


After the blocking process is identified, it can be killed or its owner notified to complete the transaction so that the blocked process can proceed. For the full story on locking and the Current Activity folder, see Chapter 38, "Locking and Performance."

Database Maintenance Plans

This folder is used to store Database Maintenance Plans created by the Database Maintenance Plan Wizard. Right-clicking the folder and selecting New Plan activates the wizard. Stepping through the wizard allows you to create schedules for DBCCs, backups, and index reorganization. In a few minutes, you can create a complex maintenance scheme for one or all of your databases. After the scheme is completed, it is available to edit if you require changes. Figure 4.15 shows the properties for a completed maintenance plan.

Figure 4.15. Displaying a maintenance plan.


Chapter 17, "Database Maintenance," will deal with the ins and outs of creating a plan with this tool. However, it is so simple and intuitive that if you are clicking along as you read this chapter, you have probably just created your first maintenance plan!

SQL Server Logs

Not to be confused with the Transaction Log, this folder contains the error, or perhaps more correctly, "information" logs. By default, seven rotating logs exist, including the current log. Each time SQL Server is taken offline, the oldest log is deleted and the current log is saved. On startup, a new current log is opened. Figure 4.16 shows the data from the current log displayed in the Details pane.

Figure 4.16. Displaying current log information.


By right-clicking the SQL Server Logs folder and selecting configure, you can change the number of archived logs. This is a good plan because an inexperienced administrator "bouncing" the server several times in an attempt to fix a problem could lose valuable troubleshooting information. Figure 4.17 shows the Configuration dialog box.

Figure 4.17. Configuring the information logs.


    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features