asked on

sql 2008 database has suddenly grown huge

We've had a package for our website that uses sql for about 7 years. The database was usually around 25 gb in size. Suddenly it has grown to 90 gb and I can't figure out why or how to fix it. It isn't the log that is huge - its the actual db. I can see the largest file is "portal" but I don't know what that file is. It isn't listed under the table view. We haven't done anything differently with the site over the past few months - in fact we've deleted a lot of old images. I thought about shrinking the db (even though some don't recommend that), but it looks like that would only affect about 6% of the space. I have no clue what to do but need to reduce the size of this db quickly.

SOLUTION

colditzz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cindyfiller

ASKER

Hmm - it did show me that 31 gb was used by the index. I do reindex every week - but that must not affect the size of it. It definitely shows that shrinking would not affect the size much.

colditzz

Thanks for the reply. I am not a dba, but have a little experience with SQL.
Personally, I would check the daily backups to see exactly when the db changed dramatically in size. Then I would attach (preferably to a different server to ensure I don't accidentally affect the current live database) the database from the night before and the night following the size change and then utilise the SP from above to see where the change originated. Apart from this, I'm afraid I can't be much further help regarding this question.

cindyfiller

ASKER

Do appreciate your comments - its pointed me to a different direction and that helps.

colditzz

Thank you and I hope you resolve it successfully.

SOLUTION

rk_india1

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

This command will show you if an immediate shrink has any chance of freeing a lot of space:

DBCC SHOWFILESTATS

If UsedExtents is not significantly below TotalExtents, then shrink at this point won't help.

SOLUTION

David Todd

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cindyfiller

ASKER

I found an email where we had cleaned out images from our web site. that was on Dec 4 and the total db size was 18 gb. When I look at the actual mdf file it now shows 90 gb - quite a huge growth for 5 or 6 weeks. When looking more close, I find the table itself is about 55 gb and the index is about 32 gb. From the tools I've run shrinking it won't get more than a couple of gb.

Scott Pletcher

Is the table a heap or does it have a clustered index?

Sometimes SQL Server gets "confused" with heaps and the storage goes bonkers.

If it is a heap, try adding a clustered index, then see what the new size is.

cindyfiller

ASKER

Honestly I don't know - but will find out! Our vendor installed this software and for the most part I've just ignored it on the back end - haven't had to really do anything with it.

David Todd

Hi,

Referencing the email you mentioned, that means in 6 weeks database has grown. Did anything change in the application, as in the size of the images has doubled or worse?

Given Scotts point above about SQL getting confused - when was the last time you did a dbcc checkdb or database integrity check?

Scott:
Would a dbcc updateusage be appropriate here? It at least couldn't hurt anything right?

Regards
David

cindyfiller

ASKER

No nothing has changed. That's why its so odd. No one has loaded videos to our website or uploaded any major changes. I'm going to check my logs tomorrow and see when I last upgraded - I don't think it was around that time. But I'll also do the suggestions above and see what comes out.

cindyfiller

ASKER

I've run the items suggested and am including the results below. I sure am learning a lot - but still not seeing anything that would be causing the problem.

Sp_spaceused
      Database size:      92108.50 mb
      Unallocated:      7572.66 mb
      Reserved:      86528992 kb
       Data:             55670240 kb
       Index:       29829720 kb
       Unused:      1029032 kb

Dbcc showfilestats
      Portal (mdf) – everything else looks really small
       Total extents      1335584
       Used extents      1235812

DBCC sqlperf(logspace) – 2 show up with larger log sizes
      Model
       Log size      56.30469 mb
       Log space used      92%
      BBNC (the DB that I’m concerned with)
       Log size      34.86719
       Log space used      10%
Note – recovery mode is simple

Dbcc checkdb

Running program listed by David: Backup size is 82 gb

I checked if tables were heap or clustered index and it appears there are both in the database.

Dbcc checkdb – the most important results are:
CHECKDB found 0 allocation errors and 0 consistency errors in database 'BBNC'.
DBCC execution completed.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cindyfiller

ASKER

I'd probably want to check with the vendor that provides us this package before changing their indexes. I don't see why it would matter to them - but always use precaution when changing something a vendor has done.

Scott Pletcher

That makes sense. But be aware: if you have heaps, esp. large ones, SQL can have internal issues keeping up with the correct space used and allocated by those heaps.

cindyfiller

ASKER

The vendor is now working on a script to reduce the size of the database based - they said our error table has grown quite large so they are targeting that initially. I'll close this for now and will try to award points to everyone because all of your input helped to provide information that convinced the vendor there was an issue.