How to get space used in SQL Server 2000

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » How to get space used in SQL Server 2000

How to get space used in SQL Server 2000

Rate Topic

Display Mode

Topic Options

Author

Message

JMSM

Posted Tuesday, March 17, 2009 8:25 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 10:08 AM
Points: 185, Visits: 752

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 Wink

Post Attachments

sp_GetSpaceUsedByDBsSQLS2005ChangeItToSQLS2000.txt (41 views, 4.42 KB)

Post #677504

MANU-J.

Posted Wednesday, March 18, 2009 8:51 PM

SSCommitted

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 1:56 PM
Points: 1,658, Visits: 8,564

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

Post #679122

Michael Valentine Jones

Posted Wednesday, March 18, 2009 9:46 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:43 AM
Points: 2,945, Visits: 10,517

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

Post #679136

JMSM

Posted Thursday, March 19, 2009 6:52 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 10:08 AM
Points: 185, Visits: 752

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 :)

Post #679351

EdVassie

Posted Friday, March 20, 2009 4:04 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 3:26 AM
Points: 2,621, Visits: 2,759

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.

Author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2012, 2008 R2, 2008 and 2005. 25 March 2013: now over 23,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.

Post #680238

dougznospam-mailbox

Posted Friday, June 19, 2009 3:35 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, November 22, 2010 12:06 PM
Points: 14, 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

Post #738672

« Prev Topic | Next Topic »

Permissions