I came across something today that I have been looking forward to for a long time. In my shop a few instances do have a significant number of databases running on them, and I have managed to develop a small script that can check the size of each database by looping through all using SP_MSforeachDB. The next level I desired was to check the free space within each database.

This is possible using the inbuilt stored procedure SP_SpaceUsed. The small problem with SP_SpaceUsed is that it returns two result sets each with a different set of columns. This makes it tricky to aggregate the result set to a table for all databases on an instance.

Fig 1 Traditional SP_SPaceUsed

Enter @oneresultset

The new syntax for SP_SpaceUsed in SQL Server 2016 is as follows:

-- Listing 1: Syntax for SP_SpaceUsed
sp_spaceused [[ @objname = ] 'objname' ]  
[, [ @updateusage = ] 'updateusage' ] 
[, [ @mode = ] 'mode' ] 
[, [ @oneresultset = ] oneresultset ] 

The details of the parameters can be found at msdn. For the purpose fo this article, we are interested in @oneresultset. Using this new parameter, we are able to get the details shown in fig 1 as one single row.

The following table gives a description of the output:

Aggregation

Now what I can do with this is introduce SP_msforeachdb which will loop through my databases using the script in listing 1.

-- Listing 2: Looping Through Using SP_MSforeachdb
exec sp_MSforeachdb
@command1= '
use [?]
exec sp_spaceused @oneresultset=1'

Looks nice but not so nice. I go a little further by writing the results to a temporary table and querying that table.

-- Listing 3: Storing the Aggregated Result Se in a Table
create table #spaceused
(database_name varchar(100)
,database_size varchar(20)
,unallocated_space varchar(20)
,reserved varchar(20)
,data varchar(20)
,index_size varchar(20)
,unused varchar(20)
)
go
insert into #spaceused
exec sp_MSforeachdb
@command1= '
use [?]
exec sp_spaceused @oneresultset=1'
go
select
database_name [Database Name]
,cast(replace(database_size,'MB','') as decimal(10,2)) [Database Size (MB)]
,cast(replace(unallocated_space,'MB','') as decimal(10,2)) [Free Space (MB)]
,(cast(replace(reserved,'KB','') as int))/1024 [Reserved (MB)]
,(cast(replace(data,'KB','') as int))/1024 [Data Space (MB)]
,(cast(replace(index_size,'KB','') as int))/1024 [Index Space (MB)]
,(cast(replace(unused,'KB','') as int))/1024 [Unused Space (MB)]
from #spaceused
order by [Unused Space (MB)] desc

We could also generate a batch that resizes database with enough room to spare. This next code produces the code that will resize our databases.

-- Listing 4: generating a Batch to Resize Databases
with spaceused_set as (
select
database_name [Database Name]
,cast(replace(database_size,'MB','') as decimal(10,2)) [Database Size (MB)]
,cast(replace(unallocated_space,'MB','') as decimal(10,2)) [Free Space (MB)]
,(cast(replace(reserved,'KB','') as int))/1024 [Reserved (MB)]
,(cast(replace(data,'KB','') as int))/1024 [Data Space (MB)]
,(cast(replace(index_size,'KB','') as int))/1024 [Index Space (MB)]
,(cast(replace(unused,'KB','') as int))/1024 [Unused Space (MB)]
from #spaceused
)
select db_name(database_id) [Database Name], 'DBCC SHRINKDATABASE(N''' + db_name(database_id) + ''', 30 );' [Shrink Statement] 
from sys.databases where database_id in
(select top(10) db_id([Database Name]) from spaceused_set order by [Unused Space (MB)] desc);

Caveat on Shrinking Databases

I should add here that there are consequences of shrinking your databases. You should use this procedure only when you absolutely need to. When you do use it, it would make sense to rebuild clustered indexes on the affected database as soon as possible. In my case this have become necessary in cases where we had to do major data purging to reclaim space. There is no point reclaiming space if you cannot return same to the Operating System.

Thanks for reading and your comments are very welcome.