Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Location
    Western New York
    Posts
    6

    Unanswered: Database is is too large need help

    We have many installations of our shopping cart database. One specific database is huge, now about 25 GIG, compared to the others that range from 20 to 75 MEG. The server this one resides on has three other instances of the same database that are normal size.

    In a particular table in the large database there are 9700 rows taking 380MB, the same table in a normal db has 162000 rows and takes 6.MB. The tables are identical and the indexes are the same.

    Any ideas out there?

  2. #2
    Join Date
    Nov 2003
    Posts
    167
    Is this 25GB all in the datafile, or do you have a runaway log file? You can always backup up the log with or without truncate_only, but you'll need to do a 'dbcc shrinkfile(<file name>, target size in mb)' to actually trim the size down after the log is truncated.

    My size issues are almost always due to the logfile, but if it's data that's causing your problem, Narayana Vyas Kondreddi wrote a procedure called Show_Huge_Tables which lists all tables by size. This might help you pinpoint your problem.

    Good luck.
    Kit Lemmonds

  3. #3
    Join Date
    Aug 2006
    Posts
    7

    Redesign data model

    Sound to me like its time for a redesign of the data model or an archive routine at least. Nothing lasts for ever.

  4. #4
    Join Date
    Feb 2004
    Location
    Western New York
    Posts
    6
    It's in the datafiles, not the log. There is no apparent reason for a row to be this large. There are many other instances of the exact same db with many more orders that are no where near this size. If I copy a large table to another db with dts the table is very small, 2 meg rather than 380 meg. The only thing I see that is different is that the statistics tables are not copied but those are not supposed to take much room and the normal dbs also have the statistics tables.

  5. #5
    Join Date
    Aug 2006
    Posts
    7
    Vinny

    What DBMS is it? You need to periodically compress database files in some cases.
    For example, if you have a DB file that has lots of deletes as well as updates you sometimes need to reclaim deleted space by running a reclamation process or a compression process. For example, in MS Access there is a compression tool. After running the compression tool the file size decreases. All it does is create a temporary file, inserts all valid records in the orginal DB and then renames them both. Is thera any similar admin tool available in your DBMS? If not could you create a new DB and simply copy the data files from the old one to see if the new one is the same size. It might be a corrupt file.

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by RayPooley
    Vinny

    What DBMS is it? You need to periodically compress database files in some cases.
    For example, if you have a DB file that has lots of deletes as well as updates you sometimes need to reclaim deleted space by running a reclamation process or a compression process. For example, in MS Access there is a compression tool. After running the compression tool the file size decreases. All it does is create a temporary file, inserts all valid records in the orginal DB and then renames them both. Is thera any similar admin tool available in your DBMS? If not could you create a new DB and simply copy the data files from the old one to see if the new one is the same size. It might be a corrupt file.
    ummm, no again.

    First he is in the ms sql server forum, so we can assume this is a MS SQL question. The DBCC SHRINKDATABASE command should only be used in case of emergency because it sucks at properly redistributing data and index pages and can cause a great deal of fragmentation which will kill database performance.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Vinny, run these commands on both the problem database and a "normal" database, and post the results:
    Code:
    exec sp_spaceused
    exec sp_helpdb dbadb
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •