Database Space Used

I'm trying to report the amount of space allocated and used for each
database. I use sysfiles to report the total space allocated to a database,
but can't find information regarding how much of that space is has been used.
I want to store that information in a table each week/month to chart growth.
Is there a system table that stores how much space of each datafile/database
is being used?
Thanks. Any help would be appreciated.
Ron
You can get that information from the stored procedure sp_spaceused. That
just sums the space used and reserved for the tables and indexes in the
database from sysindexes. You can study the code of sp_spaceused (it's in
the master database), but what you want is basically:
SELECT SUM(reserved)
FROM sysindexes
WHERE indid in (0, 1, 255)
Jacco Schalkwijk
SQL Server MVP
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:99CE9BA6-3B46-434B-B769-17FBD8BE4C9C@.microsoft.com...
> I'm trying to report the amount of space allocated and used for each
> database. I use sysfiles to report the total space allocated to a
> database,
> but can't find information regarding how much of that space is has been
> used.
> I want to store that information in a table each week/month to chart
> growth.
> Is there a system table that stores how much space of each
> datafile/database
> is being used?
> Thanks. Any help would be appreciated.
> Ron
>
|||There is an undocumented DBCC command 'DBCC SHOWFILESTATS' that returns
information on allocations per file. You can write a simple wrapper that
aggregates per filegroup or database (or both).
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:99CE9BA6-3B46-434B-B769-17FBD8BE4C9C@.microsoft.com...
> I'm trying to report the amount of space allocated and used for each
> database. I use sysfiles to report the total space allocated to a
database,
> but can't find information regarding how much of that space is has been
used.
> I want to store that information in a table each week/month to chart
growth.
> Is there a system table that stores how much space of each
datafile/database
> is being used?
> Thanks. Any help would be appreciated.
> Ron
>

Database Space Used

I'm trying to report the amount of space allocated and used for each
database. I use sysfiles to report the total space allocated to a database,
but can't find information regarding how much of that space is has been used.
I want to store that information in a table each week/month to chart growth.
Is there a system table that stores how much space of each datafile/database
is being used?
Thanks. Any help would be appreciated.
RonYou can get that information from the stored procedure sp_spaceused. That
just sums the space used and reserved for the tables and indexes in the
database from sysindexes. You can study the code of sp_spaceused (it's in
the master database), but what you want is basically:
SELECT SUM(reserved)
FROM sysindexes
WHERE indid in (0, 1, 255)
--
Jacco Schalkwijk
SQL Server MVP
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:99CE9BA6-3B46-434B-B769-17FBD8BE4C9C@.microsoft.com...
> I'm trying to report the amount of space allocated and used for each
> database. I use sysfiles to report the total space allocated to a
> database,
> but can't find information regarding how much of that space is has been
> used.
> I want to store that information in a table each week/month to chart
> growth.
> Is there a system table that stores how much space of each
> datafile/database
> is being used?
> Thanks. Any help would be appreciated.
> Ron
>|||There is an undocumented DBCC command 'DBCC SHOWFILESTATS' that returns
information on allocations per file. You can write a simple wrapper that
aggregates per filegroup or database (or both).
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:99CE9BA6-3B46-434B-B769-17FBD8BE4C9C@.microsoft.com...
> I'm trying to report the amount of space allocated and used for each
> database. I use sysfiles to report the total space allocated to a
database,
> but can't find information regarding how much of that space is has been
used.
> I want to store that information in a table each week/month to chart
growth.
> Is there a system table that stores how much space of each
datafile/database
> is being used?
> Thanks. Any help would be appreciated.
> Ron
>

database size issue

What can be the reason for a database to have abnormal size.

for example: i have a database with size 12 GB which is not in according to the amount of data it has (theoratically it should be around 3 GB). To verify this, i create another db and then import all the tables from first db to second, and as expected the second db size is about 2.5 GB. What can be the reason for additional 9 - 10 GB in first db.

It could be that the database data file has a lot of empty space in it. You can check in SSMS by going to Properties, Tasks, Shrink, Files, and check the available free space.

You could also have heavily fragmented indexes that are taking up extra space.

|||

It could be spaced used for indexes, or white space within the files.

Check sp_spaceused and see how much space it shows as being used.

The white space is probably used for rebuilding indexes.

|||

One of the possible reasons might be that the datafile is allocated to 12GB size check it out

RegarDs,

Jacx

|||

Try this to see how much space you have within the database file(s):

-- Individual File Size query

SELECTnameAS'File Name', physical_name AS'Physical Name',size/128 AS'Total Size in MB',

size/128.0 -CAST(FILEPROPERTY(name,'SpaceUsed')ASint)/128.0 AS'Available Space In MB'

