different file size

Hi
I find strange thing. I have (MSSQL2000) big database (about 90 GB). In this
database I have to files with about 10.000.000 and 30.000.000 records. I was
copy this tables to other serwer, to new created database. After it I create
all of indexes, constraint ect on this tables in new database. On views with
this tables there are no indexes. The size of new database is about 10GB.
After it I shrink, backup with truncate log and shring again source
database. It was shrink to 55GB (over 25GB!!!). After I return with this two
tables and indexes to source database file size grow to 90GB!!!
WHY?
MArek
--
www.programowanieobiektowe.plIt sounds like you moved two tables out of one database into an empty
database.
This new (empty) database grew to 10GB with the new tables.
You removed the two tables from your existing database and shrank the
database. Lots of disk space was returned to the OS.
Then you added the tables back to the original database causing it to grow.
You expected the database to grow around 10GB but it grew by much more.
How did you move the data?
Check the log file? Did it grow when you added the tables to the original
database?
I am guessing that auto growth caused your data and log files to grow a
little more than necessary. This is ok, as it leaves "room" in your
database for new data as it comes in.
You could try using the stored procedure sp_spaceused within your database
to take a look at the space consumed by data and indexes as well as the
amount of unused space within your database.
--
Keith Kratochvil
"Marek Wierzbicki" <marek.wierzbickiiiii@.azymuttttt.pl> wrote in message
news:edmp7m$8gq$1@.news2.ipartners.pl...
> Hi
> I find strange thing. I have (MSSQL2000) big database (about 90 GB). In
> this database I have to files with about 10.000.000 and 30.000.000
> records. I was copy this tables to other serwer, to new created database.
> After it I create all of indexes, constraint ect on this tables in new
> database. On views with this tables there are no indexes. The size of new
> database is about 10GB. After it I shrink, backup with truncate log and
> shring again source database. It was shrink to 55GB (over 25GB!!!). After
> I return with this two tables and indexes to source database file size
> grow to 90GB!!!
> WHY?
> MArek
>
> --
> www.programowanieobiektowe.pl|||Be sure to run DBCC UPDATEUSAGE(0) before checking the space consumed
inside a database. Without that the numbers are a bit unreliable.
One issue to consider is that creating a clustered index requires
creating a complete copy of the table. Suppose a database had one
table, size 10GB, and the database was 10GB with no free space. To
add (or re-create) a clustered index on that table requires making a
second copy of the data in the database, so when you are done you have
a 10GB table in a 20GB database.
Roy
On Wed, 6 Sep 2006 17:24:37 +0200, "Marek Wierzbicki"
<marek.wierzbickiiiii@.azymuttttt.pl> wrote:
>Hi
>I find strange thing. I have (MSSQL2000) big database (about 90 GB). In this
>database I have to files with about 10.000.000 and 30.000.000 records. I was
>copy this tables to other serwer, to new created database. After it I create
>all of indexes, constraint ect on this tables in new database. On views with
>this tables there are no indexes. The size of new database is about 10GB.
>After it I shrink, backup with truncate log and shring again source
>database. It was shrink to 55GB (over 25GB!!!). After I return with this two
>tables and indexes to source database file size grow to 90GB!!!
>WHY?
>MArek
>
>--
>www.programowanieobiektowe.pl|||> It sounds like you moved two tables out of one
> database into an empty database.
> This new (empty) database grew to 10GB with the new tables.
> You removed the two tables from your existing
> database and shrank the database. Lots of disk space was returned to the
> OS.
> Then you added the tables back to the original
> database causing it to grow.
> You expected the database to grow around 10GB
> but it grew by much more.
> How did you move the data?
select * into new_table from linkedserver..oldtable
next
create all indexes from script
> Check the log file? Did it grow when you
> added the tables to the original database?
Yes, but its in simple mode, so it have only few MB used
> I am guessing that auto growth caused your data
> and log files to grow a little more than necessary. This is ok, as it
> leaves "room" in your database for new data as it comes in.
You are right - it was auto grow with 35%!!!!!!
But why they cant be shrinked?
> You could try using the stored procedure sp_spaceused within your database
> to take a look at the space consumed by data and indexes as well as the
> amount of unused space within your database.
I will check
Marek|||> Be sure to run DBCC UPDATEUSAGE(0) before checking
> the space consumed
> inside a database. Without that the numbers are
> a bit unreliable.
I will try dio it again and check it
> One issue to consider is that creating a clustered index requires
> creating a complete copy of the table. Suppose a database had one
> table, size 10GB, and the database was 10GB with no free space. To
> add (or re-create) a clustered index on that table requires making a
> second copy of the data in the database, so when you are done you have
> a 10GB table in a 20GB database.
index was created after copy and size didnt grow
I will try it again and describe result
Marek|||Marek Wierzbicki wrote:
>> It sounds like you moved two tables out of one
>> database into an empty database.
>> This new (empty) database grew to 10GB with the new tables.
>> You removed the two tables from your existing
>> database and shrank the database. Lots of disk space was returned to
>> the OS.
>> Then you added the tables back to the original
>> database causing it to grow.
>> You expected the database to grow around 10GB
>> but it grew by much more.
>> How did you move the data?
> select * into new_table from linkedserver..oldtable
> next
> create all indexes from script
>
>> Check the log file? Did it grow when you
>> added the tables to the original database?
> Yes, but its in simple mode, so it have only few MB used
>> I am guessing that auto growth caused your data
>> and log files to grow a little more than necessary. This is ok, as it
>> leaves "room" in your database for new data as it comes in.
> You are right - it was auto grow with 35%!!!!!!
> But why they cant be shrinked?
>> You could try using the stored procedure sp_spaceused within your
>> database to take a look at the space consumed by data and indexes as
>> well as the amount of unused space within your database.
> I will check
> Marek
>
Simple mode will NOT prevent the log file from growing, it will still
grow large enough to hold whatever transaction you're pumping through
it, in this case it was a transaction large enough to hold your entire
table import. See
http://realsqlguy.com/serendipity/archives/14-When-Is-A-Transaction-Log-Not-A-Transaction-Log.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com