DB size increase from SQL 2000 to 2005

**Erland Sommarskog** · Feb 23 '08, 11:45 AM

Re: DB size increase from SQL 2000 to 2005

Artie (artie2269@yaho o.com) writes:

A few months ago a customer moved from SQL 2000 to SQL 2005. The db was
backed up on SQL 2000 and restored to SQL 2005. The application using
this data works on SQL 2005 but takes no advantage of new features. The
db on SQL 2000 was about 2.9GB, now on SQL 2005, it is 16.5GB. The db
is set to Simple recovery so trx log is only 2mb. The mdf file is
16.5GB, Management Studio shows only 5mb free space. There has not been
a huge increase in transactions.
One of the largest tables has only added 2,000 rows since the move to
SQL 2005. Yet the data and index size has jumped from about 400mb to
3.5 GB. I used the 'BigTables.sql' script found at various SQL sites:
www.databasejournal.com/img/BigTables.sql

There is very little information to work from. Have you checked the tables
for fragmentation? How many rows are there in the table that has grown
so much? Are there text/ntext/image columns in the database? Does this
query return any rows:

SELECT object_name(obj ect_id), name FROM sys.indexes
WHERE index_id = 0?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

aj · Feb 25 '08, 02:45 PM

Re: DB size increase from SQL 2000 to 2005

Erland Sommarskog wrote:

Artie (artie2269@yaho o.com) writes:

>A few months ago a customer moved from SQL 2000 to SQL 2005. The db was
>backed up on SQL 2000 and restored to SQL 2005. The application using
>this data works on SQL 2005 but takes no advantage of new features. The
>db on SQL 2000 was about 2.9GB, now on SQL 2005, it is 16.5GB. The db
>is set to Simple recovery so trx log is only 2mb. The mdf file is
>16.5GB, Management Studio shows only 5mb free space. There has not been
>a huge increase in transactions.
>One of the largest tables has only added 2,000 rows since the move to
>SQL 2005. Yet the data and index size has jumped from about 400mb to
>3.5 GB. I used the 'BigTables.sql' script found at various SQL sites:
>www.databasejournal.com/img/BigTables.sql

>
There is very little information to work from. Have you checked the tables
for fragmentation? How many rows are there in the table that has grown
so much? Are there text/ntext/image columns in the database? Does this
query return any rows:
>
SELECT object_name(obj ect_id), name FROM sys.indexes
WHERE index_id = 0?

What does it mean and what should you do if this does return rows?

TIA

aj

**Artie** · Feb 25 '08, 08:15 PM

Re: DB size increase from SQL 2000 to 2005

The largest table I previously mentioned has increased in rows from 449,000
to 451,000. There are no text/ntext/image columns in the db.

The script returned 9 tables - what does it mean? Orphaned indexes?
This db does have a weekly optimization/reindex job that runs.

"Erland Sommarskog" <esquel@sommars kog.sewrote in message
news:Xns9A4D817 C05151Yazorman@ 127.0.0.1...

Artie (artie2269@yaho o.com) writes:

>A few months ago a customer moved from SQL 2000 to SQL 2005. The db was
>backed up on SQL 2000 and restored to SQL 2005. The application using
>this data works on SQL 2005 but takes no advantage of new features. The
>db on SQL 2000 was about 2.9GB, now on SQL 2005, it is 16.5GB. The db
>is set to Simple recovery so trx log is only 2mb. The mdf file is
>16.5GB, Management Studio shows only 5mb free space. There has not been
>a huge increase in transactions.
>One of the largest tables has only added 2,000 rows since the move to
>SQL 2005. Yet the data and index size has jumped from about 400mb to
>3.5 GB. I used the 'BigTables.sql' script found at various SQL sites:
>www.databasejournal.com/img/BigTables.sql

>
There is very little information to work from. Have you checked the tables
for fragmentation? How many rows are there in the table that has grown
so much? Are there text/ntext/image columns in the database? Does this
query return any rows:
>
SELECT object_name(obj ect_id), name FROM sys.indexes
WHERE index_id = 0?
>
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

**Erland Sommarskog** · Mar 4 '08, 10:45 PM

Re: DB size increase from SQL 2000 to 2005

Artie (artie2269@yaho o.com) writes:

OK, more info:
I see in the SQL 2005 db, under Index properties, Fragmentation, the Page
Fullness is only 10%.
I restored a copy of the SQL 2000 db to 2005 and its Page Fullness is 88%
>
There is a maintenance plan running on the db with a Rebuild Index option,
with the "Change free space per page percentage to " set to 10%
This should fill pages to approx 90% not 10%.

What about installing SP2 of SQL 2005 then? But when you do it, make
sure that you get at least version 9.0.3054. The version at the download
site is 9.0.3042, and has another ugly bug with maintenance plans:
if you set up a job to run DBCC CHECKDB in all databases, it will
run all checks in the same database...

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

DB size increase from SQL 2000 to 2005

DB size increase from SQL 2000 to 2005

Comment

Comment

Comment

Comment