Post reply

Get space available for sql 2000 DB

  • July 21, 2009 at 7:34 am

    #72391

    when i right click on a SQL 2000 database in sql server managment studio and click properties i can see the 'space available'.

    how do i get his value with t-sql.

    i have tried

    create table #tmpspc

    (Fileid int,

    FileGroup int, TotalExtents int,

    UsedExtents int, Name sysname,

    FileName nchar(520))

    insert #tmpspc EXEC ('dbcc showfilestats')

    select ((totalextents*64)/1024)-((usedextents*64)/1024) from #tmpspc

    drop table #tmpspc

    but it doesnt return the same value.

    Please help.

    Thanks,

    Jules

    www.sql-library.com[/url]

  • July 21, 2009 at 7:57 am

    #1027621

    Did you try using sp_spaceused?

  • July 21, 2009 at 8:14 am

    #1027633

    that will tell me how much space is used 🙂 i want to know how much space is left is the space allocated to that database. thanks for your help.

    Jules

    www.sql-library.com[/url]

  • July 21, 2009 at 6:13 pm

    #1028039

    Um, actually it has all the information if I remember correctly. allocated and reserved space of the database...

  • July 24, 2009 at 6:37 am

    #1029607

    Hello,

    Maybe this help... I use this sql code to monitoring my datafiles.

    CREATE PROCEDURE TAMANHO_BASE @DB VARCHAR(100)

    AS

    BEGIN

    SET NOCOUNT ON

    CREATE TABLE #TMP

    (

    FILEID INT,

    FILEGROUP INT,

    TOTALEXTENTS INT,

    USEDEXTENTS INT,

    NAME VARCHAR(100),

    FILENAME VARCHAR(500)

    )

    INSERT #TMP

    EXEC ('USE ' + @DB + ' DBCC SHOWFILESTATS WITH NO_INFOMSGS')

    INSERT T_TAM_BASES

    SELECT @DB, NAME, TOTALEXTENTS, USEDEXTENTS

    FROM #TMP

    SET NOCOUNT OFF

    END

    CREATE TABLE T_TAM_BASES

    (

    NOME_BASE varchar(100),

    NOME_DATAFILE varchar(200),

    TOTALEXTENS INT,

    USEDEXTENTS INT

    )

    CREATE TABLE #TMP

    (

    FILEID INT,

    FILEGROUP INT,

    TOTALEXTENTS INT,

    USEDEXTENTS INT,

    NAME VARCHAR(100),

    FILENAME VARCHAR(500)

    )

    EXEC sp_msforeachdb 'TAMANHO_BASE ''?'''

    SELECT SUBSTRING(NOME_BASE,1,40) AS DB,

    CONVERT(VARCHAR(15), (sum(USEDEXTENTS) * 64) / 1024.0) AS USED_MB,

    CONVERT(VARCHAR(3), (sum(USEDEXTENTS) * 100) / sum(TOTALEXTENS)) AS '%',

    CONVERT(VARCHAR(15), (sum(TOTALEXTENS) * 64) / 1024.0) AS TOTAL_MB,

    CONVERT(VARCHAR(55), '|' + REPLICATE(CHAR(164), (sum(USEDEXTENTS) * 50) / sum(TOTALEXTENS)) + REPLICATE(' ', 50 -(sum(USEDEXTENTS) * 50) / sum(TOTALEXTENS)) + '|') AS CHART

    FROM T_TAM_BASES TMP

    GROUP BY NOME_BASE

    ORDER BY 3 DESC

    DROP TABLE dbo.T_TAM_BASES

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

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