Microsoft® SQL Server® 2012 Bible

599

Chapter 22: Maintaining the Database

22

Database File Size

SQL Server 7.0 moved beyond SQL Server 6.5’s method of allocated space with fi xed-size fi les

called devices. Since SQL Server 7.0, data and transaction logs can automatically grow as

required. File size is still an area of database-maintenance concern. Without some interven-

tion or monitoring, the data fi les could grow too large. The following commands and DBCC

options deal with monitoring and controlling fi le sizes.

Monitoring Database File Sizes

Three factors of fi le size should be monitored: the size of the database fi les and their

maximum growth size, the amount of free space within the fi les, and the amount of free

space on the disk drives.

The current and maximum fi le sizes are stored within the sys.database_files

database catalog view. The following code displays the name, size, and max size for the

AdventureWorks2012 sample database:

USE AdventureWorks2012;

SELECT name, size, max_size from sys.database_files;

Result:

Name size max_size

--------- ------- ---------

AdventureWorks2012_Data 25080 -1

AdventureWorks2012_Log 256 268435456

where size is the current size and max_size is the maximum size of the fi le, in 8KB

pages. A value of -1 for max_size indicates that the fi le will grow until the disk is full, and

268435456 indicates that the maximum size of the log fi le will be 2TB.

To check the current and maximum fi le sizes for all the databases, use the sys.master_

files catalog view.

To detect the percentage of the fi le that is actually used, use the sp_spaceused

system stored procedure. Optionally, you can run the DBCC UPDATEUSAGE command

to correct disk space usage inaccuracies or use the @updateusage optional param-

eter with the sp_spaceused command. The following command updates the space

usage information of the AdventureWorks2012 sample database and then runs the sp_

spaceused command.

USE AdventureWorks2012;

DBCC UPDATEUSAGE (AdventureWorks2012);

EXEC sp_spaceused;

c22.indd 599c22.indd 599 7/31/2012 9:24:31 AM7/31/2012 9:24:31 AM

http://www.it-ebooks.info