Link to home
Start Free TrialLog in
Avatar of Randy_K
Randy_K

asked on

Why would DB size quadruple after upgrade from MSDE 2000 to SQL Server 2005?

The size of the MDF has jumped from 2GB to 8GB over the weekend after the indexes were rebuilt. The database went from 1870MB with 48MB free to 7890MB with 1430MB free and the backups went from 1.9GB to 6.5GB.

We upgraded our MSDE 2000 installation to MS SQL 2005 Trial (no Service Pack) because we hit the 2GB limit. The database is for Deltek Vision. We intend to install SQL Server 2005 Standard before the end of the trial. The install of SQL Server followed the Deltek guidelines except the trial of SQL 2005 did not allow installed feature selection where they recommend not installing Business Intelligence.

MSDE was uninstalled, MSSQL 2005 was installed, and the user MDF/LDF files were attached from MSDE. Since the Deltek password was unknown a new user was added to the DBs using the Deltek Vision Resource Kit and the sa login; the old Deltek user could not be modified because of an error stating that the user was the schema.

Three tables had this error when the DB was first attached:
Warning: A column nullability inconsistency was detected in the metadata of index "RPReimbAllowancePK" (index_id = 2) on object ID 761821826 in database "visiontest". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.

This column nullability inconsistency error has not reoccurred and I cant find specific the table that the error referenced. Another table with the name minus PK at the end exists.

DBCC UPDATEUSAGE and DBCC CHECKDB have been run several times with a few table statistics fixed the first time. The compatibility mode is 2005 and the recovery mode is simple. The DB and Log are set for 10% growth to an unlimited size.

The maintenance plan ran with only a DBCC UPDATEUSAGE error in the maintenance log before I had run DBCC UPDATEUSAGE.

The server seemed to be operating fine on Friday and Monday and only a few login errors show up in the logs. I have had no complaints with the exception that some data did not post when the MSDE DB reached the file size limit.

Is this normal behavior after the indexes are rebuilt or is the DB corrupt? Is there a setting I can change that will bring this DB back down to size?
ASKER CERTIFIED SOLUTION
Avatar of twoboats
twoboats

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Randy_K
Randy_K

ASKER

Yes the Indexes have a very low fill factor when checking but the default was set to a fill factor of 0 or 100.

After looking at the fill factor Deltek recommended installing SP2 and recreating the maintenance plan because of a bug fix for the maintenance plan.
The maintenace plan was set to rebuild indexes with 10% free space but a bug made it 90%+ free.

After the install of SP2, a maintenace plan run, and a shrink the database went to 2069 MB from 9GB.

Thank you for your help.
Excellent. You're welcome.