Click here to Skip to main content
13,001,514 members (61,555 online)
Click here to Skip to main content
Add your own
alternative version


11 bookmarked
Posted 22 Jun 2009

Check Database Size with Summary

, 22 Jun 2009
Rate this:
Please Sign up or sign in to vote.
This script calculates size of every user database with summary


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

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:

DBCheckSize.JPG - Click to enlarge image


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


About the Author

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

You may also be interested in...

Comments and Discussions

GeneralMy vote of 2 Pin
Acoustic22-Jun-09 5:01
memberAcoustic22-Jun-09 5:01 
GeneralRe: My vote of 2 Pin
Kuldip Rindani23-Jun-09 6:09
memberKuldip 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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170624.1 | Last Updated 23 Jun 2009
Article Copyright 2009 by Kuldip Rindani
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid