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

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top