Caclulate database size and free space left

Dear all,
Is that possible to calculate the database size and free space left by t-sql
script?
IvanIvan
sp_helpdb 'northwind'
There is an article in the BOL for this subject
"Ivan" <ivan@.microsoft.com> wrote in message
news:ug9q%233L8FHA.1028@.TK2MSFTNGP11.phx.gbl...
> Dear all,
> Is that possible to calculate the database size and free space left by
> t-sql script?
> Ivan
>|||It had the db size and file size. However, I need the space available in the
file.
The most similar function is sp_space_used.
But I found the space left is not match with the one I saw in Enterprise
Manager.
Do you know how to calculate the space left in enterprise manager?
Ivan
"Uri Dimant" <urid@.iscar.co.il> glsD:e%23JXCgM8FHA.3752@.tk2msftngp13.phx.gbl...[vb
col=seagreen]
> Ivan
> sp_helpdb 'northwind'
> There is an article in the BOL for this subject
>
>
> "Ivan" <ivan@.microsoft.com> wrote in message
> news:ug9q%233L8FHA.1028@.TK2MSFTNGP11.phx.gbl...
>[/vbcol]|||Ivan
sp_spaceused in the BOL
"Ivan" <ivan@.microsoft.com> wrote in message
news:%23FcO0QO8FHA.2304@.TK2MSFTNGP10.phx.gbl...
> It had the db size and file size. However, I need the space available in
> the file.
> The most similar function is sp_space_used.
> But I found the space left is not match with the one I saw in Enterprise
> Manager.
> Do you know how to calculate the space left in enterprise manager?
> Ivan
>
> "Uri Dimant" <urid@.iscar.co.il>
> glsD:e%23JXCgM8FHA.3752@.tk2msftngp13.phx.gbl...
>|||I once wrote a post for that:
http://groups.google.de/group/comp... />
cf442935d5
HTH, jens SUessmeyer.|||Hi Ivan,
I use the below mentioned script for this purpose. This provides me separate
result set for Data Files and Log Files. The only possible issue with this
script is that it is using undocumented SP (DBCC SHOWFILESTATS). The positiv
e
side is that it gives the sizes as shown in EM.
--Script to calculate information about the Data Files
SET QUOTED_IDENTIFIER OFF
SET NOCOUNT ON
DECLARE @.dbname varchar(50)
declare @.string varchar(250)
set @.string = ''
create table #datafilestats
( Fileid tinyint,
FileGroup1 tinyint,
TotalExtents1 dec (8, 2),
UsedExtents1 dec (8, 2),
[Name] varchar(50),
[FileName] sysname )
create table #dbstats
( dbname varchar(50),
FileGroupId tinyint,
FileGroupName varchar(25),
TotalSizeinMB dec (8, 2),
UsedSizeinMB dec (8, 2),
FreeSizeinMB dec (8, 2))
DECLARE dbnames_cursor CURSOR FOR SELECT name FROM master..sysdatabases
OPEN dbnames_cursor
FETCH NEXT FROM dbnames_cursor INTO @.dbname
WHILE (@.@.fetch_status = 0)
BEGIN
set @.string = 'use ' + @.dbname + ' DBCC SHOWFILESTATS'
insert into #datafilestats exec (@.string)
insert into #dbstats (dbname, FileGroupId, TotalSizeinMB, UsedSizeinMB)
select @.dbname, FileGroup1, sum(TotalExtents1)*65536.0/1048576.0,
sum(UsedExtents1)*65536.0/1048576.0
from #datafilestats group by FileGroup1
set @.string = 'use ' + @.dbname + ' update #dbstats set FileGroupName =
sysfilegroups.groupname from #dbstats, sysfilegroups where
#dbstats.FileGroupId = sysfilegroups.groupid and #dbstats.dbname =''' +
@.dbname + ''''
exec (@.string)
update #dbstats set FreeSizeinMB = TotalSizeinMB - UsedSizeinMB where
dbname = @.dbname
truncate table #datafilestats
FETCH NEXT FROM dbnames_cursor INTO @.dbname
END
CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor
drop table #datafilestats
select * from #dbstats
drop table #dbstats
--
--Script to calculate information about the Log Files
set nocount on
create table #LogUsageInfo
( db_name varchar(50),
log_size dec (8, 2),
log_used_percent dec (8, 2),
status dec (7, 1) )
insert #LogUsageInfo exec ('dbcc sqlperf(logspace) with no_infomsgs')
select * from #LogUsageInfo
drop table #LogUsageInfo
---
"Ivan" wrote:

> Dear all,
> Is that possible to calculate the database size and free space left by t-s
ql
> script?
> Ivan
>
>|||I have another new task for finding free space left.
Before, I need get the free space left in SQL 2000 databse.
Now, I need get the free space left in SQL 6.5
Need help~~~~~~~~~
Ivan
"Uri Dimant" <urid@.iscar.co.il> glsD:uu74eVO8FHA.1188@.TK2MSFTNGP12.phx.gbl...[vbco
l=seagreen]
> Ivan
> sp_spaceused in the BOL
>
> "Ivan" <ivan@.microsoft.com> wrote in message
> news:%23FcO0QO8FHA.2304@.TK2MSFTNGP10.phx.gbl...
>[/vbcol]|||See my post below.
HTH, Jens Suessmeyer.|||Any SQL 6.5 version?
there's no sysfiles in sql 6.5
Ivan
"Jens" <Jens@.sqlserver2005.de>
'?:1132912972.019598.201170@.g47g2000cwa.googlegroups.com...
> See my post below.
> HTH, Jens Suessmeyer.
>