Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2006
    Posts
    6

    Unanswered: Help with SQL 6.5

    I know this is an old version but the customer cannot upgrade at this time due to a Mac software issue. I am a newbie so forgive me if I ramble. Here is my question.

    Windows Server 2003
    SQL running version 6.5
    Log size =1998 MB
    Log Avail=600 MB
    No maint. plan set up on this, and when I try to create one it warns me about running a maint. plan on a DB that is larger than 400 MB. When I try and trucate logs in EM seems like it runs but the size stays the same. The customer restarts the SQL service and users are then able to log in.

    THe database used to run on a NT 4.0 box up till 6 months ago when it was moved to the 2003 box. It ran fine up till last week. The customer tells me that people have been getting errors logging in. In the event viewer the following error reports.

    Event Type: Error
    Event Source: MSSQLServer
    Event Category: (2)
    Event ID: 17060
    Date: 8/4/2006
    Time: 7:51:51 AM
    User: N/A
    Computer: AUX-SERVER
    Description:
    The description for Event ID ( 17060 ) in Source ( MSSQLServer ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Error : 701, Severity: 17, State: 2, There is insufficient system memory to run this query..
    Data:
    0000: bd 02 00 00 11 00 00 00 .......
    0008: 00 00 00 00 07 00 00 00 ........
    0010: 6d 61 73 74 65 72 00 master.
    Any ideas? Thanks!!

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    What are the errors that the users are getting?

    And the all important question, what changed, and who changed it?

  3. #3
    Join Date
    Aug 2006
    Posts
    6

    Error

    There is insufficient system memory to run this query..I am not sure what changed...

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Is this happening every day, or does it take a few days to "build up"? Also, when it does happen, does everyone get the error message, or do a few people manage to get in, while others are locked out?

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ok, there are a few different ways to solve this problem.

    The underlying problem has to do with how SQL 6.5 allocates memory. There are issues with the way SQL 6.5 works in Windows 2000 and later releases.

    The easy solution is to buy a copy of Microsoft Virtual Server, install that onto the box you're using to run SQL 6.5, then create a virtual machine and install Windows NT 4.0 in that virtual machine. At this point, you've got the problem contained and can manage it easily and effectively.

    A much more difficult solution (but requiring no additional software or licenses) is to simply work to configure the SQL 6.5 instance so that it uses a fixed amount of memory, then adjust the XP settings in the registry so that they don't strangle themselves when they hit those limits. This isn't usually hard, but it is rather complex and it requires someone that really knows SQL 6.5 and its memory usage... It is not a job for someone without a lot of experience.

    There are a number of other possible solutions, but they all have associated risks. You'll have to decide which one suits your needs best if you decide to head down any of these paths.

    -PatP

  6. #6
    Join Date
    Aug 2006
    Posts
    6
    To answer Mcrowley...it happens every cpl days...all are not able to log in....

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oh yeah, one relatively simple way to solve this problem if you can afford daily reboots is to reboot the machine every day. This works around the memory allocation problem by not allowing the machine to reach the threshold where it can't effectively allocate memory anymore.

    If you can afford the daily reboots, then the simple answer is to just schedule a script to restart the server.

    -PatP

  8. #8
    Join Date
    Aug 2006
    Posts
    6

    well....

    the machine has been running for a few months, configured the same way, with no problems...why now did it start acting up? Took that long to build up? Total server memory is 1 gig. SQL Server is set up with 32768 (2K blocks) of memory. Like I said I am now well versed in SQL then alone version 6.5!

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The underlying problem depends on the number of occurances of certain behaviors. In other words the problem occurs after the ill-behaved code executes a certain number of times... That number depends on the hardware configuration, device drivers, services, etc.

    You've probably just reached the point where the threshold is now low enough to become a "pain point" while it hadn't been one before. This could be because of hardware changes, patches, or even network changes that forced loading additional software/drivers that were configured but not used in the past.

    -PatP

  10. #10
    Join Date
    Aug 2006
    Posts
    6
    Thanks Pat....would setting up a maintenance plan help for this database? When I try to set one up it warns against setting one up on database's larger than 400mb.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Setting up a maintenance plan might or might not help with database performance, but it won't do diddly for helping with memory problems. If my analysis of what's causing the machine (SQL Server anyway) to become non-responsive is correct, then a maintenance plan won't make any difference.

    SQL 6.5 is a much simpler creature than its successors. The maintenance plans were not too effective, and it was EASY to code a script that did a much better job, especially for databases over about 300 Mb or so. You could ensure basic database health with just two commands DBCC CHECKDB, and DBCC CHECKALLOC, but you still needed to keep an eye on the database on a regular basis to "keep the wheels on the bus"

    -PatP

Posting Permissions

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