[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 535
  • Last Modified:

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.
0
espanolanthony
Asked:
espanolanthony
1 Solution
 
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
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
 
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now