Wednesday, 12 December 2012

How to find the Space usage details of Database files in SQL Server 2012



DBAs and Developers are interested to understand the database space usage details to make correct decision. DBAs need to monitor the database space usage which is part of their maintenance activities.


In SQL Server, it is very easy to find the database space usage details. We can identify it through GUI [SQL Server Management Studio] as well as through T-SQL scripts. Let’s discuss both the methods here.


Using SQL Server Management Studio


1.   Connect to specific SQL Server Instance.

2.   Expand the database.

3.  Right Click on specific database – Reports – Standard Reports – Disk Usage.

Below mentioned screen shots explains the steps mentioned here.







Using T-SQL Scripts


We can find the space usage details through system stored procedure [sp_spaceused] as well as through system catalog [sys.database_files].

USE [EmployeeDB]
GO
EXEC sp_spaceused N'dbo.Employee';
GO

USE [EmployeeDB]
GO
SELECT file_id, name, type_desc, physical_name, size, max_size
FROM sys.database_files ;
GO

Output of these T-SQL scripts have been captured and depicted here for better understanding.