MS SQL Server - SQL Server 2000 PE SP4

Author

SQL Server 2000 PE SP4 - huge table growth

dejanm73

2006-03-05, 8:24 pm

I have a problem with SQL Server 2000 Personal Edition SP4.

When processing updates to the database tables quickly start to grow
disproportionately large.
Processing same data on same database on SQL Server 2000 Developer
Edition SP4 or SQL Server 2000 Personal Edition SP3a does not cause the
same problem.

Example:
On SQL Server 2000 Personal Edition SP4 one table returned following
usage statistics:
rows reserved data index_size
unused
1815 33865728 4169728 622592
29073408
percentage of reserved space:
datapct index_sizepct unusedpct
12% 2% 86%
After that my 20GB drive ran out of free space and I could not finish
processing the file.

On SQL Server 2000 Personal Edition SP3a same returned following usage
statistics:
rows reserved data index_size
unused
2540 2490368 507904 720896
1261568
percentage of reserved space:
datapct index_sizepct unusedpct
20% 29% 51%

As you can see on SP3a table with more rows occupies only a portion of
the space that same table on SP4 does. SQL Server 2000 Developer
Edition SP4 behaves the same as SQL Server 2000 Personal Edition SP3a.

The same database was restored in both cases and operating environment
was exactly the same except for the SQL Server Service Pack: it was a
vmware image running Windows XP Pro SP2 with all latest updates. I have
used modified sp_spaceused stored procedure to report the results for
tables with largest discrepancies. Before running the stored procedure
I have updated the statistics on all tables in the database. After this
table growth the database cannot be shrunk and the only way to reclaim
the space is to transfer all data to another database.

Is this a known problem and is there a fix for it?

Tibor Karaszi

2006-03-05, 8:24 pm

Same indexes? How about fragmentation?

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/

"dejanm73" <dejanm73@gmail.com> wrote in message
news:1141477135.823455.120680@t39g2000cwt.googlegroups.com...
>I have a problem with SQL Server 2000 Personal Edition SP4.
>
> When processing updates to the database tables quickly start to grow
> disproportionately large.
> Processing same data on same database on SQL Server 2000 Developer
> Edition SP4 or SQL Server 2000 Personal Edition SP3a does not cause the
> same problem.
>
> Example:
> On SQL Server 2000 Personal Edition SP4 one table returned following
> usage statistics:
> rows reserved data index_size
> unused
> 1815 33865728 4169728 622592
> 29073408
> percentage of reserved space:
> datapct index_sizepct unusedpct
> 12% 2% 86%
> After that my 20GB drive ran out of free space and I could not finish
> processing the file.
>
> On SQL Server 2000 Personal Edition SP3a same returned following usage
> statistics:
> rows reserved data index_size
> unused
> 2540 2490368 507904 720896
> 1261568
> percentage of reserved space:
> datapct index_sizepct unusedpct
> 20% 29% 51%
>
> As you can see on SP3a table with more rows occupies only a portion of
> the space that same table on SP4 does. SQL Server 2000 Developer
> Edition SP4 behaves the same as SQL Server 2000 Personal Edition SP3a.
>
> The same database was restored in both cases and operating environment
> was exactly the same except for the SQL Server Service Pack: it was a
> vmware image running Windows XP Pro SP2 with all latest updates. I have
> used modified sp_spaceused stored procedure to report the results for
> tables with largest discrepancies. Before running the stored procedure
> I have updated the statistics on all tables in the database. After this
> table growth the database cannot be shrunk and the only way to reclaim
> the space is to transfer all data to another database.
>
> Is this a known problem and is there a fix for it?
>

dejanm73

2006-03-05, 8:24 pm

Same database was restored in all cases.

Table in question has no primary key but it has a compound non
clustered unique constraint and 6 non unique indexes.

Same file was used to generate the updates in all cases. Updates are
affecting multiple tables and while they were being processed there
were no other user activities on the database.

To minimize impact of other environmental factors the system was inside
a vmware image.
I have even used the same vmware image and uninstalled SQL Server 2000
Personal Edition and reinstalled it with SP3 and SP4 two times and both
times SP3 did not have a problem and SP4 did.

I don't have the fragmentation values for tables in the database but I
would think this is irrelevant. When all other factors are the same SP3
and SP4 should fragment the tables in similar way so the end result
should not be a difference in database size of almost 20GB. SP3
generated the database that is 540MB and SP4 could not finish
processing all updates because the database grew so large that 20GB
drive ran out of sapace and I had to stop the process. This problem
exists on several real systems and on those the only way to recover the
unused space in each table and shrink the database is to transfer all
data to new database (I have tried updating statistics, defragrmenting
the indexes, rebuilding the indexes, recreating the indexes, shrinking
the database and shrinking the data file).

I don't really care why this is happening, it is obviously a bug
introduced in SP4. I just want to know if this is a know problem with
SP4 and has there been a fix for it. I did find posts from other people
who have experienced the problem with lots of unused space in tables
(altough I am not sure if they had the problem on PE version of SQL
Server and Service Pack 4) but no one has been able to say wether
Microsoft has acknowledged this as a problem or to suggest a way to
resolve the problem (such as revert to SP3). I will obviously have to
revert all the affected systems to SP3 if there is not other way to fix
this problem.

Sponsored Links