SQL Server Performance Forum – Threads Archive

DB sizes different

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.

Take help from this DBJournalhttp://www.databasejournal.com/features/mssql/article.php/3339681 article to maintain your stats. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

dbcc show_statistics is not meant for showing database size and free space. It shows distribution statistics. If you want to monitor database growth, you can schedule an ‘sp_spaceused’ on the database and insert the results into a table periodically.

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.

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.

No need to check for any free space if you are adding additional data file to the database.
Any help from the above link? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

In answer to your first question, dbcc showfilestats is showing extent statistics of a data file, does not include any statistics about the log file. This could be the reason for you to see different results. And to second question, to add a datafile, you need to check if you have sufficent free space on the disk that you are trying to put this file on, you don’t need to have free space in the database.

Chakry, The database I am talking about has maxsize limited to 2048 Megs per file. I need to size the database and want to be expanded on time. Let me explain with more details (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 and dbcc sqlperf(logspace), I arrived at 19.2 gigs of avl space in data files (.mdf, .ndf). The combined avl. space (Data & Log files) is around 21 gigs. Why is that difference in the free avl. space ? Satya, I found the link to be useful, but MAK is doing the same what I am trying to do. The only question I had was "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.

I believe you’re comparing the results from th Enterprise Manager and statements for database sizes, I would go with the DBCC statements rather than beleiving the EM results, which is flaky at times. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Yes Satya, I am comparing the results.. and was curious to know why is it showing that large difference between the two values. Would you advise to add a data file, in time to avoid any outage, based on space availability in data files thro’ DBCC ? Any other thoughts on this, when do I need to add a data file to the database ? RoyalSher.
*********
The world is the great gymnasium where we come to make ourselves strong.

From the query analyzer results if you feel the database available free space is less, then add another datafile to avoid unprecedented outage to the application, only point to note is to make sure no free space issues on the drive where this datafile located. hTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>