FROMsys.database_files;

|||

thanks for the info

the result of above query is

filename physical name total size available space

a_Data F:\a_Data.MDF 11411 3218.812500
a_Log F:\a_Log.LDF 1956 1935.687500

and the result of sp_spaceused

db name db size unallocated space

abc 13367 mb 3216 mb

reserved data index_size unused

8391672 kb 7487104 kb 862912 kb 41656 kb

so the reserved size seems huge: why its so and how to solve it ?

|||

You probably have a lot of index fragmentation. You can try running this script to defragment all the indexes in that database. It will also call sp_UpdateStats.

This should free up quite a bit of space inside the data file. If you want to, you can run a database shrink after that to make the data file smaller, but unless you are really worried about disk space, I would not bother doing that.

-- Declare variables

SETNOCOUNTON

DECLARE @.tablename VARCHAR(128)

DECLARE @.execstr VARCHAR(255)

DECLARE @.objectid INT

DECLARE @.indexid INT

DECLARE @.frag DECIMAL

DECLARE @.maxfrag DECIMAL

-- Decide on the maximum fragmentation to allow

SELECT @.maxfrag = 5.0

-- Declare cursor

DECLARE tables CURSORFOR

SELECT TABLE_NAME

FROMINFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE ='BASE TABLE'

-- Create the table

CREATETABLE #fraglist (

ObjectName CHAR(255),

ObjectId INT,

IndexName CHAR(255),

IndexId INT,

Lvl INT,

CountPages INT,

CountRows INT,

MinRecSize INT,

MaxRecSize INT,

AvgRecSize INT,

ForRecCount INT,

Extents INT,

ExtentSwitches INT,

AvgFreeBytes INT,

AvgPageDensity INT,

ScanDensity DECIMAL,

BestCount INT,

ActualCount INT,

LogicalFrag DECIMAL,

ExtentFrag DECIMAL)

-- Open the cursor

OPEN tables

-- Loop through all the tables in the database

FETCHNEXT

FROM tables

INTO @.tablename

WHILE@.@.FETCH_STATUS= 0

BEGIN

-- Do the showcontig of all indexes of the table

INSERTINTO #fraglist

EXEC('DBCC SHOWCONTIG ('''+ @.tablename +''')

WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')

FETCHNEXT

FROM tables

INTO @.tablename

END

-- Close and deallocate the cursor

CLOSE tables

DEALLOCATE tables

-- Declare cursor for list of indexes to be defragged

DECLARE indexes CURSORFOR

SELECT ObjectName, ObjectId, IndexId, LogicalFrag

FROM #fraglist

WHERE LogicalFrag >= @.maxfrag

ANDINDEXPROPERTY(ObjectId, IndexName,'IndexDepth')> 0

-- Open the cursor

OPEN indexes

-- loop through the indexes

FETCHNEXT

FROM indexes

INTO @.tablename, @.objectid, @.indexid, @.frag

WHILE@.@.FETCH_STATUS= 0

BEGIN

PRINT'Executing DBCC INDEXDEFRAG (0, '+RTRIM(@.tablename)+',

'+RTRIM(@.indexid)+') - fragmentation currently '

+RTRIM(CONVERT(varchar(15),@.frag))+'%'

SELECT @.execstr ='DBCC INDEXDEFRAG (0, '+RTRIM(@.objectid)+',

'+RTRIM(@.indexid)+')'

EXEC(@.execstr)

FETCHNEXT

FROM indexes

INTO @.tablename, @.objectid, @.indexid, @.frag

END

-- Close and deallocate the cursor

CLOSE indexes

DEALLOCATE indexes

-- Delete the temporary table

DROPTABLE #fraglist

GO

-- Runs UPDATE STATISTICS against all user-defined tables in the current database

EXECsp_updatestats

GO

|||

thanks for the help.

Now my real concern. We have similar stand alone database in each branch of company with the same application and configuration.

Performance of appication in other branches is good, but in this branch its very very slow. since application / hardware / configuration is same in all branches, one of the possible reason can be a problem in database, and hence i am looking deep into it.

Firstly, why this particular db size has grown so much (and why the empty spaces comes up). Even a size of 12 gb ( with most of it empty) should not be considered big performance blocker. So what can be the reasons for low performance.

|||

First, if our answers are helpful, please mark them as "Helpful" so we get credit for them. Second, you should start a fresh thread, with this entirely new question. Otherwise it will not get as much attention. You also need to be more specific about your issue.

In the meantime, what symptoms are you seeing (besides increased database size) at the one branch?

My blog post is a starting point:

http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!549.entry

This whitepaper has a lot of good performance troubleshooting information:

http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx