September 8, 2015 at 10:45 am
How to find available database space?
September 8, 2015 at 11:08 am
I have a blog post for that:
http://spaghettidba.com/2014/09/05/database-free-space-monitoring-the-right-way/
Hope this helps
-- Gianluca Sartori
September 8, 2015 at 11:22 am
I am using the script that I found online which gives for mdf and ldf files.
But, how to get total free space for the database?
set nocount on
create table #dbfileInfo(
name varchar(300),
location varchar(300),
filesizeMB decimal(9,2),
spaceUsedMB decimal(9,2),
FreespaceMB decimal(9,2))
declare @mySQL nvarchar(2000)
DECLARE @dbName varchar(MAX)
DECLARE @cur_DBName CURSOR
SET @cur_DBName = CURSOR FOR
select name from sys.databases
OPEN @cur_DBName
FETCH NEXT
FROM @cur_DBName INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @dbName
if DATABASEPROPERTYEX(@dbName, 'status') = 'ONLINE'
begin
select @mySQL =
'
use ' + @dbname + '
INSERT INTO #dbfileInfo
select
name
, filename
, convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
, convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)) as SpaceUsedMB
, convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) as FreeSpaceMB
from dbo.sysfiles a
'
exec sp_executesql @mySQL
end
FETCH NEXT
FROM @cur_DBName INTO @dbName
END
CLOSE @cur_DBName
DEALLOCATE @cur_DBName
GO
select * from #dbfileInfo
drop table #dbfileInfo
September 8, 2015 at 12:20 pm
You have everything you need in the script I linked in my previous reply.
-- Gianluca Sartori
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply