Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

QUERY: GET SPACE USED/FREE in All Databases Expand / Collapse
Author
Message
Posted Friday, March 6, 2009 11:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 10:18 AM
Points: 186, Visits: 816
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 ;)
Post #670489
Posted Friday, March 6, 2009 11:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 21, 2013 12:32 AM
Points: 2,551, Visits: 2,593
There is an undocumented system procedure that executes any T-SQL query in all databases.

EXECUTE sp_MSforeachdb 'EXECUTE sp_spaceused'


--Ramesh

Post #670513
Posted Friday, March 6, 2009 11:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 10:18 AM
Points: 186, Visits: 816
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 ;)
Post #670524
Posted Friday, March 6, 2009 11:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 10:18 AM
Points: 186, Visits: 816
Sorry,

I think that you'll not understand the info that i post before.
I need to make one report that gives this information in one row for each database in one instance.

Thanks and regards
JMSM ;)
Post #670531
Posted Friday, March 6, 2009 12:57 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 26, 2014 5:09 AM
Points: 283, Visits: 1,118
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;




Check Your SQL Servers Quickly and Easily
www.sqlcopilot.com
Post #670595
Posted Friday, March 6, 2009 3:20 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 22, 2014 7:04 AM
Points: 1,688, Visits: 8,766
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
Post #670706
Posted Thursday, August 21, 2014 8:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 18, 2014 12:21 PM
Points: 14, Visits: 120
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
Post #1605834
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse