Space availabe

  • i dont get it..

    sp_spaceused give me a different value od unallocated space compare to taskpad space available.

    can someone clear it up for me?!?

    or at what system table will i get the value shown in space available in task pad?

    sample:

    when viewing the task pad i get the space available = .44 MB

    when i run sp_spaceused i get unallocated space = .13 MB unused 128 KB

    now, where can i get this .44 MB?

  • anyone?!?

    i just want to get that 'space available' shown in taskpad (database properties) using t-sql.

  • Try this and see if that is what you are looking for.

    http://www.mssqltips.com/tip.asp?tip=1510

  • I just realized you said taskpad. Here is the 2000 version.

    CREATE TABLE #TmpFixedDrives

    (Drive char(1),

    MBFree int)

    INSERT INTO #TmpFixedDrives

    EXEC xp_fixeddrives

    CREATE TABLE #TmpSpaceUsed

    (DBName varchar(50),

    FileNme varchar(50),

    SpaceUsed float)

    INSERT INTO #TmpSpaceUsed

    EXEC('sp_msforeachdb''use ?; Select ''''?'''' DBName, Name FileNme, fileproperty(Name,''''SpaceUsed'''') SpaceUsed from sysfiles''')

    SELECT C.Drive,

    CASE WHEN (C.MBFree) > 1000 THEN

    CAST(CAST(((C.MBFree) / 1024.0) AS decimal(18,2)) AS varchar(20)) + ' GB'

    ELSE CAST(CAST((C.MBFree) AS decimal(18,2)) AS varchar(20)) + ' MB'

    END AS DiskSpaceFree,

    A.name AS DatabaseName,

    B.name AS FileNme,

    CASE B.Type WHEN 0 THEN 'DATA' ELSE Type_Desc END FileType,

    CASE WHEN (B.size * 8 / 1024.0) > 1000 THEN

    CAST(CAST(((B.size * 8 / 1024) / 1024.0) AS decimal(18,2)) AS varchar(20)) + ' GB'

    ELSE CAST(CAST((B.size * 8 / 1024.0) AS decimal(18,2)) AS varchar(20)) + ' MB'

    END AS FileSize,

    CAST((B.size * 8 / 1024.0)-(D.SpaceUsed/128.0) AS Decimal(15,2)) SpaceFree,

    B.Physical_Name

    FROM sys.databases A

    JOIN sys.master_files B

    ON A.database_id = B.database_id

    JOIN #TmpFixedDrives C

    ON Left(B.Physical_Name,1) = C.Drive

    JOIN #TmpSpaceUsed D

    ON A.name = D.DBName AND B.name = D.FileNme

    ORDER BY DiskSpaceFree, SpaceFree DESC

    DROP TABLE #TmpFixedDrives

    DROP TABLE #TmpSpaceUsed

  • thanks ken..

    but i already have that..

    i want to script it as a whole..

    with a result like

    myDatabase size space available

    not

    mydatabase Datafile size space available

    mydatabase logfile size space available

  • Try this...

    CREATE TABLE #TmpSpaceUsed

    (DBName varchar(50),

    FileNme varchar(50),

    SpaceUsed float)

    INSERT INTO #TmpSpaceUsed

    EXEC('sp_msforeachdb''use ?; Select ''''?'''' DBName, Name FileNme, fileproperty(Name,''''SpaceUsed'''') SpaceUsed from sysfiles''')

    SELECT

    A.name AS DatabaseName,

    SUM(CAST((B.size * 8 / 1024.0) AS decimal(18,2)) ) FileSize,

    SUM(CAST((B.size * 8 / 1024.0)-(D.SpaceUsed/128.0) AS Decimal(15,2))) SpaceFree

    FROM sysdatabases A

    JOIN sysaltfiles B

    ON A.dbid = B.dbid

    JOIN #TmpSpaceUsed D

    ON A.name = D.DBName AND B.name = D.FileNme

    group by A.name

    ORDER BY SpaceFree DESC

    DROP TABLE #TmpSpaceUsed

  • great!!..;p

    exactly what i'm looking for almost a week..;p

    many thanks..

    but it only display default databases..:hehe:

    may be need a little modification..;p

  • ops..;p

    wrong comment..;p

    it does get all databases.. (run it on a new instaled instance..:P)

    haha

    at last..

    many thanks again Ken..

  • one more thing..

    some free space result a (-) nevative value.. how is that?!?

    try to run:

    DBCC UPDATEUSAGE ('mydatabase') WITH NO_INFOMSGS

    GO

    and / or

    sp_spaceused @updateusage = 'TRUE'

    but still results to negative value.

  • I generally see this in the tempdb. I think it is because the database is growing.

Viewing 10 posts - 1 through 9 (of 9 total)

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