March 11, 2003 at 11:15 am
SQL 2k
Discrepancy between what disk space has been allocated and what disk space has been used. Sql is showing that I have allocated 6gb for a file group but that over 7gb of data has actually been used for indexes and tables.
Below are the queries:
--Returns the size of the Primary File group
select db_name() as DB,
'Primary' as FG, convert(varchar(10), ((size * (8192/1024))/1024)) + 'Mb' as Size
from dbo.sysfiles
where groupid = 1 --Primary
--Returns the size of the data on file group
select 'UsedKB' = sum(i.reserved * 8)
from sysindexes i(nolock)
inner join sysfilegroups fg (nolock) on i.groupid = fg.groupid
where i.indid in (0, 1, 255)
and fg.groupname = 'primary'
Additional info: On a weekly basis I run the updatestatistics dbcc. On a weekly basis I reindex all indexes. Any info on why these would be different would b appreciated.
Remember this when a developer tells you it will just be temporary. Temporary = Permanent.
March 11, 2003 at 11:40 am
Have you run "DBCC UPDATEUSAGE"?
March 11, 2003 at 11:44 am
Yes this is run weekly
Remember this when a developer tells you it will just be temporary. Temporary = Permanent.
March 11, 2003 at 2:25 pm
Another bit of information:
Running DBCC SHOWFILESTATS
returns the results as used in Enterprise Manager. These statistics show the filegroup as being 6gb available and 4.3gb used. Anyone know what this dbcc does to get these numbers?
Remember this when a developer tells you it will just be temporary. Temporary = Permanent.
February 25, 2004 at 11:05 am
For every database run
Exec ('Use [' + @dbname + '] Exec sp_SpaceUsed @updateusage = ' + '''True''')
in your script before selecting size. 'True' updates the table size to the correct size.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply