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:
You can refer below for more scripts handy:
Frequently used DMV's in SQL Server
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:
| name | file_id | physical_name | Total Size in MB | Available Space In MB | Used Space In MB | percentage Used |
| master | 1 | C:\DATA\master.mdf | 4 | 1.125 | 2.875 | 50 |
| mastlog | 2 | C:\DATA\mastlog.ldf | 1 | 0.46875 | 0.53125 | 0 |
You can refer below for more scripts handy:
Frequently used DMV's in SQL Server
I am happy to find your distinguished way of writing the post. Now you make it easy for me to understand and implement the concept. Thank you for the post.
ReplyDeleteDoreen
www.gofastek.com
Nice concept, but your calculations for percent used are wrong. percent used is simply used/available*100 Try this:
ReplyDeleteif 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',
(CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 / ([size]/128)) * 100 as ' Correct Percent 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',
((CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0) / ([size]/128))* 100 as 'Correct Percent Used'
FROM sys.database_files
This is really interesting and knowledgeable. Thanks for sharing. I really appreciate it a lot. Please do more blogs in the future. Thank you and God bless to the blogger!
ReplyDeletewww.imarksweb.org