Displaying Information About Databases

You have several ways to view information about databases and their transaction logs.

To view a list of the databases on a server

In the Server Manager window, select a server and open the Databases folder.
The databases for this server are listed.

To view a graphical representation of all the databases on a server

From the Server Manager window, select a server, and then from the toolbar, choose the Manage Databases button.

The Manage Databases window appears. A graph shows the total size of each database, and for a selected database displays the space used and the space available.
Choose the Legend button (under the toolbar) to view a legend explaining the graph.

You can also view information about an individual database.

To view information about an individual database

From the Server Manager window, select a server, open its Databases folder, and then double-click on a database.
The Edit Databases dialog box appears, displaying information about the database.
To view the option settings for this database, select the Options tab.
The Options window appears, displaying the setting of the database options.

For information about database options, see Setting Database Options, earlier in this chapter.
To view the permissions set for this database, select the Permissions tab.
The Permissions windows appears, displaying the statement permissions set for this database.

For information about database permissions, see Chapter 8, Security Concepts.

Although the Current Activity window of SQL Enterprise Manager primarily displays information about server-wide activity, the SA can open it to review database-related activity information.

To view current activity

From the Server Manager window, select a server, and then from the toolbar, choose the current Activity button.

The Current Activity window appears.

For an explanation of the Current Activity window and the information it provides, see Chapter 19, Monitoring Server Activity and Performance.

These system procedures can also be used to display database-related information.



System procedure	Description
sp_helpdb	Reports information for all the databases on SQL Server, or if you supply a database name, about that database only. Provides database name, database size, owner, database ID, creation date, and options set. For an individual database, also lists device fragments, fragment size, fragment usage (data or log), device used, and device segments.
sp_helplog	Reports the name of the device that contains the first page of the transaction log.
sp_spaceused	Provides a summary of the storage space that a database, transaction log, or database object is using. Allows you to monitor the amount of space available.

You can monitor a database's transaction log by running sp_spaceused against syslogs. It is a good idea to do this regularly. The transaction log can grow rapidly if there are frequent database modifications. If the transaction log is not on a separate database device, then space can become a problem because the log competes with the rest of the database for space.

For information on using the sp_helpdb, sp_helplog, and sp_spaceused system procedures, see the Microsoft SQL Server Transact-SQL Reference.

Another way to see how much space is used by the transaction log is to use the DBCC CHECKTABLE (SYSLOGS) or DBCC SQLPERF (LOGSPACE) commands. You can also continuously monitor log space using SQL Performance Monitor. For information, see Chapter 19, Monitoring Server Activity and Performance, and the Microsoft SQL Server Transact-SQL Reference.

You can write your own queries to get information about the amount of physical storage space available on a SQL Server. To do this, use the master database and query the sysdevices table. For example, to determine the total number of 2K blocks of storage space that exist on a SQL Server, you could issue this query against sysdevices:

select sum(high - low) 
from sysdevices 
where status in (2, 3) 

---------------------- 
7168 

(1 row affected)

You can also query the sysdatabases system table to view information about databases, and the sysusages system table to view information about the space allocated to databases.

For information about syslogs, sysdevices, sysdatabases, and sysusages, see the Microsoft SQL Server Transact-SQL Reference.

Note You should not update the system tables directly.