database size increases rapidly

I have a SQL 2005 database and since few days i noticed that the size of the database is increasing rapidly atleast 2-3 GB every day. Its good that we havd good space but i wanted to see how do i see why is that increasing so rapidly. Any scripts or any ideas why and what causes this increase? Please help.
LVL 1
espanolanthonyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MiniDevoCommented:
What does the database hold? Just text entries, pdfs, etc.?
Also, do you have some sort of automated backup for this database?
0
RiteshShahCommented:
can you please tell me whether your data file is getting increased or your log file?
0
espanolanthonyAuthor Commented:
Its that data file that is getting increased, and the database only consist of tables but millions of rows in many tables.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Lee SavidgeCommented:
Go here: http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1309027,00.html

Download the doc and copy and paste out the s-_SOS from there and put the sp in your db and run it. It will tell you the size of each table so you will be able to see what table(s) is/are filling up.

Lee

ps. I didn't write the sp but I have used it a number of time.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lee SavidgeCommented:
I meant sp_SOS not s-SOS :)

Lee
0
espanolanthonyAuthor Commented:
yes Lee that will help. thanks.
0
espanolanthonyAuthor Commented:
so how would you find lets say first 50 biggest tables in the database with their sizes and rows with index sizes also.
0
Lee SavidgeCommented:
I can't remember the output but there is a field that tells you the size in KB of the table. Alter the sp to order the values by size.

Lee
0
espanolanthonyAuthor Commented:
I am not good and altering. can someone provide?
0
Lee SavidgeCommented:
you don't need to alter the code. Run this:

exec sp_SOS @Orderby_By='D'
0
Lee SavidgeCommented:
Sorry, this: exec sp_sos @Orderby='D'

Lee
0
Lee SavidgeCommented:
Valid order by parameters are:

      ''N''  -->  Listing by object name
      ''R''  -->  Listing by number of records
      ''T''  -->  Listing by total size
      ''U''  -->  Listing by used portion (excluding free space)
      ''I''  -->  Listing by index size
      ''D''  -->  Listing by data size
      ''F''  -->  Listing by unused (free) space
      ''Y''  -->  Listing by object type ',  16, 1)
0
espanolanthonyAuthor Commented:
thanks a lot.
0
espanolanthonyAuthor Commented:
thanks a lot
0
Lee SavidgeCommented:
Glad it helped. The sp is fantastic at identifying tables that suddenly fill up. wish I could claim credit for writing it but I can't.

Lee
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.