Reserved size of a database

Hi,
Does anyone know what exactly is the reserved size of a database; what is
this space used for? Is this user configurable? Why doesn't this value show
up in database properties in Enterprise Manager GUI?
Thanks,
- V
here's output of the command (use "master"; exec sp_spaceused) when passed
to osql
database_name database_size unallocated space reserved data
index_size unused
-- -- -- -- --
-- -- --
master 4.50 MB 1.91 MB 2144 KB 976 KB
992 KB 176 KB
Read the section in Books Online about physical database architecture. "Reserved" is the size of the
extents that are allocated (used).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Volcano" <volcano@.volcano.com> wrote in message news:OcuVSsnmFHA.3020@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Does anyone know what exactly is the reserved size of a database; what is
> this space used for? Is this user configurable? Why doesn't this value show
> up in database properties in Enterprise Manager GUI?
> Thanks,
> - V
> here's output of the command (use "master"; exec sp_spaceused) when passed
> to osql
> database_name database_size unallocated space reserved data
> index_size unused
> -- -- -- -- --
> -- -- --
> master 4.50 MB 1.91 MB 2144 KB 976 KB
> 992 KB 176 KB
>
>
|||Hi Tibor,
Thanks for your reply. I read the Books Online but it didn't really help me
that much. I'm actually trying to figure out how the total size of the
database (which is nothing but, the sum of the individual file sizes,
correct?) is utilized.
So in this example:

> database_name database_size unallocated space reserved data
> index_size unused
> -- -- -- -- --
-
> -- -- --
> master 4.50 MB 1.91 MB 2144 KB 976 KB
> 992 KB 176 KB
1. database_size = 4.5 MB (4608 KB)
-- However, when I check the file sizes it totals to 5 MB. Where is the
0.5 MB gone?
2. unallocated space = 1.91 MB (1956 KB)
-- Is this already part of the database_size above?
3. reserved = 2144 KB
-- This is the size of all extents allocated put together, as you say.
ok.
4. data = 976 KB
-- Now, is this the size of extents that are actually used? so, this is
already accounted for in the reserved size (above)?
5. index_size = 992 KB
-- I guess this is for the extents actually used for indexing, from the
total reserved?
6. unused = 176 KB
-- from the total reserved, this must be the extents not used yet?
if all of the above is correct:
a) reserved = data + index_size + unused (this seems to be true)
b) database_size = reserved + unallocated space (this doesn't look correct)
4608 KB != 1956 KB + 2144 KB
Am I missing some component in b?
- V
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uprLJIpmFHA.3408@.tk2msftngp13.phx.gbl...
Read the section in Books Online about physical database architecture.
"Reserved" is the size of the
extents that are allocated (used).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Volcano" <volcano@.volcano.com> wrote in message
news:OcuVSsnmFHA.3020@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Does anyone know what exactly is the reserved size of a database; what is
> this space used for? Is this user configurable? Why doesn't this value
show
> up in database properties in Enterprise Manager GUI?
> Thanks,
> - V
> here's output of the command (use "master"; exec sp_spaceused) when passed
> to osql
> database_name database_size unallocated space reserved data
> index_size unused
> -- -- -- -- --
-
> -- -- --
> master 4.50 MB 1.91 MB 2144 KB 976 KB
> 992 KB 176 KB
>
>
|||
> -- However, when I check the file sizes it totals to 5 MB.
> Where is the 0.5 MB gone?
I did a mistake here, the "intial size" for log file is shown as 1 MB in SQL
Server Management Studio, whereas when I checked the actual file size on the
filesystem, it's 512 KB. The other data file is 4096 KB. So that explains
why the stored procedure returned 4.5 MB, which is correct.
"Volcano" <volcano@.volcano.com> wrote in message
news:OcOE7KsmFHA.1948@.TK2MSFTNGP12.phx.gbl...
Hi Tibor,
Thanks for your reply. I read the Books Online but it didn't really help me
that much. I'm actually trying to figure out how the total size of the
database (which is nothing but, the sum of the individual file sizes,
correct?) is utilized.
So in this example:

> database_name database_size unallocated space reserved data
> index_size unused
> -- -- -- -- --
-
> -- -- --
> master 4.50 MB 1.91 MB 2144 KB 976 KB
> 992 KB 176 KB
1. database_size = 4.5 MB (4608 KB)
-- However, when I check the file sizes it totals to 5 MB. Where is the
0.5 MB gone?
2. unallocated space = 1.91 MB (1956 KB)
-- Is this already part of the database_size above?
3. reserved = 2144 KB
-- This is the size of all extents allocated put together, as you say.
ok.
4. data = 976 KB
-- Now, is this the size of extents that are actually used? so, this is
already accounted for in the reserved size (above)?
5. index_size = 992 KB
-- I guess this is for the extents actually used for indexing, from the
total reserved?
6. unused = 176 KB
-- from the total reserved, this must be the extents not used yet?
if all of the above is correct:
a) reserved = data + index_size + unused (this seems to be true)
b) database_size = reserved + unallocated space (this doesn't look correct)
4608 KB != 1956 KB + 2144 KB
Am I missing some component in b?
- V
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uprLJIpmFHA.3408@.tk2msftngp13.phx.gbl...
Read the section in Books Online about physical database architecture.
"Reserved" is the size of the
extents that are allocated (used).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Volcano" <volcano@.volcano.com> wrote in message
news:OcuVSsnmFHA.3020@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Does anyone know what exactly is the reserved size of a database; what is
> this space used for? Is this user configurable? Why doesn't this value
show
> up in database properties in Enterprise Manager GUI?
> Thanks,
> - V
> here's output of the command (use "master"; exec sp_spaceused) when passed
> to osql
> database_name database_size unallocated space reserved data
> index_size unused
> -- -- -- -- --
-
> -- -- --
> master 4.50 MB 1.91 MB 2144 KB 976 KB
> 992 KB 176 KB
>
>
|||Spspaceused has a flaw (in my opinion) in that it doesn't explicitly states size of the transaction
log. I believe that size of tlog file is included in database size but no unallocated space.

> 6. unused = 176 KB
> -- from the total reserved, this must be the extents not used yet?
Almost. Pages on allocated extents where the page is not yet used.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Volcano" <volcano@.volcano.com> wrote in message news:eXsTPRsmFHA.1968@.TK2MSFTNGP14.phx.gbl...
>
> I did a mistake here, the "intial size" for log file is shown as 1 MB in SQL
> Server Management Studio, whereas when I checked the actual file size on the
> filesystem, it's 512 KB. The other data file is 4096 KB. So that explains
> why the stored procedure returned 4.5 MB, which is correct.
>
> "Volcano" <volcano@.volcano.com> wrote in message
> news:OcOE7KsmFHA.1948@.TK2MSFTNGP12.phx.gbl...
> Hi Tibor,
> Thanks for your reply. I read the Books Online but it didn't really help me
> that much. I'm actually trying to figure out how the total size of the
> database (which is nothing but, the sum of the individual file sizes,
> correct?) is utilized.
> So in this example:
> -
> 1. database_size = 4.5 MB (4608 KB)
> -- However, when I check the file sizes it totals to 5 MB. Where is the
> 0.5 MB gone?
> 2. unallocated space = 1.91 MB (1956 KB)
> -- Is this already part of the database_size above?
> 3. reserved = 2144 KB
> -- This is the size of all extents allocated put together, as you say.
> ok.
> 4. data = 976 KB
> -- Now, is this the size of extents that are actually used? so, this is
> already accounted for in the reserved size (above)?
> 5. index_size = 992 KB
> -- I guess this is for the extents actually used for indexing, from the
> total reserved?
> 6. unused = 176 KB
> -- from the total reserved, this must be the extents not used yet?
> if all of the above is correct:
> a) reserved = data + index_size + unused (this seems to be true)
> b) database_size = reserved + unallocated space (this doesn't look correct)
> 4608 KB != 1956 KB + 2144 KB
> Am I missing some component in b?
> - V
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uprLJIpmFHA.3408@.tk2msftngp13.phx.gbl...
> Read the section in Books Online about physical database architecture.
> "Reserved" is the size of the
> extents that are allocated (used).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Volcano" <volcano@.volcano.com> wrote in message
> news:OcuVSsnmFHA.3020@.TK2MSFTNGP10.phx.gbl...
> show
> -
>
>