View Size of Each Database in SQL Server 2012

In this article I describe how to View Size of Each Database stored in SQL Server.
  • 3211

Introduction

In this article I describe how to show space used by each database stored in SQL Server. Sometimes we need to find particular database size or we need to see the list of  size of each database stored in SQL Server. To show all the databases size list, there are following ways to list all the databases.

View size of particular database using SQL Server Management Studio.

  • Open Microsoft SQL Server Management Studio.
  • Connect to database engine. Open object explorer.
  • Click Databases node and expand it. Now see the list of all databases.
  • Right click on database whose size is to be viewed and select properties.

 properties of database.jpg

Output:

size of database.jpg

View size of each database on an instance of SQL Server using T-SQL.

SELECT sysdb.name,

CONVERT(varchar,ROUND(SUM(mfile.size) * 8 / 1024, 0)) + 'MB' as Size_MBs

FROM sys.master_files mfile

INNER JOIN sys.databases sysdb ON sysdb.database_id = mfile.database_id

WHERE sysdb.database_id > 4

GROUP BY sysdb.name

ORDER BY sysdb.name

Output:

view size list of each database in sql.jpg

View size of each database on an instance of SQL Server using  Sp_msforeachdb Syastem Stored Procedure:

In my previous article I described how to use  Sp_msforeachdb. You can visit from here- sp_MSforeachdb Stored Procedure in SQL Server 2012.  This stored procedure allows you to iterate through all databases to perform a specific task to run a query against all the databases. It executes SQL query against each database associated with current SQL Server instance. Run following code to view list of all databases:

declare @cmd varchar(500)

set @cmd='use [?];exec sp_spaceused '

exec sp_MSforeachdb @cmd

Output:

view size list of each database.jpg

Categories

More Articles

© 2013 dotNetheaven. All rights reserved.