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
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
Nice article, I found another post for the same: http://www.sqlservercentral.com/blogs/zoras-sql-tips/2016/01/15/four-ways-to-find-free-space-in-sql-server-database-/
ReplyDeleteThank you for your guide to with upgrade information.
ReplyDeleteSql server DBA Online Training