Re: DB size increase from SQL 2000 to 2005
- From: "Artie" <artie2269@xxxxxxxxx>
- Date: Mon, 25 Feb 2008 12:08:09 -0800
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@xxxxxxxxxxxxx> wrote in message
news:Xns9A4D817C05151Yazorman@xxxxxxxxxxxx
Artie (artie2269@xxxxxxxxx) 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, esquel@xxxxxxxxxxxxx
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
.
- Follow-Ups:
- Re: DB size increase from SQL 2000 to 2005
- From: Erland Sommarskog
- Re: DB size increase from SQL 2000 to 2005
- References:
- DB size increase from SQL 2000 to 2005
- From: Artie
- Re: DB size increase from SQL 2000 to 2005
- From: Erland Sommarskog
- DB size increase from SQL 2000 to 2005
- Prev by Date: Re: help with update
- Next by Date: Re: sql query which has got 3 dynamic parameters
- Previous by thread: Re: DB size increase from SQL 2000 to 2005
- Next by thread: Re: DB size increase from SQL 2000 to 2005
- Index(es):
Relevant Pages
|
