June 4, 2008 at 11:51 pm
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?
June 5, 2008 at 6:47 pm
anyone?!?
i just want to get that 'space available' shown in taskpad (database properties) using t-sql.
June 5, 2008 at 8:30 pm
Try this and see if that is what you are looking for.
June 5, 2008 at 8:34 pm
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
June 5, 2008 at 10:42 pm
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
June 5, 2008 at 11:05 pm
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
June 5, 2008 at 11:17 pm
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
June 5, 2008 at 11:21 pm
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..
June 5, 2008 at 11:26 pm
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.
June 6, 2008 at 7:37 am
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