469,648 Members | 1,638 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,648 developers. It's quick & easy.

DB size increase from SQL 2000 to 2005

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

Any ideas why such a large increase?
Thanks
Feb 23 '08 #1
4 1763
Artie (ar*******@yahoo.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(object_id), name FROM sys.indexes
WHERE index_id = 0?

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Feb 23 '08 #2
aj
Erland Sommarskog wrote:
Artie (ar*******@yahoo.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(object_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
Feb 25 '08 #3
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" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...
Artie (ar*******@yahoo.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(object_id), name FROM sys.indexes
WHERE index_id = 0?

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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


Feb 25 '08 #4
Artie (ar*******@yahoo.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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Mar 4 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Trevor | last post: by
12 posts views Thread by J L | last post: by
2 posts views Thread by msnews.microsoft.com | last post: by
3 posts views Thread by RamaKrishna Narla | last post: by
2 posts views Thread by Praveen | last post: by
30 posts views Thread by Neil | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.