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

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058

  • 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.

    http://dougzuck.com/hta

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply