How to get space used in SQL Server 2000
-
Hello,
I need to get the following information of my instances SQL Server 2000.
I put this working in SQL Server 2005 but in SQL Server 2000 im having a lot of troubles so i get the script that i send you in attach (sp_GetSpaceUsedByDBsSQLS2005ChangeItToSQLS2000.txt) and i've tried to make some changes in it but without success.
All that i need is that you send me one script so that i can get the same information in SQL Server 2000 or if you can help me on how to change the attached script. :satisfied:
The output should be as follow.
"Instance Name" "Number of DBs (in the instance)" "Allocated Space" "Used Space" "Free Space"
Hope that you can help me.
Thanks and regards,
JMSM 😉
-
Try this:
USE master
GO
/****** Object: StoredProcedure [dbo].[sp_GetSpaceUsedByDBs] Script Date: 03/12/2009 14:41:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create procedure [dbo].[sp_GetSpaceUsedByDBs]
as
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,
d.cmptlevel 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.dbo.sysdatabases 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.dbid = 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'),
d.cmptlevel,
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;
*/
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[MyAuxTable]') AND type in (N'U'))
DROP TABLE [dbo].[MyAuxTable]
create table MyAuxTable (dbname varchar(30), size int, used int, logsize bigint,logspaceused bigint);
insert into MyAuxTable select name,size,used,logsize,logspaceused from #dbs1;
select @@servername as 'Nome da Instancia', (select count(name) from master.dbo.sysdatabases) as 'Nº de BDs',
sum(a.size)/(select count(name) from master.dbo.sysdatabases) as 'Espaço Alocado (mb)',
sum(a.used)/(select count(name) from master.dbo.sysdatabases) as 'Espaço Utilizado (mb)',
(sum(a.size)-sum(a.used))/(select count(name) from master.dbo.sysdatabases) as 'Espaço Livre (mb)'
from MyAuxTable a, master.dbo.sysdatabases b
/*
select name,size,used,logsize,logspaceused from #dbs1;
*/
drop table #dbsize1;
drop table #dbs1;
drop table #ls;
--exec sp_GetSpaceUsedByDBs
MJ
-
The script on this link works with SQL Server 7.0, 2000, and 2005 (and probably 2008) and has a number of queries to analyze the info various ways:
Total by Database and File
Total by Database and Filegroup
Total by Database and Filegroup Type
Total by Disk, Database, and Filepath
Total by Disk and Database
Total by Database
Get Server Database File Information
-
Hello,
Thanks .. .. thanks and thanks again Michael.
You've been very useful to me hope one day i can be useful to all of the members of this fantastic forum as you.
Thanks and regards,
JMSM 🙂
-
The space used stats can be stale. This is particularly true in SQL Server 2000, and still apllies to a lesser extent to SQL 2005.
It is worth running DBCC UPDATEUSAGE maybe on a weekly basis if you want figures to rely on.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
Feel free to download this HTML Application to get a good picture of used/available space in all SQL dbs on a server.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply