SQL: Database size management

Keep up good test effeciency often requires several test environments with various configuration and test data setups etc. Add the dimension multiple AUT release versions and you probably need more test sites than you can possible dream of.

To handle multiple test environments properly requires technical knowledge, some programming skills and a good releationship with your system administrator :-)

A very good idea is to have a test environment dashboard for survaillance of critical parameters. One of those parameters is database size. For large enterprise systems with high transaction rate, production-like copies often becomes quite large, hence you have to do some preventing actions in order to keep your disk quota. Some well targeted delete scripts in combination with shrink commands often does the trick. But how to check the current size?

Fortunatly MS SQL Server has some built-in Store procedures for this:

--Get total database size in MB including log file
sp_spaceused

database_name  database_size      unallocated space
-------------- ------------------ ------------------
MyDatabase     31877.31 MB        5382.25 MB


--Get remaining disk size for each drive on the database server
xp_fixeddrives

drive MB free
----- -----------
C     8011
D     90660
E     23057

--Get disk file name, size and drive
select filename, (SUM(size)*8)/1024 As FileSizeInMB FROM master.dbo.sysaltfiles
group by filename

filename            FileSizeInMB
-----------------------    ------------
D:\MyDatabase.mdf        5806
D:\MyDatabase_log.ldf    542