QUERY: GET SPACE USED/FREE in All Databases
-
Hello again,
I need to get the following information:
- Space Allocated from each database,
- Space Used and from each database and
- Space Free from each database in one instance and Total Space Used on that instance.
Can you send me one Query/Stored Procedure that give me all this output.
I need to make one report with all this information.
I know that with sp_spaceused i get this information but i need to get this information for all databases in one cursor or somethin like this.
Thanks and regards,
JMSM 😉
-
There is an undocumented system procedure that executes any T-SQL query in all databases.
EXECUTE sp_MSforeachdb 'EXECUTE sp_spaceused'
--Ramesh
-
Hello,
Thanks for the information you send me.
But what i need is to get all this information in one table, so i need to insert this info in one temporary table to execute one procedure that send this information in one row for each table.
Thanks and regards,
JMSM 😉
-
Sorry,
I think that you'll not understand the info that i post before. :blink:
I need to make one report that gives this information in one row for each database in one instance.
Thanks and regards
JMSM 😉
-
I have a script that gets most of what you want, together with a few other bits of info.
It's a bit messy as it has evolved over time, so you could remove a few superfluous bits if you want.
create table #ls (name varchar(255), LogSize real, LogSpaceUsed real, Status int)insert #ls exec ('dbcc sqlperf(logspace)')
declare @name varchar(255), @sql varchar(1000);
select d.name, DATABASEPROPERTYEX(d.name, 'Status') Status,
case when DATABASEPROPERTYEX(d.name, 'IsAutoCreateStatistics') = 1
then 'ON' else 'OFF' end AutoCreateStatistics,
case when DATABASEPROPERTYEX(d.name, 'IsAutoUpdateStatistics') = 1
then 'ON' else 'OFF' end AutoUpdateStatistics,
case when DATABASEPROPERTYEX(d.name, 'IsAutoShrink') = 1
then 'ON' else 'OFF' end AutoShrink,
case when DATABASEPROPERTYEX(d.name, 'IsAutoClose') = 1
then 'ON' else 'OFF' end AutoClose,
DATABASEPROPERTYEX(d.name, 'Collation') Collation,
DATABASEPROPERTYEX(d.name, 'Updateability') Updateability,
DATABASEPROPERTYEX(d.name, 'UserAccess') UserAccess,
replace(page_verify_option_desc, '_', ' ') PageVerifyOption,
d.compatibility_level CompatibilityLevel,
DATABASEPROPERTYEX(d.name, 'Recovery') RecoveryModel,
convert(bigint, 0) as Size, convert(bigint, 0) Used,
case when sum(NumberReads+NumberWrites) > 0
then sum(IoStallMS)/sum(NumberReads+NumberWrites) else -1 end AvgIoMs,
ls.LogSize, ls.LogSpaceUsed,
b.backup_start_date LastBackup
into #dbs1
from master.sys.databases as d
left join msdb..backupset b
on d.name = b.database_name and b.backup_start_date = (
select max(backup_start_date)
from msdb..backupset
where database_name = b.database_name
and type = 'D')
left join ::fn_virtualfilestats(-1, -1) as vfs
on d.database_id = vfs.DbId
join #ls as ls
on d.name = ls.name
group by d.name, DATABASEPROPERTYEX(d.name, 'Status'),
case when DATABASEPROPERTYEX(d.name, 'IsAutoCreateStatistics') = 1
then 'ON' else 'OFF' end,
case when DATABASEPROPERTYEX(d.name, 'IsAutoUpdateStatistics') = 1
then 'ON' else 'OFF' end,
case when DATABASEPROPERTYEX(d.name, 'IsAutoShrink') = 1
then 'ON' else 'OFF' end,
case when DATABASEPROPERTYEX(d.name, 'IsAutoClose') = 1
then 'ON' else 'OFF' end,
DATABASEPROPERTYEX(d.name, 'Collation'),
DATABASEPROPERTYEX(d.name, 'Updateability'),
DATABASEPROPERTYEX(d.name, 'UserAccess'),
page_verify_option_desc,
d.compatibility_level,
DATABASEPROPERTYEX(d.name, 'Recovery'),
ls.LogSize, ls.LogSpaceUsed, b.backup_start_date;
create table #dbsize1 (
fileid int,
filegroup int,
TotalExtents bigint,
UsedExtents bigint,
dbname varchar(255),
FileName varchar(255));
declare c1 cursor for select name from #dbs1;
open c1;
fetch next from c1 into @name;
while @@fetch_status = 0
begin
set @sql = 'use [' + @name + ']; DBCC SHOWFILESTATS WITH NO_INFOMSGS;'
insert #dbsize1 exec(@sql);
update #dbs1
set Size = (select sum(TotalExtents) / 16 from #dbsize1),
Used = (select sum(UsedExtents) / 16 from #dbsize1)
where name = @name;
truncate table #dbsize1;
fetch next from c1 into @name;
end;
close c1;
deallocate c1;
select * from #dbs1
order by name;
drop table #dbsize1;
drop table #dbs1;
drop table #ls;
-
Hope this works for you:
Create TABLE #db_file_information(
fileid integer
, theFileGroup integer
, Total_Extents integer
, Used_Extents integer
, db varchar(30)
, file_Path_name varchar(300))
-- Get the size of the datafiles
insert into #db_file_information
( fileid
, theFileGroup
, Total_Extents
, Used_Extents
, db
, file_Path_name )
exec sp_MSForEachDB 'Use ?; DBCC showfilestats'
-- add two columns to the temp table
alter table #db_file_information add PercentFree as
((Total_Extents-Used_Extents)*100/(Total_extents))
alter table #db_file_information add TotalSpace_MB as
((Total_Extents*64)/1024)
alter table #db_file_information add UsedSpace_MB as
((Used_Extents*64)/1024)
alter table #db_file_information add FreeSpace_MB as
((Total_Extents*64)/1024-(Used_Extents*64)/1024)
select * from #db_file_information
drop table #db_file_information
MJ
-
MJ,
The DBCC command showfilestats returns the logical name and the physical name of the files associated to the current database. If you have verbose names, as in a sharepoint installation, there are 2 problems with the code.
1. The USE ? command needs to have brackets because of database names that have dashes in them.
2. The db column in your table variable should be renamed to logical file name and increased in size. I would recommend using the sysname type.
Otherwise, this is a fine bit of code. Thank you for the tip.
Your Friendly Neighborhood DBA,
Chuck
-
This was removed by the editor as SPAM
-
hope this is what you are looking for
https://gallery.technet.microsoft.com/SIZE-OF-ALL-DATABASES-IN-0337f6d5
-
Wow, this thread has come back from the dead not once, but twice now!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Ramesh Saive (3/6/2009)
There is an undocumented system procedure that executes any T-SQL query in all databases.EXECUTE sp_MSforeachdb 'EXECUTE sp_spaceused'
Just in case anyone else happens to stumble on this thread, this proc is not safe to use for production work. Lookup Aaron Bertrand's article on the topic for reasons why.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Orlando Colamatteo (2/15/2016)
Ramesh Saive (3/6/2009)
There is an undocumented system procedure that executes any T-SQL query in all databases.EXECUTE sp_MSforeachdb 'EXECUTE sp_spaceused'
Just in case anyone else happens to stumble on this thread, this proc is not safe to use for production work. Lookup Aaron Bertrand's article on the topic for reasons why.
You should just tell us, instead. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
It can skip databases: https://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/
It can be very disruptive. Scary fact: Maintenance Plans used to rely on the proc, may still.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Orlando Colamatteo (2/15/2016)
It can skip databases: https://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/It can be very disruptive. Scary fact: Maintenance Plans used to rely on the proc, may still.
I have to say that I don't know if they do or not. However, that's just another reason to not use maintenance plans. 😉
-
Orlando Colamatteo (2/15/2016)
It can skip databases: https://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/It can be very disruptive. Scary fact: Maintenance Plans used to rely on the proc, may still.
Thanks, Orlando. The good news is that I generally don't use them (sp_msforeachdb or sp_msforeachtable) because they're some of the ugliest code I've ever seen (I can't actually remember the last time I used them). I don't see why they might skip a "catalog" but I've not done a deep dive on the code and so can't say for sure. I just can't look something that ugly in the face for very long. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply