Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    46

    Unanswered: Troubleshooting: My database has started to grow TOO fast

    The primary database i'm responsible for has started to grow super fast. Every couple of days is growing by 10% (which matches with the db settings). But, the recent growth doesn't match with the historical growth. It took a couple of months to grow from 7 to 8 GB, but it has grown to about 24 Gb in the last 2 months. Bottom line - trust my assertion that it's growing alarming fast.

    I need help determine what objects are fueling the growth. If I know the objects, I can probably determine the cause. From a flip-side, it might be legit data stored very poorly. I'm open to any ideas...but I need to get ahead of this problem in the next week or so...or I'm going to run out of room on the hard drive and could start to affect my users.

    Please send my any ideas you might have.

    Thanks,

    alex8675

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Are you doing regular backups?
    Are you doing regular log dumps?
    What is the largest table? How many records are added to it every day?
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    How many files does your database have, and what are their exact file sizes? I'm trying to figure out data versus log size, and if there are more than one data file if there are enough to cause "padding issues" that can occur at certain database sizes.

    I'm typing this query from memory, but it ought to give you a good head start on what you want to know:
    Code:
    SELECT Cast(Sum(i.reserved) / 128.0 AS DECIMAL(12,2)) AS MB, o.name
       FROM dbo.sysindexes AS i
       JOIN dbo.sysobjects AS o
          ON (o.id = i.id)
       GROUP BY o.name
       ORDER BY Sum(i.reserved) DESC
    -PatP

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    For a quick summary of the files:
    Code:
    select sum(f.size)/128, count(*) as Files, isnull (fg.groupname, 'LOG') as Filegroup
    from sysfiles f left join
    	sysfilegroups fg on f.groupid = fg.groupid
    group by fg.groupname

  5. #5
    Join Date
    Dec 2003
    Posts
    46
    Quote Originally Posted by Pat Phelan
    How many files does your database have, and what are their exact file sizes? I'm trying to figure out data versus log size, and if there are more than one data file if there are enough to cause "padding issues" that can occur at certain database sizes.

    I'm typing this query from memory, but it ought to give you a good head start on what you want to know:
    Code:
    SELECT Cast(Sum(i.reserved) / 128.0 AS DECIMAL(12,2)) AS MB, o.name
       FROM dbo.sysindexes AS i
       JOIN dbo.sysobjects AS o
          ON (o.id = i.id)
       GROUP BY o.name
       ORDER BY Sum(i.reserved) DESC
    -PatP
    Great troubleshooting information...Pat's look at the indexes is the most telling. I've got a table with 9,000 rows that has an index taking up 9Gb. Silver Bullet for sure...now I need to figure out how to clean it up. Any suggestions?

    Just another little FYI...this a database built by one of our vendors. It's has 4 db files, one of which is for indexes. Unfortunately, that file doesn't seem to be growing. Which probably means the indexes aren't really stored there. Can I script the re-building the indexes on the other file and then shrinking the data file?

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    For that table, run this query:
    Code:
    select indid, name, reserved
    from sysindexes
    where id = object_name('name of table that is so big')
    With that size, I would expect the big index will be indid 255 (text/image). You still may be looking for about 7GB in unexplained growth, though.

Posting Permissions

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