DB sizes different - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

DB sizes different

Author

Topic

RoyalSher
Yak Posting Veteran

95 Posts

Posted - 2004-10-12 : 03:00:12

I have been working on gathering statistics of database growth for past few months. I started of with taking the database size, free available space by checking properties of each database (my first method). I was bored to the core after sometime, and started taking stats by running dbcc showstats on each database of 25 Sql instances I maintain across all environments (my second method).

Now, I realize that database size & free space available calculated from running this command is different from my first method. I researched all around to find out how would I reach near to the figure of first method, but to no good results. I want to know from you guys out there to help me how that figures can be achieved or else any reliable method which would help in monitoring db growth.

I am sure SQL server has some logic hidden to gather the stats, and how different is it from my second way of calculating.

I am waiting for your answers, comments, etc!

RoyalSher.
*********
The world is the great gymnasium where we come to make ourselves strong.

sify
Starting Member

18 Posts

Posted - 2004-10-12 : 03:06:01

what are your first and second methods?

We use dbcc showfilestats for the data files and dbcc sqlperf(logspace) for the log files for each server.xp_fixeddrives will get the free space.

Remember these are in extents.

RoyalSher
Yak Posting Veteran

95 Posts

Posted - 2004-10-12 : 03:08:41

yes, these are in extents and i have taken care they are converted into numbers (in Mbs).

First Method:Checking properties of database for db size & free avl. space for total database growth

Second Method:Using DBCC showstats, and refine it down to find avl space and used space in megs.

Let me know if I you need any other clarifications on this.

RoyalSher.
*********
The world is the great gymnasium where we come to make ourselves strong.

sify
Starting Member

18 Posts

Posted - 2004-10-12 : 04:01:20

The first method I guess is browsing thru the server and database and checking the database properties for the free and avl space in the General Tab.

There the Size: Data File Size + Log file size.

But the better option is the second method. You will get the details for the individual files.

If you want to arrive at the same figure for Size as per you first method in think if you do below you will get approx. there.

dbcc showfilestats and the totalextents/16 + logsize in dbcc sqlperf(logspace)

RoyalSher
Yak Posting Veteran

95 Posts

Posted - 2004-10-13 : 00:02:18

Thanks to all for there immediate help. You may want to read my mail with dbcc showstats as DBCC showfilestats, as it was a typo on dbcc showstats.

Any other help is appreciated!

RoyalSher.
*********
The world is the great gymnasium where we come to make ourselves strong.

RoyalSher
Yak Posting Veteran

95 Posts

Posted - 2004-10-13 : 04:32:44

May be I should rephrase my question. If I need to add a datafile then, do I use the method of checking avl. free space in database or in datafiles ?

Any comments ?

RoyalSher.
*********
The world is the great gymnasium where we come to make ourselves strong.

sify
Starting Member

18 Posts

Posted - 2004-10-13 : 06:52:13

1. If you check the properties of the database it will always be All datafiles Size + All Logfile Size.

2. Avl Space is free space in all data + all Log files. (This will not show accurate figure as datafile space is more important parameter to monitor). Here the log files may be free and hence you avl space will be more. But more importance is the free space in data files.

3. So computing the free data space from the dbcc showfilestats and dbcc sqlperf(logspace) will be more accurate.
4. If you want to calculate the combined available space for the database (Total Extents - Used Extents) / 16 + (LogfileSize - Logfilesize*(Percentageused/100)) might give the result you want.

We prefer to go by your second method.

RoyalSher
Yak Posting Veteran

95 Posts

Posted - 2004-10-14 : 02:21:41

Hey thanks for the prompt help!

I had few more questions as I was going thro' the whole trail of the topic.

(1) Would the data files include all primary & secondary files (.MDF, .NDF) of the database ?

(2) After your explanation I checked the calculations in the script I had customised to derive free space in the data files.

Observations:
(1) I have a db xyz and checking thro' Properties of database the db size is 49.64 gigs, with free available space of 5.1 Gigs. The log file current size is 2.5 gigs with space used is 71.6 Megs.

(2) After calculating the data file size using dbcc showfilestats, I arrived at 19.2 gigs of avl space in data files (.mdf, .ndf).

The combined avl. space per your formula, is around 21 gigs.

Why is that difference in the free avl. space ?

Any help is appreciated.

RoyalSher.
*********
The world is the great gymnasium where we come to make ourselves strong.

sify
Starting Member

18 Posts

Posted - 2004-10-14 : 05:48:49

Method 1:

Enterprise Manager

When u look at the Database Properties…
In the General Tab, u get the
Size (which is a total of the used space on the Transaction Log files and the datafiles)
Space available (which is a total of the free space on the Transaction Log files and the datafiles)

Method 2:

Query Analyser

At the database:

Datafile:

Dbcc showfilestats

Gives:

The TotalExtents column gives the total space for each datafile (Primary and secondary)
So to get the total space of the datafiles (primary and secondary) u need to total up all the values of that column.

The UsedExtents column will give u the total space used by the repective datafile, so to get a the total space occupied by the datafiles in the database u will have to total all the values in that column.

So the Space Available (in extents) = Sum_of_TotalExtents – Sum_of_UsedExtents

Space Available in Extents / 16 = Space available in MB

Transaction Log file:

Dbcc SQLperf (logspace)

For the Respective database take the Log Size (MB) and the log space used (%)

To find out the log space available in MB = LogSize – (LogSize * (log Space Used /100))

Hence to get the

Total size = (Sum_of_TotalExtents / 16) + Logsize
Available space = Datafile space available + log space available

The values will be approximately the same as those in the Enterprise manager. Make sure to cast the vaules as float to get more precise values.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources