Check database size and free space in SQL Server

Every day we have to once check database free space and below is the best query to give detail

Here is a simple SQL script to retrieve the free spaces on db files (data, log) of any database.

Query:  Quick Download


if convert(varchar(20),SERVERPROPERTY('productversion')) like '8%'
SELECT [name], fileid, filename, [size]/128 AS 'Total Size in MB',
[size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB',
CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Used Space In MB',
(100-((([size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128)/([size]/128))*100)) AS 'percentage Used'
FROM sysfiles
else
SELECT [name], file_id, physical_name, [size]/128 AS 'Total Size in MB',
[size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB',
CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Used Space In MB',
(100-((([size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128)/([size]/128))*100)) AS 'percentage Used'
FROM sys.database_files



Output:



namefile_idphysical_nameTotal Size in MBAvailable Space In MBUsed Space In MBpercentage Used
master1C:\DATA\master.mdf41.1252.87550
mastlog2C:\DATA\mastlog.ldf10.468750.531250


You can refer below for more scripts handy:

Frequently used DMV's in SQL Server

No comments

Algae Services. Powered by Blogger.