Click here to Skip to main content
Click here to Skip to main content

Check Database Size with Summary

, 22 Jun 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
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.

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:

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)

Share

About the Author

Kuldip Rindani
Systems Engineer
India India
C++, Unix Shell Scripts, DOTNET, ASP.NET, Sql Server 2000 and Oracle.

Comments and Discussions

 
GeneralMy vote of 2 PinmemberAcoustic22-Jun-09 6:01 
GeneralRe: My vote of 2 PinmemberKuldip Rindani23-Jun-09 7:09 

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

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

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