Post reply

Abnormal database growth

  • November 17, 2008 at 10:35 pm

    #72253

    Hi All,

    I have a database (MSSQL2005) which keeps growing. I have set the auto growth to 10% but still the mdf file keeps growing till the disk is full!! I have checked the unused space of each tables, it is about 2gb which is acceptable.

    The total unused space of the mdf file was 143 Gb today. I have shrink the mdf file again but i expect it to grow again by tommorow till the disk is full.

    My question is: If the unused space of the tables is about 2Gb only and the total database mdf size is 165 Gb and the unused space is 143 Gb. How can this be recovered permanently instead of doing database shrink everyday.

    Thanks for your answers.

    Cheers

    A.

  • November 18, 2008 at 12:56 pm

    #900147

    amit (11/17/2008)

    How can this be recovered permanently instead of doing database shrink everyday.

    By firing your current SQL developers and replacing them with qualified ones.

    _____________
    Code for TallyGenerator

  • November 19, 2008 at 10:12 pm

    #900874

    Hi Sergiy,

    That's a nice one... but the company wnt accept that idea.. really unfortunate... 😀

  • November 20, 2008 at 4:06 am

    #900960

    Then you stuck with daily shrinking.

    There are only 2 ways to keep place clean - don't litter there or clean it regularly.

    It appears your developers cannot write code which does not leave a lot of rubbish after itself.

    So, clean after it.

    _____________
    Code for TallyGenerator

  • November 20, 2008 at 4:34 am

    #900971

    I'd suggest scheduling a job every hour to dunp table sizes into a table and then monitor table growth (you could use excel to produce some graphs)

    here's the code i might use

    --create the table to store the data

    create table tsizes(name varchar(255),

    rows int,

    reserved varchar(255),

    data varchar(255),

    index_size varchar(255),

    unused varchar(255),

    dt datetime default getdate() )

    --schedule this every hour in your database that is growing

    exec sp_msforeachtable 'insert into tsizes(name,rows,reserved,data,index_size,unused) exec sp_spaceused ''?'' '

    then find the table that is growing and find out why - perhaps developers are using permanent tables rather than temp tables ???????

    MVDBA

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

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