Check Database Size with Summary

• Download source - 2.43 KB

Introduction

This script calculates disk space used by each user database on SQL Server. It is particularly useful in a scenario when you have lots of databases on the server and limited disk space. It also provides the summary at the Top.

From the script output, the following things can be learned:

1. Database size (in Megabytes)
2. Log file size (in Megabytes)
3. Database physical location on server
4. Total disk space used by all databases on server

Understanding the Script

Problems that I faced during creation of this script were:

Problem 1: How to Dynamically Query Each Database on Server

Solution: Using "Exec" to dynamically build query for each database and looping them with cursor helped the solution.

Declare C1 cursor For select  [name] from master..sysdatabases with (nolock) where _
		[name] not in ('master', 'model', 'msdb', 'Capabilities_Grid', _
		'CDWINNT', 'dbatools', 'alert_db','distribution', 'gdmgaudit', _
		'mssecurity')  Order By [name] 
Open C1
Fetch Next From  C1 InTo @DatabaseName
While @@Fetch_Status <>-1
Begin
	-----Query each Database now
	Execute ('Insert Into ##tempValue select Groupid, [name], _
		[filename], cast((Size*8)/1024 as float) as [size] from  ' + _
		@DatabaseName + '..sysfiles with (nolock)  ')

	Select @dbName= [name], @DBPath = [filename], @DbSize = _
		[TotalSize] From ##tempValue where groupid = 1
	Select @LogSize = [TotalSize] From ##tempValue where groupid = 0

End
Close C1
Deallocate C1

Note: One thing to remember here is for database files, groupid should be 1 (in my case!) and for Log files, it should be 0.

Problem 2: Use of "Exec" Created Another Problem - Where to Store Value Returned from Query

Solution: Since "Exec" for dynamic query execution creates a different session, local session variables are not available. Using ##GlobalTable to store the value solved the problem, since these tables are available to each and every session created in the server.

Execute ('Insert Into ##tempValue select Groupid, [name], _
	[filename], cast((Size*8)/1024 as float) as [size] from  ' + _
	@DatabaseName + '..sysfiles with (nolock)  ')

Problem 3: How to Get the Database and Log Size

Solution: Getting the size of the database was indeed a simple find. The Database and Log size can be collected from table sysfiles (system table found in every database). This table provides us with physical location, size and other useful information about DB and log files.

select [name], cast((Size*8)/1024 as float) as [size] from sysfiles

Note: Size in table is stored as SQL Server Page (1 Page = 8 KB). Therefore, multiplying the size by 8 will give us the number of KB used by the database.

I believe in the quote "A picture speaks a 1000 words" and for coding OUTPUT speaks about 1000 lines of code...

Below is the sample output from my site: