Post reply

TempDB

  • July 2, 2003 at 5:40 am

    #64833

    Hi Guys,

    I am an SQL DBA wanna be and find this site great.

    I have a question. I recently had the TempDB log file fill up. This had a knock on effect of locking SPIDS and generally causing our Billing application to misbehave.

    I had to Stop the service and then start with minimum configuration at a command prompt. Then ALTER the DATABASE to reset the size.

    I was wondering what could have caused this filling when the database is set to grow automatically and there is plenty of disk space?

    Thanks

  • July 2, 2003 at 6:08 am

    #463903

    What version/edition of SQL are u using

  • July 2, 2003 at 7:52 am

    #463923

    I'm using SQL 7 Standard Edition

  • July 2, 2003 at 9:55 am

    #463944

    TEMPDB can fill up and lock users/queries if you don't have it set to automatically grow enough. Notice I said 'enough'. The growth size has to be enough that it can grow faster than the space is filled up. For example, lets say you set it to auto grow by 100 MB. You get a bunch of transactions in a very short period of time and the transactions equal 150 MB of data. If the database can't auto grow fast enough (in this case twice), it will lock up.

    How do you have it set to grow? By percentage or by a set size? How much is it set to grow by each time?

    -SQLBill

  • July 2, 2003 at 10:07 am

    #463950

    Setup an alert, prefereable page, to let you know when it gets XX % full. Probably 70%, but if it seems to fill quickly, then you might want a lower percentage.

    Steve Jones

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

    http://www.dkranch.net

  • July 3, 2003 at 1:23 am

    #464011

    Thanks for the info guys.

    I've had the it set to grow automatically by 10%....should this be higher??

    I'll also put in an alert.

    Thanks again!

  • July 3, 2003 at 7:54 am

    #464052

    If your TEMPDB log is filling up, then yes you need to increase the amount it grows by.

    However, you might want to consider setting it to grow by MB instead of percentage.

    Grow by MB means it's growing by the same amount each time.

    Grow by % means it's going to grow by ever increasing amounts.

    For example: Grow by 10%.

    The log is currently 100 MB. The first time it grows, it will grow by 10MB. Now it's 110 MB. Next time it'll grow by 11MB. Now it's 121MB. Then it grows by 12.1MB. Eventually, you log could be up to 1000MB and grow by 100MB.

    -SQLBill

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

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