February 15, 2017 at 4:54 am
Hi Team,
kindly move my Question to SQL Server Forum, as i couldnt find it in the list.
Hello Experts, kindly help.
I've a requirement to get Log file and Datafile available free space in Percentages. here is the query i researched in Internet, but not getting percentages. Kindly help.
------------------------------Data file size----------------------------
if exists (select * from tempdb.sys.all_objects where name like '%#dbsize%')
drop table #dbsize
create table #dbsize
(Dbname varchar(30),dbstatus varchar(20),Recovery_Model varchar(10) default ('NA'), file_Size_MB decimal(20,2)default (0),Space_Used_MB decimal(20,2)default (0),Free_Space_MB decimal(20,2) default (0))
go
insert into #dbsize(Dbname,dbstatus,Recovery_Model,file_Size_MB,Space_Used_MB,Free_Space_MB)
exec sp_msforeachdb
'use [?];
select DB_NAME() AS DbName,
CONVERT(varchar(20),DatabasePropertyEx(''?'',''Status'')) ,
CONVERT(varchar(20),DatabasePropertyEx(''?'',''Recovery'')),
sum(size)/128.0 AS File_Size_MB,
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB,
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB
from sys.database_files where type=0 group by type'
go
-------------------log size--------------------------------------
if exists (select * from tempdb.sys.all_objects where name like '#logsize%')
drop table #logsize
create table #logsize
(Dbname varchar(30), Log_File_Size_MB decimal(20,2)default (0),log_Space_Used_MB decimal(20,2)default (0),log_Free_Space_MB decimal(20,2)default (0))
go
insert into #logsize(Dbname,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB)
exec sp_msforeachdb
'use [?];
select DB_NAME() AS DbName,
sum(size)/128.0 AS Log_File_Size_MB,
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as log_Space_Used_MB,
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS log_Free_Space_MB
from sys.database_files where type=1 group by type'
go
--------------------------------database free size
if exists (select * from tempdb.sys.all_objects where name like '%#dbfreesize%')
drop table #dbfreesize
create table #dbfreesize
(name varchar(50),
database_size varchar(50),
Freespace varchar(50)default (0.00))
insert into #dbfreesize(name,database_size,Freespace)
exec sp_msforeachdb
'use ?;SELECT database_name = db_name()
,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ''MB'')
,''unallocated space'' = ltrim(str((
CASE
WHEN dbsize >= reservedpages
THEN (convert(DECIMAL(15, 2), dbsize) - convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576
ELSE 0
END
), 15, 2) + '' MB'')
FROM (
SELECT dbsize = sum(convert(BIGINT, CASE
WHEN type = 0
THEN size
ELSE 0
END))
,logsize = sum(convert(BIGINT, CASE
WHEN type <> 0
THEN size
ELSE 0
END))
FROM sys.database_files
) AS files
,(
SELECT reservedpages = sum(a.total_pages)
,usedpages = sum(a.used_pages)
,pages = sum(CASE
WHEN it.internal_type IN (
202
,204
,211
,212
,213
,214
,215
,216
)
THEN 0
WHEN a.type <> 1
THEN a.used_pages
WHEN p.index_id < 2
THEN a.data_pages
ELSE 0
END)
FROM sys.partitions p
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
LEFT JOIN sys.internal_tables it
ON p.object_id = it.object_id
) AS partitions'
-----------------------------------
if exists (select * from tempdb.sys.all_objects where name like '%#alldbstate%')
drop table #alldbstate
create table #alldbstate
(dbname varchar(25),
DBstatus varchar(25),
R_model Varchar(20))
--select * from sys.master_files
insert into #alldbstate (dbname,DBstatus,R_model)
select name,CONVERT(varchar(20),DATABASEPROPERTYEX(name,'status')),recovery_model_desc from sys.databases
--select * from #dbsize
insert into #dbsize(Dbname,dbstatus,Recovery_Model)
select dbname,dbstatus,R_model from #alldbstate where DBstatus <> 'online'
insert into #logsize(Dbname)
select dbname from #alldbstate where DBstatus <> 'online'
insert into #dbfreesize(name)
select dbname from #alldbstate where DBstatus <> 'online'
select
d.Dbname,
d.Free_Space_MB,
l.log_Free_Space_MB
from #dbsize d join #logsize l
on d.Dbname=l.Dbname join #dbfreesize fs
on d.Dbname=fs.name where name not in ('master','model','msdb')
order by Dbname
thanks in advance
February 15, 2017 at 5:06 am
Not getting what percentages? To find the percentage of free space in a file, divide the free space by the file size and multiply by 100.
John
February 15, 2017 at 9:09 am
Yes, this is what my requirement, but unable to keep that in the posted Query. i need help with that query to return percentages. please kindly modify accordingly and provide.
thanks much
February 15, 2017 at 9:13 am
vinod.mallolu - Wednesday, February 15, 2017 9:09 AMYes, this is what my requirement, but unable to keep that in the posted Query. i need help with that query to return percentages. please kindly modify accordingly and provide.thanks much
Select db_name(),fileid,((CAST ( (fileproperty(name, 'SpaceUsed')*8)/1024 as float))
/(CAST ( (size * 8)/1024 as float)) )*100
as Used
From dbo.sysfiles
you can work that into your requirement.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
February 15, 2017 at 9:20 am
Excellent.
this is the one which i've been using, but the requirement is to display for all databases. this query will give the result for the current database only.
tried using msforeachdb, but no luck. kindly help.
February 15, 2017 at 9:27 am
vinod.mallolu - Wednesday, February 15, 2017 9:20 AMExcellent.
this is the one which i've been using, but the requirement is to display for all databases. this query will give the result for the current database only.tried using msforeachdb, but no luck. kindly help.
"Work that into your requirement" was the important phrase there. That means you will have to do a bit of thinking for yourself. Here's a clue: you already have all the columns you need in your temp table, so why not select from that instead of from sysfiles?
John
February 15, 2017 at 9:30 am
vinod.mallolu - Wednesday, February 15, 2017 9:20 AMExcellent.
this is the one which i've been using, but the requirement is to display for all databases. this query will give the result for the current database only.tried using msforeachdb, but no luck. kindly help.
exec sp_msforeachdb
'use [?]
Select db_name(),fileid,
((CAST ( (fileproperty(name, ''SpaceUsed'')*8)/1024 as float))
/(CAST ( (size * 8)/1024 as float)) )*100
as Used
From dbo.sysfiles
go'
you might want to modify it to insert into temp tables etc
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
February 16, 2017 at 1:26 am
Hi Expert, it is working amazingly and giving result as expected.
but the problem is, All the results should come as one output. this is the requirement for a tool to monitor our databases.
if the percentage of free space available is less than 15%, we need to get an alert. for that, tools team required this query.
the Query you did provided is serving the exact requiremnt, but output should be single, not multiple.
kindly help.
February 16, 2017 at 10:08 am
drop table #temp
create table #temp(dbname varchar(25), fileid int, used float)
exec sp_msforeachdb
'use [?]
insert into #temp
Select
db_name(),fileid,
((CAST ( (fileproperty(name, ''SpaceUsed'')*8)/1024 as float))
/(CAST ( (size * 8)/1024 as float)) )*100
From dbo.sysfiles
go
'
select * from #temp
Just be careful of divide by zero, I did not build in any error checking for you.
You can also improve it by using CASE WHEN FILEID = 1 then 'DATA' etc...
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply