I created a DB with a preallocated size of 20GB and I need to shink
this down to a smaller size. If I go into DB properties and enter a
new value it says that the size must be greater than the current size.
Is there any way to do this? Also when I try and restore the DB is
wants 20GB of disk space even though it's only using about 1GB of
data. Any help will be muchly appreciated.
Darren.Use DBCC SHRINKDATABASE or DBCC SHRINKFILE. These commands are documented in Books Online.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Darren" <walker_712@.hotmail.com> wrote in message news:cd9d3666.0405102239.21563966@.posting.google.com...
> I created a DB with a preallocated size of 20GB and I need to shink
> this down to a smaller size. If I go into DB properties and enter a
> new value it says that the size must be greater than the current size.
> Is there any way to do this? Also when I try and restore the DB is
> wants 20GB of disk space even though it's only using about 1GB of
> data. Any help will be muchly appreciated.
> Darren.|||Hi Darren,
Unfortunatly you can't. The smallest size you can shrink
your database to is the size the database was initally set
to when you created it, in this case 20gb.
Although there are lots of ways of doing it, personally I
would save the data, drop the database, recreate it with 1
gb, then copy the database back into it.
J
>--Original Message--
>I created a DB with a preallocated size of 20GB and I
need to shink
>this down to a smaller size. If I go into DB properties
and enter a
>new value it says that the size must be greater than the
current size.
>Is there any way to do this? Also when I try and restore
the DB is
>wants 20GB of disk space even though it's only using
about 1GB of
>data. Any help will be muchly appreciated.
>Darren.
>.
>|||Julie,
DBCC SHRINKFILE should be able to shrink the size below the initial size...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Julie" <anonymous@.discussions.microsoft.com> wrote in message news:b30801c43735$c4a30970$a301280a@.phx.gbl...
> Hi Darren,
> Unfortunatly you can't. The smallest size you can shrink
> your database to is the size the database was initally set
> to when you created it, in this case 20gb.
> Although there are lots of ways of doing it, personally I
> would save the data, drop the database, recreate it with 1
> gb, then copy the database back into it.
> J
>
>
> >--Original Message--
> >I created a DB with a preallocated size of 20GB and I
> need to shink
> >this down to a smaller size. If I go into DB properties
> and enter a
> >new value it says that the size must be greater than the
> current size.
> >Is there any way to do this? Also when I try and restore
> the DB is
> >wants 20GB of disk space even though it's only using
> about 1GB of
> >data. Any help will be muchly appreciated.
> >
> >Darren.
> >.
> >|||Ok, now hanging my head in shame.
Moving swiftly along...
>--Original Message--
>Julie,
>DBCC SHRINKFILE should be able to shrink the size below
the initial size...
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message news:b30801c43735$c4a30970$a301280a@.phx.gbl...
>> Hi Darren,
>> Unfortunatly you can't. The smallest size you can shrink
>> your database to is the size the database was initally
set
>> to when you created it, in this case 20gb.
>> Although there are lots of ways of doing it,
personally I
>> would save the data, drop the database, recreate it
with 1
>> gb, then copy the database back into it.
>> J
>>
>>
>> >--Original Message--
>> >I created a DB with a preallocated size of 20GB and I
>> need to shink
>> >this down to a smaller size. If I go into DB properties
>> and enter a
>> >new value it says that the size must be greater than
the
>> current size.
>> >Is there any way to do this? Also when I try and
restore
>> the DB is
>> >wants 20GB of disk space even though it's only using
>> about 1GB of
>> >data. Any help will be muchly appreciated.
>> >
>> >Darren.
>> >.
>> >
>
>.
>|||LOL...
As if we didn't do this all of us, from time to time. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Julie" <anonymous@.discussions.microsoft.com> wrote in message news:b38e01c4373e$eb86b330$a301280a@.phx.gbl...
> Ok, now hanging my head in shame.
> Moving swiftly along...
>
> >--Original Message--
> >Julie,
> >
> >DBCC SHRINKFILE should be able to shrink the size below
> the initial size...
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >
> >
> >"Julie" <anonymous@.discussions.microsoft.com> wrote in
> message news:b30801c43735$c4a30970$a301280a@.phx.gbl...
> >> Hi Darren,
> >>
> >> Unfortunatly you can't. The smallest size you can shrink
> >> your database to is the size the database was initally
> set
> >> to when you created it, in this case 20gb.
> >>
> >> Although there are lots of ways of doing it,
> personally I
> >> would save the data, drop the database, recreate it
> with 1
> >> gb, then copy the database back into it.
> >>
> >> J
> >>
> >>
> >>
> >>
> >> >--Original Message--
> >> >I created a DB with a preallocated size of 20GB and I
> >> need to shink
> >> >this down to a smaller size. If I go into DB properties
> >> and enter a
> >> >new value it says that the size must be greater than
> the
> >> current size.
> >> >Is there any way to do this? Also when I try and
> restore
> >> the DB is
> >> >wants 20GB of disk space even though it's only using
> >> about 1GB of
> >> >data. Any help will be muchly appreciated.
> >> >
> >> >Darren.
> >> >.
> >> >
> >
> >
> >.
> >
Showing posts with label size. Show all posts
Showing posts with label size. Show all posts
Wednesday, March 28, 2012
Resizing an existing DB
I created a DB with a preallocated size of 20GB and I need to shink
this down to a smaller size. If I go into DB properties and enter a
new value it says that the size must be greater than the current size.
Is there any way to do this? Also when I try and restore the DB is
wants 20GB of disk space even though it's only using about 1GB of
data. Any help will be muchly appreciated.
Darren.
Use DBCC SHRINKDATABASE or DBCC SHRINKFILE. These commands are documented in Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Darren" <walker_712@.hotmail.com> wrote in message news:cd9d3666.0405102239.21563966@.posting.google.c om...
> I created a DB with a preallocated size of 20GB and I need to shink
> this down to a smaller size. If I go into DB properties and enter a
> new value it says that the size must be greater than the current size.
> Is there any way to do this? Also when I try and restore the DB is
> wants 20GB of disk space even though it's only using about 1GB of
> data. Any help will be muchly appreciated.
> Darren.
this down to a smaller size. If I go into DB properties and enter a
new value it says that the size must be greater than the current size.
Is there any way to do this? Also when I try and restore the DB is
wants 20GB of disk space even though it's only using about 1GB of
data. Any help will be muchly appreciated.
Darren.
Use DBCC SHRINKDATABASE or DBCC SHRINKFILE. These commands are documented in Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Darren" <walker_712@.hotmail.com> wrote in message news:cd9d3666.0405102239.21563966@.posting.google.c om...
> I created a DB with a preallocated size of 20GB and I need to shink
> this down to a smaller size. If I go into DB properties and enter a
> new value it says that the size must be greater than the current size.
> Is there any way to do this? Also when I try and restore the DB is
> wants 20GB of disk space even though it's only using about 1GB of
> data. Any help will be muchly appreciated.
> Darren.
Resizing an existing DB
Hi Darren,
Unfortunatly you can't. The smallest size you can shrink
your database to is the size the database was initally set
to when you created it, in this case 20gb.
Although there are lots of ways of doing it, personally I
would save the data, drop the database, recreate it with 1
gb, then copy the database back into it.
J
>--Original Message--
>I created a DB with a preallocated size of 20GB and I
need to shink
>this down to a smaller size. If I go into DB properties
and enter a
>new value it says that the size must be greater than the
current size.
>Is there any way to do this? Also when I try and restore
the DB is
>wants 20GB of disk space even though it's only using
about 1GB of
>data. Any help will be muchly appreciated.
>Darren.
>.
>
Julie,
DBCC SHRINKFILE should be able to shrink the size below the initial size...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Julie" <anonymous@.discussions.microsoft.com> wrote in message news:b30801c43735$c4a30970$a301280a@.phx.gbl...[vbcol=seagreen]
> Hi Darren,
> Unfortunatly you can't. The smallest size you can shrink
> your database to is the size the database was initally set
> to when you created it, in this case 20gb.
> Although there are lots of ways of doing it, personally I
> would save the data, drop the database, recreate it with 1
> gb, then copy the database back into it.
> J
>
>
> need to shink
> and enter a
> current size.
> the DB is
> about 1GB of
|||Ok, now hanging my head in shame.
Moving swiftly along...
>--Original Message--
>Julie,
>DBCC SHRINKFILE should be able to shrink the size below
the initial size...
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message news:b30801c43735$c4a30970$a301280a@.phx.gbl...[vbcol=seagreen]
set[vbcol=seagreen]
personally I[vbcol=seagreen]
with 1[vbcol=seagreen]
the[vbcol=seagreen]
restore
>
>.
>
|||LOL...
As if we didn't do this all of us, from time to time. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Julie" <anonymous@.discussions.microsoft.com> wrote in message news:b38e01c4373e$eb86b330$a301280a@.phx.gbl...[vbcol=seagreen]
> Ok, now hanging my head in shame.
> Moving swiftly along...
>
> the initial size...
> message news:b30801c43735$c4a30970$a301280a@.phx.gbl...
> set
> personally I
> with 1
> the
> restore
Unfortunatly you can't. The smallest size you can shrink
your database to is the size the database was initally set
to when you created it, in this case 20gb.
Although there are lots of ways of doing it, personally I
would save the data, drop the database, recreate it with 1
gb, then copy the database back into it.
J
>--Original Message--
>I created a DB with a preallocated size of 20GB and I
need to shink
>this down to a smaller size. If I go into DB properties
and enter a
>new value it says that the size must be greater than the
current size.
>Is there any way to do this? Also when I try and restore
the DB is
>wants 20GB of disk space even though it's only using
about 1GB of
>data. Any help will be muchly appreciated.
>Darren.
>.
>
Julie,
DBCC SHRINKFILE should be able to shrink the size below the initial size...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Julie" <anonymous@.discussions.microsoft.com> wrote in message news:b30801c43735$c4a30970$a301280a@.phx.gbl...[vbcol=seagreen]
> Hi Darren,
> Unfortunatly you can't. The smallest size you can shrink
> your database to is the size the database was initally set
> to when you created it, in this case 20gb.
> Although there are lots of ways of doing it, personally I
> would save the data, drop the database, recreate it with 1
> gb, then copy the database back into it.
> J
>
>
> need to shink
> and enter a
> current size.
> the DB is
> about 1GB of
|||Ok, now hanging my head in shame.
Moving swiftly along...
>--Original Message--
>Julie,
>DBCC SHRINKFILE should be able to shrink the size below
the initial size...
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message news:b30801c43735$c4a30970$a301280a@.phx.gbl...[vbcol=seagreen]
set[vbcol=seagreen]
personally I[vbcol=seagreen]
with 1[vbcol=seagreen]
the[vbcol=seagreen]
restore
>
>.
>
|||LOL...
As if we didn't do this all of us, from time to time. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Julie" <anonymous@.discussions.microsoft.com> wrote in message news:b38e01c4373e$eb86b330$a301280a@.phx.gbl...[vbcol=seagreen]
> Ok, now hanging my head in shame.
> Moving swiftly along...
>
> the initial size...
> message news:b30801c43735$c4a30970$a301280a@.phx.gbl...
> set
> personally I
> with 1
> the
> restore
Resizing an existing DB
Hi Darren,
Unfortunatly you can't. The smallest size you can shrink
your database to is the size the database was initally set
to when you created it, in this case 20gb.
Although there are lots of ways of doing it, personally I
would save the data, drop the database, recreate it with 1
gb, then copy the database back into it.
J
>--Original Message--
>I created a DB with a preallocated size of 20GB and I
need to shink
>this down to a smaller size. If I go into DB properties
and enter a
>new value it says that the size must be greater than the
current size.
>Is there any way to do this? Also when I try and restore
the DB is
>wants 20GB of disk space even though it's only using
about 1GB of
>data. Any help will be muchly appreciated.
>Darren.
>.
>Julie,
DBCC SHRINKFILE should be able to shrink the size below the initial size...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Julie" <anonymous@.discussions.microsoft.com> wrote in message news:b30801c43735$c4a30970$a3
01280a@.phx.gbl...[vbcol=seagreen]
> Hi Darren,
> Unfortunatly you can't. The smallest size you can shrink
> your database to is the size the database was initally set
> to when you created it, in this case 20gb.
> Although there are lots of ways of doing it, personally I
> would save the data, drop the database, recreate it with 1
> gb, then copy the database back into it.
> J
>
>
> need to shink
> and enter a
> current size.
> the DB is
> about 1GB of|||Ok, now hanging my head in shame.
Moving swiftly along...
>--Original Message--
>Julie,
>DBCC SHRINKFILE should be able to shrink the size below
the initial size...
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message news:b30801c43735$c4a30970$a301280a@.phx.gbl...
set[vbcol=seagreen]
personally I[vbcol=seagreen]
with 1[vbcol=seagreen]
the[vbcol=seagreen]
restore[vbcol=seagreen]
>
>.
>|||LOL...
As if we didn't do this all of us, from time to time. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Julie" <anonymous@.discussions.microsoft.com> wrote in message news:b38e01c4373e$eb86b330$a3
01280a@.phx.gbl...[vbcol=seagreen]
> Ok, now hanging my head in shame.
> Moving swiftly along...
>
> the initial size...
> message news:b30801c43735$c4a30970$a301280a@.phx.gbl...
> set
> personally I
> with 1
> the
> restoresql
Unfortunatly you can't. The smallest size you can shrink
your database to is the size the database was initally set
to when you created it, in this case 20gb.
Although there are lots of ways of doing it, personally I
would save the data, drop the database, recreate it with 1
gb, then copy the database back into it.
J
>--Original Message--
>I created a DB with a preallocated size of 20GB and I
need to shink
>this down to a smaller size. If I go into DB properties
and enter a
>new value it says that the size must be greater than the
current size.
>Is there any way to do this? Also when I try and restore
the DB is
>wants 20GB of disk space even though it's only using
about 1GB of
>data. Any help will be muchly appreciated.
>Darren.
>.
>Julie,
DBCC SHRINKFILE should be able to shrink the size below the initial size...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Julie" <anonymous@.discussions.microsoft.com> wrote in message news:b30801c43735$c4a30970$a3
01280a@.phx.gbl...[vbcol=seagreen]
> Hi Darren,
> Unfortunatly you can't. The smallest size you can shrink
> your database to is the size the database was initally set
> to when you created it, in this case 20gb.
> Although there are lots of ways of doing it, personally I
> would save the data, drop the database, recreate it with 1
> gb, then copy the database back into it.
> J
>
>
> need to shink
> and enter a
> current size.
> the DB is
> about 1GB of|||Ok, now hanging my head in shame.
Moving swiftly along...
>--Original Message--
>Julie,
>DBCC SHRINKFILE should be able to shrink the size below
the initial size...
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message news:b30801c43735$c4a30970$a301280a@.phx.gbl...
set[vbcol=seagreen]
personally I[vbcol=seagreen]
with 1[vbcol=seagreen]
the[vbcol=seagreen]
restore[vbcol=seagreen]
>
>.
>|||LOL...
As if we didn't do this all of us, from time to time. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Julie" <anonymous@.discussions.microsoft.com> wrote in message news:b38e01c4373e$eb86b330$a3
01280a@.phx.gbl...[vbcol=seagreen]
> Ok, now hanging my head in shame.
> Moving swiftly along...
>
> the initial size...
> message news:b30801c43735$c4a30970$a301280a@.phx.gbl...
> set
> personally I
> with 1
> the
> restoresql
Resizing an existing DB
I created a DB with a preallocated size of 20GB and I need to shink
this down to a smaller size. If I go into DB properties and enter a
new value it says that the size must be greater than the current size.
Is there any way to do this? Also when I try and restore the DB is
wants 20GB of disk space even though it's only using about 1GB of
data. Any help will be muchly appreciated.
Darren.Use DBCC SHRINKDATABASE or DBCC SHRINKFILE. These commands are documented in
Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Darren" <walker_712@.hotmail.com> wrote in message news:cd9d3666.0405102239.21563966@.posting
.google.com...
> I created a DB with a preallocated size of 20GB and I need to shink
> this down to a smaller size. If I go into DB properties and enter a
> new value it says that the size must be greater than the current size.
> Is there any way to do this? Also when I try and restore the DB is
> wants 20GB of disk space even though it's only using about 1GB of
> data. Any help will be muchly appreciated.
> Darren.
this down to a smaller size. If I go into DB properties and enter a
new value it says that the size must be greater than the current size.
Is there any way to do this? Also when I try and restore the DB is
wants 20GB of disk space even though it's only using about 1GB of
data. Any help will be muchly appreciated.
Darren.Use DBCC SHRINKDATABASE or DBCC SHRINKFILE. These commands are documented in
Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Darren" <walker_712@.hotmail.com> wrote in message news:cd9d3666.0405102239.21563966@.posting
.google.com...
> I created a DB with a preallocated size of 20GB and I need to shink
> this down to a smaller size. If I go into DB properties and enter a
> new value it says that the size must be greater than the current size.
> Is there any way to do this? Also when I try and restore the DB is
> wants 20GB of disk space even though it's only using about 1GB of
> data. Any help will be muchly appreciated.
> Darren.
Resize my LDF file size ! how?
Yes my DB file is 10MB but my LOG file (LDF) is 300MB
is there a way to reduce or compres that one?
thanxTake a backup of database and check the log file size.
|||Run DBCC Shrinkfile SQL Server returns space less what it calls active log. Run a search for DBCC shrinkfile in the BOL(books online) and you can also set up a job to run it as needed. Hope this helps.
is there a way to reduce or compres that one?
thanxTake a backup of database and check the log file size.
|||Run DBCC Shrinkfile SQL Server returns space less what it calls active log. Run a search for DBCC shrinkfile in the BOL(books online) and you can also set up a job to run it as needed. Hope this helps.
Resize DB in SQL 7
I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
leaves just enough room on the partition for the nightly backup files, but I
keep getting a msg saying that the drive is almost full. I cannot resize the
partition, and shrinking the db does not change the allocation size. How can
I reduce the allocation size and free up some disk space? Thanks in advance
for any suggestions.
Back it up
remove it
recreate it, smaller
restore it
Jan
"Ron B." <RonB@.discussions.microsoft.com> wrote in message
news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
> I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
> leaves just enough room on the partition for the nightly backup files, but
I
> keep getting a msg saying that the drive is almost full. I cannot resize
the
> partition, and shrinking the db does not change the allocation size. How
can
> I reduce the allocation size and free up some disk space? Thanks in
advance
> for any suggestions.
|||That will not do anything, as restore will create database files with same size as when you took the
backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jan Doggen" <j.doggen@.BLOCKqsa.nl> wrote in message news:%2386SdCEuEHA.2072@.tk2msftngp13.phx.gbl...
> Back it up
> remove it
> recreate it, smaller
> restore it
> Jan
> "Ron B." <RonB@.discussions.microsoft.com> wrote in message
> news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
> I
> the
> can
> advance
>
|||One thing to try is to rebuild your indexes. Read about DBCC DBREINDEX. And then try a shrink again.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ron B." <RonB@.discussions.microsoft.com> wrote in message
news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
>I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
> leaves just enough room on the partition for the nightly backup files, but I
> keep getting a msg saying that the drive is almost full. I cannot resize the
> partition, and shrinking the db does not change the allocation size. How can
> I reduce the allocation size and free up some disk space? Thanks in advance
> for any suggestions.
|||You can try to use DTS copy the whole database to
another. detach two DBs and reattach the new DB with old
DB name.
Good luck
Mike
>--Original Message--
>One thing to try is to rebuild your indexes. Read about
DBCC DBREINDEX. And then try a shrink again.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Ron B." <RonB@.discussions.microsoft.com> wrote in
message
>news:636A452B-109A-439C-8DA4-
B1401C97E44C@.microsoft.com...[vbcol=seagreen]
2.9 GB used. This[vbcol=seagreen]
nightly backup files, but I[vbcol=seagreen]
full. I cannot resize the[vbcol=seagreen]
allocation size. How can[vbcol=seagreen]
space? Thanks in advance
>
>.
>
|||In message <636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com>, Ron B.
<RonB@.discussions.microsoft.com> writes
>I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
>leaves just enough room on the partition for the nightly backup files, but I
>keep getting a msg saying that the drive is almost full. I cannot resize the
>partition, and shrinking the db does not change the allocation size. How can
>I reduce the allocation size and free up some disk space? Thanks in advance
>for any suggestions.
Using Enterprise Manager and Query Analyser:
1) EM - Backup the database and log file in question.
2) QA - Use DBCC DBREINDEX on each table in your database.
(May be better using a cursor in sproc - saves typing).
3) EM - Backup the database and log file again.
4) EM - Use tools 'Truncate Transaction Log' if log file too big.
5) EM - Use tools 'Shrink Database'.
Obviously, the backup at step 1 should be kept for good measure (just in
case) however the backup at step 3 can be deleted.
In SQL Server 7, the Shrink Database does not work very well, if at all,
until a backup has been performed. The re-indexing is required to reduce
fragmentation before a shrink is tried. I have found from time to time
that the above process needs to be run twice to get the actual physical
files to shrink any, specially if it has not been done for a very long
time.
Kind Regards,
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
leaves just enough room on the partition for the nightly backup files, but I
keep getting a msg saying that the drive is almost full. I cannot resize the
partition, and shrinking the db does not change the allocation size. How can
I reduce the allocation size and free up some disk space? Thanks in advance
for any suggestions.
Back it up
remove it
recreate it, smaller
restore it
Jan
"Ron B." <RonB@.discussions.microsoft.com> wrote in message
news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
> I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
> leaves just enough room on the partition for the nightly backup files, but
I
> keep getting a msg saying that the drive is almost full. I cannot resize
the
> partition, and shrinking the db does not change the allocation size. How
can
> I reduce the allocation size and free up some disk space? Thanks in
advance
> for any suggestions.
|||That will not do anything, as restore will create database files with same size as when you took the
backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jan Doggen" <j.doggen@.BLOCKqsa.nl> wrote in message news:%2386SdCEuEHA.2072@.tk2msftngp13.phx.gbl...
> Back it up
> remove it
> recreate it, smaller
> restore it
> Jan
> "Ron B." <RonB@.discussions.microsoft.com> wrote in message
> news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
> I
> the
> can
> advance
>
|||One thing to try is to rebuild your indexes. Read about DBCC DBREINDEX. And then try a shrink again.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ron B." <RonB@.discussions.microsoft.com> wrote in message
news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
>I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
> leaves just enough room on the partition for the nightly backup files, but I
> keep getting a msg saying that the drive is almost full. I cannot resize the
> partition, and shrinking the db does not change the allocation size. How can
> I reduce the allocation size and free up some disk space? Thanks in advance
> for any suggestions.
|||You can try to use DTS copy the whole database to
another. detach two DBs and reattach the new DB with old
DB name.
Good luck
Mike
>--Original Message--
>One thing to try is to rebuild your indexes. Read about
DBCC DBREINDEX. And then try a shrink again.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Ron B." <RonB@.discussions.microsoft.com> wrote in
message
>news:636A452B-109A-439C-8DA4-
B1401C97E44C@.microsoft.com...[vbcol=seagreen]
2.9 GB used. This[vbcol=seagreen]
nightly backup files, but I[vbcol=seagreen]
full. I cannot resize the[vbcol=seagreen]
allocation size. How can[vbcol=seagreen]
space? Thanks in advance
>
>.
>
|||In message <636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com>, Ron B.
<RonB@.discussions.microsoft.com> writes
>I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
>leaves just enough room on the partition for the nightly backup files, but I
>keep getting a msg saying that the drive is almost full. I cannot resize the
>partition, and shrinking the db does not change the allocation size. How can
>I reduce the allocation size and free up some disk space? Thanks in advance
>for any suggestions.
Using Enterprise Manager and Query Analyser:
1) EM - Backup the database and log file in question.
2) QA - Use DBCC DBREINDEX on each table in your database.
(May be better using a cursor in sproc - saves typing).
3) EM - Backup the database and log file again.
4) EM - Use tools 'Truncate Transaction Log' if log file too big.
5) EM - Use tools 'Shrink Database'.
Obviously, the backup at step 1 should be kept for good measure (just in
case) however the backup at step 3 can be deleted.
In SQL Server 7, the Shrink Database does not work very well, if at all,
until a backup has been performed. The re-indexing is required to reduce
fragmentation before a shrink is tried. I have found from time to time
that the above process needs to be run twice to get the actual physical
files to shrink any, specially if it has not been done for a very long
time.
Kind Regards,
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
Resize DB in SQL 7
I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
leaves just enough room on the partition for the nightly backup files, but I
keep getting a msg saying that the drive is almost full. I cannot resize th
e
partition, and shrinking the db does not change the allocation size. How ca
n
I reduce the allocation size and free up some disk space? Thanks in advance
for any suggestions.Back it up
remove it
recreate it, smaller
restore it
Jan
"Ron B." <RonB@.discussions.microsoft.com> wrote in message
news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
> I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
> leaves just enough room on the partition for the nightly backup files, but
I
> keep getting a msg saying that the drive is almost full. I cannot resize
the
> partition, and shrinking the db does not change the allocation size. How
can
> I reduce the allocation size and free up some disk space? Thanks in
advance
> for any suggestions.|||That will not do anything, as restore will create database files with same s
ize as when you took the
backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jan Doggen" <j.doggen@.BLOCKqsa.nl> wrote in message news:%2386SdCEuEHA.2072@.tk2msftngp13.ph
x.gbl...
> Back it up
> remove it
> recreate it, smaller
> restore it
> Jan
> "Ron B." <RonB@.discussions.microsoft.com> wrote in message
> news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
> I
> the
> can
> advance
>|||One thing to try is to rebuild your indexes. Read about DBCC DBREINDEX. And
then try a shrink again.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ron B." <RonB@.discussions.microsoft.com> wrote in message
news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
>I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
> leaves just enough room on the partition for the nightly backup files, but
I
> keep getting a msg saying that the drive is almost full. I cannot resize
the
> partition, and shrinking the db does not change the allocation size. How
can
> I reduce the allocation size and free up some disk space? Thanks in advan
ce
> for any suggestions.|||You can try to use DTS copy the whole database to
another. detach two DBs and reattach the new DB with old
DB name.
Good luck
Mike
>--Original Message--
>One thing to try is to rebuild your indexes. Read about
DBCC DBREINDEX. And then try a shrink again.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Ron B." <RonB@.discussions.microsoft.com> wrote in
message
>news:636A452B-109A-439C-8DA4-
B1401C97E44C@.microsoft.com...
2.9 GB used. This[vbcol=seagreen]
nightly backup files, but I[vbcol=seagreen]
full. I cannot resize the[vbcol=seagreen]
allocation size. How can[vbcol=seagreen]
space? Thanks in advance[vbcol=seagreen]
>
>.
>|||In message <636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com>, Ron B.
<RonB@.discussions.microsoft.com> writes
>I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
>leaves just enough room on the partition for the nightly backup files, but
I
>keep getting a msg saying that the drive is almost full. I cannot resize t
he
>partition, and shrinking the db does not change the allocation size. How c
an
>I reduce the allocation size and free up some disk space? Thanks in advanc
e
>for any suggestions.
Using Enterprise Manager and Query Analyser:
1) EM - Backup the database and log file in question.
2) QA - Use DBCC DBREINDEX on each table in your database.
(May be better using a cursor in sproc - saves typing).
3) EM - Backup the database and log file again.
4) EM - Use tools 'Truncate Transaction Log' if log file too big.
5) EM - Use tools 'Shrink Database'.
Obviously, the backup at step 1 should be kept for good measure (just in
case) however the backup at step 3 can be deleted.
In SQL Server 7, the Shrink Database does not work very well, if at all,
until a backup has been performed. The re-indexing is required to reduce
fragmentation before a shrink is tried. I have found from time to time
that the above process needs to be run twice to get the actual physical
files to shrink any, specially if it has not been done for a very long
time.
Kind Regards,
--
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.comsql
leaves just enough room on the partition for the nightly backup files, but I
keep getting a msg saying that the drive is almost full. I cannot resize th
e
partition, and shrinking the db does not change the allocation size. How ca
n
I reduce the allocation size and free up some disk space? Thanks in advance
for any suggestions.Back it up
remove it
recreate it, smaller
restore it
Jan
"Ron B." <RonB@.discussions.microsoft.com> wrote in message
news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
> I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
> leaves just enough room on the partition for the nightly backup files, but
I
> keep getting a msg saying that the drive is almost full. I cannot resize
the
> partition, and shrinking the db does not change the allocation size. How
can
> I reduce the allocation size and free up some disk space? Thanks in
advance
> for any suggestions.|||That will not do anything, as restore will create database files with same s
ize as when you took the
backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jan Doggen" <j.doggen@.BLOCKqsa.nl> wrote in message news:%2386SdCEuEHA.2072@.tk2msftngp13.ph
x.gbl...
> Back it up
> remove it
> recreate it, smaller
> restore it
> Jan
> "Ron B." <RonB@.discussions.microsoft.com> wrote in message
> news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
> I
> the
> can
> advance
>|||One thing to try is to rebuild your indexes. Read about DBCC DBREINDEX. And
then try a shrink again.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ron B." <RonB@.discussions.microsoft.com> wrote in message
news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
>I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
> leaves just enough room on the partition for the nightly backup files, but
I
> keep getting a msg saying that the drive is almost full. I cannot resize
the
> partition, and shrinking the db does not change the allocation size. How
can
> I reduce the allocation size and free up some disk space? Thanks in advan
ce
> for any suggestions.|||You can try to use DTS copy the whole database to
another. detach two DBs and reattach the new DB with old
DB name.
Good luck
Mike
>--Original Message--
>One thing to try is to rebuild your indexes. Read about
DBCC DBREINDEX. And then try a shrink again.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Ron B." <RonB@.discussions.microsoft.com> wrote in
message
>news:636A452B-109A-439C-8DA4-
B1401C97E44C@.microsoft.com...
2.9 GB used. This[vbcol=seagreen]
nightly backup files, but I[vbcol=seagreen]
full. I cannot resize the[vbcol=seagreen]
allocation size. How can[vbcol=seagreen]
space? Thanks in advance[vbcol=seagreen]
>
>.
>|||In message <636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com>, Ron B.
<RonB@.discussions.microsoft.com> writes
>I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
>leaves just enough room on the partition for the nightly backup files, but
I
>keep getting a msg saying that the drive is almost full. I cannot resize t
he
>partition, and shrinking the db does not change the allocation size. How c
an
>I reduce the allocation size and free up some disk space? Thanks in advanc
e
>for any suggestions.
Using Enterprise Manager and Query Analyser:
1) EM - Backup the database and log file in question.
2) QA - Use DBCC DBREINDEX on each table in your database.
(May be better using a cursor in sproc - saves typing).
3) EM - Backup the database and log file again.
4) EM - Use tools 'Truncate Transaction Log' if log file too big.
5) EM - Use tools 'Shrink Database'.
Obviously, the backup at step 1 should be kept for good measure (just in
case) however the backup at step 3 can be deleted.
In SQL Server 7, the Shrink Database does not work very well, if at all,
until a backup has been performed. The re-indexing is required to reduce
fragmentation before a shrink is tried. I have found from time to time
that the above process needs to be run twice to get the actual physical
files to shrink any, specially if it has not been done for a very long
time.
Kind Regards,
--
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.comsql
Resize DB in SQL 7
I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
leaves just enough room on the partition for the nightly backup files, but I
keep getting a msg saying that the drive is almost full. I cannot resize the
partition, and shrinking the db does not change the allocation size. How can
I reduce the allocation size and free up some disk space? Thanks in advance
for any suggestions.Back it up
remove it
recreate it, smaller
restore it
Jan
"Ron B." <RonB@.discussions.microsoft.com> wrote in message
news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
> I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
> leaves just enough room on the partition for the nightly backup files, but
I
> keep getting a msg saying that the drive is almost full. I cannot resize
the
> partition, and shrinking the db does not change the allocation size. How
can
> I reduce the allocation size and free up some disk space? Thanks in
advance
> for any suggestions.|||That will not do anything, as restore will create database files with same size as when you took the
backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jan Doggen" <j.doggen@.BLOCKqsa.nl> wrote in message news:%2386SdCEuEHA.2072@.tk2msftngp13.phx.gbl...
> Back it up
> remove it
> recreate it, smaller
> restore it
> Jan
> "Ron B." <RonB@.discussions.microsoft.com> wrote in message
> news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
>> I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
>> leaves just enough room on the partition for the nightly backup files, but
> I
>> keep getting a msg saying that the drive is almost full. I cannot resize
> the
>> partition, and shrinking the db does not change the allocation size. How
> can
>> I reduce the allocation size and free up some disk space? Thanks in
> advance
>> for any suggestions.
>|||One thing to try is to rebuild your indexes. Read about DBCC DBREINDEX. And then try a shrink again.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ron B." <RonB@.discussions.microsoft.com> wrote in message
news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
>I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
> leaves just enough room on the partition for the nightly backup files, but I
> keep getting a msg saying that the drive is almost full. I cannot resize the
> partition, and shrinking the db does not change the allocation size. How can
> I reduce the allocation size and free up some disk space? Thanks in advance
> for any suggestions.|||You can try to use DTS copy the whole database to
another. detach two DBs and reattach the new DB with old
DB name.
Good luck
Mike
>--Original Message--
>One thing to try is to rebuild your indexes. Read about
DBCC DBREINDEX. And then try a shrink again.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Ron B." <RonB@.discussions.microsoft.com> wrote in
message
>news:636A452B-109A-439C-8DA4-
B1401C97E44C@.microsoft.com...
>>I have a db with an allocated size of 24.3 GB, but only
2.9 GB used. This
>> leaves just enough room on the partition for the
nightly backup files, but I
>> keep getting a msg saying that the drive is almost
full. I cannot resize the
>> partition, and shrinking the db does not change the
allocation size. How can
>> I reduce the allocation size and free up some disk
space? Thanks in advance
>> for any suggestions.
>
>.
>|||In message <636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com>, Ron B.
<RonB@.discussions.microsoft.com> writes
>I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
>leaves just enough room on the partition for the nightly backup files, but I
>keep getting a msg saying that the drive is almost full. I cannot resize the
>partition, and shrinking the db does not change the allocation size. How can
>I reduce the allocation size and free up some disk space? Thanks in advance
>for any suggestions.
Using Enterprise Manager and Query Analyser:
1) EM - Backup the database and log file in question.
2) QA - Use DBCC DBREINDEX on each table in your database.
(May be better using a cursor in sproc - saves typing).
3) EM - Backup the database and log file again.
4) EM - Use tools 'Truncate Transaction Log' if log file too big.
5) EM - Use tools 'Shrink Database'.
Obviously, the backup at step 1 should be kept for good measure (just in
case) however the backup at step 3 can be deleted.
In SQL Server 7, the Shrink Database does not work very well, if at all,
until a backup has been performed. The re-indexing is required to reduce
fragmentation before a shrink is tried. I have found from time to time
that the above process needs to be run twice to get the actual physical
files to shrink any, specially if it has not been done for a very long
time.
Kind Regards,
--
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
leaves just enough room on the partition for the nightly backup files, but I
keep getting a msg saying that the drive is almost full. I cannot resize the
partition, and shrinking the db does not change the allocation size. How can
I reduce the allocation size and free up some disk space? Thanks in advance
for any suggestions.Back it up
remove it
recreate it, smaller
restore it
Jan
"Ron B." <RonB@.discussions.microsoft.com> wrote in message
news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
> I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
> leaves just enough room on the partition for the nightly backup files, but
I
> keep getting a msg saying that the drive is almost full. I cannot resize
the
> partition, and shrinking the db does not change the allocation size. How
can
> I reduce the allocation size and free up some disk space? Thanks in
advance
> for any suggestions.|||That will not do anything, as restore will create database files with same size as when you took the
backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jan Doggen" <j.doggen@.BLOCKqsa.nl> wrote in message news:%2386SdCEuEHA.2072@.tk2msftngp13.phx.gbl...
> Back it up
> remove it
> recreate it, smaller
> restore it
> Jan
> "Ron B." <RonB@.discussions.microsoft.com> wrote in message
> news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
>> I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
>> leaves just enough room on the partition for the nightly backup files, but
> I
>> keep getting a msg saying that the drive is almost full. I cannot resize
> the
>> partition, and shrinking the db does not change the allocation size. How
> can
>> I reduce the allocation size and free up some disk space? Thanks in
> advance
>> for any suggestions.
>|||One thing to try is to rebuild your indexes. Read about DBCC DBREINDEX. And then try a shrink again.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ron B." <RonB@.discussions.microsoft.com> wrote in message
news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
>I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
> leaves just enough room on the partition for the nightly backup files, but I
> keep getting a msg saying that the drive is almost full. I cannot resize the
> partition, and shrinking the db does not change the allocation size. How can
> I reduce the allocation size and free up some disk space? Thanks in advance
> for any suggestions.|||You can try to use DTS copy the whole database to
another. detach two DBs and reattach the new DB with old
DB name.
Good luck
Mike
>--Original Message--
>One thing to try is to rebuild your indexes. Read about
DBCC DBREINDEX. And then try a shrink again.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Ron B." <RonB@.discussions.microsoft.com> wrote in
message
>news:636A452B-109A-439C-8DA4-
B1401C97E44C@.microsoft.com...
>>I have a db with an allocated size of 24.3 GB, but only
2.9 GB used. This
>> leaves just enough room on the partition for the
nightly backup files, but I
>> keep getting a msg saying that the drive is almost
full. I cannot resize the
>> partition, and shrinking the db does not change the
allocation size. How can
>> I reduce the allocation size and free up some disk
space? Thanks in advance
>> for any suggestions.
>
>.
>|||In message <636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com>, Ron B.
<RonB@.discussions.microsoft.com> writes
>I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
>leaves just enough room on the partition for the nightly backup files, but I
>keep getting a msg saying that the drive is almost full. I cannot resize the
>partition, and shrinking the db does not change the allocation size. How can
>I reduce the allocation size and free up some disk space? Thanks in advance
>for any suggestions.
Using Enterprise Manager and Query Analyser:
1) EM - Backup the database and log file in question.
2) QA - Use DBCC DBREINDEX on each table in your database.
(May be better using a cursor in sproc - saves typing).
3) EM - Backup the database and log file again.
4) EM - Use tools 'Truncate Transaction Log' if log file too big.
5) EM - Use tools 'Shrink Database'.
Obviously, the backup at step 1 should be kept for good measure (just in
case) however the backup at step 3 can be deleted.
In SQL Server 7, the Shrink Database does not work very well, if at all,
until a backup has been performed. The re-indexing is required to reduce
fragmentation before a shrink is tried. I have found from time to time
that the above process needs to be run twice to get the actual physical
files to shrink any, specially if it has not been done for a very long
time.
Kind Regards,
--
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
Monday, March 26, 2012
reset the size of temp db
Hi All,
the size of my temp db has increased drastically. I would like to reset it
to the original size.
I tried restarting the sql server but it is not reset.
How can i reset it to the default size (8 mb for the data and .5 for the log
files)
Rgds,
Anu
> How can i reset it to the default size (8 mb for the data and .5 for the log
> files)
Use the ALTER DATABASE command (documented in Books Online).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:92FC242B-82B4-4FF8-BDCF-2B56252B6660@.microsoft.com...
> Hi All,
> the size of my temp db has increased drastically. I would like to reset it
> to the original size.
> I tried restarting the sql server but it is not reset.
> How can i reset it to the default size (8 mb for the data and .5 for the log
> files)
> Rgds,
> Anu
|||Hi
use tempdb
dbcc shrinkfile('Tempdev',8,truncateonly)
"Anuradha" wrote:
> Hi All,
> the size of my temp db has increased drastically. I would like to reset it
> to the original size.
> I tried restarting the sql server but it is not reset.
> How can i reset it to the default size (8 mb for the data and .5 for the log
> files)
> Rgds,
> Anu
sql
the size of my temp db has increased drastically. I would like to reset it
to the original size.
I tried restarting the sql server but it is not reset.
How can i reset it to the default size (8 mb for the data and .5 for the log
files)
Rgds,
Anu
> How can i reset it to the default size (8 mb for the data and .5 for the log
> files)
Use the ALTER DATABASE command (documented in Books Online).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:92FC242B-82B4-4FF8-BDCF-2B56252B6660@.microsoft.com...
> Hi All,
> the size of my temp db has increased drastically. I would like to reset it
> to the original size.
> I tried restarting the sql server but it is not reset.
> How can i reset it to the default size (8 mb for the data and .5 for the log
> files)
> Rgds,
> Anu
|||Hi
use tempdb
dbcc shrinkfile('Tempdev',8,truncateonly)
"Anuradha" wrote:
> Hi All,
> the size of my temp db has increased drastically. I would like to reset it
> to the original size.
> I tried restarting the sql server but it is not reset.
> How can i reset it to the default size (8 mb for the data and .5 for the log
> files)
> Rgds,
> Anu
sql
reset the size of temp db
Hi All,
the size of my temp db has increased drastically. I would like to reset it
to the original size.
I tried restarting the sql server but it is not reset.
How can i reset it to the default size (8 mb for the data and .5 for the log
files)
Rgds,
Anu> How can i reset it to the default size (8 mb for the data and .5 for the logn">
> files)
Use the ALTER DATABASE command (documented in Books Online).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:92FC242B-82B4-4FF8-BDCF-2B56252B6660@.microsoft.com...
> Hi All,
> the size of my temp db has increased drastically. I would like to reset it
> to the original size.
> I tried restarting the sql server but it is not reset.
> How can i reset it to the default size (8 mb for the data and .5 for the l
og
> files)
> Rgds,
> Anu|||Hi
use tempdb
dbcc shrinkfile('Tempdev',8,truncateonly)
"Anuradha" wrote:
> Hi All,
> the size of my temp db has increased drastically. I would like to reset it
> to the original size.
> I tried restarting the sql server but it is not reset.
> How can i reset it to the default size (8 mb for the data and .5 for the l
og
> files)
> Rgds,
> Anu
the size of my temp db has increased drastically. I would like to reset it
to the original size.
I tried restarting the sql server but it is not reset.
How can i reset it to the default size (8 mb for the data and .5 for the log
files)
Rgds,
Anu> How can i reset it to the default size (8 mb for the data and .5 for the logn">
> files)
Use the ALTER DATABASE command (documented in Books Online).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:92FC242B-82B4-4FF8-BDCF-2B56252B6660@.microsoft.com...
> Hi All,
> the size of my temp db has increased drastically. I would like to reset it
> to the original size.
> I tried restarting the sql server but it is not reset.
> How can i reset it to the default size (8 mb for the data and .5 for the l
og
> files)
> Rgds,
> Anu|||Hi
use tempdb
dbcc shrinkfile('Tempdev',8,truncateonly)
"Anuradha" wrote:
> Hi All,
> the size of my temp db has increased drastically. I would like to reset it
> to the original size.
> I tried restarting the sql server but it is not reset.
> How can i reset it to the default size (8 mb for the data and .5 for the l
og
> files)
> Rgds,
> Anu
reset the size of temp db
Hi All,
the size of my temp db has increased drastically. I would like to reset it
to the original size.
I tried restarting the sql server but it is not reset.
How can i reset it to the default size (8 mb for the data and .5 for the log
files)
Rgds,
Anu> How can i reset it to the default size (8 mb for the data and .5 for the log
> files)
Use the ALTER DATABASE command (documented in Books Online).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:92FC242B-82B4-4FF8-BDCF-2B56252B6660@.microsoft.com...
> Hi All,
> the size of my temp db has increased drastically. I would like to reset it
> to the original size.
> I tried restarting the sql server but it is not reset.
> How can i reset it to the default size (8 mb for the data and .5 for the log
> files)
> Rgds,
> Anu|||Hi
use tempdb
dbcc shrinkfile('Tempdev',8,truncateonly)
"Anuradha" wrote:
> Hi All,
> the size of my temp db has increased drastically. I would like to reset it
> to the original size.
> I tried restarting the sql server but it is not reset.
> How can i reset it to the default size (8 mb for the data and .5 for the log
> files)
> Rgds,
> Anu
the size of my temp db has increased drastically. I would like to reset it
to the original size.
I tried restarting the sql server but it is not reset.
How can i reset it to the default size (8 mb for the data and .5 for the log
files)
Rgds,
Anu> How can i reset it to the default size (8 mb for the data and .5 for the log
> files)
Use the ALTER DATABASE command (documented in Books Online).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:92FC242B-82B4-4FF8-BDCF-2B56252B6660@.microsoft.com...
> Hi All,
> the size of my temp db has increased drastically. I would like to reset it
> to the original size.
> I tried restarting the sql server but it is not reset.
> How can i reset it to the default size (8 mb for the data and .5 for the log
> files)
> Rgds,
> Anu|||Hi
use tempdb
dbcc shrinkfile('Tempdev',8,truncateonly)
"Anuradha" wrote:
> Hi All,
> the size of my temp db has increased drastically. I would like to reset it
> to the original size.
> I tried restarting the sql server but it is not reset.
> How can i reset it to the default size (8 mb for the data and .5 for the log
> files)
> Rgds,
> Anu
reset the log file size
hi ,
the log file of a DB has grown in size. i need to now truncate this file and
reset the log file size.
the db has been configured for unrestricted file growth by 10% for the log
file.
how do i do this.
rgds,
anuAnu,
See if the following helps:
http://support.microsoft.com/defaul...kb;en-us;272318
BG, SQL Server MVP
www.SolidQualityLearning.com
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:A838D0B8-C48A-4B1B-822F-E0773EBDCEBB@.microsoft.com...
> hi ,
> the log file of a DB has grown in size. i need to now truncate this file
> and
> reset the log file size.
> the db has been configured for unrestricted file growth by 10% for the log
> file.
> how do i do this.
> rgds,
> anu|||What recovery model is the database in?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:A838D0B8-C48A-4B1B-822F-E0773EBDCEBB@.microsoft.com...
> hi ,
> the log file of a DB has grown in size. i need to now truncate this file a
nd
> reset the log file size.
> the db has been configured for unrestricted file growth by 10% for the log
> file.
> how do i do this.
> rgds,
> anu
the log file of a DB has grown in size. i need to now truncate this file and
reset the log file size.
the db has been configured for unrestricted file growth by 10% for the log
file.
how do i do this.
rgds,
anuAnu,
See if the following helps:
http://support.microsoft.com/defaul...kb;en-us;272318
BG, SQL Server MVP
www.SolidQualityLearning.com
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:A838D0B8-C48A-4B1B-822F-E0773EBDCEBB@.microsoft.com...
> hi ,
> the log file of a DB has grown in size. i need to now truncate this file
> and
> reset the log file size.
> the db has been configured for unrestricted file growth by 10% for the log
> file.
> how do i do this.
> rgds,
> anu|||What recovery model is the database in?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:A838D0B8-C48A-4B1B-822F-E0773EBDCEBB@.microsoft.com...
> hi ,
> the log file of a DB has grown in size. i need to now truncate this file a
nd
> reset the log file size.
> the db has been configured for unrestricted file growth by 10% for the log
> file.
> how do i do this.
> rgds,
> anu
Wednesday, March 21, 2012
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
> -
>
>
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
> -
>
>
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 KBRead 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...
>> -- 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
>>
>
>sql
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 KBRead 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...
>> -- 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
>>
>
>sql
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 KBRead the section in Books Online about physical database architecture. "Rese
rved" 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 sho
w
> 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 un
allocated 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 S
QL
> Server Management Studio, whereas when I checked the actual file size on t
he
> 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 m
e
> 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 i
n
> 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
> -
>
>
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 KBRead the section in Books Online about physical database architecture. "Rese
rved" 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 sho
w
> 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 un
allocated 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 S
QL
> Server Management Studio, whereas when I checked the actual file size on t
he
> 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 m
e
> 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 i
n
> 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
> -
>
>
Tuesday, March 20, 2012
Required Help
Pl tell me this senario
if we create new database in sql 2000 with size 1024 KB now we insert 500 KB data in that database. so what is actual size of database now?
That depends how your database (files) is (are) extended. The default is defined by the model database which is by default 10Percent / unlimited unless you change the model database or the files in the appropiate database.
The next steps for extending the database would be in your case:
1MB
1,1 MB
1,21 MB
1,33 MB
1,46 MB
1,61 MB
(round about)
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Dear Jens,
Pl. tell me in brieaf
Tuesday, February 21, 2012
ReportViewer.LocalReport.Render - PDF filesize over 5 MB
I am testing the .Render method of a local report and can get to export to excel and pdf no problem. My xls file size is under 50 K for a small chart graphic report but every pdf generated tops 5 MB. Is this an issue that an be resolved or a bug in the ReportViewer?
Any solution to this problem? I've got a 10 page report that is less than 1MB in Excel and more than 40MB in PDF!|||This sucks!
I found this post saying that this is a known problem:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=379152&SiteID=1&mode=1
I have a PDF that renders as 25MB. If you open it in acrobat and save a copy of it the copy is saved as 270KB. 95 times smaller!!!
Subscribe to:
Posts (Atom)