Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2003
    Posts
    18

    Unanswered: SQL out of memory

    Has anyone experienced SQL running out of memory? If so, what kind of memory settings do you recommend.

    My system just seems to hang for about 10 minutes - no error message ANYWHERE - just wondering should I blame lack of memory or thread starvation...?

  2. #2
    Join Date
    Jul 2003
    Posts
    5
    My 2 euro-cents: no updates are possible because transaction log is full ?

  3. #3
    Join Date
    Jul 2003
    Posts
    18
    Originally posted by Picard
    My 2 euro-cents: no updates are possible because transaction log is full ?
    How about if the log was 14 gig and had to grow by 10% - would I see an outage or significant performance degradation while the log grew?

  4. #4
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    probably you'd notice when it was busy. Why would you let your log grow so large? Eventually you will run out of space and you have no option than to backup your log and truncate it but why wait until that time.
    Johan

  5. #5
    Join Date
    Jul 2003
    Posts
    18
    I wouldn't!!! But I don't have any control of their backup policy. I just noticed it today because they've been having problems every couple of days (I suspect when the file grows a bit more)...

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    MWilkes, you are correct. The growing of the file goes a little like this:

    step one: Realize you are out of space
    step two: Grow the file to the size specified (10% of 14 GB is a pile of disk, too)
    step three: Wait until the entire addition to the file has been prepared for use by SQL Server.

    Since this is the log portion that is growing, all transactions waiting to write to the transaction log are queued up waiting for new space in the transaction log.

    To get a sense of how long this takes, create a database 1.4 GB in size. You will probably see an eerie correlation in times.

    As a side note, I usually keep all of my file growths down to 5-50 MB depending on the system and how quickly it grows (but that is just a personal preference).

  7. #7
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Make sure to schedule the backup jobs for Tlog also and if its not required simply adopt SIMPLE RECOVERY MODEL (if its ver 2K) or Trunc.log at chkpt. (ver 7) of SQL Server.

    As of now you can use DBCC SHRINKFILE to reduce the Tlog size.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •