October 16, 2001 at 8:43 am
I use the Snitz forum with SQL server and am worried about the actual space I am using on the server.
Using sp_spaceused I have the following result. (see below)
What does “unallocated space” mean and “reserved”
I only have 20 Mb of space. How much space have I actually got left in the current tables / datbase.
Also any info on how to help maintain the server would also be appreciated . Got access to stored procedures on SQl server 7 but not full access.
database_size unallocated space
------------------ ------------------
21.75 MB 12.41 MB
Link to extra info on table sizes.
http://www.speedwaymanager.co.uk/download/sqlsize.txt
Thanks in advance
Edited by - speedway on 10/16/2001 08:45:59 AM
October 16, 2001 at 10:36 am
The total space is the amount used on disk by the mdf/ldf files. The unallocated is the space in the mdf file that is not used.
So, if I create a 10MB database, 10MB is allocated on the disk (physically), but only the space for system objects is initially there (1MB). This means I have 9MB "unallocated". This does not have anything to do with the growth of the db.
If I now add a table with 8MB of data, I will be using 9MB and have 1MB unallocated. there is still 10 MB used on the disk. If I allow growth, and I add 1.5MB, the database will grow by 10% (default) to 11MB. My data will grow to 10.5MB and I will have .5MB unallocated.
Steve Jones
October 16, 2001 at 1:22 pm
Link to extra info on table sizes.
http://www.speedwaymanager.co.uk/download/sqlsize.txt
What about the reserved space?
I keep running out of space on my internet server when I create a new table.
Thanks in advance
Edited by - speedway on 10/16/2001 08:45:59 AM
[/quote]
October 16, 2001 at 3:34 pm
You can use dbcc shrinkfile to reclaim the 'reserved' space, to some extent anyway. Have to be member of sysadmins or dbo to do it though. You definitely need to set the growth percentage - 20mb is NOT a lot of space.
Andy
October 17, 2001 at 3:36 am
quote:
You can use dbcc shrinkfile to reclaim the 'reserved' space, to some extent anyway.
Done that thanks Andy but it has not made any difference.
(Only allowed 20mb of space on sql server)
Why does it allocate so much reserved space.
FORUM_REPLY Table
Reserved = 10512 KB
Data = 8192 KB
Thanks
Edited by - speedway on 10/17/2001 03:39:05 AM
October 17, 2001 at 8:09 am
Well, the default is to create a database the same size as the model, which may have some extra space to start with. The "why" is that to allocate more space, do whatever housekeeping SQL does to map pages and whatever takes time - so generally you want to avoid doing so as much as possible and if you have to do it, do it when the users won't notice the additional server load. Generally I'll keep 10-20% free space inside the db. I also try to keep up to 50% of my drive space free, to allow for backup, tempdb, whatever.
Andy
November 4, 2002 at 10:57 pm
I have a db that I added 2 image colums to . In our application, you add it to a text file database dictionery then 'verify' it, ie compiles the text file & creates any db schema changes. If there are any changes, the app backs up the table, drops it, recreates it as dictated in the text file, then restores the data.
I found if I added 2 image columns, my database doubledin size, because the unallocated space doubled, because the an amount of reserved space equal to the original size of the db was created. Why I have no idea. Adding other types of columns did not produce this behaviour.
November 4, 2002 at 10:58 pm
I have a db that I added 2 image colums to . In our application, you add it to a text file database dictionery then 'verify' it, ie compiles the text file & creates any db schema changes. If there are any changes, the app backs up the table, drops it, recreates it as dictated in the text file, then restores the data.
I found if I added 2 image columns, my database doubled in size, because the unallocated space doubled, because the an amount of reserved space equal to the original size of the db was created. Why? - I have no idea. Adding other types of columns did not produce this behaviour.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply