Post reply

Database size growth checklist

  • September 28, 2011 at 8:21 am

    #248482

    Hi All,

    I am seeing a very rapid growth in one of the production database mdf file size. Right now its at 900GB!:w00t:

    I went through many forums here but was not able to find a good check list or steps that a DBA has to go through to determine or root cause the problem.

    Can gurus or people who faced the same problems post some thing on how I can approach this problem.

    Thanks in advance.

  • September 28, 2011 at 9:03 am

    #1387837

    Can be many different reasons.

    IMO try to identify top tables by size first (DB- right click- reports- standard reports), then- what (apps) and why adds data to theses tables.

    This can probably help

  • September 28, 2011 at 9:21 am

    #1387847

    Hi,

    There are many standard reports that will show you where most of the space has accumulated (right click database > Reports > Standard Reports). Disk Usage by Top Tables, for example, orders the largest tables to show how your data's distributed. The Disk Usage report will also show you the data/log file autogrow events... these are quite useful to start with.

    Out of interest, do you use Instant File Initialisation (your SQL Server service account will have perform volume maintenance tasks privilege) enabled.

    Regards, Andrew

  • September 28, 2011 at 9:31 am

    #1387854

    I checked the reports and found the tables that make use of the most disk space. After speaking to application team, they said that those tables are the mostly used and data consuming ones.

    Thanks.

    Not sure what you mean by

    Instant File Initialisation (your SQL Server service account will have perform volume maintenance tasks privilege) enabled

    Any more help/info on this Andrew?

    Also, I would like to do all the possible things/steps I need to do before I confirm to the people that its really the application usage that is causing this issue.

    what all do I have to do?

  • September 28, 2011 at 9:43 am

    #1387869

    Here's a very good explanation of Instant File Initialization:

    http://sqlblog.com/blogs/tibor_karaszi/archive/2009/03/09/do-you-have-instant-file-initialization.aspx

  • October 3, 2011 at 9:32 am

    #1389609

    Thats a really good article. I dint know about this at all.

    Apart from that, what I would like to see if some points that a new DBA/developer needs to go through when DB size problems arise.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply