Microsoft® SQL Server® 2012 Bible

600

Part V: Enterprise Data Management

Result:

database_name database_size unallocated space

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

AdventureWorks2012 197.94 MB 15.62 MB

reserved data index_size unused

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

184648 KB 96672 KB 81440 KB 6536 KB

To determine the size and percentage of used space within the transaction log, use the

DBCC SQLPERF (LOGSPACE) command:

DBCC SQLPERF (LOGSPACE);

Result :

Database Name Log Size(MB) Log Space Used(%) Status

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

Master 0.9921875 41.73228 0

Tempdb 0.4921875 76.68651 0

Model 0.4921875 57.14286 0

Msdb 0.4921875 47.61905 0

AdventureWorks2012 1.992188 35.19608 0

DBCC execution completed. If DBCC printed error messages,

contact your system administrator.

To monitor the amount of free space on the server’s disk drives, you can use the undocu-

mented extended stored procedure xp_fixeddrives:

EXEC master..xp_fixeddrives;

Result:

drive MB Free

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

C 429

F 60358

Because xp_fixeddrives is an undocumented stored procedure, there is no support for this, and it can be

removed from SQL Server at any time.

Shrinking the Database

Unless the database is confi gured to automatically shrink in the background, the fi le space

freed by deleting unused objects and rows will not be returned to the disk operating sys-

tem. Instead, the fi les remain at the largest size to which the data fi le may have grown.

If data is regularly added and removed, constantly shrinking and growing the database

would be a wasteful exercise. However, if disk space is at a premium, a large amount of data

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

http://www.it-ebooks.info