Find out total database file sizes on a Sql Server instance

Sometimes you want to know how much space Sql Server databases on an instance are taking from a system. This should be the sum of all data files and log files. Note this is different from how much space actual data is taking.

Here is one way to do it. Note it uses sysaltfiles, a system table that might be deprecated in future releases. For Sql Server 2005, it is recommended that you use sys.master_file:

select sum(size)*8/1024 as 'TotalSizeInMeg', sum(size)*8/1024/1024 as 'TotalSizeInGig' from sysaltfiles

Another way to get that is to use sp_helpdb. Executing sp_helpdb without any parameters will give you total size for each database in megabytes. Adding them up will give you total databases size.

Since I am digging Perl, below is the Perl script to do that. You might notice the numbers you get from sysaltfiles and sp_helpdb are different, that’s due to rounding.

use strict;
use Win32::SqlServer;

# Get server name from the command line
my $ServerName = shift or die "Please enter server name as the first only parameter needed";

# Create object and login in one step with
# integrated security.
my $sqlsrv = sql_init($ServerName, undef, undef, 'master');

# Our SQL statement. In this case, this is a system stored procedure.
my $stmnt = < <SQLEND;
sp_helpdb
SQLEND

# Run query. The return value is a reference to an array.
my $result = $sqlsrv->sql_sp($stmnt);
my $TotalSize = 0;
# Print results. Each array entry is a hash reference.
foreach my $row (@$result) {
   print "$$row{name}: $$row{db_size}  \n";
   if ($$row{db_size} =~ /(\d+\.\d+)/ix) {
	$TotalSize = $TotalSize + $1;
   }
}
print "Total databases size is $TotalSize meg";