I have a database that is growing almost exponentially in size. It is on SQL Server 2000, and is limited to this particular database. We have thousands of databases and have never seen this behavior. Growth is set to 10%. Here's some details:
I have an app that is taking several 5-6 MB text files and using INSERT statements to put the data into the database. This app has been used for years without issue on other databases (100% verified it's not the app). By about the 7th file, my database size went from 4 GB to 65 GB! I confirmed it's not the app because normal day to day use increases the database size enourmously as well - it was up to 10 gigs from 3 months of use, where another database (different site, but same database/app using it) is usually under 1 GB.
Here is what I did to try and fix it:
- Run a script that determines the table name, rows, reserved KB, data KB, index_size KB, unused KB.
- Find the tables with the most unused KB. If it's a table without view dependency, foriegn key constraints, etc, I do a SELECT INTO another table.
- Then I DROP the bad table and recreate it. Then do an INSERT INTO back into the new table. Everything is fine then - unused KB is minimal for that table.
- Then I proceed with the shrink database, which will bring it from 65 GB to 45 GB. The next shrink will bring it from 45 GB to 35 GB. The next from 35 to 28. Each time I shrink, it cuts it, but the cut gets smaller and smaller.
- When I run the query to see the usused KB, all of the tables' unused KB get smaller. However, some tables stay quite high (5-10 gigs).
- I have tried running the DBREINDEX or INDEXFRAG commands on all tables, and am able to shrink the file a little more, but only in small increments at a time.
- If I were to not perform a select into, and not run any index rebuilding or defragging, the database size shrinks to a fixed amount and that's it. I would only be able to shrink it from 65 to 58 gigs solely because of the 10% growth attribute.
I also performed the following test:
- Make a copy of the production database. Restore it into a test database. Shrink the database as best as I can with the methods above. Run the text importing application tool, and 265 MB of text files makes a 3 gig DB become ~3.8 gigs. That is what is supposed to happen, but is not happening in our production environment. I even got all users off the system, backed up production, deleted the database, created a new one, and restored the backup. Yet, the same problem keeps coming up.
I'm out of ideas on what to do!