Manage Learn to apply best practices and optimize your operations.

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.

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...

  • Choosing a Backup and Restore Strategy (entire section)


For More Information

This was last published in June 2001

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.