Click here to Skip to main content
15,867,921 members
Articles / Database Development / MySQL

Check Database Size with Summary

Rate me:
Please Sign up or sign in to vote.
2.33/5 (2 votes)
22 Jun 2009CPOL2 min read 21.3K   12   2
This script calculates size of every user database with summary

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.

SQL
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.

SQL
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.

SQL
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:

DBCheckSize.JPG - Click to enlarge image

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
United States United States
VC++, MFC, COM, Unix Shell Scripts
Linux Kernel Development

Comments and Discussions

 
GeneralMy vote of 2 Pin
TylerBrinks22-Jun-09 5:01
TylerBrinks22-Jun-09 5:01 
GeneralRe: My vote of 2 Pin
Kuldip Rindani23-Jun-09 6:09
Kuldip Rindani23-Jun-09 6:09 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.