SQL 2000 Database growth enormous

2013-06-21  Source: Original Site  Category:Microsoft SQL Server  Views:0 


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!

Related articles
  • SQL 2000 Database growth enormous 2013-06-21

    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

  • Restore SQL 2000 database onto a SQL 7 box 2012-04-05

    Is it possible to restore a SQL 2000 database backup onto a SQL 7 box? I tried and it quite rightly complained about database structure versions are different. Is there a way to backup SQL 2000 in such a way that only SQL 7 features are preserved? an

  • Can I setup SQL 2005 and SQL 2000 database server on the same machine? 2014-08-18

    Can I setup SQL 2005 and SQL 2000 database server on the same machine?

  • How can I compact MS SQL 2000 Database? 2015-02-18

    Now my database is contain a big size and still grow up. I think may I can compact it to small size like ms-access. How can I compact MS SQL 2000 Database?

  • Crystal Report 7 Memo Field Problem with Sql 2000 database? 2013-07-07

    i am using Crystal Report 7 which uses sql 2000 server database. There is field named "Details" in sql database having length 254. in crystal report when i run it. it doesnt show me that field of table and also showing me error " Blob Field or memo f

  • restoring Ms sql 2000 database 2014-08-09

    Greetings! I am not a dba by any means, as you will notice when you read my question. I have "inherited" a clone Windows 2000 server that is running MS SQL 2000 with some ASP scripts and one database. Recently it suffered a massive RAID coronary and

  • Installing Director Server with MS SQL 2000 database 2014-08-17

    Windows 2003 with MS SQL 2000 Previously I had installed Director 5.10.2 with the SQL server it was functioning correctly. I uninstalled 5.10.2 to install 5.10.3. When installing 5.10.3 I reached the database definition and defined my database as bef

  • Transfer PostgreSQL data to a SQL 2000 database 2015-01-04

    I would like to take my database from PostgreSQL and put it into SQL 2000. Is it possible to do this? I am currently running Fedora Core 2 (Tettnang) with PostgreSQL V. 7.4.7 - jmathews

  • SQL 2000 database connection problem 2013-03-09

    I have installed windows 2000 with service pack 4 and SQL 2000 server with service pack 2 on the same machine. Connection the sql server using windows authentication. Able to open SQL enterprise manager and query analyser using windows authentication

  • Syncronizing SQL 2000 databases through USB or Com Port 2012-08-22

    If i have two Windows 2003 Server boxes running SQL Server 2000, is there a way to sync the databases through a USB port or a Serial Com Port? I would preferr some software that keeps those two databases the same at any given point in time. And i wan

  • Connecting to SQL 2000 Database 2014-04-27

    Hi. This is my first post, so please excuse any awkwardness... I have installed PHP 4.3.4 as ISAPI on a WIN2000 box, along with MS SQL Server 2000. I followed the directions for installation from the zip file, making sure the PHP.ini file was edited

  • SQL 2000 Database overwrite 2013-01-09

    I have a SQL DTS job that runs nightly. What this is supposed to do is to copy from Database1 to Database2 and to overwrite anything that was in Database 2 previously. This was set up as an import/export job. The process runs nightly as it's supposed

  • SQL 2000 Databases 2012-01-20

    I have the following databases: Hawk ->the one I setup master msdb model tempdb pubs Can I delete master, msdb, model, tempdb, or pubs? Will this have any affect on my Hawk database?

  • SQL 2000 database table replication 2013-07-08

    I hope someone can assist me with this. I have two databases and i need to replicate (on a regular basis) a couple of tables in one into the other. Any suggestions of the best method of doing this? with

  • Saving Multiple BMP or JPG Files on system not in SQL 2000 database. 2014-05-11

    I am developing an application in which requirement is as per title can anybody help.

  • Migrate MS SQL 2000 to Progress DB 2012-01-17

    all. Newbie here with what may be a trivial question. How do I migrate an MS SQL 2000 Database (schema and data) to a Progress Database (10.1a)? We do not have DataServer so connecting to MS SQL via schema holder in Progress is not doable. Please, an

  • Paradox 8, Windows Server 2003, and MS SQL 2000 2012-07-04

    We have a vendor that is using Paradox 8 to access a MS SQL 2000 database on a server that we just migrated to Windows Server 2003 (from Wiindows 2000 Server). We have started to get the following error periodically on the local workstation while usi

  • Internation characters from MX6.1/MS SQL 2000/MS Server 2003 2012-09-28

    We have just upgraded from CF 5 to CF MX6.1. We have japanese characters inserted in the code of our .cfm page. We also have the same characters being called from our MS SQL 2000 database through a ODBC socket connection, and placed in the same page.

  • Log shipping SQL 2000 2012-12-26

    I am trying to setup log shipping for SQL 2000 database. I don't see option to "specify the transaction log share". According to MS Log Shipping instruction, "specify the transaction log share" screen shows up if I have specified that the plan will i

  • IDENTITY column in SQL 2000 and linked tables in MS Access 2013-02-08

    Please help We have an application written in MS Access. The tables are linked to a SQL 2000 database. The problem is that sometimes insert a new record in a table freezes and times out after a while without anything has happened. When installing the