SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to get space used in SQL Server 2000


How to get space used in SQL Server 2000

Author
Message
JMSM
JMSM
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1092 Visits: 979
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 ;-)
Attachments
MANU-J.
MANU-J.
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7146 Visits: 8766
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
Michael Valentine Jones
Michael Valentine Jones
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14113 Visits: 11848
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
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058
JMSM
JMSM
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1092 Visits: 979
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 Smile
EdVassie
EdVassie
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13651 Visits: 3894
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: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: 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
dougznospam-mailbox
dougznospam-mailbox
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 110
Feel free to download this HTML Application to get a good picture of used/available space in all SQL dbs on a server.

http://dougzuck.com/hta


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search