June 10, 2011 at 9:42 am
Hey guys,
Im confused on this property Space Available under General Tab on the Database Properties...I have a database that is 21 GB in size and it says I have 0.70 MB Space Available? I looked up this property and it says Displays the amount of available space in the database in megabytes...Im confused on what this means...I looked at where I have the Data and Log Files going on the server and both drives still have over 150+ GB in free space available...Can someone please explain this to me? I have googled this and really havent found anything in depth...
Thanks in advance!
June 10, 2011 at 9:48 am
The data and log files preallocate the space. So from day 1 even with 0 rows of data you can size the db at 1 TB if you want to. Space available will be 1 TB in the DB.
The drive's free space I think you already understand. The best praxties says to figure out how much space the db will need in the next 6 months and size it accordingly from day one.
It's even better if you can go years out.
June 10, 2011 at 9:51 am
Just some other tidbit information concerning this database...I have the Data file set to Autogrowth By 1 MB , unrestricted growth and Log file set to Autogrowth By 10 percent, restricted growth to 2097152 MB...I believe those were just the default values set when I created the database...
Basically Im wanting to make sure this database isnt going to crash with only 0.70 MB Space Available when more data is loaded into the database tables???
June 10, 2011 at 9:54 am
Thanks Ninja...So can you change that after you have already created the database? This is all done on a development server and yes there is going to be a whole lot more data in the tables once we go live with it...But that database has not been created yet in production...
June 10, 2011 at 9:56 am
asm1212 (6/10/2011)
Thanks Ninja...So can you change that after you have already created the database? This is all done on a development server and yes there is going to be a whole lot more data in the tables once we go live with it...But that database has not been created yet in production...
Yes you can change the file sizes in the database properties.
I'd definitely stay away from growth at 1mb AND 10%.
Figure out what your drive(s) can do in 1-2 seconds and put those figures in there. I'd probably go with a few 100 mbs for a dev server unless I had fast sub systems.
June 10, 2011 at 10:00 am
I apologize I am still confused...So the Space Available is based on the Data and Log Files correct? And you are saying I need to change what I currently have? How do I go about determining what to change it to?
June 10, 2011 at 10:05 am
Yes it is based on the "empty" space in the data and log files. It has nothing to do with the size of the drive.
When doing 1 mb it actually physically fragments the db file(s) on the drive so performance can really suffer after a while. That's the 2nd reason pre-sizing is the best option.
I can't recommend you anything as to what size to use. I would need to look at the data volume documentations you have (or should have 😉 ) to do the math.
For dev work I wouldn't sweat too much over this. Maybe start at 5-10 GB and then adjust accordingly. For prod you really need to do your homework tho.
June 10, 2011 at 10:16 am
Thank you Ninja...You did spread a lil light on it for me...However I still dont have my head around it yet...I will just have to do some more studying up on it!
June 10, 2011 at 10:23 am
what's unclear?
There's free space on the drive.
You create a db and it creates a 100 mb file.
There's NO DATA in that file because you haven't done any inserts in the db so IN the file you have 100 MB of free space.
After you've inserted 100 MB if data in the data file it will be FULL with 0 mb of space available and it,ll have to grow again to accomodate new data.
June 10, 2011 at 3:09 pm
One thing you also need to understand is that if that value is always a low value (e.g. .7 MB like you have), it is probably a good indication that you are not backing up the transaction log.
If the database is set to full recovery model - you have to backup the transaction log on a regular (and frequent) basis. You could set the database recovery model to simple - which will not require a transaction log backup, but if you do so and forget to switch it back and setup transaction log backups when you migrate to production you could be putting your organization at risk.
Please read the article I link to in my signature on managing the transaction log.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply