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

How to get space used in SQL Server 2000 Expand / Collapse
Author
Message
Posted Tuesday, March 17, 2009 8:25 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 4:53 AM
Points: 186, Visits: 818
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.

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


  Post Attachments 
sp_GetSpaceUsedByDBsSQLS2005ChangeItToSQLS2000.txt (47 views, 4.42 KB)
Post #677504
Posted Wednesday, March 18, 2009 8:51 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 22, 2014 7:04 AM
Points: 1,688, Visits: 8,766
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
Posted Wednesday, March 18, 2009 9:46 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Saturday, October 25, 2014 3:18 AM
Points: 3,108, Visits: 11,504
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
Posted Thursday, March 19, 2009 6:52 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 4:53 AM
Points: 186, Visits: 818
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
Posted Friday, March 20, 2009 4:04 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:07 AM
Points: 2,888, Visits: 3,262
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 2014, 2012, 2008 R2, 2008 and 2005. 18 October 2014: now over 31,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
Posted Friday, June 19, 2009 3:35 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 »

Add to briefcase

Permissions Expand / Collapse