Q

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

This was first published in June 2001

Dig deeper on Microsoft SQL Server Installation

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close