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:
Column name | Data type | Description |
database_name | nvarchar(128) | Name of the current database. |
database_size | varchar(18) | Size of the current database in megabytes. database_size includes both data and log files. |
unallocated space | varchar(18) | Space in the database that has not been reserved for database objects. |
reserved | varchar(18) | Total amount of space allocated by objects in the database. |
data | varchar(18) | Total amount of space used by data. |
index_size | varchar(18) | Total amount of space used by indexes. |
unused | varchar(18) | Total amount of space reserved for objects in the database, but not yet used. |
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.