Post reply

Data file growth

  • May 12, 2010 at 9:48 am

    #221088

    I have a D drive and on this drive we only have all the mdf files. Every night at a certain time the available space on this drive drops dramatically until about 11 PM, then the space available increases again. Seeing that this drive only has data files, it must be that something is written to a database (filling up the drive) and then something is deleted (making space available again).

    Would you agree?

    Plus more importantly how can I determine what is causing this data file growth?

    any help please

  • May 12, 2010 at 10:59 am

    #1166387

    Maybe you should log in to the machine at about 10:45pm and see what file has gotten larger, or if there is some file other than a .MDF file that is present.

    Is there some maintenance plan that may be running at 11PM that might be shrinking databases or something? Any idea WHY the space reappears at 11pm?

    Rob Schripsema
    Propack, Inc.

  • May 13, 2010 at 2:31 am

    #1166700

    There are definitely no other files on that drive besides the data files. I have no idea why this is happening.

    There are not maintenance that would shrink a data file. The only 'shrink' related maintenance plan is the DB backups and the transaction log backups, but they will only shrink the log files, not the data files. Plus that only occurs much later (3 AM).

    I think I will try Perfmon with the data file counters.

    Or maybe a trace with the "Date file growth" event.

  • May 13, 2010 at 4:33 am

    #1166752

    sp_readerrorlog use above query to read the things going on server at that time

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • May 17, 2010 at 2:51 am

    #1168146

    I think something is creating a temporary file on that drive and then it removes the file when it is done.

    Perhaps the backup routine is writing the backups to this drive before moving it away somewhere else ?

    To see what program is creating a temporary file you could use process monitor.

    http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx

    Setup a filter that only captures file create events on this drive and let it run for the night.

    /SG

  • May 17, 2010 at 6:23 am

    #1168218

    what are the sizes of databases?

    how the auto growth option set for each database?

    also

    u can start trace on the server for some time (say 30 min) on server

    and see if u can capture some data.

    Sanket Ahir
    Don't run behind the success, Try to be eligible & success will run behind u......

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

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