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...
- DBCC SHRINKDATABASE
- DBCC SHRINKFILE
- ALTER DATABASE
- BACKUP
- Choosing a Backup and Restore Strategy (entire section)
For More Information
- What do you think about this answer? E-mail us at [email protected] with your feedback.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our SQL Server gurus are waiting to answer your toughest questions.
