Ask the Expert

Enterprise Manager database size reports

I see in my Enterprise Manager that for one of the databases it shows the size as 60 Mb. and space available as 57 MB; that means there is only 3 MB of data. So can I control this size to be near 3 MB in my case? I mean this free space to 1 or 2 percent of the size of actual data? Thanks in advance.

    Requires Free Membership to View

The database size Enterprise Manager reports includes both the data files and the transaction logs, every database has at least one PRIMARY data file and one transaction log file; optionally the database may also have secondary data files. There is a choice between shrinking either the entire database or individual files. To shrink the database use DBCC SHRINKDATABASE(database_name, target_percent), target_percent is the amount of free space to leave in the database (ex. DBCC SHRINKDATABASE(Northwind, 2)). To shrink an individual file use DBCC SHRINKFILE(logical_file_name, target_size), target_size is the size in Mb. -- ex. DBCC SHRINKFILE (Northwind_Log, 3). To control the growth of the database use the ALTER DATABASE command to set the percent growth(ex. ALTER DATABASE myDB MODIFY FILE (NAME = DataFile1, FILEGROWTH = 2%)). Another consideration for controlling the size of the database is ensuring the transaction log is backed up and/or truncated regularly. The use of the database -- transactional versus reporting -- and acceptable level of data loss govern how the transact log is backed up.

For further information look in Books On Line for...

  • DBCC SHRINKDATABASE
  • DBCC SHRINKFILE
  • ALTER DATABASE
  • BACKUP
  • Choosing a Backup and Restore Strategy (entire section)

 

For More Information

This was first published in June 2001

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: