Search FAQs

How to View Database Information

Issue
A view of the basic database information can be very useful in determining its configuration, maintaining and monitoring its growth
and to aid in estimating future resource and configuration requirement. (ie. a system upgrade)
This information can also be very helpful to identify the cause of some database issues.

Product Line
Struxureware Power Monitoring
Power Monitoring Expert
ION Enterprise
ION EEM

Environment
SQL Server 2005

SQL Server 2008 R2

SQL Server 2012,

SQL Server 2014

SQL Server 2016

Cause
Database maintenance and troubleshooting possible database issues.

Resolution
A SQL stored procedure, sp_helpdb, can be used to obtain and examine database information.

To do so, in SQL Server Management Studio, access the Query view panel.
This can be accomplished by right-clicking the name of a server to which you have already connected in the Object Explorer view and then selecting New Query.
Alternatively, you can click New Query on the main toolbar, select Database Engine Query, and then establish a connection to the Database on a specific server.

After you have accessed the Query view, use the following command, where <dbname> is the name of the database you want to examine:
sp_helpdb <dbname>
go

Viewing the database information in this way, presents an overview of the database as well as a listing of current data and log files.
Below is a summary of the information available when using this stored procedure to display the database properties .
This data is returned in two different result sets. Scroll down in the Results pane to see the additional result set.

compatibility_level - The current compatibility level of the database. The level 90 indicates SQL Server 2008 compatibility.
created - The date the database was created.
db_size - The total size of the database, including all data and log files.
dbid - The unique identifier for the database on the current server.
filegroup - The filegroup associated with the database file. Filegroups allow you to group sets of database files together.
fileid - The unique identifier for the file in the current database.
filename - The full file name and path.
growth - The number of megabytes or percent by which the file grows.
maxsize - The maximum file size. Unlimited means there is no limit.
name - The name of the database or file (without a file extension).
owner - The database owner.
size - The current size of a file.
status - The database status.
usage - The way the file is used, such as data only or log only.